RSS
 

Snagit…

21 Mar

If you read my last post, you may have noticed that my images now include boxes, balloon text and highlighting.  This is due to a friend insisting the he couldn’t live without Snagit for blogging or documentation and that I simply must try it.  I figured I’d sign up for the free trial and a week later… here I am, scribbling all over screen shots and sharing my lack of anything resembling artistic talent with the world.  So far… it does seem pretty useful.  Much better and easier editing of screen captures and it allows me to point things out a bit more readily. 

My question to all of you is… is there anything better out there that I should evaluate before I take the plunge? 

 
1 Comment

Posted in All

 

Random Thoughts on Aliasing

20 Mar

 

Here are many different thoughts/facts/preferences on aliasing.  Most of this applies to column aliasing.  Regardless of whether or not you like aliasing tables, you should still be aliasing columns.  If you do happen to be one of the people who is against table aliasing (some of you may be wondering "Who are these mythical people against table aliasing?", but trust me, they exist), keep in mind that three and four part names in the select clause is on the list of unsupported features in a future version of SQL Server as of SQL 2008.

 

Aaron Bertrand’s ( Blog | Twitter ) recent alias posts

Inconsistent Table Aliasing: Aliasing only some of the tables in a query or aliasing all of the tables and only some of the columns.

Meaningless Alias Names: Using meaningless alias names like A,B,C or T1,T2,T3.  (1 letter aliases are fine, this refers to ones that have nothing to do with the table.)

Problem caused by Inner Queries: A specific problem that can bite you when you’re not paying attention to alias names on inner queries.

The third one of these in particular saved me some time recently, as I had just read that post a couple weeks before, and although I was familiar with the concept, having just read his post about it pushed it to the front of my memory when a query I was writing didn’t make sense.  The second one I disagree slightly with.  I completely agree that you shouldn’t use meaningless alias names, but in the post he mentions that he doesn’t like using any alias names over 3 letters long.  Here I have a different stance, mainly because I like to:

Maintain table aliases across code in a database

I like to maintain alias names across all my queries (wherever possible) so that I know at a glance what tables I’m pulling from without having to read through the entire query.  It makes it easier for me to read my own code and lets me spot things that would normally be a little bit harder to see.  I’d definitely advise this practice.  If you use Redgate SQL Prompt, you can use the built in alias overrides to make it use the aliases you want.   Obviously, this only applies to tables that are frequently used.  If you have 3000 tables in your database, it’s doubtful you’re going to have preset aliases for all of them. 

SqlPrompt

In reality, I do have quite a few 1 and 2 letter alias names for my most commonly used tables, these are just examples.  I’d love to have all 1-3 letter aliases, but sometimes it’s easier for me to remember/use a slightly longer or more descriptive alias.  At a certain point, depending on the number of tables and such, 1-3 letter aliases can become just about as ambiguous and meaningless as T1.  My most common aliases are usually 2-5 letters with some oddballs going as far as 8 or so characters.

 

Aliasing Column Names within a Table Alias

Brad Schulz ( Blog | Twitter ) wrote a post a short while ago focusing on UNPIVOT.  What does that have to do with aliasing?  Not much.  His post wasn’t about aliasing at all, but it did offhandedly introduce me to something I’d never seen before.  He actually redefines COLUMN names in a table alias.  I had never seen this before.  While I haven’t really used it much yet; I still think it’s incredibly cool and figured I’d point it out.  You can basically do this:

SELECT TOP 10 O.TableName, I.IndexName
FROM (SELECT do.NAME, do.id FROM sysobjects do) O(TableName, TableID)
    INNER JOIN (SELECT di.NAME, di.id FROM sysindexes di) I(IndexName, TableID) ON O.TableID = I.TableID

While this might seem pointless to some (I could just as easily have created a CTE, aliased the columns within the derived tables or just renamed them in the select clause), I think it might be really useful in some cases.  Maybe it’s just because I’m still working with SQL 2000 on a daily basis and I constantly need to deal with nested derived tables.  Either way, now you know (or perhaps you already did, I might be the only person on the planet who didn’t know you could do this).

