What can cause poor performance of a query? Any or a combination of these: No indexes exist for the tables Unnecessarily complicated joins Excess usage of cursors Excess usage of
What types of indexes are there? Answer: Clustered Sort and store data based on key values Non-clustered Each key is a pointer to the data row
Setting ARITHIGNORE?helps to control whether error messages will be returned from overflow or divide-by-zero errors occurring during a query. SET ARITHIGNORE ON | OFF
LOCK_TIMEOUT?specifies the number of milliseconds a statement should wait for a lock to be released, for example: SET LOCK_TIMEOUT 1000 –Wait one second
Reseed all Autonumber fields in all tables with the following query: EXEC sp_MSForEachTable ‘ IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1 DBCC CHECKIDENT (”?”, RESEED, 0)
To get the last day of the current month, do the following: SELECT CONVERT(VARCHAR(25), DATEADD(DAY, -(DAY(GETDATE())), DATEADD(MONTH, 1, GETDATE())), 105) LastDay
You can recompile certain stored procedures with the following command: EXEC sp_recompile ‘Procedure_Name’; GO
You can RESEED?Identity Columns of all tables by using sp_MSForEachTable?to loop through all the tables and executing the DBCC CHECKIDENT?command if the specific table has an identity column: EXEC sp_MSForEachTable
You can make use of the built-in sys.fn_builtin_permissions SQL function to get a list of available permissions, as shown below: SELECT * FROM sys.fn_builtin_permissions(DEFAULT);SELECT * FROM sys.fn_builtin_permissions(”);