Welcome to Sign in | Join | Help

Browse by Tags

All Tags » Tutorials   (RSS)
I recently seen a discussion on the merits of using SQL Server as an application lock manager. Application would start a transaction and acquire app locks, using sp_getapplock. No actual data would be queried or modified in this transaction. The question Read More...
The SQL Server transaction log contains the history of every action that modified anything in the database. Reading the log is often the last resort when investigating how certain changes occurred. It is one of the main forensic tools at your disposal Read More...
If you are a developer writing applications that use SQL Server and you are wondering what exactly happens when you ‘run’ a query from your application, I hope this article will help you write better database code and will help you get started Read More...
SQL Server 2012 SP1 has shipped a great enhancement to XML: Selective XML Indexes. When properly used these indexes can speed up the searching of XML columns tremendously, at little disk/size cost: The selective XML index feature lets you promote only Read More...
The SMO object model for SQL Server ServiceQueue does allow one to enable or disable a queue, but the property that modifies the queue status is not intuitive, it is IsEnqueueEnabled: Gets or sets the Boolean property that specifies whether the queue Read More...
A recent inquiry from one of our front line CSS engineers had me look into how case sensitive collations decide the sort order. Consider a simple question like How should the values 'a 1', 'a 2', 'A 1' and 'A 2' sort? create table [test] ( [col] varchar(10) Read More...
The typical SQL Server activation procedure is contains a WHILE (1=1) loop and exit conditions based on checking @@ROWCOUNT. Error handling is done via a BEGIN TRY ... BEGIN CATCH block. This pattern is present in many Service Broker articles on the web, Read More...
Columnar storage has established itself as the de-facto option for Business Intelligence (BI) storage. The traditional row-oriented storage of RDBMS was designed for fast single-row oriented OLTP workloads and it has problems handling the large volume Read More...
LSNs, or Log Sequence Numbers, are explained on MSDN at Introduction to Log Sequence Numbers: Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, Read More...
You probably can easily answer a question like ‘What columns does this table have?’. Whether you use the SSMS object explorer, or sp_help, or you query sys.column, the answer is fairly easy to find. But what is I ask ‘What are the physical Read More...
Certain SQL Server query execution operations are calibrated to perform best by using a (somewhat) large amount of memory as intermediate storage. The Query Optimizer will choose a plan and estimate the cost based on these operators using this memory Read More...
Looking at this question on StackOverflow: Conversion failed when converting from a character string to uniqueidentifier error in SQL Server one can see a reproducible example where a string split UDF works fine in the SELECT statement, but it gives a Read More...
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 Read More...
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_BLOCKED EXPRESSION Read More...
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. Read More...
More Posts Next page »