SQLblog.com - The SQL Server blog spot on the web

Fun With Batches - Make Your Query Fail

John Paul Cook — Thu, 02 Jul 2009 01:46:00 GMT

It's a common practice for people who write T-SQL scripts to test each statement one at a time before finally running the entire script. This can lead to unexpected surprises. When each statement in a script is executed one at a time, each is separate batch. If there are no GO statements, the entire script executed as whole is a single batch. Let's look at some code. /* Execute the following three queries as a batch. The third query will fail. The error message says #test exists even though the previous...(read more)

SSIS 2008 and the New Lookup

ejohnson2010 — Wed, 01 Jul 2009 21:33:44 GMT

SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read my mind because SQL Server 2008 has a new shiny Lookup transformation that is, in my opinion, much improved. In 2005, the lookup had cache options, but they were really heavy handed. And you could deal with a lookup not finding a match by using the...(read more)

Why is IntelliSense not working?

AaronBertrand — Wed, 01 Jul 2009 18:05:00 GMT

I see people complain that IntelliSense (new in SQL Server 2008's Management Studio) is not working. Most of the time, it is for one of two reasons: The object is not in the local IntelliSense cache, since it was created recently. You can fix this easily by going to Edit > IntelliSense > Refresh Local Cache, or CTRL+SHIFT+R. The query window is connected to a downlevel server (e.g. SQL Server 2005), where IntelliSense does not function (see Connect #341872 from Whitney Weaver for more info...(read more)

Why have we not seen TPC-C and TPC-E benchmarks using SSD storage?

jchang — Wed, 01 Jul 2009 16:48:00 GMT

Dell recently published a TPC-H report for the PowerEdge T610, 2 x Xeon 5570, with 4 FusionIO 80GB SSD storage devices at 100GB scale factor. So why have we not seen TPC-C or TPC-E OLTP benchmark results published? Now it is much more feasible to run the TPC-H data warehouse benchmark on SSD because the Scale Factor 100 size is still allowed, for which the Line item table is 100GB for data only, not indexes or other tables. The full SF 100 tpch database is about 170GB for all tables and indexes....(read more)

When CHECK constraints using UDFs fail for multirow updates

Alexander Kuznetsov — Wed, 01 Jul 2009 14:08:00 GMT

I have made up a simple example when a CHECK constraint that uses a UDF should succeed but always fails. Suppose you need to move some stuff in your car, and you don't want to load more than 100 pounds into your small car at a time. Here is the DDL: CREATE SCHEMA Data AUTHORIZATION dbo ; GO CREATE SCHEMA Readers AUTHORIZATION dbo ; GO CREATE TABLE Data.ItemsToMove ( TripNumber INT NOT NULL, ItemName VARCHAR ( 50 ), ItemWeightInPounds FLOAT ); GO CREATE FUNCTION Readers.TotalWeightPerTrip ( @TripNumber...(read more)

Making Presentations Accessible for Colorblind Individuals

John Paul Cook — Wed, 01 Jul 2009 11:26:00 GMT

As I prepare for my SQL PASS 2009 presentation "Solving the Bill of Materials Problem", I'm trying to make sure I use colors that are visible to people with colorblindness. I've found two tools to help accomplish this. Colorlab available at http://colorlab.wickline.org/colorblind/colorlab/ renders the standard web color palette as seen by people with various color vision deficiencies. Vischeck http://www.vischeck.com/vischeck/vischeckImage.php allows you to upload your own images (e.g., PowerPoint...(read more)

CodeCampSA (Adelaide) is on again this year

Greg Low — Wed, 01 Jul 2009 03:47:00 GMT

One of the smaller events I try to get to each year is the Code Camp that happens in Adelaide ( www.codecampsa.com ). It might be smaller but I always enjoy it and I'm sure that Peter Griffiths and David Gardiner will be putting on a good show again this year. I'd love to see as many of you there as possible. It's July 18th and 19th in Adelaide. Details are at the site: www.codecampsa.com . Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!...(read more)

Best Thing I Learned at PASS Summit

drsql — Wed, 01 Jul 2009 01:29:14 GMT

I have learned many things at every PASS Summit I have attended. First off, all of the people who are speakers are just human beings like you and me (including everyone's favorite Kalen Delaney). I learned that it is really expensive to put on a conference like PASS, and I have especially learned to enjoy six dollar bagels and four dollar cans of coke, while they still exist. And I have learned that speaking in front of 200-2000 people isn't nearly as scary as it initially seemed. I have even learned...(read more)

Best Thing that I learned at PASS

RickHeiges — Tue, 30 Jun 2009 17:55:00 GMT

I remember my first PASS Summit in Denver in January 2002 (Delayed from Sep 2001 in Orlando due to Sep 11). One of the last sessions that I attended was led by Fabian Pascal. He runs a website called www.dbdebunk.com and is rather entertaining at times. At the time that I attended the Summit, I was teaching at High Point University. At many academic institutions, practicality doesn't count. Perhaps I did not realize when I was listening to Fabian at the time, but it soon became clear to me why vendors...(read more)

Best Thing I Learned at PASS Summit

Jonathan Kehayias — Mon, 29 Jun 2009 19:12:00 GMT

I have only been to one PASS Summit and it was the one in November 2008. If you had asked me last year this time about attending a large convention like PASS Summit, TechEd or SQL Connections, I would have told you to save your money for something more useful. A few things conspired together last year that got me to attend Summit. First Microsoft decided to do the SQL Hero's Contest in conjunction with PASS Summit and the Extended Event Manager I wrote was selected as a finalist in the contest. Second,...(read more)

SQL storms from your data grids

Linchi Shea — Mon, 29 Jun 2009 17:41:00 GMT

As organizations start to deploy various data grids into their application layers, they may start to experience what I’d call a SQL storm —a massive number of simple SQL requests that are issued over a large number of concurrent database connections at a high frequency. This is not about SQL viruses or denial of service attacks. But the end result is similar. On the SQL Server machine, the CPU usage is being pegged to 100% for a sustained time period, and the applications that share the SQL Server...(read more)

Best Thing I Learned at PASS

AllenMWhite — Mon, 29 Jun 2009 11:35:00 GMT

At the 2003 PASS Summit in Seattle the organization had a "SIG Challenge" that involved developing a database solution to a business problem. They assigned people to teams and allowed us to work on the problem only when sessions weren't occurring, but we could start as early as 6:30am and work as late as 11:30pm. My teammates and I took advantage of those long hours. We didn't win, but we became fast friends, and meet up every year (when possible) at the PASS Summit, and even met in Hamburg last...(read more)

The Best Thing I've Learned at PASS

AaronBertrand — Sat, 27 Jun 2009 15:30:00 GMT

I have attended PASS for several years, and discovered at my very first event that this is an extremely valuable conference to attend. The actual technical tidbits I’ve learned from attending sessions and having conversations with my peers, PASS officials and Microsoft employees alike have been priceless, but far too many to mention. Besides, how could I pick one item among so many? To be quite honest, the best thing I’ve learned has not been anything technical at all, but rather the mere and simple...(read more)

Connect digest : 2009-06-27

AaronBertrand — Sat, 27 Jun 2009 15:09:00 GMT

It was slim pickings this week. One item I thought deserved some attention was one that was filed a year ago today, and the rest are collectively related items involving ... ========================= Clustering Enhancements #353984 : Add support for multi-subnet clusters This is one that has been lacking since SQL Server first started supporting clusters: clustering across subnets. Obviously it can be desirable to have failover events cross rack or even data center boundaries. I am sure there are...(read more)

SAN vs. Disk Arrays: It goes a long way to be slightly more specific!

Linchi Shea — Fri, 26 Jun 2009 21:45:00 GMT

In the SQL Server communities, it's common to hear people talking about HP SAN, EMC SAN, 3Par SAN, and so on as if there were such things as HP SAN, EMC SAN, etc. Technically, SAN stands for Storage Area Network, but can be, and has been, used in two different ways. First, outside the storage communities, people often view everything beyond the drive at the OS level as the SAN with no regard to how that SAN is architected or configured as long as that drive is presented from some kind of SAN infrastructure....(read more)

Windows Live Writer – Good or Bad?

Andrew Kelly — Fri, 26 Jun 2009 21:27:00 GMT

<Updates at bottom of post> OK this will probably start a fire storm from Die- Hard Live Writer (LW) geeks but oh well :). I don’t blog as much as I would like and one reason is that I have had so much trouble getting posts properly formatted that it takes way too much time or frustration than it is worth for me anyway. I asked around a while back for the proper tool to use and everyone said “Live Writer” so that is what I started using. Well in the beginning I had an awful time getting images...(read more)

Cleaning up Un-Named Defaults

Andrew Kelly — Fri, 26 Jun 2009 16:07:00 GMT

I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to clean them up and since there were about a hundred I didn’t want to create all the cleanup code by hand and wrote a little script to generate the DROP &...(read more)

Spooky: What do you connect to?

TiborKaraszi — Fri, 26 Jun 2009 14:12:00 GMT

I only recently discovered that SSMS will connect to different things. For instance, press the "New query" button. What were you connected to? The answer is the same server as your "current" server. But what is the current server? It is the server where you happened to have focus when the pressed the "New query" button. So, can you say whether you had focus in a query window, Object Exporer or Registered Servers? This also applies to when you double-click a .sql file. And it doesn't stop there. Open...(read more)

Customizing the TCP Port for the Dedicated Administration Console (DAC)

Jonathan Kehayias — Fri, 26 Jun 2009 04:30:56 GMT

If you have a multi-instance SQL Server 2005/2008 Server that is behind a firewall, and you want to be able to remotely connect to the DAC, you have to create firewall rules that allow the TCP ports for the DAC through the firewall. You can easily set the port that the SQL Server Instances listen on using the SQL Server Configuration Manager. However, it doesn't include the DAC in the tool. I had this problem over two years ago and ended up giving up on finding a solution to being...(read more)

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates

Alexander Kuznetsov — Fri, 26 Jun 2009 02:36:00 GMT

Surely this sounds like common knowledge, yet I decided to post a simple benchmark demonstrating how slow they are - in this case almost 100 times slower than a foreign key. Recently Pure Krome asked a question on stackoverflow about implementing subtypes in the database and the discussion lead to such benchmarking. The DDL for parent and child tables Here is the parent table: CREATE TABLE dbo.Animals ( AnimalId INT NOT NULL IDENTITY PRIMARY KEY , AnimalType TINYINT NOT NULL, -- 1: Mammal, 2:Reptile,...(read more)

The Hidden Costs of INSERT EXEC

Adam Machanic — Thu, 25 Jun 2009 20:19:00 GMT

INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision--seemingly purely aesthetic--can bring to the fore. To illustrate the first of these examples I will be using the following table: USE tempdb GO CREATE TABLE dbo.MyTable ( [SalesOrderID] [int] NOT NULL,...(read more)

What to do when Management Studio hangs?

AaronBertrand — Thu, 25 Jun 2009 19:52:00 GMT

I have had a few occasions where I have been using Management Studio, and am suddenly and bluntly informed by Windows that I am demanding too much of the application. Windows is probably right; and it is only partially due to the fact that SSMS can be a resource hog at times, and partially due to the fact that I could have 30 or 40 active query windows at any one time. So what ends up happening is the app stops responding, and "(Not Responding)" gets painted onto the title bar. The temptation is...(read more)

Table restore and filegroups

TiborKaraszi — Thu, 25 Jun 2009 10:49:00 GMT

The story usually goes something like: Q - How can I restore only this table? A - Put it on its own filegroup and you can do filegroup level backup and restore. The problem with above answer is that it most likely misses the point. We need to ask ourselves: Why do you want to do a table level restore? The answer to the question is very often that the table need to be reverted to an earlier point in time, possibly because some accident happened; like deleting all rows in the table by mistake. (See...(read more)

Using user_settable Event in Extended Events

Jonathan Kehayias — Wed, 24 Jun 2009 21:42:00 GMT

A question came to me on Twitter today from Jason Hall regarding the user_settable Event in SQL Server 2008's Extended Events. You can find the event in the the sys.dm_xe_objects DMV with the following query: SELECT * FROM sys.dm_xe_objects o WHERE o.name = 'user_settable' AND o.object_type = 'event' What first struck me as odd is I couldn't find this event in the Extended Events Manager , and it is because the Event has no Keyword associated with it. There are actually three Events...(read more)

DrillThrough Actions and (semi) Security in SSAS OLAP cubes

AlbertoFerrari — Wed, 24 Jun 2009 15:52:11 GMT

Using SSAS 2005/2008, there is no way to apply security to DRILLTHROUGH actions, so we cannot decide whether a specific user is authorized or not to perform a specific action. This is a “by design” behavior, since DRILLTHROUGH actions are initiated at the client side, we can decide whether a user can perform or not DRILLTHROUGH but, once we let him do it, he can query anything he wants. Nevertheless, if we are not concerned with security but only with user experience, there is a simple trick to define...(read more)