Yesterday was a big day in our household.  In addition to our two resident mothers, we also commemorated our eldest son's sixteenth birthday.  Earlier in the week, after a delicate negotiation, we agreed on a blended celebration dinner at the...( read more )

I had a question from a reader about contradiction detection. The basic idea is to determine that this kind of query: SELECT * FROM TABLE WHERE col1 > 5 and col1 < 0; ...is utterly meaningless and requires no work to be done. It will always return zero rows. In fact, in many cases the SQL Server QP will detect cases like this and actually remove the table from the query completely. In its place, you have this magical "constant scan" operator, which is really just an in-memory row generator... [read more]

Recursion in Common Table Expressions (CTE’s), how does it work? How can I use it with the agancy list model (parent / child columns)? In this blog entry I show how recursion works, how to use it with the agency list model and talk about other aspects and uses for CTE’s; I also demonstrate some of the areas where CTE’s aren’t that good and how to help remedy that performance penalty suffered. This weekend I’ve been at the Scottish Developers Conference in Glasgow – a great day, I gave my making ... [read more]

This post will (hopefully) close a thread which runs here and here . When we last addressed this issue, I mentioned that an answer to the operator precedence question for DTS raised by Scott R. would depend on either my research or the kindness of others....( read more )

I have talked in the past about how passwords for SQL logins are protected in SQL Server (see this post ). I would like to describe this scheme in a more generic way and compare it with the alternative of encrypting the passwords, because I have seen people wondering which method they should use. First, what is authentication? Authentication is the process we go through to verify the identity of a user. It should not be confused with authorization, which is about what actions we allow an already... [read more]

This is the 96th edition of the weekly review of database blogs, Log Buffer

Have you ever written something and then lost it... for whatever reason: your own stupidity ( come on we've all accidentally done something at some point where we lost data or a spreadsheet or a document or something... ), the software eats it ( this might be self-inflicted but I've been in apps that just hang and that's it... there's nothing you can do except power off ), or....whatever. Well, during those times... have you ever thought - I'd do almost anything to get that data (and time) back?... [read more]

One of the things I really enjoy when doing performance tuning on 2005 (I still work on a mix of several SQL 2000 and some SQL 2005) is the sys.dm_db_missing_index* DMVs. As the query processor evaluates queries, it detects if that specific query could benefit from an index and how much it expect that index would reduce the cost (in terms of IO), exposing these information as views that we can query. I won't delve in the structure of it, but you can use my procedure sp_dba_missingindex as an... [read more]

SQL Server 2008 has not yet been released but nevertheless there are already some test results from the CTP versions. Do note that the performance will probably improve a bit when the final product arrives but the results are already quite amazing. They can be found on https://www.microsoft.com/sqlserver/2008/en/us/benchmarks.aspx . One phrase that will probably catch your eye is "Load 1TB of data in less than 30 minutes using ETL tools", now that is a world record. Apart from the performance im... [read more]

I'll be giving a talk at the Philly.NET Code Camp on 5/17/2008 . The Single Responsibility Principle . Dependency Injection . Inversion of Control . If you don't already know and apply these concepts in your day-to-day development this talk is for you. The talk will present a very easy learning curve into these topics. We will cover the "why" as well as the "how." We will take a simple application written without these techniques and transform it step-by-step. While we will see that these things... [read more]

After a long while I've finally managed to create a release version. I've also added some cool new features. It is completely free and has no expiration date like the prior beta versions. I've also changed the RSS feed to Feedburner to which you can subscribe to at: http://feeds.feedburner.com/SsmsToolsPack If you're subscribed to the old feed please change it. In SSMS Tools Pack 1.0 you can find these features: - Uppercase/Lowercase keywords : Set all keywords to uppercase or lowercase letters.... [read more]

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design the DBCC SHRINKFILE operation is a single-threaded operation that means you cannot define or configure the server to use multiple CPUs or a dedicated CPU. So troubleshooting the performance problem... [read more]

In this post James Kovacs is talking about becoming a ReSharper Jedi. Recently I attended a training class given by JP Boodhoo who is probably Obi Wan to these guys, if not Yoda himself. I admit to being completely overwhelmed for most of the week, not the least reason being the obsession with this topic - Keyboard=Good Mouse=Bad. I was shocked that someone would do training this way - after all, I was there to learn programming concepts, and that was greatly hindered by the fact that I just cou... [read more]

