Category Archives: SQL Server Denali
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
— Add theSELECT statement with parameter references here
SELECT e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName,
p.LastName, p.Suffix, e.JobTitle, pp.PhoneNumber,
p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City,
FROM HumanResources.Employee AS e INNERJOIN
Person.Person AS p
ONp.BusinessEntityID =e.BusinessEntityID INNERJOIN
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
WHERE e.NationalIDNumber = @NationalIDNumber
How to use it. Very Simple 🙂
SELECT * FROM PV_GetEmployeeInformationBySSN (‘112457891’)
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.
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.
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
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
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.
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 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.
- TIMEFROMPARTS () which returns output in TIME format.
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
- SMALLDATETIMEFROMPARTS() which returns output in SMALLDATETIME2 format.
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
- DATETIMEFROMPARTS() which returns output in DATETIME format.
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
- DATETIME2FROMPARTS() which returns output in DATETIME2 format.
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
- DATETIMEOFFSETFROMPARTS() which returns output in datetimeoffset format.
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
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;
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
SELECT IIF(@weather =‘Rainy’,‘Oh! its raining’,‘Sun is shinning..Enjoy’)
@weather = ‘Rainy’,IIF(
@umbrella = 1,‘Its raining but you cantake umbrella with you’
,‘Its raining, stay inside’)
,‘Sun is shining..Enjoy’)