Columns Added to a Table Down the Road

I’m currently in the middle of implementing a large scale update to our primary ERP system.  The main tables are all controlled by a third party vendor.  We also write a ton of custom code over the top of these tables for external reporting and websites and such.  During this upgrade, I’ve noticed columns added to tables that they were not previously in.  For the most part this wouldn’t pose a problem, but in some cases, these additional columns could break queries due to ambiguity errors.  This is even more likely with more ‘general’ column names… such as ‘ref_num’.  This could happen at any time and will break queries if you did not alias the existing ref_num column (even though it was the only one there at the time).

Intellisense Filtering

Even if you don’t currently use the built in Intellisense in SQL, chances are that you will at some point in the future.  I’ve found that using aliases tends to make it much less annoying.  I say less annoying because it still bugs the crap out of me from time to time, but aliasing columns makes it less dumb.

 

ORDER BY and Ambiguous Column Names

The order by clause may demonstrate a slightly different variation of column name ambiguity breaking queries (depending on SQL version from what I can tell).  Due to the order that queries are processed internally, you can refer to columns in the select list by alias in the order by clause.  Observe the following example.

OrderByError

Removing I.name from the Select list will make the error disappear, even though both sysobjects and sysindexes have a *name* column.

 OrderByErrorGone

As you can imagine, this could lead to confusion on more complex queries and waste some troubleshooting time.  It’s also good to be aware of the way the order by is working in case you do something like this:

OrderByErrorGone2

(Note that this example is from a 2K8 database, the same type of query (that doesn’t use a DMV) fails in 2K for all the above examples.)

 

Group fields in the select list by the table they come from

This is just personal preference and not something that is likely to have any impact other than readability, but I find it makes queries a lot easier to deal with and to understand.  It also makes it easy to comment out all references to a table when you are troubleshooting queries.  I actually go so far as to add a comment above the group of fields from a particular table in really large queries, but again, purely personal preference.

 

In closing, a short totally unbiased checklist of some of the pros and cons of aliasing. 

ProConGrid

(And depending on whether or not you use some sort of Intellisense software, the ‘Against’ might not even be true!)

 
1 Comment

Posted in All

 

Tally Table CTE

19 Mar

Now that I have several posts on what you can do with a Tally table, I figured I’d share my favorite way to create one inline.  I still prefer to have a physical tally table (usually in a Utility database that can be accessed from anywhere and doesn’t need to be created in each individual database) for permament code, but for times when you need one on the fly, this is my preferred method.  I can’t really take the credit for this query, the base construct is based on something I’ve seen attributed to Itzik Ben-Gan.   I’ve modified it a bit and changed up the formatting to be the way I like it.  Anything over a few thousand rows I’d probably use a physical tally table for, but on small numbers you shouldn’t see much of a performance hit with this script.

-- Tally Table CTE script (SQL 2005+ only)
-- You can use this to create many different numbers of rows... for example:
-- You could use a 3 way cross join (t3 x, t3 y, t3 z) instead of just 2 way to generate a different number of rows.
-- The # of rows this would generate for each is noted in the X3 comment column below.
-- For most common usage, I find t3 or t4 to be enough, so that is what is coded here.
-- If you use t3 in ‘Tally’, you can delete t4 and t5.

; WITH
-- Tally table Gen            Tally Rows:     X2                X3
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 4            ,    8
t2 AS (SELECT 1 N FROM t1 x, t1 y),            -- 16            ,    64
t3 AS (SELECT 1 N FROM t2 x, t2 y),            -- 256            ,    4096
t4 AS (SELECT 1 N FROM t3 x, t3 y),            -- 65536        ,    16,777,216
t5 AS (SELECT 1 N FROM t4 x, t4 y),            -- 4,294,967,296,    A lot
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM t3 x, t3 y) -- Change the t3's to one of the other numbers above for more/less rows
 