This may be old news, but it's also the kind of "old news" that's actually becoming more and more relevant these days. ;) More than a month ago the Microsoft SQL Server documentation team have announced a new Live Search Macro: The SQL Server 2008 Books Online Scoped Search macro. Those of you who've found the 'old' one useful, must have been anticipating the 'new' one.

The nomenclature used for referencing software releases and updates can be quite confusing. Let's consider an example to illustrate what the various terms mean. As a new version of a product is being developed, it may be made available to select customers and community members for early testing. This is sometimes called alpha builds of the product. As development progresses and the product becomes more and more polished, it's provided to a wider audience. This used to be called beta releases; fo... [read more]

JumpStart 2008 is an event held in Seattle in February. All the content delivered there is now available for everyone (after a quick registration). http://sqlserver2008jumpstart.microsofttraining.com/content/DownloadMaterial.asp?CcpSubsiteID=69&section=Overview Here are some examples of what's waiting for you there: OS01 - Executive Update - Winning Business Today and Tomorrow Play session View PowerPoint OS02 - SQL Server 2008: The Data Platform for Software + Services Play session View... [read more]

This morning's post on Operator Precedence was a little misleading, as Scott pointed out in his comment: The example expression highlighted in green above (A <> 0 AND B / A > 1) is said to evaluate differently in SQL 2000 and SQL 2005, but I...( read more )

In this post from last week, I gave an example of a query with a conversion where the optimizer pushes the conversion below a join. The result is that the conversion may be run on rows that do not join which could lead to avoidable failures. I ran this query on SQL Server 2005. After I published that post, a reader pointed out to me that my example query generates a different plan on SQL Server 2000: CREATE TABLE T1 (A INT, B CHAR(8)) INSERT T1 VALUES (0, '0') INSERT T1 VALUES (1, '1') INSERT T1... [read more]

I have always told this in my classes to my students: managing a database is just like driving a car... it means everyone drives a car but few have a proper knowledge of driving rules, they just drive because they learnt how to do it and the rest is pure instinct... and this sometimes can lead to accidents. In the database world it is exactly the same, both developers and administrators can manage a database, they just need to install the software, suggest a model using their intuition and load ... [read more]

[UPDATED 9 May 2008; the information presented doesn't exactly answer Greg's question. There's an update here ; and DTS is discussed here ] I received an inquiry yesterday from Greg Husemeier, who I met when he came to Redmond for the SQL Ranger program....( read more )

I recently found out that we are not properly checking for data corruption in our "Integrity Checks" SQL job. I thought that a SQL job would fail if the job step that runs DBCC CHECKDB returned errors, but apparently that's not the case. Check out Paul Randal's blog for more information. To properly check for data corruption via a SQL job, you should raise an error if @@ERROR does not equal zero. In the next few days, I'll rewrite my isp_DBCC_CHECKDB stored procedure to include this. I will be t... [read more]

As most you already know, Notification Services is not part of Microsoft SQL Server 2008. I think this is a shame since SSNS is really a great product. It may be rather complex at first glance, but it's a great product nonetheless. I'll probably devote a blog to it's abrupt deprecation at some point in the future. In the meantime, if you're preparing to upgrade to SQL Server 2008, you should verify that you don't have any rogue SSNS instances running on any of your servers. I hope your environme... [read more]

This may be the typical situation within your database environment where the you might have killed a SPID (Process) that has been running for long time, without knowing the ROLLBACK operations for such processes. Say if you are executing a stored procedure...( read more )

Looks like the WinDBG team was busy over the weekend and posted a new build of WinDBG: 6.9.3.113 . The big fixes look like squashed bugs and performance improvements in symbol server portions. I bet you could drop those DLLs into your Visual Studio directories and get the benefit there as well.

Here's an obscure piece of SQL you may not be aware of: The "ALL" option when using a GROUP BY. Consider the following table: Create table Sales ( SaleID int identity not null primary key, CustomerID int, ProductID int, SaleDate datetime, Qty int, Amount money ) insert into Sales (CustomerID, ProductID, SaleDate, Qty, Amount) select 1,1,'2008-01-01',12,400 union all select 1,2,'2008-02-25',6,2300 union all select 1,1,'2008-03-02',23,610 union all select 2,4,'2008-01-04',1,75 union all select 2,2... [read more]