|
|
- The Case statement can be used to derive a column contents based on its contents i.e. :-
|
SELECT pub_name,
CASE WHEN state IS NULL OR state=""
THEN 'Not Supplied'
ELSE state
END
FROM Publishers
|
- Use the SELECT statement to set the value of a variable i.e SET variable='New Value'.
- Use SELECT db_name() to get the name of the current database.
- To limit the number of rows returned use SELECT TOP 5 * FROM Customers.
- To create a new table use SELECT * INTO Customers2 FROM Customers.
- Use the following query to count the number of committed transactions in all the tables in a database.
|
SELECT t.name, rows
FROM sysobjects t INNER JOIN sysindexes i ON t.id=i.id
WHERE i.indid < 2
ORDER BY t.name
|
- To use a table name as a parameter to a stored procedure use dynamic SQL :-
|
CREATE proc dbcc_table
@tablename varchar(30)
AS
BEGIN
EXEC ("DBCC CHECKTABLE (" + @tablename + ")")
END
|
- Use the following SQL to locate all the tables containing the specified column -
|
SELECT a.name, b.name
FROM syscolumns a INNER JOIN sysobjects b ON
a.id = b.id
AND
b.type = 'u'
WHERE a.name = 'column name'
|
- Use the following SQL to generate the number of rows in all the tables in the current database -
|
SET NOCOUNT ON
DECLARE @table VARCHAR(80), @sql VARCHAR(200)
DECLARE table_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE TYPE = 'u'
ORDER BY 1
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'number of rows in ' + @table
SELECT @sql= 'SELECT COUNT(*) FROM ' + @table
EXEC @sql
FETCH NEXT FROM table_cursor INTO @table
END
CLOSE table_cursor
DEALLOCATE table_cursor
|
- Use LEFT(string,num) and RIGHT(string,num) to return portions of a string variable.
- Use SUBSTRING(string,start5,length) to return a portion of a string
- Use CHARINDEX('d', string) to find the location of the letter 'd' within the string.
- Use LEN(string) to return the length of a string.
- Use REVERSE(string) to reverse the contents of a string.
- Use UPPER(string) and LOWER(string) to convert the string to either upper or lower case.
- Use EXEC sp_rename 'OldName', 'NewName' to rename a table.
- Use EXEC sp_rename 'Table.[OldField]','NewField' to rename a table column.
- Use EXEC sp_rename 'OldKey', 'NewKey', OBJECT to rename a primary or foreign key.
- Use the EXISTS clause rather than the IN clause as it is more efficient and performs faster.
- Try to avoid using the SUBSTRING function in your SQL WHERE clauses as can force a table scan.
- If you need to use temporary tables consider adding an index to boost performance.
- Don't encapsulate non database modifying SQL in a transaction as there is an performance overhead.
- Use the BETWEEN clause in your SQL rather than the IN clause as it's generally more efficient.
- Don't use an '*' to return all the columns in a JOIN query as it will return some rows twice. Select only the columns you require.
- To obtain better performance use the DISTINCT option instead of the GROUP BY clause as shown below.
|
SELECT ClientID
FROM ClientDetails
WHERE ClientStatus = 10
GROUP BY ClientID
SELECT DISTINCT ClientID
FROM ClientDetails
WHERE ClientStatus = 10
|
- Use the TRUNCATE TABLE command to delete all the rows in a table, rather than the DELETE statement as it is not logged.
- Use the command 'SELECT rows FROM sysindexes WHERE id=OBJECT_ID('tablename') AND indid < 2' to determine the number of rows in a table, rather than 'SELECT COUNT(*) FROM tablename
- If you use the IS NULL in your where clause then the tables index cannot be used and a table scan is performed.
- Use the TOP operator over the SET ROWCOUNT command to limit the number of rows returned from a SELECT statement.
- The UNION ALL statement is much faster that UNION, because UNION ALL statement does not look for duplicate rows, while the UNION statement does look for duplicate rows, whether they exist or not.
- Consider rewriting a query that has a number of OR clauses into a query using the UNION ALL statement in order to boost performance.
- After opening a Cursor you can use the @@CURSOR_ROWS scalar function to determine the number of rows found by the cursor.
- Use READ ONLY cursors where possible to reduce concurrency and locking.
- Use FAST FORWARD cursors as they are read only and produce the least amount of overhead. Can only be used with the FETCH NEXT fetch operation.
- If you need a updatable cursor use a FORWARD ONLY cursor. Again can only use the FETCH NEXT fetch option.
- To show how long a query or Stored Procedure takes to run add the following sql code
|
DECLARE @starttime datetime
SELECT @starttime = GETDATE()
... Your code here ...
SELECT DATEDIFF(ms, @starttime, GETDATE() AS 'Total Time (ms)'
|
To show times for multiple stage Stored Procedure add the following sql code
|
CREATE TABLE #save_time (start_time DATETIME NOT NULL)
INSERT #save_time VALUES (GETDATE())
GO
... Your code here - can include GO statements
GO
SELECT 'Elapsed Time, sec' = DATEDIFF(second, start_time, GETDATE()) FROM #save_time
DROP TABLE #save_time
GO
|
- Use the following script to run DBCC SHOWCONFIG commands on all the indexes in one or more tables.
|
SELECT 'dbcc showcontig (' +
CONVERT(varchar(20),id) + ','" +
CONVERT(varchar(20),indid + ') -- ' +
object_name(id) + '.' +
name
FROM sysindexes
WHERE id = object_id('table1')
OR id = object_id('table2')
ORDER BY
object_name(id), indid
|
