Archive for February, 2012

SQL Server 2012 (a.k.a Denali) – New feature | FileTables

February 28, 2012 7 comments

In my [previous posts] I discussed about new features of Denali, now SQL Server 2012.

Here, in this post I’ll talk about a new feature that I explored recently, when I was working with creating tables in SQL Server 2012. I observed a new item in object explorer that took my attention and created eagerness to explore it, and that new thing is FileTables.

As per MS BOL, The new FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.

In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications. The FileTable feature builds on top of SQL Server FILESTREAM technology.

–> Let’s see how can we use this new feature by a simple example below:

USE [master]

-- Create a new Database with Filestream enabled:
	NAME = N'newFileStreamDB', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\DATA\newFileStreamDB.mdf' , 
	SIZE = 5120KB , 
	NAME = newFileStreamGroupFiles,
	FILENAME= 'D:\SQL_Server2012\FileTables\Files', 
 LOG ON ( 
	NAME = N'newFileStreamDB_log', 
	FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER12RC0\MSSQL\DATA\newFileStreamDB_log.ldf' , 
	SIZE = 1024KB , 
	MAXSIZE = 2048GB , 
	DIRECTORY_NAME = N'FileTables' 

-- Use the new Database:
USE [newFileStreamDB]

-- Creating a new FileTable
CREATE TABLE [dbo].[firstFileTable] 
	FILESTREAM_ON [newFileStreamGroup]
	FILETABLE_DIRECTORY = N'myfirstFileTable', 


So, what all objects it creates under this new FileTable:

1. The table is created with following predefined Columns:

2. with Indexes:

3. with Constraints:

4. It also creates a FK reference key:


–> Now, when checking under Object Explorer under Tables you won’t see any table (even after refreshing it). But there is a new folder named FileTables, expand it and you can see this table there, shown below:


–> Right click on it and select ‘Explore FileTable Directory’, it will open the folder as shown below:

Here, I’ve manually created 3 files, 1 notepad, 1 powerpoint and 1 word doc. You can also copy files from other locations and paste/drop them here. As you paste/drop files here, SQL Server internally updates the [firstFileTable] file table.

–> We can check the table by simply issuing SELECT statement on the table:

SELECT * FROM dbo.firstFileTable

-- Final Cleanup
DROP TABLE dbo.firstFileTable


I liked this new feature very much as this will ease the work and reduce the overhead of maintaining files with databases.

In my [next post] you can check how you to use Full Text Search with files stored in FileTables.


Best Practices while creating Stored Procedures

February 26, 2012 8 comments

Stored Procedures in SQL Server provides lot of benefits compared to single/multiple adhoc SQL statements. They help in achieving consistent implementation of logic across applications and improve performance. They also provides us flexibility to design desired business rules and logic by combining various SQL statements, control flow IF-ELSE statements, WHILE loops, and other programming constructs.

Thus to design them it requires a generic approach and some best practices that comes with experience as you work with them. I’m listing some of the best practices that must be followed everytime you create Stored Procedures:

1. SET NOCOUNT ON: Always use ‘SET NOCOUNT ON’ statement at the begening of your code inside the SP to reduce unnecessary network round trips.

2. PARAMETER SNIFFING: Do not use SP parameters directly within the WHERE clause of SQL statements. This may cause the case of Prameter Sniffing. To avod this assign the parameter values to local variables and then use them with SQL queries.

3. Use table variables: Try to use table variables instead of temporary tables inside the SP to cache small record sets whenever possible.

4. Use of Temp Tables: If you think that the temporary record set may exceed upto millions of rows, then try using temporary table instead of table variables. Also try creating indexes on temporary tables if required.

5. Avoid using CURSORS: This will make your SP slow and degrade the performance. Try using more effecient SET based approaches instead of using CURSORS.

6. Use TRY-CATCH block: for error handling, more info [link].

7. Using Transactions: Use Transactions by using BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION blocks to make sure that your actions follow ACID properties [link]. But keep the transctions as short as possible to create less blocking in database and thus avoiding deadlocks.

8. Aliasing objects: Do not use SQL Server reserve keywords while naming SQL Server objects, like for: tables, columns, views, etc. Although it allows most of the time, but its better to use other and better naming conventions.

9. Avoid GOTO stmt: Do not use GOTO statements in your code as it is considered a bad programming practice (and in every other programming language). Try comming up with a better approach/logic, use conditional IF-ELSE logic, WHILE loops and other programming constructs.

10. Avoid ‘sp_’ prefix : Do not prefix the SP name with ‘sp_’. If it begins with ‘sp_’ then the compiler will first search it in master database and then in the current one, thus delaying the execution.

11. Use fully qualified objects name: in your SQL queries, this helps in quickly finding the cached/compiled plan. Also execute the SP by using its fully qualified name, like: EXEC dbo.SPname

12. Use WITH ENCRYPTION: You can also use ‘WITH ENCRYPTION’ option while creating SP to hide the code.

13. Add Comments: Try to put comments wherever possible to give details and idea to other developers what actually your code is doing.

14. Use BEGIN-END code block: Try to bind your whole SP code within BEGIN-END block. Although it is optional but it looks good and gives a better picture.

16. Beautify your code: Well indent your code by uisng TABs, Spaces and New line (ENTER). Try going vertically downwards with your code rather expanding horizontally. This will make your code easy to read and more understandable to other developers.

17. Use following template to create your SP:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

You can also generate this template, in Object Explorer goto Database -> Programability -> Stored Procedures: Right click and select ‘New Stored Procedure…’. The above code will get generated.

>> Check & Subscribe my [YouTube videos] on SQL Server.