SQL Server Articles

In this article, you will learn what query optimisation statistics are and how SQL Server query optimizer uses statistics. You will also learn how to create and manage statistics, and what are the different ways to determine when statistics were last updated in SQL Server. What are Query Optimization Statistics? The term statistics refers specifically [...]

Recently I was given a task to export data in XML format using SSIS. I initially thought it should be straight forward – just dump it in a flat file and give it a name xml type, however SSIS has no XML destination just an XML source. I had no luck dumping the XML in [...]

Parameter sniffing is when SQL Server compiles a stored procedure’s execution plan with the first parameter that has been used and then uses this plan for subsequent executions regardless of the parameters. First let’s look at a worked example. Assume we have table as below. CREATE Table TblData (ID INT IDENTITY PRIMARY KEY , Name [...]

In the first part of SQL Server Transactions and Locking article series, you learned the basics of SQL Server transactions, how they work and how to handle them, and how to write procedures that use transactions. In this part, I will give an overview of SQL Server locking and transaction isolation levels, how to set [...]

Rolling out SQL Servers is a key task for DBAs.  While for some DBAs this can be a frequent exercise, others will have to face it one time or other throughout their career in an organisation. Successfully planning an installation requires a fair bit of knowledge about SQL Server and a basic understanding of the [...]

Continuing from Part 2 of the Database Migration Checklist series: Step 10: Full-text catalogs and full-text indexing This is one area of SQL Server where people do not seem to take notice unless something goes wrong. Full-text functionality is a specialised area in database application development and is not usually implemented in your everyday OLTP [...]

Continuing from Part 1  , our Migration Checklist continues: Step 5: Update statistics It is always a good idea to update the statistics of the database that you have just installed or migrated. To do this, run the following command against the target database: sp_updatestats The sp_updatestats system stored procedure runs the UPDATE STATISTICS command [...]

It is a fact of life: SQL Server databases change homes. They move from one instance to another, from one version to the next, from old servers to new ones.  They move around as an organisation’s data grows, applications are enhanced or new versions of the database software are released. If not anything else, servers [...]

In many situations, data modification requires several steps. For example, you may need to change the values ​​in two separate tables. You can use transactions to complete these two operations as a unit, or if an error occurs, does not change any of the tables. Another key consideration is that most databases must be compatible [...]

Software Usage Tracker New to MAP 5.0 is another feature called the Software Usage Tracker. This is an audit mechanism that allows you to track the number of users or devices accessing your servers. The information from these audits can help people responsible for managing software licensing to get a better picture of Microsoft server [...]