No Comments

Posted in All

 

SCSUG March Meeting Recap

12 Mar

The Monsoon

My trip to the meeting, short as it was, involved some of the worst driving conditions I’ve ever personally encountered.  It was coming down in sheets and I was doing 40mph on I-95.  Before you roll your eyes about how I’m one of "those guys", note that only 2 or 3 people passed me for the several miles that I drove that speed, and they were still going under 50.  It was bad.  If I didn’t need to give a presentation at the meeting I’d probably have waited till later or skipped the evening.  Overall, I think the weather had a large impact on the attendance for this meeting(we only had 8 or 9 people), which is really a shame because it was a great presentation.  In any case , had a couple slices of Domino’s Pizza ("New recipe" apparently involves melting a couple sticks of butter over the top of the pizza.), gave a quick presentation of all the upcoming SQL/Dev events that I knew about(after quickly adding the Orlando Code Camp thanks to a comment from another member… pretty sad that I missed that one) and cleared the way for David Pless.

The PFE

I really enjoyed the presentation.  Tons of information (We didn’t even get through half of what he had slides for and he has offered to come and give us a part 2 to cover some more of it).  One thing I particularly enjoyed about the presentation it is that he makes an effort not to just throw out ‘best practices’; but explains the reasoning behind them and why they don’t always apply.  This is a very important element that is all too often overlooked.  SQL Server is so broad and there is so much to know that I think it’s common to know ‘to do’ something without actually knowing why.  Without that knowledge, you can’t make an educated decision on when not to apply the rule instead of the exception.  A couple things I took out of the meeting were:

  1. How to better diagnose server level performance problems and what not to use as symptoms.
  2. Auto Update stats can be set to asynchronous so that it doesn’t add the overhead to the process that triggers it.
  3. Using local variables in a query can impact the performance vs. hard coded values.  Adding WITH RECOMPILE can sometimes address this.  This is somewhat vague… it behaves somewhat similarly to parameter sniffing… but it’s not quite the same.  I intend to do some research on the specifics here when I have some time and will post what I figure out.

The World of Loud Music

After the meeting, we all(only 1 person didn’t come with) headed over to World of Beer to have a brew or two and some less formal banter.  When we weren’t going deaf from the obnoxiously loud outdoor speakers that they kept turning up, we had some great conversation.  Thanks again to David Pless for driving down and sharing his knowledge and experience with us.  I’d encourage anyone in the area to come on by for our next meeting on April 8th, where Herve Rogerro will talk about SQL 2005/2008 performance.  If you’re not in the area, I’d encourage you to see if there is an active users group near you.

 

Note:  I uhh… ‘borrowed’ the format of this post from Jack Corbett because I really liked it.  Thanks Jack!

 
No Comments

Posted in All

 

SCSUG (Space Coast SQL Users Group) Meeting Tonight Featuring PFE David Pless

11 Mar

In case you missed David Pless in Orlando on Tuesday, you have another chance to see his presentation tonight (Thursday March 11, 2010 6:30 PM EST) at the SCSUG meeting (Brevard County, Fl).

David will be speaking about how to troubleshoot performance issues on SQL Server 2005 and SQL Server 2008.

"In this discussion I cover performance impact analysis using DMVs, SET options in SQL Server, and Profiler and perfmon analysis…[as well as]… SQLNexus, PAL (Performance Analysis of Logs), RML Utilities, SQLDiag, and other tools. Finally I discuss reading query plans graphically and what you can learn from reading the XML plans."  — David Pless

The meeting is at the Space Coast Credit Union HQ off of Wickham Road in Melbourne, Fl.

More information on the Space Coast SQL Users Group is available here.

 
No Comments

Posted in All

 

Tally Table – Delimited list to Table

27 Feb

