|
21.Can u rename a column name/ table name? If yes, then will the S.P Work?
Yes you can and the SP will work fine. But if you try to drop and recreate the procedure with same code then it will give error if column name is hard coded.
22.What is roll-forward?
when the server starts it see whether a checkpoint has been fired or not. If the it found the check-point it means data has to write into the physical device, this is so called roll-forward
23.How will you tell the row-size?
o Row size of a table can be estimated for that you have to know the storing structure of Sybase means what space Sybase takes to store fixed length column or variable length column. Ex varchar, char, int, …..
Select
TableName = obj.name,
RowSize =
Case col.status
when 0 then (sum(col.length))
else (sum(col.length)) + 4 –If col contains null value
end
from sysobjects obj,syscolumns col
where obj.id = col.id
and obj.type = ‘U’
–and id = object_id(‘NameOfTheTable’)
group by obj.name,col.status
you can add one more clause for fix and variable length of columns but please do it by you own.
OR
Sp_spaceused table_name get the space used by the table say 2GB.
Select count(*) from table_name say 30000 Rows
Now the size of the column can be :- 2GB/30000 Rows = 70 KB (approx)
24.How will you optimize a query?
- Check the query plan.
- Make sure that the preceding columns of index are available in search criteria.
- Make sure the Statistics are up-to-date: – Run update statistics on the tables in question if the data in these tables is changed drastically.
- Check Search Arguments: – If possible avoid inequality operator. Avoid operations on columns and constant expressions that can not be evaluated at query compile time.
- If possible, use index covering.
- If stored procedure accepts parameters, it is better to recompile it at every execution.
- Check the indexes are on proper columns.
- Describe the tables in from clause so as the smaller table occurs first and then the larger tables ( this statement is not available in Sybase book
25.What is the diff. between a sub-query and a correlated sub-query?
Sub-query: – A sub-queryis a select statement that is nested inside another select, insert, update, or delete statement, inside a conditional statement.
Correlated sub-query: – The sub-query, which depends on the outer query for its values, is called as correlated sub-query.
- The subquery_select_listcan consist of only one column name, except in the ‘exists’ sub-query, where an (*) is usually used in place of the single column name.
- text and image datatypes are not allowed in sub-queries.
- Sub-query cannot include the order by clause, the compute clause,
- There is a limit of 16 nesting levels.
- The maximum number of sub-queries on each side of a union is 16.
26.If I have a main query and a sub-query, which would execute first?
If a sub query is non-correlated then it will be executed first if it is a correlated sub-query then that will be executed in parallel with the main query
27.What is an Index? How many types of indexes are there?
Index is used to provide faster access to the data in a table than scanning every page. Sometimes index can be used as mechanism for enforcing uniqueness.
There are 2 types of indexes: Clustered and Non clustered
28.What is the diff. Between clustered and non-clustered indexes?
- Clustered Index: – Data is physically sorted. The bottom or leaf level of a clustered index contains the actual data pages of the table. So there can be only one clustered index per table.
- Non-Clustered Index: – With a non-clustered index, the physical order of the rows is not the same as their indexed order. The leaf level of a non-clustered index contains pointers to rows on data pages. More precisely, each leaf page contains an indexed value and a pointer to the row with that value. Up to 249 non-clustered indexes are allowed in a table.
29.Why are there only 249 non-clustered Indexes in SQL-Server?
Explain.
Values defined for indid (Index ID) column in sysindexes table are as follows:
0 – table does not have clustered index
1 – clustered index
>1 and <= 250 (i.e. 249) – non clustered indexes
255 – text or image data.
30.What is Index Covering?
When all the keys of non-clustered index are used in select statement and also in where clause then the non-clustered index does not need to go to last level of index it just pick the data from leaf-level so it takes less time to execute the query this is called Index-Covering.
31.How can you force an Index?
Just write the name or the index number in the bracket to force the index
32.What is Isolation? How many Isolation levels are there? Explain?
- The isolation level refers to the degree to which data can be accessed by other users during a transaction. In other words, it is locking behavior of the server when a transaction is executed.
- There are four isolation levels defined. Each isolation level specifies the kinds of actions that are not permitted while concurrent transactions are executing. Higher levels include the restrictions imposed by the lower levels.
- Global variable: – @@isolation
- Command: – set transaction isolation level
- Level 0 (READ UNCOMMITTED): – Allows dirty reads. Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid. Though second transaction is allowed to read the data, it prevents from changing the data that has already been modified by the first uncommitted transaction
To allow this SQL Server does not attempt to acquire read locks on data pages for a select, so the select command is not blocked by any exclusive locks. In addition, as no read locks are acquired, no update operations attempting to acquire exclusive locks are blocked by the read operation.
- Level 1 (READ COMMITTED): – Prevents dirty reads. If one transaction is modifying the data, no other transaction is allowed to read or change the same unless the first transaction is either committed or rolled back. This is the default isolation level supported by Adaptive Server.
- Level 2 (REPEATABLE READS): – allows a single page to be read for many times within the same transaction and guarantees that the same value is read each time. This option prevents other transactions from updating a data row that has been read until the transaction in which it was read is committed or rolled back.
- Level 3 (SERIALIZABLE READS): –ensures that data read by one transaction is valid until the end of that transaction, hence preventing phantom rows. Adaptive Server supports this level through the holdlock keyword of the select statement, which applies a read-lock on the specified data. Phantom rows occur when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies the data (through an insert, delete, update, and so on). If the first transaction repeats the read with the same search conditions, it obtains a different set of rows.
33.What are diff. types of locks, Explain?
- Shared Lock: – used by process that is reading pages. Multiple shared locks can be held on one page; a single shared lock prevents any exclusive lock from being acquired. Shared locks typically are held only for the duration of the read on a particular page. If holdlock is specified, shared locks are held until the completion of the command or the transaction it is in.
- Update lock: – used by process that updates or deletes data, but have not yet done so. Update locks are acquired as the set of rows to be updated or deleted is being determined. Update locks are read-compatible with shared locks during the pre modification phase, but no other update lock or exclusive lock can be acquired on the page. Update locks automatically are updated to exclusive locks when the data change occurs.
- Exclusive Lock: – used by process that currently adding, changing or deleting information on data pages. Exclusive locks prevent any other type of lock (exclusive, update or shared) from being acquired. Exclusive locks are held on all affected pages until an explicit transaction or a command in the implicit transaction is complete.
34.What is update lock?
Lock acquired by update or delete statement at the time determining rows to be updated or deleted by a particular query
35.What is deadlock? How SQL Server handles it? How to avoid deadlock?
Deadlock is the situation when two different user connections fight for the same resources. One user connection acquires a lock on a particular page. The next step is to acquire a lock on the next affected by the transaction. However, a different user connection has already locked that page and is requesting a lock on the page the first user connection has locked. In this situation, neither user connection can continue until the other has continued. This is deadlock.
The SQL Server continually checks for deadlocks and automatically aborts one of the conflicting transactions. The transaction that is aborted is the one belonging to the process that has accumulated the least amount of CPU time since the start of its session, not the amount of CPU time for the specific transaction.
Methods for reducing lock contention, such as changing the locking scheme, avoiding table locks, and not holding shared locks
Some of other methods are
Acquire locks on objects in the same order
Well-designed applications can minimize deadlocks by always acquiring locks in the same order. Updates to multiple tables should always be performed in the same order. In applications with large numbers of tables and transactions that update several tables, establish a locking order that can be shared by all application developers.
Delaying deadlock checking
Adaptive Server performs deadlock checking after a minimum period of time for any process waiting for a lock to be released (sleeping). This deadlock checking is time-consuming overhead for applications that wait without a deadlock. If your applications deadlock infrequently, Adaptive Server can delay deadlock checking and reduce the overhead cost. You can specify the minimum amount of time (in milliseconds) that a process waits before it initiates a deadlock check using the configuration parameter deadlock checking period. Valid values are 0–2147483. The default value is 500. deadlock checking period is a dynamic configuration value, so any change to it takes immediate effect. If you set the value to 0, Adaptive Server initiates deadlock checking when the process begins to wait for a lock. If you set the value to 600, Adaptive Server initiates a deadlock check for the waiting process after at least 600 ms. For example:
sp_configure “deadlock checking period”, 600
Setting deadlock checking period to a higher value produces longer delays before deadlocks are detected. However, since Adaptive Server grants most lock requests before this time elapses, the deadlock checking overhead is avoided for those lock requests. Adaptive Server performs deadlock checking for all processes at fixed intervals, determined by deadlock checking period. If Adaptive Server performs a deadlock check while a process’s deadlock checking is delayed, the process waits until the next interval. Therefore, a process may wait from the number of milliseconds set by deadlock checking period to almost twice that value before deadlock checking is performed. sp_sysmon can help you tune deadlock checking behavior.
36.28 How many levels of locking is there? (2, Table level, Column level) How does the server change the levels? (lock escalation)
Adaptive Server has two levels of locking:
- For tables that use allpages locking or datapages locking, either page
locks or table locks.
- For tables that use datarows locking, either row locks or table locks
Page or row locks are less restrictive (or smaller) than table locks. A page lock locks all the rows on data page or an index page; a table lock locks an entire table. A row lock locks only a single row on a page. Adaptive Server uses page or row locks whenever possible to reduce contention and to improve concurrency.
Adaptive Server uses a table lock to provide more efficient locking when an entire table or a large number of pages or rows will be accessed by a statement.
Page and row locks
The following describes the types of page and row locks:
- Shared locks :Adaptive Server applies shared locks for read operations. If a shared lock has been applied to a data page or data row or to an index page, other transactions can also acquire a shared lock, even when the first transaction is active. However, no transaction can acquire an exclusive lock on the page or row until all shared locks on the page or row are released. This means that many transactions can simultaneously read the page or row, but no transaction can change data on the page or row while a shared lock exists. Transactions that need an exclusive lock wait or “block” for the release of the shared locks before continuing. By default, Adaptive Server releases shared locks after it finishes scanning the page or row. It does not hold shared locks until the statement is completed or until the end of the transaction unless requested to do so by the user. For more details on how shared locks are applied.
- Exclusive locks :Adaptive Server applies an exclusive lockfor a data modification operation. When a transaction gets an exclusive lock, other transactions cannot acquire a lock of any kind on the page or row until the exclusive lock is released at the end of its transaction. The other transactions wait or “block” until the exclusive lock is released.
- Update locks :Adaptive Server applies an update lockduring the initial phase of an update, delete, or fetch (for cursors declared for update) operation while the page or row is being read. The update lock allows shared locks on the page or row, but does not allow other update or exclusive locks. Update locks help avoid deadlocks and lock contention. If the page or row needs to be changed, the update lock is promoted to an exclusive lock as soon as no other shared locks exist on the page or row. In general, read operations acquire shared locks, and write operations acquire exclusive locks. For operations that delete or update data, Adaptive Server applies page-level or row-level exclusive and update locks only if the column used in the search argument is part of an index. If no index exists on any of the search arguments, Adaptive Server must acquire a table-level lock.
Table locks
The following describes the types of table locks.
- Intent lock :An intent lockindicates that page-level or row-level locks are currently held on a table. Adaptive Server applies an intent table lock with each shared or exclusive page or row lock, so an intent lock can be either an exclusive lock or a shared lock. Setting an intent lock prevents other transactions from subsequently acquiring conflicting table-level locks on the table that contains that locked page. An intent lock is held as long as page or row locks are in effect for the transaction.
- Shared lock :This lock is similar to a shared page or lock, except that it affects the entire table. For example, Adaptive Server applies a shared table lock for a select command with a holdlock clause if the command does not use an index. A create nonclustered index command also acquires a shared table lock.
- Exclusive lock This lock is similar to an exclusive page or row lock, except it affects the entire table. For example, Adaptive Server applies an exclusive table lock during a create clustered index command. update and delete statements require exclusive table locks if their search arguments do not reference indexed columns of the object.
37.What are global variables? Can I update them?
Global variable are Sybase defined variables. We cannot update the global variable.
38.Inside a SP what global variablesare used? Explain them.
- @@error : –Error number reported for last SQL statement
- @@sqlstatus : – Status of previous fetch statement in a cursor
- @@rowcount : – Number of rows processed by preceding command
- @@trancount : – Transaction nesting level
- @@transtate :- Current state of the transaction
- @@tranchained : – Current transaction mode (1 – Chained, 0 – Unchained)
- @@servername : – Name of the local SQL Server
- @@version : – SQL Server and O/S release level
- @@spid : – Current process ID
- @@identity : – Last identity value used in an insert
- @@nestlevel : – Nuber of levels nested in a stored procedure or trigger
39.Apart from the global variables you listed are there any other variables?
@@isolation : – Current isolation level
40.What are diff. Types of temporary tables? What is the diff. Between them?
- Permanent temporary table (tempdb..TableName) : – These are real tables created in the tempdb database and exist until explicitly dropped or until the SQL Server is restarted. They are useful for nonpermanent data that needs to be shared between multiple users.
- Temporary temporary table (#TableName) : – These tables are created in tempdb database to hold an intermediate result set. They exist only for the duration of a user session or the stored procedure in which they are created.
- Work tables: – These tables are created by Sybase to resolve complex queries where in the data is taken to workable tables and then further manipulation is done on to it for achieving the result. These tables are dropped when query execution completes. User can’t access these tables.
|