=tg= Thomas Grohser - Category: "Tips&Tricks"

Filtered Index with WHERE col IN (...) clause

=tg= — Thu, 14 Jul 2011 10:27:00 +0000

I was asked if it is possible to create an filtered index with a WHERE col IN (…) clause and then use WHERE col = n with n being one of the values of the (…) list So let’s create a small sample create table idx ( ID int NOT NULL IDENTITY(1,1) primary key clustered, Data int ) insert into idx (Data) values (1) go 100 insert into idx (Data) values (2) go 2 insert into idx (Data) values (3) go 2 create index idx1 on idx(ID) where Data in (2,3) select ID from idx where Data in (2, 3) -- Index is used select ID from idx where Data = 2 -- Table scan select ID from idx where Data in (2, 3) AND Data = 2 -- Table scan -- But that’s not because SQL is not smart we created the index wrong drop index idx1 on idx create index idx1 on idx(ID) INCLUDE (Data) -- if the data we filter on is not in the index SQL has to do a lookup on the table where Data in (2,3) -- and therefore chooses to make an full table scan select ID from idx where Data in (2, 3) -- Index is used select ID from idx where Data = 2 -- Index is used !!!! THATS what we wanted select ID from idx where Data = 1 -- Table scan as expected

Just deployed my first SQL Azure database.

=tg= — Fri, 28 Jan 2011 15:39:53 +0000

Quiet an adventure as with all IT related things: SQL Azure is almost like SQL Server. Emphasis on the almost. But after a little fighting I got it running. Time will tell us how well it is going to run.

My tip to all is to have a deployment script that only contains what you realy need and not a generated ones that specifies each and every default option. Why: SQL Azure does not know many of the default options and therefore the script fails in that case.

How to assign a text containing a single quote to a char, varchar, nchar or nvarchar valiable or colum

=tg= — Thu, 27 Jan 2011 12:12:00 +0000

Sometimes the need to assign a text like "She's the boss" to a text column or variable arises.

DECLARE @MyText AS NVARCHAR(100) = 'She's the boss';

You can already see on the syntax coloring that something is wrong. To put a single quote inside a string literal you must precede it with another single quote

DECLARE @MyText AS NVARCHAR(100) = 'She''s the boss';

If you run the whole batch below

DECLARE @MyText AS NVARCHAR(100) = 'She''s the boss';
SELECT @MyText;

You will see it produces the desired result: She's the boss

To give you another example the text ‘I am between single quotes’ would look like this

DECLARE @MyText AS NVARCHAR(100) = '''I am between single quotes''';
SELECT @MyText;