Dealing with delimited lists (Usually separated by a comma) in SQL is a problem easily handled by a simple function and a Tally Table.  (Tally tables are also often referred to as Numbers tables or spt_values tables.  If you still don’t know what that is, please see this excellent article on Tally tables written by my friend and SSC heavyweight Jeff Moden.)  This particular implementation is somewhat specific in nature but can give you an alternative to Dynamic SQL when you want to pass in a list as a parameter and do an IN in a Stored Procedure. The following function will take your delimiter and string and parse it into a table so you can do your IN.  (I’m leaving my standard header on the function in this case because there are some good notes in there.)

/*
=============================================================================================
CREATE DATE:     02/27/2010
LAST MODIFIED:    02/27/2010
CREATED BY:        SETH PHELABAUM
PURPOSE:        Splits a string based on a passed in delimiter and returns a table.
ISSUES:            Strings with extra 's will break this function, handle that on the end that calls it.
Notes:            To make it a simpler function, I removed the peice that trimmed spaces around commas.  Do
this before or after calling it.
Revision History:
Date     By        Change Made
-------- ---      -------------------------------------
=============================================================================================
GRANT SELECT ON TVF_TallySplit TO [Somebody]
SELECT * FROM TVF_TallySplit(',','Orange,Apple,Banana,Pear,Watermelon,Grape')
SELECT * FROM TVF_TallySplit('*','Orange*Apple*Banana*Pear*Watermelon*Grape')
DROP FUNCTION TVF_TallySplit
*/

CREATE FUNCTION TVF_TallySplit(
@Delim            CHAR(1),            -- List Delimiter
@String            VARCHAR(8000))
RETURNS TABLE
AS

RETURN(
SELECT SUBSTRING(@Delim + @String + @Delim,N+1,CHARINDEX(@Delim,@Delim + @String + @Delim,N+1)-N-1) ListValue
FROM Tally
WHERE N < LEN(@Delim + @String + @Delim)
AND SUBSTRING(@Delim + @String + @Delim,N,1) = @Delim )

What to do with this
Let’s say you have a table containing names of your favorite fruits.  (In case you were wondering… No, these aren’t my favorite fruits; they were just ones that immediately came to mind when writing this.  I don’t even like half of these.)

CREATE TABLE Fruits(
Name        VARCHAR(25))

INSERT INTO Fruits(Name)
SELECT 'Apple' UNION ALL SELECT 'Banana' UNION ALL SELECT 'Grapefruit' UNION ALL
SELECT 'Kiwi' UNION ALL SELECT'Tomatoe' UNION ALL SELECT 'Grape' UNION ALL
SELECT 'Orange' UNION ALL SELECT 'DragonFruit' UNION ALL SELECT 'Strawberry'

You then ask someone else what their favorite fruits are and want to see what fruits you have in common.  You might think you could just write a query for that like this:

DECLARE @YFFruits VARCHAR(200)

SET @YFFruits = 'Orange,Apple,Banana,Pear,Watermelon,Grape'
-- OR SET @YFFruits = '''Orange'',''Apple'',''Banana'',''Pear'',''Watermelon'',''Grape'''

SELECT * FROM Fruits WHERE Name LIKE (@YFFruits)
-- OR SELECT * FROM Fruits where Name IN (@YFFruits)

However, this won’t work because in all these cases SQL is looking for a single fruit named : ‘Orange,Apple,Banana,Pear,Watermelon,Grape’ not any fruit in what is really a list.

A common solution for this is to use Dynamic SQL which would make your query this:

DECLARE @YFFruits VARCHAR(200)
SET @YFFruits = '''Orange'',''Apple'',''Banana'',''Pear'',''Watermelon'',''Grape'''
EXEC('SELECT * FROM Fruits WHERE Name IN (' + @YFFruits + ')')

This works and will properly match up your fruits.

The above function allows you to accomplish your goal without Dynamic SQL with a query that looks like this:

DECLARE @YFFruits VARCHAR(200)
SET @YFFruits = 'Orange,Apple,Banana,Pear,Watermelon,Grape'
SELECT * FROM Fruits WHERE Name IN (SELECT * FROM Util.dbo.TVF_TallySplit(',',@YFFruits))

