SQL Server General DBA

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 [...]

Like other mainstream commercial database systems, Microsoft SQL Server allows you to create multiple indexes on the same column of a table. This  increases the likelihood of having duplicate indexes in the database because SQL Server does not prevent you from creating duplicate indexes, infect we can create up to 999 duplicate indexes on each [...]

Introduction In this first Part  of the series I will discuss the OLTP and OLAP and the transition from OLTP to OLAP in pursuit performance improvements as well as the benefits of the each technology. Note**: This article is intended for the users who are new to the concepts of the data warehousing. What is [...]