New Datatypes in SQL Server 2008

SQL Server 2008 has introduced 4 new datatype:

• Date and Time: Four new date and time data types have been added, making working with time much easier than it ever has in the past. They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
• Spatial: Two new spatial data types have been added--GEOMETRY and GEOGRAPHY--which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
• HIERARCHYID: The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
• FILESTREAM: FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that allows unstructured data to be stored in the file system instead of inside the SQL Server database.

Date and Time:

In SQL Server 2005 and earlier, SQL Server only offered two date and time data types: DATETIME and SMALLDATETIME. While they were useful in many cases, they had a lot of limitations, including:

• Both the date value and the time value are part of both of these data types, and you can’t choose to store one or the other. This often causes a lot of wasted storage (because you store data you don’t need or want); adds unwanted complexity to many queries because the data types often had to be converted to a different form to be useful; and often reduces performance because WHERE clauses with these data and time data types often had to include functions to convert them to a more useful form, preventing these queries from using indexes.
• They are not time-zone aware, which often requires extra coding for time-aware applications.
• Precision is only .333 seconds, which is often not granular enough for some applications.
• The range of supported dates is not adequate for some applications, and the range does not match the range of .NET CLR DATETIME data type, which requires additional conversion code.

To overcome these problems, SQL Server 2008 introduces four new date and time data types, which include:

• DATE: As you can imagine, the DATE data type only stores a date in the format of YYYY-MM-DD. It has a range of 0001-01-01 through 9999-12-32, which should be adequate for most business and scientific applications. The accuracy is 1 day, and it only takes 3 bytes to store the date.
• TIME: TIME is stored in the format: hh:mm:ss.nnnnnnn, with a range of 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 3 to 5 bytes.
• DATETIME2: DATETIME2 is very similar to the older DATETIME data type, but has a greater range and precision. The format is YYYY-MM-DD hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 6 to 8 bytes.
• DATETIMEOFFSET: DATETIMEOFFSET is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.

Prompting for parameter in SSIS

A question in MSDN forum drew my attention towards the missing functionality of

Prompting for parameters in SSIS

A question might struck in your mind that if we have it in SSRS than why not in SSIS. Well I am a great fan of Microsoft as they provide alternative for any thing under the sun (Sometimes SUN itself). In this post I would like to demonstrate how we can implement prompt for variable values in SSIS..

For the implemention, Let me consider a simple table having Name and Email Id.
Through SSIS package we will prompt for Name and accept Name from User.

CREATE TABLE [tblSSISPrompt]
(
[Name] varchar(15),
[Email] varchar(50)
)
GO
--Fill table with some data

INSERT INTO [tblSSISPrompt] VALUES ('Rahul', 'Rahul.Kumar@sqlsvrsol.com')
INSERT INTO [tblSSISPrompt] VALUES ('Prashant','Prashant.M@sqlsvrsol.com')
INSERT INTO [tblSSISPrompt] VALUES ('Mark','Mark.W@sqlsvrsol.com')
GO

SELECT * FROM [tblSSISPrompt]

Now we start with SSIS
A) Declare two variable
a) SqlStmt to store SQL query
b) Email to get Email from the resultset from Sql query


B) Use Script Task to prompt and prepare SQL query and store in SqlStmt variable
a) Specify SqlStmt as ReadWriteVariables


b) Code for prompt and prepare SQL query

Public Sub Main()
Dim Name As String

'Prompt
Name = InputBox("Enter Name", "Name Dialog")

'Prepare SQL query
Dts.Variables("SqlStmt").Value = _
"SELECT Email FROM tblSSISPrompt WHERE Name = '" + Name + "'"

'msgbox
MsgBox(Dts.Variables("SqlStmt").Value, , "Query")

End Sub

C) Use Execute SQL Task to execute the query



D) Execute the Package
a) As you execute the package, it will prompt for Name


b) Script will display query in Message box


c) Finally Execute SQL Task will execute the query

Congrats!!! thats Prompting for parameters in SSIS

If you want to do the same thing in C#, read first comment.

Zip and unzip files in a folder