You can also simply join to the table instead of using the IN keyword which gives you more flexibility in your query writing.

DECLARE @YFFruits VARCHAR(200)
SET @YFFruits = 'Orange,Apple,Banana,Pear,Watermelon,Grape'
SELECT Fruits.*
FROM Fruits
INNER JOIN Util.dbo.TVF_TallySplit(',',@YFFruits)) T ON Fruits.Name = T.ListValue

Note that because I wanted to keep the function somewhat simple, it does not handle extra spaces around the commas.  Single quotes within the string will also break it which limits its usage somewhat.  If this is a concern for your implementation, you either need to replace the single quotes on both sides or use a different method.  Despite the fact that the example above uses a list of strings, in real life situations I use this mainly for lists of uniqueidentifiers or numbers where single quotes/spaces are never an issue.

 
1 Comment

Posted in All

 

Data Type Precedence and Implicit Conversions

21 Feb

In my last post, I noted that one of the biggest differences between ISNULL and COALESCE was the fact that ISNULL attempted to convert the second parameter to the data type of the first parameter where as COALESCE converted according to the Data Type Precedence table.  A reader requested that I go into more detail on what that means.  At first I wasn’t sure what I was going to explain, there didn’t seem like a lot to talk about once I linked the BOL article on Data Type Precedence(which I meant to do in my initial post but apparently never did).  After thinking about it for a while, I realized that one thing that isn’t really pointed out in the BOL page is what these implicit conversions can do to performance if you aren’t paying attention.   This post got a bit long.

This isn’t a topic that I’m real familiar with, so I had to do some research / tests of my own to write this.  I’ve had to fix the varchar/nvarchar one several times, but others I can’t truly explain.  Why does a char->varchar comparison not trigger an implicit conversion?  Honestly, I’m not sure.  My guess would be that the optimizer is simply smart enough to not do it, but as I said, that’s just a guess.  While attempting to find the answer to this online, I stumbled across a brilliant script written by Jonathan Kehayias that focuses on finding implicit conversions in the plan cache.

The examples below focus on non-numeric conversions.  I did a good amount of testing on different numeric conversions, and although I’ve read that SQL 2000 had specific issues, I was not able to easily duplicate this with the numeric types in any compatibility level with my 2K8 installation (so I left those examples out).  If anyone has any good examples of this behavior with numeric data types, I’d be happy to add them.

Test Setup: (Note that because I am dropping/creating a real table and user defined types, you should be careful which database you execute this against.  I would suggest creating a new one and executing it there)

--- Drop and Re-create User Defined Type
IF EXISTS (SELECT * FROM sys.types WHERE name = 'UDVC') DROP TYPE UDVC
IF EXISTS (SELECT * FROM sys.types WHERE name = 'UDNVC') DROP TYPE UDNVC
CREATE TYPE UDVC FROM VARCHAR(60)
CREATE TYPE UDNVC FROM nvarchar(60)

--- Drop and Re-Create Test Table
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'DTP' AND TYPE = 'U') DROP TABLE DTP
SELECT    TOP 100000 -- If you use too few rows, the performance differences aren't as apparent.
CAST(NEWID() AS nvarchar(60)) NVCCol,
CAST(NEWID() AS VARCHAR(60)) VCCol,
CAST(NEWID() AS CHAR(60)) CCol,
CAST(NEWID() AS sql_variant) SQLVCVarCol
INTO DTP
FROM Util..Tally --I keep a Tally table in a Utility Database named Util.
--Either change to the location of your tally table or use the other FROM statement below.
--FROM master..spt_values A CROSS JOIN master..spt_values B CROSS JOIN master..spt_values C

CREATE INDEX IX_NVCCol ON DTP(NVCCol)
CREATE INDEX IX_VCCol ON DTP(VCCol)
CREATE INDEX IX_CCol ON DTP(CCol)
CREATE INDEX IX_SQLVCVarCol ON DTP(SQLVCVarCol)GO

