Other stuff

SQL stuff

October 2009 Blog Posts

Ladies and gentlemen, boys and girls, the STP is back to rock your world!
The new feature is SQL Snippets to speed up your development.
This new version brings one completely new option, full GUI redesign, completely rewritten installer, improved error handling and reporting, bug fixes and old features improvements.
Type in the assigned shortcut, press enter or tab and get the snippet you need.
It's quick, clean and saves time!
Some of more visible improved features include:
Completely new installer using the powerful Windows Installer XML (WiX) toolset. Now there’s only one MSI installer file for...

We all know how to find only duplicated rows in a table. Since SQL Server 2005 this became really simple using a ROW_NUMBER() window function like this: USE AdventureWorks
GO
;WITH cteDupes AS
(
-- find all rows that have the same AddressLine1 and City.
-- we consider those rows are duplicates so we partition on them
SELECT ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID) AS RN,
*
FROM ...

Simply put:
I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.
But let’s dig a bit deeper into each key type to see why this is. Here’s a little table with column names that tell us what kind of a key each column is.
Surrogate keys
A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data...