Many a time we need to zip or unzip more then one file present in a folder. SSIS provides a easy way to handle this.

For my example I am using 7-zip though we have many other compress software like (pkzip or winzip)

Steps to zip/unzip files
A) Declare a variable FileName as string datatype


B) Use ForEach loop to loop through the files in the folder and map file names to FileName variable
a) Provide folder name inEnumerator configuration


b) map FileName Variable


C) Use Execute Process task to zip/unzip to individual files
a) Browse to Exe of 7z in Executable


b) Use expression to configure the argument


D) Execute the package


Use the files........

Returning numeric value in Execute SQL task

Yesterday night a question on MSDN fourm drew my attention towards a wired but ture fact that SSIS does not accept numeric value as double in Execute SQL task.

So how do we use our numeric value in Execute SQL Task

SSIS considers the numeric values as string when they are returned from Execute SQL task, so to use that value we have to follow two steps

1) Map the value to a String or Object datatype variable
2) Convert this value to double and store in other variable

Let me take an example to show how it works

I will consider below table for the example and extract maximum value of Num column
CREATE TABLE tblAbc
(
[Id] INT IDENTITY (1,1),
[Num] NUMERIC(10,4)
)
GO

INSERT INTO tblAbc VALUES (123.23)
INSERT INTO tblAbc VALUES (555.23)
GO
--query we will use to extract max of Num
SELECT Max(Num)as Num FROM tblAbc

Now its implementation
A) Declare two variables 1) objNum as object and 2) dblNum as double datatype



B) Use Execute SQL task to extract numeric value and map it to objNum variable


map result to objNum variable


C) Use script task to convert objNum value into double and assign it to dblNum variable
a) specify
Readonlyvariable as ObjNum
Readwritevariable as dblNum

b) Write below code as script

Dts.Variables("dblNum").Value = CDbl(Dts.Variables("objNum").Value
MsgBox(Dts.Variables("dblNum").Value, , "dblNum")

D) Excute the package


Now we have dblNum having our numeric value and can use it for any calculation purpose

Difference between Stored Proc and User-defined Functions

Today morning my close friend Prashanth (DotNet expert) asked difference between Stored Proc and Functions . So I am dedicating this post to him, also today is bithday of beautiful Buddu, his girlfriend.

Difference betweem Stored Proc and User-defined Functions

• Stored Procedure have pre-compiled execuction plan where as functions do not.
• Functions are used for computations where as procedures are mainly used for performing business logic.
• Functions can only have 'in' parameter where as SP can have both 'in' and 'out' parameters.
• Function does not allow DML (insert, update, delete) queries on an object where as SP allows.
• Functions can be used inline where as SP can't be.
• Stored Procedure can retun more than one value at a time while funtion returns only one value at a time.
• Functions MUST return a value, procedures need not be.

Please leave your comments..

Functions

With my personal experience of SQL Server, I believe Function (User defined functions) are under utilized then their potential so I thought of writing this post in complete honor of UDFs.

Microsoft SQL Server provides three programmable objects
a) User defined Functions b) Stored Procedure c) Triggers

" Functions are pre-prepared pieces of code that may accept parameters, and always return a value"

UDFs support the creation of rich programming logic, including looping, flow control, decision making and branching.
UDFs can be divided into two types based on the value they return
a) Scaler functions b)Table-valued functions

A) Scaler functions: Scalar functions accept 0 or more input parameters and return a single scalar value.
You use the CREATE FUNCTION Transact-SQL statement to create a function. The general syntax of the statement is as follows:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ EXECUTE_AS_Clause ]
}

Example from AdventureWorks database
CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
-- Returns the stock level for the product. This function is used internally only
BEGIN
DECLARE @ret int;

SELECT @ret = SUM(p.[Quantity])
FROM [Production].[ProductInventory] p
WHERE p.[ProductID] = @ProductID
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage

IF (@ret IS NULL)
SET @ret = 0

RETURN @ret
END;
GO

--Execute
SELECT
p.[ProductID]
,p.[Name]
, dbo.ufnGetStock(p.ProductID) as [Stock]
FROM Production.Product p;

Result


