Articles tagged

18 November 2014
18 November 2014

Bitwise Operations in T-SQL

How can you, in T-SQL, get a list of columns that have changed within a trigger? How can you see what bits are set within a varbinary or integer? How would you pass a bitmap parameter to a system stored procedure? Phil Factor shows how, introduces the bitwise operations, illustrates ways of peeking at bit-level operations, and explains the concepts behind the coding of integer datatypes.… Read more
07 October 2014
07 October 2014

Set-based Constraint Violation Reporting in SQL Server

When you're importing data into an RDBMS in bulk and an exception condition is raised because of a constraint violation, you generally need to fix the problem with the data and try again. The error won't tell you which rows are causing the violation. What if you've thousands of rows to search when it happens? There are solutions, writes William Sisson.… Read more
06 October 2014
06 October 2014

Questions about Primary and Foreign Keys You Were Too Shy to Ask

It is strange that one can ask simple questions about extended events or Hekaton at professional events and conferences without feeling embarrassed, yet nobody likes to ask vital questions about SQL Server primary keys and foreign keys. Once more, Rob Sheldon is 'drawn to one side' to answer those questions about keys that one is too shy to ask.… Read more
18 September 2014
18 September 2014

Filling In Missing Values Using the T-SQL Window Frame

Since SQL Server delivered the entire range of window functions, there has been far less justification for using the non-standard ex-Sybase 'Quirky Update' tricks to perform the many permutations of running totals in SQL Server. One of these related problems is the 'Data Smear'. Do window functions make this easier, and what is performance like? Dwain Camps investigates… Read more
12 August 2014
12 August 2014

Quickly Investigating What’s in the Tables of SQL Server Databases

From SQL Server Management Studio it is difficult to look through the first few rows of a whole lot of tables in a database. This is odd, since it is a great way to get quickly familiar with a database. Phil was persuaded to tidy up a SQL routine he uses to investigate databases quickly in a browser. He explains how to use it, how it works, and how to use it from PowerShell.… Read more
11 July 2014
11 July 2014

Calculating and Verifying Check Digits in T-SQL

A lot of numbers that we use everyday such as Bank Card numbers, Identification numbers, and ISBN codes, have check digits. As part of the routine data cleansing of such codes on data entry we must check that the code is valid- but do we? Dwain Camps shows how it can be done in SQL in such a way that it could even be used in a constraint, to keep bad data out of the database.… Read more
05 June 2014
05 June 2014

Row Versioning Concurrency in SQL Server

The optimistic concurrency model assumes that several concurrent transactions can usually complete without interfering with each other, and therefore do not require draconian locking on the resources they access. SQL Server 2005, and later, implements a form of this model called row versioning concurrency. It works by remembering the value of the data at the start of the transaction and checking that no other transaction has modified it before committing. If this optimism is justified for the pattern of activity within a database, it can improve performance by greatly reducing blocking. Kalen Delaney explains how it works in SQL Server. … Read more
29 May 2014
29 May 2014

On Comparing Tables in SQL Server

How do you compare two SQL tables? Every SQL Developer or DBA knows the answer, which is 'it depends'. It is not just the size of the table or the type of data in it but what you want to achieve. Phil sets about to cover the basics and point out some snags and advantages to the various techniques.… Read more
06 March 2014
06 March 2014

On Handling Dates in SQL

The calendar is inherently complex by the very nature of the astronomy that underlies the year, and the conflicting historical conventions. The handling of dates in TSQL is even more complex because, when SQL Server was Sybase, it was forced by the lack of prevailing standards in SQL to create its own ways of processing and formatting dates and times. Joe Celko looks forward to a future when it is possible to write standard SQL date-processing code with SQL Server.… Read more
14 January 2014
14 January 2014

Generating Test Data in TSQL

To test SQL, you need test data. There are usually many reasons why you can't use production data. Although it is usually enough to use a utility to generate test data, sometimes your requirements will compel you to resort to code to supplement this. Hugo shows how he used SQL and C# to generate large volumes of test data involving related columns and complex distributions.… Read more