1.If you are create trigger for insert in which you are going to insert 10
rows how many time it will fire?Only once
2. Compare IN and EXISTS?
in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns
3. What is Index Covering?
Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.
4. What is Checkpoint?
The point at which all data pages that have been changed are guaranteed to have been written to the database device.
5. What is direct update and deffered update?
Direct Update:An update operation that takes place in a single step, that is, the log records are written and the data and index pages are changed. Direct updates can be performed in three ways: in-place update, on-page update, and delete/insert direct update.
Deffered Update:An update operation that takes place in two steps. First, the log records for deleting existing entries and inserting new entries are written to the log, but only the delete changes to the data pages and indexes take place. In the second step, the log pages are rescanned, and the insert operations are performed on the data pages and indexes.
6. What is @@spid,@@rowcount,@@error?
returns the no of rows affected by the preceding query
7. I what to see all locks in the server?
sp_lock,syslocks
8. What is sp_help and sp_helpdb?
sp_help use to display the objects in a database,sp_helpdb used to display the databases in your server
9. What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.
Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.
10. Explain Join order for Performance?
The optimal query plan for a join involves picking the best indexes for each table and the most efficient order to process the tables in the joins. The query optimizer checks all the join orders for cost optimization. The minimum I/O time which reduces cost will be selected for processing. If many tables are joined, SQL breaks into possible tables of a group. Examine the tables in the group and query optimizer performs permutation on each table and makes the final four tables, which minimize the cost and I/Os.
11. Explain Index and Performance?
Clustered index not reusing available space on preceding pages which makes pages grow longer and the search takes long time to complete. It is better to use cluster index for data value that is somewhat randomly distributed throughout the table.
In non-cluster point of view, first check the impact on the performance of OLTP environment for addition of non-clustered index. It is better to use non-cluster where low number of duplicates are present. It is good for large number of duplicate values. It is more effective if less than 10 to 20% of data is to be accessed through non clustered. Non-clustered is useful for single row lookups, joins, queries on columns that are highly selective and for queries with small range retrievals
12. what is a ghost record ?
These are records that have been logically deleted but not physically deleted from the leaf level of an index.
The reasons for this are complicated, but basically having ghost records simplfies key-range locking and transaction rollback.
The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits. Once this is done, it is deleted by an asynchronous background proces (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.Ghost records will be mentioned later in the series when I discuss page compaction.
13. Compare Delete and Truncate?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
14. What is the use of timestamp?
timestamp is a data type that exposes automatically generated binarynumbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
15. What are the Inbuilt User defined datatypes available in Sybase?
sysnames,timestamps
16. Write a query to select all records that do not have a null value?
17. How the Hotspot can be Resolved?
using heap table
18. How can I select the first 500 rows from a table having million rows & generate a flat file?
set rowcount 500
19. Write a query to find out the Nth max and min?
To find Nth max:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)
To find Nth min:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name
20. What system procedures are used to list all the indexes?
sp_indexes
21. What are the modes of transaction? And how do you change from one mode to another?
unchained mode: The default mode, called unchained or Transact-SQL mode,requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.
chained mode: The SQL standards-compatible mode, called chained mode,implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert, open, fetch, select, and update. You must still explicitly end the transaction with commit transaction or rollback transaction.You can set either mode using the chained option of the set command.
23. How do u run a file with lot of queries on ISQL?
isql -U -P -S -iinput file
24. What is the error code of Deadlock and How will you eliminate Deadlock?
errorcode for dead lock is 1205 and for stored procedure is -3.
25. How do you handle Error in Sybase?
The @@ERROR Function: SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designate error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
SP_ADDMESSAGE:Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:
RAISERROR:You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.
26. How do you handle error in BCP?
-e
27. Write a query to convert the date to dd/mm/yy?
select convert(char,date_exp,101)
28. When a query is sent to the database and an index is not being used, what type of execution is taking table scan place?
29. What is an advantage to using a stored procedure as opposed to passing an SQL query from an faster execution , avoid dead lock,avoid network traffic application
30. What are defaults? Is there a column to which a default can't be bound?
When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound
31. Where do you think the users names and passwords will be stored in sql server?
syslogins
32. What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
33 . What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.
34. What is bit datatype and what's the information that can be stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
35. What are constraints? Explain different types of constraints?
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
36. What are “GRANT” and “REVOKE’ statements?
37. What are Wildcard operators in sybase?
Selects documents that contain matches to a character string containing variables. The WILDCARD operator lets you define a search string with variables, which can be used to locate related word matches in documents.
38. How will you find second maximum value in a table?
select max(column) from table where column<(select max(column) from table)
39. What is Optimistic locking?
Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
40. What are the types of Direct update not in place?
cheap direct update and Expensive direct update
41. How the Trigger differ from rules and constraints?
42. If I want current database name?
db_name()
43. If I want to check my query syntax what shall I do?
set parseonly on
44. Why we should go for Deallocating the cursors?
45. How do you call the Remote stored Procedures?
exec server_name.db_name.proc_name
46. What is the Drawbacks of Normalization?
Although most successful databases are normalized to some degree, there is one substantial drawback of a normalized database: reduced database performance. The acceptance of reduced performance requires the knowledge that when a query or transaction request is sent to the database, there are factors involved, such as CPU usage, memory usage, and input/output (I/O). To make a long story short, a normalized database requires much more CPU, memory, and I/O to process transactions and database queries than does a denormalized database. A normalized database must locate the requested tables and then join the data from the tables to either get the requested information or to process the desired data. A more in-depth discussion concerning database performance occurs in Hour 18, "Managing Database Users."
47 Explain BCP and Performance?
48. Explain Tempdb and Performance?
49. What is Thresholds and how will you add ?
50. What is the maximum row length limited by size of data page in SQL Server?
2KB
51. what is In & OUT in stored procedure?
The output keyword may be specified in the sp creation statement if the parameters can be passed both in and out of the sp,when passing out you must specifiy output keyword in execution statement
52. Tell me the limitation of the Trigger?
any create command,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger
53.can I update view?
yes,but if view had been created using only one table than update is possible
54. what is difference between constraints and rules?
rules deals with constant and one rule can be bounded to one column,constraints can bound many column and it can be compared with columns in another table
55. how will you bind a rule?
sp_bindrule
56. explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor
57. What are the steps involved in creating cursor?
Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors
58. What is Transaction Log?
It is a database level system table called syslogs,which contains a sequential list of all modification to every objects in the database.
59. compare Rollback transaction and Rollback Trigger?
60. How will bcp out the Identity column?
-E
61. What is Demand lock and Dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. Demand Lock:A demand lock prevents any more shared locks from being set on a data resource (table or data page). Any new shared lock request has to wait for the demand lock request to finish.
62 . How to get the last month’s last Day?
select datename(dw,dateadd(dd,30-datepart(dd,getdate()),dateadd(mm,-1,getdate())))
63. What is patindex()?
returns the starting postion of the specified ,else 0
64. How do I get the Duplicate rows from a table?
select * from table name group by column1 having count(*)>1
65. what is command permission and object permission?
OP is Grant/revoke to these objects for permissions which includes select,update,delete,execute.CP is grant /revoke permission for create (db,table,view,procedure,defaults)
66. Explain Stored Procedures on performance?
Query optimizer generates a query plan for a st. proc based on the parameters parsed in the first time it is executed. To make st.prc to perform well, first the quires used in the procedure are to be checked. Also since it is compiled and stored in memory, procedure may become inefficient over time. Further execution used only if it is gain otherwise again the st. proc is recompiled.
For example when it was first compiled, it may be fast as the table size is small and the table scan is better choice. But when the table grows into huge, index selection may be a better option than the table scans for that query. But the procedure will still do only table scan as it is pre-compiled, so as a general rule, if the table size changes by 20%, we have to run update statistics on the table and recompile the stored procedure. For stored proc in which, the parameter has the strong influence over the result set, we have to make it with recompile option, so that it compiles every time it is accessed.
67. What is sp_dboption?
It is used to enable or disable db option for your database
68. Explain Denormalization techniques?
Denormalization is used for performance boosting. In which normalized db is denormalized for the sake of performance. According to normalization all columns has to depend on the primary key, but some times creating a manipulated field.
Redundant column may be used in a table when we always join the first table to get a column data from the second table using foreign key. Data partitioning techniques like vertically splitting the tables, horizontally splitting the tables can be used to denormalize a db. Vertical split technique is typically used to minimize the size of the primary table or to move infrequently accessed columns to a separate table. Horizontal split technique involves splitting the table at row level into two or more tables. For ex, if we can split the table into two tables, so that the first table has the recent entries and a table that has historical data, we can drastically increase the size of the new table, whose columns are going to access frequently
69. Which is better Stored procedure or Trigger?
1. If you intend to set or modify some column values in the proc/trig, a trig is the right place to do it. A BEFORE trigger can directly modify the row buffer before it is updated/inserted. A procedure would have to issue a UPDATE, thus a performance penalty.
---------------------
2. The trigger has all the row data automatically available to it. For the procedure to have the same information, you'll either have to pass all values to the proc via parms, or the proc will need to re-read the data, a performance penalty.
-----------------------------
3. If your application issues a single update, delete, or insert that modifies multiple rows, a FOR EACH ROW trigger will be fired for each row. It could be difficult to provide the SP the information that allows it to identify the set of rows that were modified.
70. I made two tables A B id1 age id1 age 1 Asked me to find out all the id1's which are in table A and select * from A where not exists(select id1 from B) do not exist in table b
71. How did you know the index was the cause of the performance degration?
72. Compare left and Right Outer Join?
For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL
73. What is Join and Its Types?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
74. what are different global variables ?
@@rowcount-returns no of rows processed by preceeding command.
@@error- returns the error code for last or current query.
@@transtate- returns the current transaction state.
@@tranchained-returns the current transcation mode.
@@server- returns the server name.
@@version-returns the version of the sql server and OS used in a system.
@@spid- current process id.
@@identity-returns the lastly incremented identity value.
@@nestlevel-No of level in stored procedures/trigger.
@@sqlstatus-status of previous fetch statement in cursor.
75. what is temporary tables and its types?
Sub query : A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery.
Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.
76. Compare Union and Union all?
union remove the duplicates and go for worktable creation where as union all allow duplicates and don't go for WT
77. Compare Subquery and Correlated Subquery?
Sub query : A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery.
Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.
78. What is Update Statistics?
79. Compare Primary key and Unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
80. Advantages and disadvantages of cursors?
81. Compare Select into and Insert into?
select into it will create default table and copy the value to that table from other,insert into use to insert the value only in existing table
82. What is “COMPUTE BY” clause in Sybase?
When ever we use compute by ,we must also include order by
83. What is Check point in Transaction Log?
Checkpoint writes all dirty pages for the database from cache to disk,starting with the log and also it reduces the amount of work the server need to do at recovery time
84. Compare 2 nd and 3 rd Normal form?
Second normal form-nonkey fields must depend on the entire pr. Key.
Third normal form---nonkey fields must not depend on other nonkey fields.
85. What is –I option in Sybase tools?
It is the name of the interface file to use when trying to find a server to connect to
86. Is there any way to create Primary or Foreignkey on a table other than Query?
sp_primarykey,sp_foreginkey
87. If I want to round the value 999.99 as 1000 ?
round(999.99,0)
88. What is Distribution page?
server keeps distribution information for each index on a separate page in the datebase
89. Compare Composite Index and Multiple Index
90. What is Row Padding?
Locking of the page for a long time make the other process to wait. So the time required to complete the process will increase which is not desirable. SQL prevents dirty reads by using shared locks. Make the row big enough so that only one row fit on a page which avoid the locking of page. This is called row padding.
91. Explain Database and Performance?
92. How will you create Login name and User in Sybase?
sp_addlogin,sp_adduser
93. Is it necessary to drop & recreate all procedures and triggers every few months ?
94. What is difference between Count() and Count(*)?
count(*) will inclued null rows and faster
95. Advantage of Stored Procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency
96. In a query which is better? using IN or EXISTS?
in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns
97. what are triggers? advantage ? disadvantage?
98. How do u copy the output results into a file?
isql -Usa -P -S -oos_file_name
99. Write a query to find out the Nth max and min?
To find Nth max:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)
To find Nth min:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name
2. Compare IN and EXISTS?
in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns
3. What is Index Covering?
Index covering is a mechanism for using the leaf level of nonclustered index the way the data page of a clustered index would work. Index covering occurs when all columns referenced in a query are contained in the index itself.
4. What is Checkpoint?
The point at which all data pages that have been changed are guaranteed to have been written to the database device.
5. What is direct update and deffered update?
Direct Update:An update operation that takes place in a single step, that is, the log records are written and the data and index pages are changed. Direct updates can be performed in three ways: in-place update, on-page update, and delete/insert direct update.
Deffered Update:An update operation that takes place in two steps. First, the log records for deleting existing entries and inserting new entries are written to the log, but only the delete changes to the data pages and indexes take place. In the second step, the log pages are rescanned, and the insert operations are performed on the data pages and indexes.
6. What is @@spid,@@rowcount,@@error?
returns the no of rows affected by the preceding query
7. I what to see all locks in the server?
sp_lock,syslocks
8. What is sp_help and sp_helpdb?
sp_help use to display the objects in a database,sp_helpdb used to display the databases in your server
9. What are the different types of lock in Sybase?
Shared locks :SQL server applies shared lock for read operations. All the processes can read, but no process can write.
Update lock :allows many processes to read, but no other process can get an excl. or update lock. This lock is applied during update or delete but changes to excl lock when modification takes place and exists till the transaction is completed.
Exclusive lock: SQL server applies exclusive lock for data modification operations. When the transaction gets exclusive lock, other transactions cannot obtain any other type of locks until the exclusive lock is released at the end of the transaction.
10. Explain Join order for Performance?
The optimal query plan for a join involves picking the best indexes for each table and the most efficient order to process the tables in the joins. The query optimizer checks all the join orders for cost optimization. The minimum I/O time which reduces cost will be selected for processing. If many tables are joined, SQL breaks into possible tables of a group. Examine the tables in the group and query optimizer performs permutation on each table and makes the final four tables, which minimize the cost and I/Os.
11. Explain Index and Performance?
Clustered index not reusing available space on preceding pages which makes pages grow longer and the search takes long time to complete. It is better to use cluster index for data value that is somewhat randomly distributed throughout the table.
In non-cluster point of view, first check the impact on the performance of OLTP environment for addition of non-clustered index. It is better to use non-cluster where low number of duplicates are present. It is good for large number of duplicate values. It is more effective if less than 10 to 20% of data is to be accessed through non clustered. Non-clustered is useful for single row lookups, joins, queries on columns that are highly selective and for queries with small range retrievals
12. what is a ghost record ?
These are records that have been logically deleted but not physically deleted from the leaf level of an index.
The reasons for this are complicated, but basically having ghost records simplfies key-range locking and transaction rollback.
The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits. Once this is done, it is deleted by an asynchronous background proces (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.Ghost records will be mentioned later in the series when I discuss page compaction.
13. Compare Delete and Truncate?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
14. What is the use of timestamp?
timestamp is a data type that exposes automatically generated binarynumbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
15. What are the Inbuilt User defined datatypes available in Sybase?
sysnames,timestamps
16. Write a query to select all records that do not have a null value?
17. How the Hotspot can be Resolved?
using heap table
18. How can I select the first 500 rows from a table having million rows & generate a flat file?
set rowcount 500
19. Write a query to find out the Nth max and min?
To find Nth max:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)
To find Nth min:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name
20. What system procedures are used to list all the indexes?
sp_indexes
21. What are the modes of transaction? And how do you change from one mode to another?
unchained mode: The default mode, called unchained or Transact-SQL mode,requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.
chained mode: The SQL standards-compatible mode, called chained mode,implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert, open, fetch, select, and update. You must still explicitly end the transaction with commit transaction or rollback transaction.You can set either mode using the chained option of the set command.
23. How do u run a file with lot of queries on ISQL?
isql -U -P -S -iinput file
24. What is the error code of Deadlock and How will you eliminate Deadlock?
errorcode for dead lock is 1205 and for stored procedure is -3.
25. How do you handle Error in Sybase?
The @@ERROR Function: SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designate error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
SP_ADDMESSAGE:Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:
RAISERROR:You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.
26. How do you handle error in BCP?
-e
27. Write a query to convert the date to dd/mm/yy?
select convert(char,date_exp,101)
28. When a query is sent to the database and an index is not being used, what type of execution is taking table scan place?
29. What is an advantage to using a stored procedure as opposed to passing an SQL query from an faster execution , avoid dead lock,avoid network traffic application
30. What are defaults? Is there a column to which a default can't be bound?
When you are inserting suppose forgot to enter the value for particular column than you will provide some constant value for that .identity ,timestamps column are default cannot bound
31. Where do you think the users names and passwords will be stored in sql server?
syslogins
32. What is lock escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
33 . What are user defined datatypes and when you should go for them?
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.
34. What is bit datatype and what's the information that can be stored inside a bit column?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
35. What are constraints? Explain different types of constraints?
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
36. What are “GRANT” and “REVOKE’ statements?
37. What are Wildcard operators in sybase?
Selects documents that contain matches to a character string containing variables. The WILDCARD operator lets you define a search string with variables, which can be used to locate related word matches in documents.
38. How will you find second maximum value in a table?
select max(column) from table where column<(select max(column) from table)
39. What is Optimistic locking?
Optimistic index locking does not acquire an address lock on the root page of an index during normal data manipulation language operations (DML). If your updates and inserts can cause modifications to the root page of the accessed index, optimistic index locking restarts the search and acquires an exclusive table lock, not an address lock.
40. What are the types of Direct update not in place?
cheap direct update and Expensive direct update
41. How the Trigger differ from rules and constraints?
42. If I want current database name?
db_name()
43. If I want to check my query syntax what shall I do?
set parseonly on
44. Why we should go for Deallocating the cursors?
45. How do you call the Remote stored Procedures?
exec server_name.db_name.proc_name
46. What is the Drawbacks of Normalization?
Although most successful databases are normalized to some degree, there is one substantial drawback of a normalized database: reduced database performance. The acceptance of reduced performance requires the knowledge that when a query or transaction request is sent to the database, there are factors involved, such as CPU usage, memory usage, and input/output (I/O). To make a long story short, a normalized database requires much more CPU, memory, and I/O to process transactions and database queries than does a denormalized database. A normalized database must locate the requested tables and then join the data from the tables to either get the requested information or to process the desired data. A more in-depth discussion concerning database performance occurs in Hour 18, "Managing Database Users."
47 Explain BCP and Performance?
48. Explain Tempdb and Performance?
49. What is Thresholds and how will you add ?
50. What is the maximum row length limited by size of data page in SQL Server?
2KB
51. what is In & OUT in stored procedure?
The output keyword may be specified in the sp creation statement if the parameters can be passed both in and out of the sp,when passing out you must specifiy output keyword in execution statement
52. Tell me the limitation of the Trigger?
any create command,drop command,alter table,grant,revoke,select into,truncate,update statistics are not permitted in trigger
53.can I update view?
yes,but if view had been created using only one table than update is possible
54. what is difference between constraints and rules?
rules deals with constant and one rule can be bounded to one column,constraints can bound many column and it can be compared with columns in another table
55. how will you bind a rule?
sp_bindrule
56. explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a cursor
57. What are the steps involved in creating cursor?
Declaring cursors,declaring variables,opening cursors,fetching rows,main loop,closing the cursor,deallocating cursors
58. What is Transaction Log?
It is a database level system table called syslogs,which contains a sequential list of all modification to every objects in the database.
59. compare Rollback transaction and Rollback Trigger?
60. How will bcp out the Identity column?
-E
61. What is Demand lock and Dead lock?
A deadlock occurs when there is a cyclic dependency between two or more transactions for the same set of resources. Demand Lock:A demand lock prevents any more shared locks from being set on a data resource (table or data page). Any new shared lock request has to wait for the demand lock request to finish.
62 . How to get the last month’s last Day?
select datename(dw,dateadd(dd,30-datepart(dd,getdate()),dateadd(mm,-1,getdate())))
63. What is patindex()?
returns the starting postion of the specified ,else 0
64. How do I get the Duplicate rows from a table?
select * from table name group by column1 having count(*)>1
65. what is command permission and object permission?
OP is Grant/revoke to these objects for permissions which includes select,update,delete,execute.CP is grant /revoke permission for create (db,table,view,procedure,defaults)
66. Explain Stored Procedures on performance?
Query optimizer generates a query plan for a st. proc based on the parameters parsed in the first time it is executed. To make st.prc to perform well, first the quires used in the procedure are to be checked. Also since it is compiled and stored in memory, procedure may become inefficient over time. Further execution used only if it is gain otherwise again the st. proc is recompiled.
For example when it was first compiled, it may be fast as the table size is small and the table scan is better choice. But when the table grows into huge, index selection may be a better option than the table scans for that query. But the procedure will still do only table scan as it is pre-compiled, so as a general rule, if the table size changes by 20%, we have to run update statistics on the table and recompile the stored procedure. For stored proc in which, the parameter has the strong influence over the result set, we have to make it with recompile option, so that it compiles every time it is accessed.
67. What is sp_dboption?
It is used to enable or disable db option for your database
68. Explain Denormalization techniques?
Denormalization is used for performance boosting. In which normalized db is denormalized for the sake of performance. According to normalization all columns has to depend on the primary key, but some times creating a manipulated field.
Redundant column may be used in a table when we always join the first table to get a column data from the second table using foreign key. Data partitioning techniques like vertically splitting the tables, horizontally splitting the tables can be used to denormalize a db. Vertical split technique is typically used to minimize the size of the primary table or to move infrequently accessed columns to a separate table. Horizontal split technique involves splitting the table at row level into two or more tables. For ex, if we can split the table into two tables, so that the first table has the recent entries and a table that has historical data, we can drastically increase the size of the new table, whose columns are going to access frequently
69. Which is better Stored procedure or Trigger?
1. If you intend to set or modify some column values in the proc/trig, a trig is the right place to do it. A BEFORE trigger can directly modify the row buffer before it is updated/inserted. A procedure would have to issue a UPDATE, thus a performance penalty.
---------------------
2. The trigger has all the row data automatically available to it. For the procedure to have the same information, you'll either have to pass all values to the proc via parms, or the proc will need to re-read the data, a performance penalty.
-----------------------------
3. If your application issues a single update, delete, or insert that modifies multiple rows, a FOR EACH ROW trigger will be fired for each row. It could be difficult to provide the SP the information that allows it to identify the set of rows that were modified.
70. I made two tables A B id1 age id1 age 1 Asked me to find out all the id1's which are in table A and select * from A where not exists(select id1 from B) do not exist in table b
71. How did you know the index was the cause of the performance degration?
72. Compare left and Right Outer Join?
For left join all row was selected in outer table and for inner table row which are not meet the join condition are filled with NULL. In Right join inner table all row was selected and for outer table the rows which not meet the join conditins will filled with NULL
73. What is Join and Its Types?
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
74. what are different global variables ?
@@rowcount-returns no of rows processed by preceeding command.
@@error- returns the error code for last or current query.
@@transtate- returns the current transaction state.
@@tranchained-returns the current transcation mode.
@@server- returns the server name.
@@version-returns the version of the sql server and OS used in a system.
@@spid- current process id.
@@identity-returns the lastly incremented identity value.
@@nestlevel-No of level in stored procedures/trigger.
@@sqlstatus-status of previous fetch statement in cursor.
75. what is temporary tables and its types?
Sub query : A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery.
Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.
76. Compare Union and Union all?
union remove the duplicates and go for worktable creation where as union all allow duplicates and don't go for WT
77. Compare Subquery and Correlated Subquery?
Sub query : A select statement that is nested inside another select, insert, update or delete statement, or inside another subquery.
Correlated subquery: A subquery that cannot be evaluated independently, but that depends on the outer query for its results. Also called a repeating subquery, since the subquery is executed once for each row that might be selected by the outer query.
78. What is Update Statistics?
79. Compare Primary key and Unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
80. Advantages and disadvantages of cursors?
81. Compare Select into and Insert into?
select into it will create default table and copy the value to that table from other,insert into use to insert the value only in existing table
82. What is “COMPUTE BY” clause in Sybase?
When ever we use compute by ,we must also include order by
83. What is Check point in Transaction Log?
Checkpoint writes all dirty pages for the database from cache to disk,starting with the log and also it reduces the amount of work the server need to do at recovery time
84. Compare 2 nd and 3 rd Normal form?
Second normal form-nonkey fields must depend on the entire pr. Key.
Third normal form---nonkey fields must not depend on other nonkey fields.
85. What is –I option in Sybase tools?
It is the name of the interface file to use when trying to find a server to connect to
86. Is there any way to create Primary or Foreignkey on a table other than Query?
sp_primarykey,sp_foreginkey
87. If I want to round the value 999.99 as 1000 ?
round(999.99,0)
88. What is Distribution page?
server keeps distribution information for each index on a separate page in the datebase
89. Compare Composite Index and Multiple Index
90. What is Row Padding?
Locking of the page for a long time make the other process to wait. So the time required to complete the process will increase which is not desirable. SQL prevents dirty reads by using shared locks. Make the row big enough so that only one row fit on a page which avoid the locking of page. This is called row padding.
91. Explain Database and Performance?
92. How will you create Login name and User in Sybase?
sp_addlogin,sp_adduser
93. Is it necessary to drop & recreate all procedures and triggers every few months ?
94. What is difference between Count() and Count(*)?
count(*) will inclued null rows and faster
95. Advantage of Stored Procedure?
faster execution,reduce network traffic,Modular programming,reduced operator eror,enforced consistency
96. In a query which is better? using IN or EXISTS?
in allow duplicate values and sub query have oe column,exists not allow duplicates and inner query can have multi columns
97. what are triggers? advantage ? disadvantage?
98. How do u copy the output results into a file?
isql -Usa -P -S -oos_file_name
99. Write a query to find out the Nth max and min?
To find Nth max:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name)from table_name T2 where T2.column_name>T1.col_name)
To find Nth min:
select * from table_name T1 where (N-1)=(select count (distinct T2.column_name) from table_name T2 where T2.column_name
1 comments:
Very nice job... really appreciable... keep it up
Post a Comment