Relational Database Design:Simple rules for creating primary key

Introduction

Some years ago, I had some discussions with one of my friends about using defining the primary key. In the old days, as a software developer you had to implement your database first so most people though about how does the database works and how they can implement their solution in a suitable way.
In these days, developers use ORMs most of the time which is very good, but at the same time, they start to ignore how their code will implement inside the DB. So it is important to consider how your data will be translated by ORM and what will you have inside your DB afterwards.


Some Rules 
Well I think most people these rules, but there is no harm in mentioning them.

Primary keys should never change

Your RDBMS, uses keys to manage tables, sort them, find them and make relation between them and the most important key obviously is the primary key. If you ever try to change the value of the primary key, it will affect other related tables.

You cannot use a natural key or a key form other system

It is possible for natural keys to change, so you are obviously violation the first rule. You might say, oh someones ID will not change. But it is possible for the government the change the system of producing the ids then you have to change a lot of stuff.

They cannot have any formula

It is also a violation of rule 1, since you may need to change the formula in the feature.

The uniqueness has to be easy to maintain

Your RDBMS will prevent you from putting a duplicate inside your primary key, so if you are generating your key with a method that can create duplicates, you will have lots of problems that you cannot fix easily.

Use short but suitable key type

All RDBMS' use B+ threes as they index structure. They need to put your keys inside a table and fetch them so if you use a big key, your RDBMS can put less items inside a page and therefore, it has to access the disc more times  which is the bottle neck of every business application.

In SQL Server, number of index rows in each page can be calculated using this formula:
Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

Which and size of each row equals:

Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (for row header overhead of an index row) + 6 (for the child page ID pointer)

Considering the above, size of index row for int is
int (4 bytes): 4+3+1+6=14
Which means you can put 506 rows inside a page


What are choises

Considering the above, I will start with the worst one!

Never use (n)char or (n)varchar!

If you are using (n)char or (n)varchar for your key, I am almost sure that you are violating of all rules above, since no one will store a string key generated by his system :)
You will also need to worry about upper and lower case and the size of the key is also obviously big!

For a varchar(50) you have: (2+1+50)+3+6+1 = 63
Which means you have only 124 keys inside a page which is awful

Even for a varchar(20) you have: (2+1+20)+3+6+1 = 33
Which means you have only 231 keys inside a page which is still awful

And also, when you are creating your key, you will be vulnerable to concurrent requests. Like when 2 threads ask for a new key inside your application 

Less than 1% of the times use GUID

GUIDs are good data structure that can help you make sure that your key is unique. But at the same time, they are very big and they cannot be stored as a cluster index because there is no order in generating them.
The good fact about them is that they are easy to move because there would be no conflict. Also, some times, you have to create the key inside a code, then it is of course better to use a GUID to reduce the chance of generating the same key, but I would say, try avoiding them as you can.
For a GUID you have: (2+1+16)+3+6+1 = 29

Which means you have only 261 keys inside a page which is still bad


99% percent of the times use int with identity 

Int with the option of identity will help you to keep everything simple. It is very small and will take not much space. Use it with identity so you can make sure that the code will not generate a duplicate because of concurrent process. And it can also contain more than 2 billion different keys which is enough for most business systems.

Don't use small int or tiny int
Tiny int and small int are too small but their size doesn't have that much effect in comparison with integer.

For smallint (2 bytes): 2+3+1+6=12
which means you can put 578 rows inside a page which is 14% improvement but it can contain only 32,000 different values which is not that much

For tiny int(1 bytes): 1+3+1+6=11
which means you can put 622 rows inside a page which seems to be 7% improvement but since it can contain only 256 values :| you cannot use the other 366! :) 

Less than 1% of the times use Bigint
Ok! In very special projects, you might have a table that can contain more than 2 Billion rows! Like you are working for Amazon :) Then use bigint which I don't think happens for most developers in their professional life time :)

References:
Please find the formula for calculating size of rows, etc here in MSDN
And for size of variables for to this page also in MSDN

How to compare 2 Sql Server Databases with VS 2015

Introduction

There are some times that you need to check your DB, either the data or schema. Sometimes you have a back up and you want to see what has been added updated or removed from your data, like when you have an error that you cannot find the cause.
Sometimes, you want to see if someone has changed the schema and you want to know what is the change.

Solution 
It is an old problem right? But in the old days you had to pay lots of money to be able to do that.
Take a look at Red Gate SQL data compare or SQL Data Examiner . Yup you have to pay at least 300$ to do that. :|
But the good news is that your Visual Studio has the ability to Do a lot of Data and Schema comparison which is free and more importantly it is inside our great tool Visual Studio. :)

How to do it?
Inside Visual Studio 2015 (mine is professional) Go to Tools >SQL Server. you will see 2 options for comparing Data, or Schema.

Insert your connection Data, click compare and you are good to go :)

Search for a string in all tables, rows and columns of a DB

Several days ago, I needed to search my database for special string. Then I saw a very good query in here, but since it wasn't an article, I thought why not write a small post about it.

Code:

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_schema   SYSNAME,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'string to search'

DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL  -- Only strings have this and they always have it

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(@column_name) + ' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.' + QUOTENAME(@table_name) + ', ' + QUOTENAME(@column_name) + ''''

        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
END

CLOSE tables_cur

DEALLOCATE tables_cur

This query is very useful, specially when you don't know about the structure of a project. For instance, you need to check something in a CMS (lets say for debugging your code) like EpiServer but you know nothing about its structure, then finding your values will be a real pain.

Where my block has been used on server? A question that all back-end developer have in mind

The story started when I had a task to remove old blocks that we don't use any more. From a back-ender's point of view, I just needed to find all references in my code and make sure that it has not been used anywhere. But of course it is not correct when dealing with a CMS like EpiServer since they can be used in a ContentArea.

Yes, I know, we have also [AllowedType] attribute that we use in order to limit contentAreas, but still, there can be some items in blocks tree that has not been used in any page and may cause a problem if an editor click on them.

So what shall we do?

First, Every content in EpiServer will be saved in tblContent. if you want to make sure that there is no instance from your typename it is enough to check that table.Code:

SELECT c.*   FROM [dbo].[tblContent] c
inner join [dbo].[tblContentType] ct on c.fkContentTypeID = ct.pkID
where  [ModelType] like '%TypeName%'

But it is only 10% of the cases, besides where is the fun?!!!
I want to know how many instances of my type exists and where are they now? also I want to know how to find their parent in the tree. So I wrote this code:

Code:

declare @TypeName nvarchar(50)
set @TypeName = 'myBlockType'

select tbl2.contentName as contentName, tbl2.contentSegment as ContentSegment, tbl2.ContentGUID as ContentGUID,
con2pkID ContainerID,con2ContentGUID ContainerGUID, conLang.name as ContainerName,conLang.URLSegment as ContainerURLSegment, contype.Name as ContainerTypeName, 
contype.ModelType as ContainerModelType, con2.fkParentID as ContainersParentId, conContainerParentLanguage.Name as ContainerParentName, conContainerParentType.Name as ContainerParentName

from [dbo].[tblContentProperty] cp  
 inner join 
(SELECT  c.*, cl.name as contentName,  cl.URLSegment as contentSegment FROM [dbo].[tblContent] c
inner join [dbo].[tblContentType] ct on c.fkContentTypeID = ctpkID
inner join tblContentLanguage cL on cL.fkContentID = c.pkID
where  ct.Name like @TypeName)  tbl2 on cp.LongString like '%'+cast( tbl2.ContentGUID as nvarchar(50))+'%'
inner join tblContent con2 on con2.pkID = cp.fkContentID
inner join [dbo].[tblContentType] contype on con2.fkContentTypeID = contypepkID
inner join tblContentLanguage conLang on conLang.fkContentID = con2pkID
inner join tblContent conContainerParent on conContainerParent.pkID = con2fkParentID
inner join tblContentType conContainerParentType on conContainerParent.fkContentTypeID = conContainerParentTypepkID
inner join tblContentLanguage conContainerParentLanguage on conContainerParentLanguage.fkContentID = con2.fkParentID

This code will show you all instances of a type, and where they have been contained including typename and the address that we can find the parent in the tree.


The result will be something like this

But wait a minute! what if I can't find the container it self?!
Well, that is easy :)  the query is 90% the same, but we just need to search for the contentId (instance) instead of the type name.
Of course if you search for the containers type name, you will eventually find it, But it is easier to change 2 lines of code :)

Code:

declare @ContentGUID nvarchar(50)
set @ContentGUID = 'B6845FFC-5475-4FC3-C701-5A5D6FD5F967' -- put you content GUID in here 

select tbl2.contentName as contentName, tbl2.contentSegment as ContentSegment, tbl2.ContentGUID as ContentGUID,
con2pkID ContainerID,con2ContentGUID ContainerGUID, conLang.name as ContainerName,conLang.URLSegment as ContainerURLSegment, contype.Name as ContainerTypeName, 
contype.ModelType as ContainerModelType, con2.fkParentID as ContainersParentId, conContainerParentLanguage.Name as ContainerParentName, conContainerParentType.Name as ContainerParentName

from [dbo].[tblContentProperty] cp
 inner join
(SELECT  c.*, cl.name as contentName,  cl.URLSegment as contentSegment
FROM [dbo].[tblContent] c
inner join tblContentLanguage cL on cL.fkContentID = c.pkID
where c.ContentGUID like @ContentGUID)  tbl2 on cp.LongString like '%'+cast( tbl2.ContentGUID as nvarchar(50))+'%'
inner join tblContent con2 on con2.pkID = cp.fkContentID
inner join [dbo].[tblContentType] contype on con2.fkContentTypeID = contypepkID
inner join tblContentLanguage conLang on conLang.fkContentID = con2pkID
inner join tblContent conContainerParent on conContainerParent.pkID = con2fkParentID
inner join tblContentType conContainerParentType on conContainerParent.fkContentTypeID = conContainerParentTypepkID
inner join tblContentLanguage conContainerParentLanguage on conContainerParentLanguage.fkContentID = con2.fkParentID