Monthly Archives: November 2011

SQL Server: How to Create a Parameterized Views

In SQL Server functionality of parametrized views can beachieved 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 theparameters for the function here
       @NationalIDNumber VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(
       — Add theSELECT statement with parameter references here
       SELECT   e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,
              p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,
              pnt.NameAS PhoneNumberType,ea.EmailAddress,
              p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,
              sp.NameAS StateProvinceName,a.PostalCode,
              cr.NameAS CountryRegionName,
              p.AdditionalContactInfo
       FROM            HumanResources.Employee AS e INNERJOIN
              Person.Person AS p
              ONp.BusinessEntityID =e.BusinessEntityID INNERJOIN
              Person.BusinessEntityAddressAS bea
              ONbea.BusinessEntityID =e.BusinessEntityID INNERJOIN
              Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
              Person.StateProvince AS sp
              ONsp.StateProvinceID =a.StateProvinceID INNERJOIN
              Person.CountryRegion AS cr
              ONcr.CountryRegionCode =sp.CountryRegionCode LEFTOUTER JOIN
              Person.PersonPhone AS pp
              ONpp.BusinessEntityID =p.BusinessEntityID LEFTOUTER JOIN
              Person.PhoneNumberType AS pnt
              ONpp.PhoneNumberTypeID =pnt.PhoneNumberTypeID LEFTOUTER JOIN
              Person.EmailAddress AS ea
              ONp.BusinessEntityID =ea.BusinessEntityID
       WHERE e.NationalIDNumber = @NationalIDNumber
)
GO
How to use it. Very Simple 🙂
SELECT * FROM PV_GetEmployeeInformationBySSN (‘112457891’)
Advertisements

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

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

In prior versions, a common error which I havealways faced is that key column can’t be listed as include column. 

Now in SQL Server 2012, Index Create Dialog box willautomatically disable check boxes for those columns which are already added askey columns. Similarly, if a column is already added as include column it willbecome disable on key column selection tab.

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

SQL Server: TOP…WITH TIES a Beauty of TSQL

TOP clause is commonly used to get top requiredrows from a result set. Beauty of this clause is that it can be used with WITHTIES clause, to retrieve all similar rows to base result set.
According to BOL “WITH TIES Specifiesthat additional rows be returned from the base result set with the same valuein the ORDER BY columns appearing as the last of the TOP n(PERCENT) rows. TOP…WITH TIES can be specified only in SELECT statements, andonly if an ORDER BY clause is specified.
For example from following simple table I need toget records which have minimum purchase date value. In first method we will usecommon 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 ourdesired 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 reallya smart code. Let’s compare their performance.


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

SQL Server 2012: Introducing New Edition "Business Intelligence"

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

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

During shifting databases acrossdifferent 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. Thisprocedure takes no parameters. It will remap orphaned users in the currentdatabase to EXISTING logins of the same name. This is usefull in the case a newdatabase is created by restoring a backup to a new database, or by attaching thedatafiles to a new server.
*******************************************************/
UseMaster 
Go 
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
BEGIN
DROP PROCEDUREdbo.sp_fixusers
IF OBJECT_ID(‘dbo.sp_fixusers’) IS NOT NULL
PRINT ‘<<< FAILEDDROPPING PROCEDURE dbo.sp_fixusers >>>’
ELSE
PRINT ‘<<<DROPPED PROCEDURE dbo.sp_fixusers >>>’
END
GO
CREATE PROCEDUREdbo.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 ‘<<< FAILEDCREATING PROCEDURE dbo.sp_fixusers >>>’
go