1. Check that all the columns where seraches or sorts take place are indexed
unless they are frequently updated. Consider adding an index to all columns
needed by frequently used queries.
2. Column order does matter when creating indexes. The column that does the
most to narrow down the result set should be the first in the index.
3. Be sure that database update statistics are run and kept up-to-date.
4. Once the table structures are finalized and database is populated, inpect the
query plans on frequently used to queries to study the query plans. This area
can give surprises around table scans that are happening.
5. Avoid subselects in mostly used queries.
6. Monitor query performance and plans as database size grows.
Some useful SQL Server code
1. How to read registry values using a stored procedure:
/*This example reads the machine name from registry.*/
/*Note that this usage is not portable*/
/*SQL 8.0 introduces SERVERPROPERTY function*
/*for doing such tasks*/
Create Proc spReadRegistry
@mcName varchar(129) OUTPUT
as
begin
/*Read machine name from registry*/
exec master..xp_regread 'KEY_LOCAL_MACHINE', 'System\CurrentControlSet\Control\ComputerName\ActionComputerName',
'ComputerName', @mcName OUTPUT
end
2. How to update an integer column in a loop:
This procedure updates a column intColumn and updates all rows where col2 = 'x'. Values of intColumn start from 0 and get incremented.
Create Proc spLoopUpdate
as
begin
declare @counter int
select @counter =0
update TABLE set intColumn = @counter,
@counter=@counter+1
where col2 = "x"
end
3. How to create extended stored procedures:
/*a) Write SQL in a stored procedure or a triger to invoke the extended stored procedure*/
Create Proc spInvokeExtendedSP
as
begin
/*Note that extended stored proc has to exist in master db*/
exec master..xp_extendexProc @param1 @param2
end
/*b) Install the extended stored procedure in master db*/
exec sp_addextendedproc xp_functionindll 'dllname'
/*Now build a dll that has this exported function*/
int xp_functionindll(SRV_PROC *srvproc)
{
/*read parameters*/
int len = (*pfnsrv_paramlen(srvproc, 1);
char szParam1[255];
strncpy(szParam1, (STRING)(*pfnsrv_paramdata)(srvproc,1), len);
/*add your code to do processing*/
return 0;
}
SQL Tips for Microsoft SQL Server
1. SQL server can make use of the partial indexes. For example, if there are two
columns in a where clause and only column1 has index on it, SQL will still use the
index on column1.
2. Row size calculations: SQL server has a limitation of 8K on the rowsize. However,
the way to calculate this row size varies from SQL 6.5 to SQL 7.0
3. Unique on an index: Whereever possible, use UNIQUE clause in the index as this
speeds up queries.