SQL Server database development top tips _mssql

Make sure that the data type in your code is consistent with the type of column in the database
It is important to ensure that the data types of your application are consistent across tiers. For example, if a column has a data type of nvarchar (50), you should use the same type of local variable in the code query and stored procedure.
Similarly, the Ado.net code in the data tier should specify the same data type and length. Why is this important? Because if the data type matches the query, SQL Server needs to first make an implicit conversion of the data type so that they can match.
There are also cases where SQL Server cannot use this index even if the index is set for the reference column. As a result, your query might use the index scan instead of the index seeking when the variable is consistent with the column type, which takes longer to execute.
Large-scale updates in batch processing
Developers sometimes need to make data modifications to all or most of the columns in one or more columns of a table. Usually, this is not a problem for small tables.
However, if the table is large, your UPDATE statement will lock the entire table, making it unusable or even unreadable. What's more, updating a frequently changing table can paralyze an entire application or Web site. Sometimes, in extreme cases, a large, single transaction will cause the transaction log to grow dramatically and eventually deplete the database server disk space.
Therefore, a good strategy is to perform batch-mass updates and combine frequent transaction log backups. In my experience, it is best to have a batch of 10,000 to 50,000 workloads. When you start thinking about applying batch processing, it's difficult to determine thresholds because it depends on a number of factors, such as how to make I/O faster, how to make the tables more efficient, and so on.
You can consider a guideline. In Ado.net, the typical command timeout is about 30 seconds. When the update is started, the other processes are still waiting until the update is complete. So if you expect the update to be longer than 20-25 seconds, you might want to make a batch update. Otherwise, it will end with the application timeout.
The following simple code shows how to update a column in a table, applying a batch size of 10,000:
while (0 = 0)
BEGIN
UPDATE Top (10000)
Person
SET Status = 2
WHERE Status = 1
IF @ @ROWCOUNT = 0
Break
End
applying For-each stored procedures
Sometimes you might want to perform the same operation on all objects of a particular type. For example, you might want to assign specific permissions to all tables in the database. Developers often set such tasks through pointers, but two simple stored procedures in SQL Server can be easier to implement: Sp_msforeachtable and Sp_msforeachdb.
Each stored procedure executes a command as a parameter. In the command, you embed the table name or database name as a question mark placeholder in the parameter. When the command runs, SQL Server replaces the question mark flag with the table or database name and executes it.
For example, the following code makes a full backup of each database in addition to tempdb on the server:
EXEC sp_msforeachdb ' IF '? ' <> ' tempdb ' BACKUP DATABASE?
To Disk= ' C:\backups\? Bak ' with INIT '
This is another example of how to apply these stored procedures. The following code deletes data from all tables in the database after the foreign key is disabled. Of course, when you use this code, you need to practice it carefully.
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 '
Build Database Version
As with your application versioning, it is a good way for developers to perform a digital version of the database.
It doesn't take a lot of work to do versioning, you just create a version table that contains the version number column and the timestamp column. When you deploy those scripts, you will better allocate the version number of each script collection and update the version table, and it will be easier to check for errors and database comparisons. You can even number the script so that if the number established in the database is no higher than the number established in the script, the script does not execute. The awbuildversion in the sample database AdventureWorks is a good example to look at.
Minimize Network Sessions
This technique is primarily for Web applications that take data from a database. Inexperienced developers often don't realize that database calls are expensive operations. This is not a big problem for small applications. However, as many websites become so hot that thousands of users are online at the same time, it is necessary to consider the scalability of the Web page and the optimization of the loading time.
I've seen pages with up to 15 database calls, and most of the stored procedures are executing to return a single row or value. It is worth remembering that a separate stored procedure in SQL Server can return multiple result sets. In a stored procedure, you can use a DataSet object in Ado.net and a DataTable object to form a collection.