T-SQL Archives - Page 21 of 26

27Apr 2009 by Grant Fritchey 1 Comment

Tim Ford’s Top 5 Indexing Best Practices

All I can really add to this is, yeah, me too. If you want some absolutely great advice on indexes, read this post. It's a must. And might I add, I've been the bad guy in Tim's example. Once, many, many years ago, I was reading from the SQL Server 7.0 documentation. It suggested that compound indexes were no longer needed since the optimizer could build them on the fly using index intersection. I had a performance problem and a consultant was telling me to use a compound index. I swore up and down it wouldn't work because Microsoft said so. He kept pushing and I kept pushing back. Finally, after a rather heated discussion in which I was convinced I had the upper hand, I got off the phone…

24Apr 2009 by Grant Fritchey 15 Comments

Unpacking the View

SQL Server, T-SQL

A view is simply a query that behaves something like a table. Most people know this. Most people also know that a view is simply a mask on top of what might be a very complex query. It all seems really simple. You call the view inside a simple query, the view runs the underlying complex query. Most people might not know that when a view is called and it gets sent to the optimizer, the optimizer unpacks the view and binds the component parts of the query necessary to create an execution plan that will return the data requested. What I didn't know until recently was that the optimizer is VERY smart. Not only does it unpack the query of the view, but it will change the query that…

21Apr 2009 by Grant Fritchey No Comments

Spatial Data

Spatial Data, T-SQL

I work for an insurance company. If you think that maybe, we might be interested in the physical location of the things we insure, you'd be right. Actually, we're an insurance company predicated on the idea that risk can be managed. That means that not only do we know where your factoryÂ is located. We know the wind zone, earthquake zone, flood zone, rain zone and temperature zone it's in. We send engineers out to the site to inspect it and recommend upgrades. We track the upgrades and the condition of your facility. With all that location specific information, just how important do you think it is that with SQL Server 2008 we're finally getting a spatial data type? Yeah, exactly. We're in the process of launching our first full implementation…

13Apr 2009 by Grant Fritchey 4 Comments

Paul Randal’s Database Size Survey

SQL Server, T-SQL

If you have three minutes to spare, swing by Paul Randal's blog and answer his survey questions about the size and distribution of your database. The results are very interesting. I was most interested in the number of respondents to each of the questions.Â As each size category switched, fewer and fewer people responded. However, a lot more people responded than I expected. 94 last I looked had databases under 10gb in size, but 42 had databases over 1tb. Yeah, that's only 1/2, but, holy cow, it's 1/2. I wish I had a database to manage that was over 1tb. Back in the 7.0/2000 days I was at a dot com that was getting close. When I left they had 700gb. I understand they got close to 850 before the…

11Apr 2009 by Grant Fritchey No Comments

SQL Server Execution Plans Published… Again

T-SQL

My first book is finally in print. OK. I know. It sounds funny. But my first book was printed in only a limited print run from Red Gate, most of which they gave away at Tech Ed last year. Then they offered it for free in an electronic form. Very few people got a printed copy. Well, if you were waiting around for the dead tree version, it's here! Thanks again to Tony Davis & Brad McGehee for all the work they did. It just wouldn't have been possible without them.

07Apr 2009 by Grant Fritchey 3 Comments

SQL Server Standard Article Available

PASS, SQL Server, T-SQL

Unfortunately PASS decided to put the SQL Server Standard to sleep right after I got an article published in it (and no, it wasn't my fault). However, PASS, being the service oriented organization that they are, has decided to make back issues of the magazine available online. So, to read the article I wrote comparing various methods of retrieving versioned data using different TSQL constructs, click this link. Go to page 14. Oh yeah, and you can see other peoples articles here too. There are other things coming out of the editorial committee soon (although I need to get one of them done myself... yikes).

07Apr 2009 by Grant Fritchey 6 Comments

NULL Is Not NULL

SQL Server, T-SQL

NULL as a concept seems so easy but it leads to so many problems for people. To put it as simply as possible, NULL does not equal anything. It does not "not equal" anything either. It can't be compared to other values in any way. There was a recent post over at SQL Server Central where the user had a query problem that one of the great people over at SSC solved, handily. They also pointed out that the bit of code being used "WHERE nt.NullableString NOT LIKE 'null%'" was also a problem. The user insisted that it was eliminating the NULL values. Well, yeah, sort of, it was, but not because it was actually applying a filter to the NULLs. Remember, a NULL does not equal or "not equal"…

26Mar 2009 by Grant Fritchey No Comments

INNER JOIN Experiment in Order

SQL Server, T-SQL

And a new blog. Christopher Stobbs, one of the frequent posters over at SSC decided to try his hand at blogging. His first post is up and it's pretty interesting. He ran an experiment with all different orders of tables, largest to smallest, smallest to largest to see if the order in the joins made a difference. For the answer, click

13Mar 2009 by Grant Fritchey 7 Comments

Disabling Database Encryption

T-SQL

SQL Server 2008 introduced TDE (either Total Database Encryption or Transparent Database Encryption, I've seen both) which is a way to get a passive encryption in place on the database to prevent people from stealing backups or detaching files & stealing them. It works with a Certificate in the server, and there are mechanisms around password protection of the Certificates, management etc. Setting it up & turning it on are very easy. This example is from a chapter on a book I'm working on: USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Chapter8Backup'; GO CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = 'Chapter 8 Certificate' GO USE AdventureWorksLT GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate GO ALTER DATABASE AdventureWorksLT SET ENCRYPTION ON GO…

10Mar 2009 by Grant Fritchey No Comments

Code for 2008 Transact-SQL Recipes

T-SQL

I've been reading through JosephÂ Sack's book "SQL Server 2008 Transact SQL Recipes" for the past few days. Especially since he saved my bacon so thoroughly by being the technical editor on my new book (and might I add, the technical editor on the other book, Brad McGehee, saved my bacon as well and bacon is very important to me). Anyway, the code is availablefor the SQL Recipes book. If you've even looked at the book, the shear volume of code samples is amazing. Having Joe save you the typing is a second service (the first being the book itself).

Category: T-SQL