Monthly Archives: August 2011
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
While working with different typeof data, I have not found a single day, without data type conversion error.Sometime error could be just because of a single row, out of million of correctrows. SQL Server never mentions which row is the culprit, it just throw anerror. Sometime you want to ignore those problematic rows but can’t because youhave no other option to found those wrong data.You can correct them manually or write anintelligent query. Consider following simple example
CREATE TABLE #TempTable (Val1 VARCHAR(10))
INSERT INTO #TempTable
SELECT ‘2.01’ UNION ALL
SELECT ‘A2.4’ UNION ALL
SELECT ‘6.51’ UNION ALL
SELECT ‘$37’ UNION ALL
SELECT CONVERT(float,Val1) AS Val1
Result of above selectquery is an error, because row:2 data can’t be converted into float.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
So whatwill I do, so I can execute my query without any error.
SELECT CASE ISNUMERIC(Val1)
WHEN 1 THEN
WHEN‘$’ THEN NULL
ELSE NULL END AS Val1
To avoid such error, with out writing long quires, SQL Server Denali has introduced a new function TRY_CONVERT(). Thisfunction try to convert values according to your given format and if failed itwill return NULL (instead of error). Let’s try this amazing function.
SELECT TRY_CONVERT(float,Val1) AS Val1
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’)
After donwloding SQL Server Denali from here, I thought, itwill take only few mintues to install but unfortunatly it took 3 hours. I triedevery thing but every time error was same “Data is invalid”
To install SQL Server Denali CTP 3 (at 32bit machine) you must have
For x64 name of files is same, just replace x86 with x64.
I found that “Data is invalid” error occurs when you havenot downloaded “SQLFULL_x86_ENU_Core.box” properly. So I you are also facingsame error, repeat your download process for only “SQLFULL_x86_ENU_Core.box”and re-execute SQLFULL_x86_ENU_Install.exe.