Regardless of your professional level, IT is often helpful to learn new skills and best practices from other IT experts. This article contains the advanced SQL Server development skills I have encountered. I hope some of these skills will be helpful for your database development and management.
Regardless of your professional level, IT is often helpful to learn new skills and best practices from other IT experts. This article contains some advanced SQL Server development skills I have encountered. I hope some of these skills will be helpful for your database development and management.
Make sure that the data type in the Code is consistent with the column type in the database
It is important to ensure that the data types at each layer of your application are consistent. For example, if the data type of a column is NVARCHAR (50), you should use the same type of local variables in code query and stored procedures.
Similarly, the ADO. NET code in the data layer should also specify the same data type and length. Why is this important? If the data type matches the query, SQL Server needs to perform implicit conversion of the Data Type to enable them to match.
In some cases, even if an index is set for the reference column, SQL Server cannot use this index. Therefore, when the variables and column types are the same, your query may use Index Scan instead of Index Seeking, which takes a longer time.
Medium Scale update
Developers sometimes need to modify all or most of one or more columns in a table. Generally, this is not a problem for small tables.
However, if the table is large, your update statement will lock the entire table so that it cannot be used or even read. Furthermore, updating a frequently-changing table may paralyze the entire application or website. Sometimes, in extreme cases, a large, single transaction will cause a sharp increase in transaction logs and eventually exhaust the disk space of the database server.
Therefore, a good strategy is to Perform Batch large-scale updates and combine frequent transaction log backups. In my experience, it is best to have a batch of 10,000 to 50,000 workloads. It is difficult to determine the threshold when you start to consider batch processing of applications, because it depends on many factors, such as how to make I/O faster and how to make the table more efficient.
You can consider a guideline. In ADO. NET, the typical command timeout time is about 30 seconds. When the update starts, other processes remain in the waiting status until the update ends. Therefore, if you want the Update Time to exceed 20-25 seconds, you 'd better perform an update. Otherwise, it will end with the application timeout.
The following simple code shows how to update a column in a table. The application batch size is 10,000:
WHILE (0 = 0)
BEGIN
Update top (1, 10000)
Person
SET Status = 2
WHERE Status = 1
IF @ ROWCOUNT = 0
BREAK
END
Application FOR-EACH stored procedure
Sometimes you may need to perform the same operation on all objects of a specific type. For example, you may need 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 easily implemented: sp_msForEachTable and sp_msForEachDB.
Each stored procedure runs commands as a parameter. In the command, you embed the table name or database name as a question mark placeholder into the parameter. When running the command, SQL Server replaces the question mark with the table name or database name and runs the command.
For example, the following code backs up all databases 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. Exercise carefully when using the 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'
Create Database Version
For developers, it is a good way to implement digital version of the database just like the version of your application.
Version execution does not require a lot of work. You only need to 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 numbers of each script set and update the version table. It is easier to check errors and compare them with databases. You can even number the script. In this way, if the number created in the database is no higher than the number created in the script, the script will not be executed. The AWBuildVersion in the sample database AdventureWorks is a good example.
Minimize network sessions
This technique mainly targets network applications that fetch data from databases. Experienced developers often do not realize that Database Calling is costly. This is not a big problem for small applications. However, because many websites become very popular and thousands of users are online at the same time, it is necessary to consider the scalability and the optimization of webpage loading time in advance.
I have seen as many as 15 Database calls on web pages, and most of the stored procedures being executed are to return a single row or value. Note that a separate stored procedure in SQL Server can return multiple result sets. In a stored procedure, you can use the DataSet object in ADO. NET and combine the DataTable object into a set.
