Monthly Archives: August 2011

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

    •  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 : TRY_CONVERT(), a Good Addition

    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 ’56’
    —Simple convert
    SELECT CONVERT(float,Val1)  AS Val1
    FROM #TempTable
    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.
           WHEN 1 THEN 
                  WHEN‘$’ THEN NULL
                  ELSECONVERT(float,Val1) END 
           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
    FROM #TempTable

    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,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”

    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 )
      1. SMALLDATETIMEFROMPARTS() which returns output in SMALLDATETIME2 format.
      SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
      1. DATETIMEFROMPARTS() which returns output in DATETIME format.
      DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
      1. DATETIME2FROMPARTS() which returns output in DATETIME2 format.
      DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
      1. 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.
      Or you can add number months to get last date.

      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’)
            @weather = ‘Rainy’,IIF(
                                @umbrella = 1,‘Its raining but you cantake umbrella with you’
                                             ,‘Its raining, stay inside’)
                               ,‘Sun is shining..Enjoy’)

      SQL Server Denali: CTP3 Insallation Error "The Data is invalid"

      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
      1. Integrated_CT2776682.exe
      3. SQLFULL_x86_ENU_Install
      For x64 name of files is same, just replace x86 with x64.

      I found that “Data is invalid” error occurs when you havenot downloaded “” properly. So I you are also facingsame error, repeat your download process for only “”and re-execute SQLFULL_x86_ENU_Install.exe.