Friday, November 18, 2011

SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can be achieved by creating an in-line table valued function. Let’s see how to convert a commonly used view HumanResources.vEmployee in AdventureWorks to a parametrized view.

CREATE FUNCTION PV_GetEmployeeInformationBySSN
(     
       -- Add the parameters for the function here
       @NationalIDNumber VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(
       -- Add the SELECT statement with parameter references here
       SELECT   e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,
              p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,
              pnt.Name AS PhoneNumberType, ea.EmailAddress,
              p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,
              sp.Name AS StateProvinceName, a.PostalCode,
              cr.Name AS CountryRegionName,
              p.AdditionalContactInfo
       FROM            HumanResources.Employee AS e INNER JOIN
              Person.Person AS p
              ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN
              Person.BusinessEntityAddress AS bea
              ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN
              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
              Person.StateProvince AS sp
              ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
              Person.CountryRegion AS cr
              ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN
              Person.PersonPhone AS pp
              ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
              Person.PhoneNumberType AS pnt
              ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN
              Person.EmailAddress AS ea
              ON p.BusinessEntityID = ea.BusinessEntityID
       WHERE e.NationalIDNumber = @NationalIDNumber
)
GO

How to use it. Very Simple :)
SELECT * FROM PV_GetEmployeeInformationBySSN ('112457891')

Wednesday, November 16, 2011

SQL Server 2012: A New More Flexible Create Index Dialog box

With every new version SQL Server is becoming more power full yet more users friendly. Today, while creating index on SQL Server 2012, I have found that Index Creation Dialog box is become more easy to use.
In prior versions, more irritating thing for me (at least), was naming a non-clustered index. Now dialog box suggests you more meaningful name. Secondly dialog box contains two tabs to add key columns and include columns.


In prior versions, a common error which I have always faced is that key column can’t be listed as include column. 
Now in SQL Server 2012, Index Create Dialog box will automatically disable check boxes for those columns which are already added as key columns. Similarly, if a column is already added as include column it will become disable on key column selection tab.

Remaining options are almost same to SQL Server R2, while COMPRESSION option is removed.


------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

Tuesday, November 15, 2011

SQL Server: TOP…WITH TIES a Beauty of TSQL


TOP clause is commonly used to get top required rows from a result set. Beauty of this clause is that it can be used with WITH TIES clause, to retrieve all similar rows to base result set.
According to BOL “WITH TIES Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.
For example from following simple table I need to get records which have minimum purchase date value. In first method we will use common IN clause.

--Create temporary table
CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)
--Insert few rows to hold
INSERT INTO #MyTable
SELECT '11/11/2011', 100 UNION ALL
SELECT '11/12/2011', 110 UNION ALL
SELECT '11/13/2011', 120 UNION ALL
SELECT '11/14/2011', 130 UNION ALL
SELECT '11/11/2011', 150
--Get all records which has minimum purchase date (i.e. 11/11/2011)
SELECT * FROM #MyTable
WHERE Purchase_Date IN
       (SELECT MIN(Purchase_Date) FROM #MyTable)

We can also get our desired results by using TOP…WITH TIES.
SELECT TOP(1) WITH TIES * FROM #MyTable
ORDER BY Purchase_Date
By executing above query, you can find TOP WITH TIES worked amazingly but does this short code is really a smart code. Let’s compare their performance.

Though TOP…WITH TIES clause really shortened our code but you can see that it performed poorly as compare to our traditional code. This happened just because of ORDER BY clause.
This poor performance can be controlled by placing a well defined index.

Friday, November 4, 2011

SQL Server 2012: Introducing New Edition "Business Intelligence"


Along with other major editions, Microsoft is now going to add a brand new business-intelligence SKU and core-based licensing to its coming SQL Server 2012 database offering due next year. Read complete story.




------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box

Thursday, November 3, 2011

SQL Server: How to Remap Existing Database Users on New Instance

During shifting databases across different instances a common problem is ORPHAN USERS and remapping of these orphan database users on new instance. That’s what we were facing in these days, but thanks to Chad Mattox who provided a simple solution.
/*******************************************************
This procedure should be created in the Master database. This procedure takes no parameters. It will remap orphaned users in the current database to EXISTING logins of the same name. This is usefull in the case a new database is created by restoring a backup to a new database, or by attaching the datafiles to a new server.
*******************************************************/
Use Master 
Go 
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'
END

GO
CREATE PROCEDURE dbo.sp_fixusers
AS
BEGIN

DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
BEGIN
EXEC sp_changedbowner 'sa'
END
ELSE
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
END
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go