Tests:

-- Test 1: Compare varchar and nvarchar against varchar column.
-- These will show a massive difference because it must convert the varchar column VCCol to nvarchar to compare them
-- In the execution Plan, you will see that the first uses an index scan and the second uses an index seek.
SELECT VCCol FROM DTP WHERE VCCol = 'A'
SELECT VCCol FROM DTP WHERE VCCol =  N'A'

Test1

-- Test 2: Compare varchar and nvarchar against nvarchar column.
-- These also show a very small difference because nvarchar is higher in the precedence list and so it only has to convert 'A'
-- to an nvarchar rather than the entire column.
SELECT NVCCol FROM DTP WHERE NVCCol = 'A'
SELECT NVCCol FROM DTP WHERE NVCCol =  N'A'

Test2

-- Test 3:  Compare varchar and nvarchar against SQLVariant column.
-- Although this may seem very similar to Test 1, you won't see much of a difference here.  This is because sql_variant is not
-- is near the top of the DTP table (higher than varchar/nvarchar), so you only have to convert the single value.
SELECT SQLVCVarCol FROM DTP WHERE SQLVCVarCol = 'A'
SELECT SQLVCVarCol FROM DTP WHERE SQLVCVarCol = N'A'

Test3

[/cc_sql]– Test 4:  Compare varchar and sql_variant against varchar column.
– Here you get the massive difference you would expect because you must convert the entire column to a sql_variant.
– This one actually has a different plan all together and not just an index scan.
DECLARE @a sql_variant
SET @a = ‘A’
SELECT VCCol FROM DTP WHERE VCCol = ‘A’
SELECT VCCol FROM DTP WHERE VCCol =  @a[/cc_sql]

SQLVariantPar

-- Test 5: Compare User Defined Types (with bases of varchar and nvarchar) against varchar column.
-- This behaves exactly as Test 1 did.  Conversions seem to be handled like they would be if the data types were the base types.
DECLARE @a UDVC
DECLARE @b UDNVC
SET @a = 'A'
SET @b = 'B'
SELECT VCCol FROM DTP WHERE VCCol =@a
SELECT VCCol FROM DTP WHERE VCCol = @b

Test5

--Test 6:  Compare char and varchar against char column
-- There is not any performance loss here.  It seems like there should be, but at least in my tests there is not.
DECLARE @a VARCHAR(60), @b CHAR(60)
SET @a = 'A'
SET @b = 'A'
SELECT CCol FROM DTP WHERE CCol = @b
SELECT CCol FROM DTP WHERE CCol = @a

Test6

The varchar/nvarchar conversions can be especially painful / tricky when the code is being passed in from elsewhere.  One of the places that I’ve seen issues with this is LINQ to SQL.  It is entirely possible that it was just our setup that was mismatched (I wasn’t involved in that end of it), but I figured I’d throw it out there anyways.

 
No Comments

Posted in All

 

ISNULL() VS. COALESCE()

11 Feb

There are a lot of arguments about which of these to use.  In my opinion, they both have their place.  Here are a few facts about the two functions.

  • ISNULL can accept only two arguments where as COALESCE can accept any number of input parameters
  • ISNULL is not ANSI standard and is proprietary to TSQL.
  • With ISNULL, the datatype of the second argument is converted to equal the data type of the first argument where as COALESCE converts according to data type precedence. 
  • ISNULL is generally accepted to be faster than COALESCE.
  • COALESCE is a much cooler word and will usually earn you either an impressed glance or a blank confused stare when slipped into casual conversation. 

Here are a few examples that demonstrate some of the functional differences between the two conversion methods. 

declare @a varchar(5)
select ISNULL(@a, 'ISNULL Test 1')        -- Result: ISNUL
SELECT COALESCE(@a, 'COALESCE Test 1')    -- Result: COALESCE Test 1

