February 2013 | SansSQL

Sunday, February 24, 2013

Hide System databases and objects

Did you know that the System databases and objects can be hidden in SQL Server Management Studio?
This is not a new feature though. The option to hide system databases and objects was present in SQL Server 2000 as well.
Before Hiding

Procedure To Hide:
  1. Open SQL Server Management Studio (SSMS)
  2. Click "Tools" and Choose "Options"
  3. Expand "Environment" and click on "General"
  4. Select the Check box "Hide System objects in Object Explorer"
  5. Click "OK"
  6. For the changes to take effect, you have to restart the SQL Server Management Studio
  7. Close and reopen the SQL Server Management Studio.
After Hiding:

Saturday, February 23, 2013

Database Normalization

What is Normalization?

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. 

Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. For Example, A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database. 

What is an "inconsistent dependency"? 

While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken. 

Rules for database normalization

There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications. 

As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. 

First Normal Form

  • Eliminate repeating groups in individual tables.
  • Create a separate table for each set of related data.
  • Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data. 
For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2. 
What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

Second Normal Form

  • Create separate tables for sets of values that apply to multiple records.
  • Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key (a compound key, if necessary). 
For example, consider a customer's address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer's address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third Normal Form

  • Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table. 

For example, in an Employee Recruitment table, a candidate's university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key. 

Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible inter-field dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. However, many small tables may degrade performance. 

Other Normalization Forms

Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but are rarely considered in practical design. Disregarding these rules may result in less than perfect database design, but should not affect functionality.


Un-normalized table:
Item Color 1 Color 2 Price Tax
Pen Black Blue 10 0.5
Scale Silver Yellow 2 0.2
Pen Black Blue 10 0.5
Bag Grey Black 150 10
This table is not normalized because,
  •   There are multiple color columns.
  •   Duplicate records exists or no primary key.
First Normal Form (aka 1NF):
Item Colors Price Tax
Pen Black 10 0.5
Pen Blue 10 0.5
Scale Silver 2 0.2
Scale Yellow 2 0.2
Bag Grey 150 10
Bag Black 150 10
This table is now in 1NF.
Multiple Color columns are merged into one column

Second Normal Form (aka 2NF):
Item Price Tax Item Colors
Pen 10 0.5 Pen Black
Scale 2 0.2 Pen Blue
Bag 150 10 Scale Silver
Scale Yellow
Bag Grey
Bag Black
These tables are now in 2NF.
The price and tax depends on Items but not on color. 
"Item" is Primary Key.

Third Normal Form (aka 3NF):
Item Price Price Tax Item Colors
Pen 10 10 0.5 Pen Black
Scale 2 2 0.2 Pen Blue
Bag 150 150 10 Scale Silver
Scale Yellow
Bag Grey
Bag Black
These tables are now in 3NF.
Tax depends on the Price and not on the primary Key "Item"

Friday, February 15, 2013

T-SQL Query to get the list of files in a folder

Here is a T-SQL Query to list all the files in a folder. This uses a undocumented extended stored procedure to get the details.

DECLARE @Path nvarchar(500) = 'E:\Test' --Change the path

 (FileNames nvarchar(500)
  ,depth int
  ,isFile int)

EXEC xp_DirTree @Path,1,1

SELECT FileNames from @FindFile where isFile=1

Tuesday, February 12, 2013

T-SQL Query to find Index size of all tables

This query gives results in 2 parts by making use of a undocumented stored procedure sp_MSIndexSpace
  1. The size of each individual index of a table
  2. The total size of index on a table
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name='TempIndexSpace')
DROP TABLE tempdb..TempIndexSpace
CREATE TABLE tempdb..TempIndexSpace 
(ObjectName nvarchar(100)
 ,IndexID int
 ,IndexName nvarchar(100)
 ,[IndexSize(KB)] int
 ,Comments nvarchar(max))
exec sp_msforeachtable 'INSERT INTO tempdb..TempIndexSpace (IndexID,IndexName,[IndexSize(KB)],Comments) EXEC sp_MSIndexSpace [?];
UPDATE tempdb..TempIndexSpace SET ObjectName=''?'' WHERE ObjectName IS NULL'

-- This gives output per index
SELECT * FROM tempdb..TempIndexSpace

-- This gives output per table
SELECT  ObjectName,SUM([IndexSize(KB)]) AS [Index Size (KB)] FROM tempdb..TempIndexSpace
GROUP BY ObjectName