03Jun 2009 by Grant Fritchey 2 Comments

ORDER BY Speed

T-SQL
I answered a question on SSC with the comment that while an INT would perform better than a DATETIME in an ORDER BY query, assuming each has a viable index, that the difference wouldn't be all that terribly substantial. Then I realized, maybe that's not true. So I ran up a quick test, just to see. First I created a little test table with the right indexes and loaded it with data: CREATE TABLE dbo.IntDate (IntCol INT NOT NULL, DateCol DATETIME NOT NULL); CREATE INDEX ixInt ON dbo.IntDate(IntCol); CREATE INDEX ixDate ON dbo.IntDate(DateCol); SELECT TOP 10000 IDENTITY( INT,1,1 ) AS n INTO #Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2; INSERT INTO dbo.IntDate ( IntCol ,DateCol) SELECT t.n, DATEADD(dd,- t.n,GETDATE() ) FROM #Tally AS t; DROP TABLE #Tally; Then I ran these…
Read More
29May 2009 by Grant Fritchey 3 Comments

It’s a major award

Misc
I try to shy away from humor in any of my posts because what I think is funny, others think is irritating or stupid (examples: I LOATHE Adam Sandler and I thought Hudson Hawk was funny. So there. I'm completely out of the main stream of America where comedy is concerned). I wanted to post something funny about getting an upgraded listing over at SQLRockStar's Blog Rankings. I've started it several times, looking to the Oscars and trying to  imitate Sally Fields or maybe that little Italian guy, toss in a Native American being really sombre & sad, a few streakers, an old guy doing push-ups (ohe-handed, even though he cheated a bit on the form, I still can't do those, even cheating)... None of it worked. I do appreciate wit, I just…
Read More
28May 2009 by Grant Fritchey 3 Comments

Spatial Index & Performance & Brain Pain

Spatial Data, T-SQL
In my previous post, Spatial Data Hurts My Brain, I showed how a query used the spatial index, but hurt performance. Several people jumped in to try to help out. Thanks to Bob Beauchamin, Isaac Kunin, Gail Shaw, and Valerie Yakich (via Twitter). I've definately learned a bit more than I knew previously about spatial data and spatial indexes. I've posted about it before, but it bears repeating, if you're just starting out, I can't recommend Alistair Aitchison's book, Beginning Spatial with SQL Server 2008, enough. Here's where I'm at. I think I was confusing/confused/confounded/something about what to expect from a spatial index. I'm going to present two queries and two indexes and try to explain why each works well, or not together, mainly as a means for enabling my…
Read More
27May 2009 by Grant Fritchey 13 Comments

Spatial Data Hurts My Brain

Spatial Data, T-SQL
I'm still barely scratching the surface working with spatial data in SQL Server 2008. We've ported some of the data into a table where we built a geography spatial data column and we're begginning to work with point data. The requirements from the developers are, so far, very simple. They'll feed me a point and I find all the locations "close" to it. We had to go round & round on what defines "close" but finally settled on, I think, 15km. The query to answer a question like this is ridiculously simple (a few object names have been changed): SELECT ebe.[Location].STDistance(@Location) AS Distance, ebe.[InterestId], ebe.[Location].Lat AS Latitude, ebe.[Location].Long AS Longitude, ebe.[OrgId] FROM dbo.[ebe] AS ebe WHERE ebe[OrgId] = @OrgId AND ebe.[Location].STDistance(@Location) < @CloseDistance I'm not even hard-coding the "close" value…
Read More
27May 2009 by Grant Fritchey 16 Comments

WHOOOP!

PASS, SQL Server, T-SQL
I just got the good word, I submitted an abstract for a spotlight session at the PASS Summit and it was accepted. Jazzed doesn't begin to describe it. If you're interested in hearing what I think about Best Practices for working with Execution Plans, please swing by.
Read More
20May 2009 by Grant Fritchey 5 Comments

Odd TSQL Behavior

SQL Server, T-SQL
Before I describe this, let me thank Lynn Pettis over at SQL Server Central for coming up with the answer. This morning a developer walked up and asked me what would happen if he ran a query that looked like this: SELECT 1.SomeString I said that he'd get an error. No, he says, try it. So I try it and I get this: Somestring 1 Try it yourself. It works just fine. I'd never seen that before and didn't have a clue what it was. Thinking that Microsoft had supplied some new short hand to define aliases I ran this: SELECT 'dude'.dudette Which resulted in the error:   Msg 258, Level 15, State 1, Line 1 Cannot call methods on varchar.   Which is what I would have expected. I…
Read More
14May 2009 by Grant Fritchey No Comments

Powershell Blog Worth Reading

PowerShell
I don't usually make a big deal about my blog roll. I usually add either people I read regularly or people who ask to be added. I don't have a hierarchy of belonging & all that (yet). But I want to point out one that I just recently put up. Chad Miller, creator of SQL Server PowerShell Extension, blogs about SQL Server & PowerShell. If you're not already reading his stuff, you should. Unlike me, he really knows what he's talking about.
Read More
14May 2009 by Grant Fritchey 4 Comments

More PowerShell Basics

PowerShell
Because of the data center move, we have a number of different functions that we're running on totally different sets of servers on different days. None of this is part of our normal maintenance routines, so I've been using it as a great opportunity to stretch a little with PowerShell. I'm still learning, a lot. The latest task was to get the databases of a list of servers backed up. I initially tried it using SMO. It works great. But it's serial across all the servers as well as across the databases. I'm fine with serial backups on the databases (very, very fine, I saw a parallel backup of all databases once, pretty sparks, like the Fourth of July) but I really wanted all the servers to get backed up…
Read More