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
It is easy to import Excel data into database tables via TSQL, using OLEDB, either by the OPENROWSET function or linking to the spreadsheet as a server. The problem is that there are certain things that aren't obvious that you need to know about, and you feel awkward about asking such simple questions. … Read more
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
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
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
Nobody seems to ask questions about SQL Expressions in forums, even though expressions can cause all sorts of problems. Even the books on T-SQL skate over them in haste to get to more complicated topics. It is time for frank, straight-forward Q&A, and who better than Robert Sheldon to give the A?… Read more
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
Although SQL Data Types seem to cause a lot of grief for database developers and can be tricky in their use, we seem to be expected to know all about them, and so it is embarrassing to ask questions about them in forums. Rob Sheldon continues in his mission to answer all those questions that we hesitate to ask.… Read more
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
There are some aspects of tables in SQL Server that a lot of people get wrong, purely because they seem so obvious that one feels embarrassed about asking questions. Robert Sheldon reckons that no questions about SQL Tables are off-limits, and deserve frank answers. … Read more
NEO4J, the graph database, can be used to provide answers that are very tricky for relational databases, including providing diagrams to show how SQL tables relate to each other, and the shortest chain of relationships between two tables, as David Poole demonstrates… Read more
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
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
Although SQL Server supports XML well, XML's little cousin JSON gets no love. This is frustrating now that JSON is in so much demand. Maybe, Phil suggests, it is possible to leverage all that XML, and XPath, goodness in SQL Server to produce JSON in a versatile way from SQL Queries? Yes, it so happens that there are plenty of alternatives.… Read more
Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google. Besides the obvious Full-Text search, there are plenty of techniques for finding that pesky data that resists the normal SELECT blandishments. Phil Factor describes some alternative techniques.… Read more
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
There are a number of real-life reporting tasks in SQL that require a 'gaps and islands' analysis. There are a number of techniques around that work, but finding ones that scale well makes for a tougher, but interesting, challenge.… Read more
Window Functions in SQL greatly simplify a whole range of financial and statistical aggregations on sets of data. Because there is less SQL on the page, it is easy to assume that the performance is better too: but is it? Dwain gets out the test harness to investigate.… Read more
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