B) Table-Valued Functions: The only difference in table-valued functions is that they return a table as output. Therefore, they are generally used in the FROM clause of a SELECT statement and possibly joined to other tables or views.

Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <>
[ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

Example from AdventureWorks :
CREATE FUNCTION [dbo].[ufnGetContactInfo] (@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
[ContactID] int PRIMARY KEY NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[Email] [nvarchar] (150) Null
)
AS
BEGIN
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT
c.[ContactID]
,c.[FirstName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
WHERE c.[ContactID]= @ContactID;
END;

RETURN;
END;
GO
select * from ufnGetContactInfo(1209)
Result



C) Restrictions on UDFs
Although we can execute virtually any valid batch of code with in function, but still they do have some restrictions:
Most significant is you cannot use a function to change the state of any object in a database or the database itself.Therefore,you cannot insert, update, or delete data in tables, nor can you create, alter, or drop objects in the database. However, you can create one or more table variables and issue INSERT, UPDATE, and DELETE statements against the table variable


Please leave comment...

Views - the other face

To put it in a simple way : A view is a SELECT statement that has a name and is stored in Microsoft SQL Server.

Views act as virtual tables to provide several benefits.

a) Re-usability : A view gives developers a standardized way to execute queries, enabling them to write certain common queries once as views and then include the views in application code so that all applications use the same version of a query.

b) Security : A view provides a level of security by giving users access to just a subset of data contained in the base tables that the view is built over and can give users a more friendly, logical view of data in a database.

c) Performance : a view with indexes created on it can provide dramatic performance improvements, especially for certain types of complex queries.

We can categorize views as :
a) regular views, b) updateable views, c) indexed views.

A) Syntax of view creation
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

ENCRYPTION : Should View be encrypted
SCHEMABINDING : will not allow you to drop a table, view or function reference by this view without dropping the view
VIEW_METADATA : returns metadata about a view to client-side data access libraries.

Select statement can be of any complexity as long as it is a valid query with following exceptions

• should not have COMPUTE or COMPUTE BY clause
• should not have INTO keyword
• should not have OPTION clause
• should not reference a temporary table or table variable
• should not have ORDER BY clause unless it also specifies the TOP operator

Example of a view can be on AdventureWorks database
CREATE VIEW [vwEmployee]
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]


B) Modifying Data Through Views (UPDATEBLE VIEW)
Although views allows you to update data with some exception but it will be best practice if we use INSTEAD triggers.

Exception for data modification through VIEWS

• All changes must directly reference column, not derivations of a column
• View definition cannot contain GROUPBY or DISTINCT clause
• You cannot modify columns that are derived through aggregate functions (AVG,COUNT, SUM,MIN,SUBSTRING)
• You cannot reference columns generated by using operators such as union, crossjoin, and intersect.
• You cannot use TOP when you specify WITH CHECK OPTION

Example:
UPDATE [vwEmployee ]
SET [Phone] ='123-256-1685'
WHERE [employeeID] =1

C) INDEXED VIEW

An indexed view, also called a materialized view, causes SQL Server to execute the SELECT statement in the view definition. SQL Server then builds a clustered index on the view’s results, and stores the data and index within the database. As you change data in the base tables, SQL Server propagates these changes to the indexed view. If the result of the view could change from one execution to another or could change if different query options were set, the entire set of data SQL Server calculated and stored would be invalidated. Therefore, all the operators or functions that can cause varying results are disallowed.

Restrictions for index views

• View should be Schema bound
• The SELECT statement cannot reference other views.
• All functions must be deterministic. For example, you cannot use getdate() because every time it is executed, it returns a different date result.
• AVG, MIN, MAX, and STDEV are not allowed.

Example to create index on above view,
We will need to have it schema bound
ALTER VIEW [vwEmployee]
WITH
SCHEMABINDING
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]

Now we can create index as
CREATE UNIQUE CLUSTERED INDEX [IX_vwEmployee_EmpID] ON [vwEmployee]
(
[EmployeeID] ASC
)

Welcome

Hi,

Welcome to my blog which is solely devoted to SQL Server.

Good Luck and Cheers
Rahul Kumar
MCTS - SQL Server 2005