Category: Blog

Hashing Collisions
One of the problems with hashing is that you can have collisions from values that are not very similar. This means that if you are using hashing as a way to identify similar values, you need to make further checks with the original data after the hash matches are gathered.

This post will show a few examples of the collisions that can occur if you use the CHECKSUM() or BINARY_CHECKSUM() functions.

If we examine this code:
```
-- Checksum
declare 
  @i varchar(200)
, @j varchar(200);

select @i = 'LE';
select @j = 'AAAAAAAAAAAAAAAALE';

select 
  Plaintext = @i
, checksum = CHECKSUM(@i)
UNION ALL 
SELECT
  Plaintext = @j
, checksum = CHECKSUM(@j);
GO
```
This returns a result like this:

Note that these two values are the same as far as the checksum hash goes.

If we switch to BINARY_CHECKSUM(), we can get similar results.
```
-- binary_checksum is no better
declare 
  @i varchar(200)
, @j varchar(200)
, @k varchar(200);


select @i = 'LE'
select @j = 'Ou'
select @k = 'MU'

select 
  Plaintext = @i
, BINARY_CHECKSUM(@i)
UNION ALL 
SELECT
  Plaintext = @j
, BINARY_CHECKSUM(@j)
UNION ALL 
SELECT
  Plaintext = @k
, BINARY_CHECKSUM(@k)
GO
```
While these two functions can be useful, you do have to be careful with the results. A matching hash from these functions does not mean that the source data is the same.
April 11, 2012
Enabling Contained Databases in SQL Server 2012
One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

Enable the Instance setting

There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

Once you click the script button, you’ll get this T-SQL:
```
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
```
This will enable containment for the instance and you’ll be halfway there.

Database Containment

The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in 2012. Again, please don’t click OK, but click script.

The code will appear as below:
```
USE [master]
GO
ALTER DATABASE [cdb2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
```
You can also set this value when you create a database:
```
-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
```
That’s it.

These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.
April 9, 2012
SQL Server 2012 Beta Exams
I signed up for a few of the SQL Server 2012 Beta certification exams, and have been going through them over the last few days. The exams I signed up for are:
I took the first one on Monday, and the second on Wednesday. I have the other two scheduled for Friday and next Wednesday.

I can’t really comment on what was in the exam, but I did see some evolutions of the testing software, which was nice. I like a few of the changes, and they should help people demonstrate a few more skills than the old multiple-guess exams. There are still multiple choice questions, but some new ways to ask someone to show knowledge.

I’m not really worried about being certified, but I do like to see how the certification process works, I might write some stuff to help people (I’ve worked on three cert books in the past) and most importantly, I get a rough idea of what I know on the exams. They’ll ask me a few things I might not have looked at in depth, and I certainly found a few holes in my knowledge over the last few exams.

If you have the chance to take a beta, I’d encourage you. They’re free, and the time they take can be a valuable aid in helping plan your future learning.
April 5, 2012

Using 2008 Features in a 2000 Compatibility Database

I saw a note recently from someone asking if they could use CROSS APPLY on a SQL Server 2008 instance with an older database in SQL 2000 compatibility mode. You can.

CREATE DATABASE sql2kCompat
;
go
ALTER DATABASE SQL2KCompat 
  SET COMPATIBILITY_LEVEL = 80
;
go

Once I have a database, I can access any of the newer views and DMVs. For example:

USE SQL2KCompat
;
go
SELECT 
 * 
  FROM sys.dm_database_encryption_keys
;
go

This doesn’t return anything because I don’t have keys, but I do get the headers. Now let’s add some data.

CREATE TABLE [Department](
   [DepartmentID] [int] NOT NULL PRIMARY KEY,
   [Name] VARCHAR(250) NOT NULL,
)
;
GO
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (1, N'Engineering')
;
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (2, N'Administration')
;
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (3, N'Sales')
, (4, N'Marketing')
, (5, N'Finance')
;
GO
CREATE TABLE [Employee](
   [EmployeeID] [int] NOT NULL PRIMARY KEY,
   [FirstName] VARCHAR(250) NOT NULL,
   [LastName] VARCHAR(250) NOT NULL,
   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
)
;
GO
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (1, N'Orlando', N'Gee', 1 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (2, N'Keith', N'Harris', 2 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (3, N'Donna', N'Carreras', 3 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (4, N'Janet', N'Gates', 3 ) 
;
go

I create a few objects, which are standard, but notice the third insert statement for the Department table. It uses the new insert syntax for multiple rows in one statement. That’s not legal in SQL Server 2000, but it works here.

Now I can use CROSS APPLY

SELECT * FROM Department D
 CROSS APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
;
GO

This returns me results, just as it does in a SQL Server 2008 database.

It appears that SQL 2008 functions work, which is what I’d hope would happen. However for the purposes of backwards compatibility, the functions that are from SQL Server 2000, should work as expected in SQL Server 2000.

The thing to be aware of is something that wasn’t legal in SQL Server 2000

SELECT
  q.sql_handle 
, t.text 
 FROM sys.dm_exec_query_stats AS q
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t

you get an error. Passing a column into a function wasn’t allowed in SQL Server 2000, so this is a problem.

And a little cleanup

USE MASTER
;
GO
DROP DATABASE SQL2KCompat
;
go

April 4, 2012

Category: Blog

Hashing Collisions

Enabling Contained Databases in SQL Server 2012

Enable the Instance setting

Database Containment

SQL Server 2012 Beta Exams

Using 2008 Features in a 2000 Compatibility Database