Temat: Tips & Tricks for SQL Server database development

No matter what your level of expertise, it always helps to learn new tips and best practices from other IT professionals. This article contains a collection of the top SQL Server development tips that I have come across. Hopefully, some of these will help you with your database development and administration operations.
1) Always match datatypes in code with the columns in the database (SAME)
It's important to make sure that your datatypes match across all layers in your application. For example, if a column's datatype is NVARCHAR (50), you should have the code in queries and stored procedures use local variables of the same datatype.
Similarly, the ADO.NET code in the data layer should specify the same datatype and length. Why is this important? Because if the datatypes and queries do not match, SQL Server needs to perform an implicit conversion of the datatypes to match them.
There are also some scenarios where SQL Server cannot use an existing index, even though the referenced column is indexed. Therefore, your query might end up using Index Scan instead of Index Seek, resulting in execution times with longer orders of magnitude than if the variables and columns were of the same type.
2) Do mass updates in batches (SIMILAR - but Oracle locking is handled differently, so it's not lock escalation - but rather that more frequent COMMIT's are sometimes more efficient than one big transaction)
Developers sometimes need to modify data in one or more columns for all or most rows in a table. This is usually not an issue as long as the table is fairly small.
If the table is large, however, your update statement will lock the entire table and make it unavailable, even for data reads. Further more, a highly volatile table can bring down the entire application or website for the duration of the update. At times, a large, single transaction like this will greatly expand the size of the transaction log and -- in extreme scenarios -- contribute to running out of disk space on the database server.
It is therefore a good practice to do mass updates in batches, combined with frequent transaction log backups. In my experience, a batch of 10,000 or 50,000 works best. It is difficult to specify a threshold of when you should start considering batching, as it all depends on factors such as how fast you disk I/O is, how heavily the table is used, and more.
There is one guideline you can use though. A typical command timeout in ADO.NET is about 30 seconds. While the update takes place, other processes have to wait until it is finished. So if you expect that your update will take longer than 20-25 seconds, you are better off doing a batch update, otherwise you will end up with application timeouts.
Here is a sample code that shows how to update a column in a table, using 10,000 as batch size:
WHILE ( 0 = 0 )
BEGIN
UPDATE TOP ( 10000 )
Person
SET Status = 2
WHERE Status = 1
IF @@ROWCOUNT = 0
BREAK
END
3) Utilize FOR-EACH stored procedures. (SIMILAR - but using the PL/SQL bulk collect instead - example inserted below)
Once in a while you might need to perform the same action on all objects of a certain type. For example, you might need to assign a specific permission for all tables in the database. Developers often resort to cursors for tasks like this, but SQL Server comes with two handy stored procedures that make things a lot easier: sp_msForEachTable and sp_msForEachDB.
Each of these takes a command to be executed as a parameter. You can embed a question mark in the parameter as a placeholder for the table or database name in the command. At runtime, SQL Server replaces the question mark with the name of the table or database and executes it.
For example, the following code runs a full backup for each database on the server, except for TempDB:
EXEC sp_msforeachdb 'IF ''?'' <> ''tempdb'' BACKUP DATABASE ?
TO DISK=''c:\backups\?.bak'' WITH INIT'
Here is another example of how these stored procedures can be useful -- and somewhat dangerous. The following code deletes data in all tables in the database after disabling the foreign key. Naturally, you'll want to exercise caution when using this code:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Example 6-13 Fetching Bulk Data With a Cursor
DECLARE
TYPE IdsTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
ids IdsTab;
names NameTab;
CURSOR c1 IS
SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK';
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO ids, names;
CLOsE c1;
-- Here is where you process the elements in the collections
FOR i IN ids.FIRST .. ids.LAST
LOOP
IF ids(i) > 140 THEN
DBMS_OUTPUT.PUT_LINE( ids(i) );
END IF;
END LOOP;
FOR i IN names.FIRST .. names.LAST
LOOP
IF names(i) LIKE '%Ma%' THEN
DBMS_OUTPUT.PUT_LINE( names(i) );
END IF;
END LOOP;
END;
/
4) Version your database builds (SAME)

It's considered a good practice for developers to implement numeric versioning of databases, just like they do with applications.
It doesn't require a lot of effort to implement versioning – you just have to create a table with a version number and additional timestamps. Once you get better at assigning a build number to each set of scripts and updating the version table when you deploy those scripts, it becomes much easier to troubleshoot and compare your databases. You could even code your scripts so that they don't execute if the build number in the database is not higher than the build number in the script. The AWBuildVersion table in the AdventureWorks sample database is a good example to look at.
5) Minimize the number of network calls (SAME - one of the reasons PL/SQl can sometimes be so powerful - as one network call for entire package/procedure)
This tip applies mainly to Web applications that pull data from a database. Less experienced developers often don't realize that each database call is a relatively expensive operation. It's not a big deal in small applications, but since many websites could become popular and used by thousands of simultaneous users, you need to start thinking about scalability and optimizing your page load times in advance.