declare @b tinyint
SELECT ISNULL(@b, 99999)                -- Result: **Error**
SELECT COALESCE(@b, 99999)                -- Result: 9999

declare @c char(5)
SELECT ISNULL(@c, 'a') + 'B'            -- Result: a    B
SELECT COALESCE(@c, 'a') + 'B'            -- Result: aB
 
2 Comments

Posted in All

 

IN and NOT IN

28 Jan

One of the most common mistakes made in T-SQL is thinking that these behave identically.  I’ve personally opened up a forum topic on it because I didn’t know what the difference was.  This post will join a small army of other places on the net devoted to correcting this misunderstanding.

They aren’t completely dissimilar; they behave exactly as you would expect them to… with the exception of NULL’s.  Because nothing EQUALS NULL (Dependent upon settings, see below) the difference in the internal logic matters.  Gail Shaw initially explained this to me when I asked the question on the forums and I wanted to use her explanation here, but I can’t seem to find it; so here’s my own version of an explanation:

When you use IN, you’re really saying "WHERE myvalue = ‘A’ OR myvalue = ‘B’ OR myvalue = NULL"
Your NULLS won’t cause the entire statement to fail because it’s only an OR.

When you use NOT IN you’re really saying ‘WHERE myvalue <> ‘A’ AND myvalue <> ‘B’ AND myvalue <> NULL “
This is where the problem arises.  Since a NULL in SQL is an unknown value, you can’t test = or <> on it and you get no results.  Without the NULL, you’d be fine.

Here’s a simple example to demonstrate. 

DECLARE @T TABLE(
Val varchar(5)) 

DECLARE @T2 TABLE(
Val varchar(5)) 

INSERT INTO @T(Val)
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL
SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'F' 

INSERT INTO @T2(Val)
SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL
SELECT NULL 

SET ANSI_NULLS ON
SELECT * FROM @T WHERE Val IN (SELECT * FROM @T2)
SELECT * FROM @T WHERE Val NOT IN (SELECT * FROM @T2)
SELECT * FROM @T WHERE Val NOT IN (SELECT * FROM @T2 WHERE Val IS NOT NULL)

This issue is further complicated by the ANSI_NULLS setting.  While I believe most people have this turned ON, the fact that it is an option introduces another variable into the mix.  NOT IN will not fail in the same way if you have ANSI_NULLS set to OFF.  (Try the above example again after changing ON to OFF)

 
No Comments

Posted in All

 

Windows LiveSynch

20 Jan

After a while, most people who work with SQL have accumulated quite a few scripts that they use for investigation, troubleshooting, commonly requested ad hoc queries etc.  I’ve seen people suggest a few different methods for keeping this ‘script library’ available on several different machines.  Some people recommend saving everything to an online storage repository so that it is available to any machine they log into.  This works, but it’s a lot of hassle to me.  Instead of just saving things normally, you have to constantly make sure you copy everything to this online box from every machine you use them on.  This is still a good option if you are constantly on public or random machines, but I find it unwieldy if I just need to synch between my laptops/desktop/work computers.

My favorite method for doing this is using Microsoft LiveSync (or FolderShare).  You install the client on each of the machines you normally need to access your scripts from, tell it which folders you want to sync… and you’re done.  Every time you create or modify one of the files in your watch folders from any of the computers, it is automatically updated on all the other computers that are subscribed to that folder.  You can specify multiple different folders (Subfolders off of the main folder are also synched and retain their structure) and choose which machines synchronize each one.  I like tools that just do what I need them to do with zero management, and that’s what this one does.  So long as I save everything underneath the synched folder, it is available on all my machines.

The only limitation I’ve found so far with this tool is that it can’t synch individual files.  I wanted to use it to maintain my chrome bookmarks, but haven’t figured out how to use it for that.  I have been told that there are other tools that do the same thing for non windows machines, but can’t remember what the names were.

 
No Comments

Posted in All

 
« Older Entries
Newer Entries »