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’)

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 2011 (Denali): Changing Backup Files Default Path

Up to SQLServer 2008 R2 (10.5), we were unable to change default path (easily) for “Backupfiles”, though it was possible after making some changes in registry at path. “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\ MSSQLServer\BackupDirectory”

Note: For SQL Server 2005 its MSSQL.1, for SQL Server 2008, its MSSQL10 and for SQL Server 2008 R2 its MSSQL10_50

 

Thanks toSQL Server 2011 (Code name DENALI), as now we can change this default path for backup filesto our required one by just opening server properties page and on “DatabaseSettings” tab.

Try Taking abackup from SSMS and now you can find that it has already pointing toward yougiven path.

SQL Server Denali: New Backup/Restore Options

Backupprocess in SQL Server Denali is quite same to previous versions. But there arefew changes in restore dialog box.

• Restore dialog box is now divided into three tabs instead of two. General tab is almost same to existing versions but options tab is further divided into “options” and “files” tabs.

• A good thing about new restore page is “Backup Timeline”. Backup Timeline dialog box is useful to graphically locate and specify backups to restore a database to a point-in-time.For detail 

http://blogs.msdn.com/b/wesleyb/archive/2011/07/18/restore-improvements-in-sql-server-denali-ctp3-management-studio.aspx

•  With SQL Server Denali, now you can restore corrupt pages.

SQL Server Denali: CTP 3 Product Guide Released

Product guide of SQL Server DenaliCTP 3 is released and is available for free download (about 456 MB in size) from Microsoft Download Center.  Guide is in formof an application and includes useful resources and demos that will help you inyour evaluation of CTP3.  The said guide contains:

• 14 Product Datasheets
• 8 PowerPoint Presentations
• 5 Technical White Papers
• 13 Hands-On Lab Preview Documents
• 6 Click-Through Demonstrations
• 13 Self-Running Demonstrations
• 26 Links to On-Line References
• 44 Links to On-Line Videos including 26 Presentations from North America TechEd 2011

SQL Server Denali: Partition Limit Enhancement

Microsoft SQL Server Denali (CTP 3) supports up to 15,000 partitions by default. In earlier versions (upto SQL Server 2008), the number of partitions was limited to 1,000 by default. Though partition number was enhanced in SQL Server 2008 SP2, but for only Enterprise Edition.

SQL Server Denali: Format(), a Most Wanted Function

Most of SQL Server developers/DBAs think that convertingDateTime to a specific format is bit difficult as you need to remember specificformat number, which you can use with CONVERT(). Like if you need to convertdate to German format i.e.dd.mm.yy,then you can do it as follow:

SELECT CONVERT(NVARCHAR(30),GETDATE(),104) AS GermanDateFormat

Finding it tough to remember theseconversion code/number, I put these format codes in my early post.But now using SQL Server Denali, you can use a most demanded function FORMAT().

FORMAT ( value, format [, culture ] )

 

DECLARE @d datetime = GETDATE();

SELECT  FORMAT ( @d, ‘dd/MM/yyyy’) AS ‘dd/MM/yyyy’,

        FORMAT ( @d, ‘dd.MM.yyy’)  AS ‘dd.MM.yy’,

        FORMAT ( @d, ‘dd/MMM/yyy’) AS ‘dd/MMM/yy’,

        FORMAT ( @d, ‘MMM dd, yy’) AS ‘MMM dd, yy’,

        FORMAT ( @d, ‘MMMM dd, yyyy (dddd)’) AS ‘MMMM dd, yyyy (dddd)’,

        FORMAT ( @d, ‘dddd MMMM dd, yyyy ‘) AS ‘MMMM dd, yyyy (dddd)’,

        FORMAT ( @d, ‘hh:mm:ss’) AS ‘hh:mm:ss’

FORMAT()is not limited to Date/Time only

DECLARE @I int = 15;

SELECT FORMAT(@I,‘c’) AS Currency,

              FORMAT(@I,‘e’) AS Scientific,

              FORMAT(@I,‘p’) AS Percent_,

              FORMAT(@I,‘x’) AS HexaDecimal

Note: In above examples we have not used, third parameter i.e. culture. If value for culture is not provided then it will use session default language. In my case it is “en-US”

For more information http://msdn.microsoft.com/en-us/library/hh213505%28v=SQL.110%29.aspx

SQL Server Denali: Get Date/Time from Parts

SQL Server Denali has madedevelopers life quite easy by introducing new useful functions. We have alreadydiscussed EOMONTH() in anearly post.

DATEFROMPARTS() is a new function introduced in SQL Server Denali,which will help us to avoid lengthy code to get date from year, month and dayinputs. DATEFROMPARTS() takes three input parameters YEAR, MONTH and DAYand returns value in DATE format.

DATEFROMPARTS ( year, month, day )

DATEFROMPARTS() is not onlyfunction to get DATETIME values from parts but 5 other functions are alsointroduced.

1. TIMEFROMPARTS () which returns output in TIME format.

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

2. SMALLDATETIMEFROMPARTS() which returns output in SMALLDATETIME2 format.

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

3. DATETIMEFROMPARTS() which returns output in DATETIME format.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

4. DATETIME2FROMPARTS() which returns output in DATETIME2 format.

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

5. DATETIMEOFFSETFROMPARTS() which returns output in datetimeoffset format.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

SQL Server Denali: New Function to Get Last Date of Month

 A new function is introduced in SQL Server DenaliCTP3, though which you can easily find out last date of any month. Prior tothis we were using different methods to get last date of month. Following one,I like most.

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GetDate())+1,0))

But now it’s more easy with EOMONTH() function. Just provide a date in DateTimeformat or in string format and it will return last date of requiredmonth.

SELECT EOMONTH (GETDATE()) AS Result;

Or you can add number months to get last date.

SELECT EOMONTH (GETDATE(),3) AS Result;

SQL Server Denali: IIF Logical Function

If you have developed some sort of applications usingMicrosoft Access, then you are definitely familiar with “IIF” logical function.In SQL Server, prior to SQL Server Denali we can use “CASE” instead of “IIF” asthis logical function was not available. But in SQL Server Denali CTP3, “IIF”is available with same ease and functionality.

According to BOL”IIF is a shorthand way for writing a CASE statement. Itevaluates the Boolean expression passed as the first argument, and then returnseither of the other two arguments based on the result of the evaluation. Thatis, the true_value is returned if the Boolean expression is true, and thefalse_value is returned if the Boolean expression is false or unknown.true_value and false_value can be of any type. The same rules that apply to theCASE statement for Boolean expressions, null handling, and return types alsoapply to IIF.

The fact that IIF is translated into CASE also has an impact on other aspectsof the behavior of this function. Since CASE statements can nested only up tothe level of 10, IIF statements can also be nested only up to the maximum levelof 10. Also, IIF is remoted to other servers as a semantically equivalent CASEstatement, with all the behaviors of a remoted CASE statement.”

Let’s try it with simple example.

 

(Only applicable for SQL Server DenaliCTP 3)

DECLARE @weather VARCHAR(50) = ‘Rainy’, — Rain/Sunny

             @umbrella BIT = 1 –1= Yes we have, 0=Nowe don’t have

–Single IIF

SELECT IIF(@weather =‘Rainy’,‘Oh! its raining’,‘Sun is shinning..Enjoy’)

–MultipleIIF            

SELECT IIF(

      @weather = ‘Rainy’,IIF(

                          @umbrella = 1,‘Its raining but you cantake umbrella with you’

                                       ,‘Its raining, stay inside’)

                         ,‘Sun is shining..Enjoy’)