SQL Server supports online index and table rebuild operations which allow for maintenance operations to occur w/o significant downtime. While a table is being rebuild, or a new index is being built on it, the table is fully utilizable. It can be queried and any updates done to the table while the online rebuild operation is occurring will be contained in the final rebuilt table. A detailed explanation on how these online rebuild operations work can be found in the Online Indexing Operations in SQL Server 2005 white paper. But Online Index Build operations in SQL Server 2005, 2008 and 2008 R2 do not support tables that contain LOB columns, attempting to do so would trigger an error:

Read the rest of this entry »

Starting with SQL Server 11 the the SEND verb has a new syntax and accepts multiple dialogs handles to send on:

SEND
   ON CONVERSATION [(]conversation_handle [,.. @conversation_handle_n][)]
   [ MESSAGE TYPE message_type_name ]
   [ ( message_body_expression ) ]
[ ; ]

Read the rest of this entry »

Prior to SQL Server 2012 when you add a new non-NULLable column with default values to an existing table a size-of data operation occurs: every row in the table is updated to add the default value of the new column. For small tables this is insignificant, but for large tables this can be so problematic as to completely prohibit the operation. But starting with SQL Server 2012 the operation is, in most cases, instantaneous: only the table metadata is changed, no rows are being updated.

Lets look at a simple example, we’ll create a table with some rows and then add a non-NULL column with default values. First create and populate the table:

Read the rest of this entry »

In my previous article How to use columnstore indexes in SQL Server we’ve seen how to create a columnstore index on a table and how certain queries can significantly reduce the IO needed and thus increase in performance by leveraging this new feature. But once a columnstore index is added to a table the table becomes read-only as it cannot be updated. Trying to insert a new row in the table will result in an error:

insert into sales ([date],itemid, price, quantity) values ('20110713', 1,1.0,1);

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

Read the rest of this entry »

Column oriented storage is the data storage of choice for data warehouse and business analysis applications. Column oriented storage allows for a high data compression rate and as such it can increase processing speed primarily by reducing the IO needs. Now SQL Server allows for creating column oriented indexes (called COLUMNSTORE indexes) and thus brings the benefits of this highly efficient BI oriented indexes in the same engine that runs the OLTP workload. The syntax for creating columnstore indexes is described on MSDN at CREATE COLUMNSTORE INDEX. Lets walk trough a very simple example of how to create and use a columnstore index. First lets have a dummy sales table:

Read the rest of this entry »

This year SIGMOG keynotes are available online at https://services.choruscall.eu/links/sigmod1106.html

• June 13^th: A Quest for Beauty and Wealth (or, Business Processes for Database Researchers)
• June 14^th: Internet-Scale Storage (I highly recommend this session)
• June 15^th: SIGMOD Awards (this includes a segment on the SQL Server team)
• June 16^th: Managing Scientific Data

How do large-scale sites and applications remain SQL-based? is a recent article from Michael Rys (Blog|Twitter) that highlights the relational SQL Server based web-scale deployment at MySpace. I have talked before about how MySpace uses Service Broker as a reliable messaging backbone to power the communications between +1000 databases, allowing them to scale-out and partition the user information into individual shards. Here are some more details about this architecture:

• MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Data
• Developing Large Scale Web Applications and Services(video)
• MySpace SQL Server Service Broker (slides)

Read the rest of this entry »

If you have an MDF file laying around and you don’t know what version is, how can you determine the version, preferably without altering the file? You could try to attach it to a SQL Server instance, but if you’re not careful the operation may end up upgrading the MDF file to that instance’s current version. Even if it doesn’t upgrade it because it happens to be the same version as the one supported by that instance, it can still run recovery on the database and thus alter the file. Is there a way to determine the version in a non destructive fashion?

Lets look into a database and dump a page. Not any page, but page 9 of the primary filegroup. This page happens to be the database boot page:

Read the rest of this entry »

There are probably no articles that I reference more often than Erland’s. There are questions on forums, discussion groups and stackoverflow that come back over and over, again and again. For some of these topics Elands has prepared articles that are, ultimately, the article to point anybody for more details on that topic. His coverage is deep, the articles are well structured, and they pretty much exhaust the topic. If somebody still have questions about that topic after reading the article, then I say that person knows too much about SQL Servero … or not enough. I’m writing this blog entry not so much as to announce his latest addition Slow in the Application, Fast in SSMS? Understanding Performance Mysteries (is hard for me to believe anyone that follows my blog does not know about them…) but more to create for myself a memo pad from where to pick up the link to the articles whenever I need them:

The Curse and Blessings of Dynamic SQL

Dynamic-SQL, the technique of generating and execute SQL code on-the-fly is sometimes irreplaceable, sometimes abused, and almost always done in a SQL-injection prone, dangerous, way. This article covers the pros and cons of this technique, when to use and when not, how to use it and what pitfalls to avoid.

Arrays and Lists in SQL Server

In lack of a true array type in the Transact-SQL language, developers have resorted to all sort of workarounds to pass sets of data as parameters to Transact-SQL procedures and batches: strings containing comma delimited items, XML variables, temp tables, table value parameters. Erland has a series of articles covering this topic in SQL Server 2008 (where table valued parameters are available), SQL Server 2005 (where XML data type became available) and SQL Server 2000 (the Dark Age).

How to Share Data Between Stored Procedures

All you need to know about how to share data between procedures and how to choose among the various solutions: table valued parameters, table valued functions, temp tables, XML data type, INSERT-EXEC, OPENQUERY, cursor variables, CLR.

Error Handling

Another series that covers error handling in Transact-SQL in SQL Server 2005 and later using TRY-CATCH blocks as well as SQL Server 2000 before TRY-CATCH blocks were available.

Giving Permissions through Stored Procedures

Code signing is, by a large margin, the perfect way to effectively grant granular control to non-privileged users for specific operations in SQL Server 2005 and later. This powerful mechanism is though seldom used, because of the arcane ‘black magic’ that goes into getting the sequence of operations correct to effectively sign a procedure, and because of the complication that arrise from the EXECUTE AS impersonation context required for signed procedures. This article goes to great length in shading some light on this esoteric topic.

Slow in the Application, Fast in SSMS?

This article describes parameter-sniffing, the process of ‘tuning’ a query plan to a specific value of the input parameters, and how this process can sometime ‘pollute’ the query plan cache with a plan that is optimized for a specific set of parameters, but performs poorly on other parameter values. The article describes what is parameter sniffing, how to identify when it happens, and how to fix it and prevent it.

In a previous article I have shown how it is possible to use efficient streaming semantics when Download and Upload images from SQL Server via ASP.Net MVC. In this article I will go over an alternative approach that relies on the FILESTREAM column types introduced in SQL Server 2008.

What is FILESTREAM?

FILESTREAM storage is a new option available in SQL Server 2008 and later that allows for BLOB columns to be stored directly on the file system as individual files. As files, the data is accessible through the Win32 file access API like ReadFile and WriteFile. But at the same time the same data is available through the normal T-SQL operations like SELECT or UPDATE. Not only that, but the data is contained logically in the database so it will be contained in a database backup, it is subject to ordinary transaction commit and rollback behavior, it is searched by SQL Server FullText indexes and it follows the normal SQL Server security access rules: if you are granted SELECT permission on the table, then you can open the file to read. There are some restrictions, eg. a database with FILESTREAM cannot be mirrored. For a full list of restrictions and limitations, see Using FILESTREAM with Other SQL Server Features. Note that SQL Server Express edition does support FILESTREAM storage.

Read the rest of this entry »