Avoid Running T-Sql Scripts In the Wrong Azure Database

There are problems with "USE" in Sql Azure. If you prefix an SSMS script with “USE {DatabaseName}” to make sure it runs in the right place the statement will either be ignored (you’re already in the database referred to) or raise an error. That will kill the current batch, but if there is a GO statement in the script any statements after it will still run, and of course that will be in the wrong database. What to do? Continue reading

Advertisements

The INSERT statement and Column Defaults in Sql Server

When you INSERT into a table which has a column with a default value defined, and you omit that column from your statement, what will happen? The default will be written. What if you write an explicit Null (and the column is nullable)? In nearly all cases the Null is written.

You may not want this – it’s one of the cases where it might be nice if there were more than one type of Null, as some people advocated in the early days. Then we could distinguish a “known unknown” (© D. Rumsfeld) Continue reading

Complex T-Sql Column Expressions? You must try this!

There’s an outstanding video by Kendra Little called “5 T-Sql Features You’re Missing Out On” which you can reach from my Links (Topical) page under “Database > Transact-Sql”. It describes a special way of using the CROSS APPLY statement in T-SQL. This is a technique that can change the way you write Sql, or at least it can in cases where you have an expression that is used in several columns of a SELECT statement. Continue reading

“Test-First” Database Development (2)

I’ve made additions and changes to the GitHub repository for “Test First” development of the database schema. All procedure names will now start with “Test”, so that they’re easier to find in Object Explorer and Intellisense. I’ve added a “TestHeader” stored procedure. You can put this at the top of your TDD script and it’ll add the date/time, database user, Sql Server version etc., to your results.

The spObjectExists procedure has been replaced by TestObjectExists, with the following signature:
CREATE PROCEDURE [dbo].[TestObjectExists]
@i_ObjectType sysname
,@i_ObjectName sysname = Null
,@i_ParentSchemaName sysname = Null --not needed if testing schema's existence
,@i_DbName sysname = Null
AS
Continue reading

“Test-First” Database Development (1)

I’m looking at the practicalities of using Continuous Delivery/Agile techniques where the database is a “high scale” project in Agile terms, and therefore relatively difficult (for more detail about this Agile terminology refer to Scott Ambler’s writings).

As well as thinking about the big issues, I want to start putting together some of the pieces to make it work. I’m doing this in Sql Server 2014 Transact-Sql. I’ve started with automated testing of reporting procedures. There may be utilities that can do this; finding suitable tools is part of the research. It still seems worthwhile having alternatives in Transact-Sql if possible. There’s no purchase or installation to approve, and the code is easy to understand and to edit. Continue reading

Sql Server Collation Cheat Sheet

This post is not written for an architect or DBA who has to choose a collation and wants an in-depth explanation, but for a developer (perhaps one with an error to fix) who needs a primer on the subject.

Put very simply, collations have to do with languages and with the rules used to compare characters. This is relevant in joins and sorting. You might think that a collation ought to be specified for the query; that in your sql statement you should determine the rules to use for comparing values. You can do that (although you’ll set it at column, not query level), but the columns in the tables have a collation property which is what the query uses by default. This is a convenience as it standardises comparisons and saves you having to specify the rules over and over again. It’s when the two conflict that you have an error which may make it necessary to override one or more default collations. Continue reading

OPENROWSET and BULK INSERT (3)

{Previous Article in Series}
{Next}
This is the promised post about using a Powershell script to inspect large files. It won’t be possible to open a very large file in Excel of course, and some text readers may struggle under the weight of data. This script is an alternative that runs under most if not all versions of Windows, and doesn’t need an installation or a licence. Continue reading