Wednesday, August 17, 2011

SQL Server Denali: New Backup/Restore Options

Backup process in SQL Server Denali is quite same to previous versions. But there are few 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.




------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box

Thursday, August 11, 2011

SQL Server Denali: CTP 3 Product Guide Released


Product guide of SQL Server Denali CTP 3 is released and is available for free download (about 456 MB in size) from Microsoft Download Center.  Guide is in form of an application and includes useful resources and demos that will help you in your 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



------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box


 

Tuesday, August 9, 2011

SQL Server Denali : TRY_CONVERT(), a Good Addition

While working with different type of 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 correct rows. SQL Server never mentions which row is the culprit, it just throw an error. Sometime you want to ignore those problematic rows but can’t because you have no other option to found those wrong data.You can correct them manually or write an intelligent 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 '56'
GO
---Simple convert
SELECT CONVERT(float,Val1)  AS Val1
FROM #TempTable
Result of above select query 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 what will I do, so I can execute my query without any error.
SELECT CASE ISNUMERIC(Val1)
       WHEN 1 THEN 
              CASE LEFT(Val1,1)
              WHEN '$' THEN NULL
              ELSE CONVERT(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(). This function try to convert values according to your given format and if failed it will return NULL (instead of error). Let’s try this amazing function.
SELECT TRY_CONVERT(float,Val1)  AS Val1
FROM #TempTable
 























------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box

Monday, August 8, 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.



------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box

Saturday, August 6, 2011

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


Most of SQL Server developers/DBAs think that converting DateTime to a specific format is bit difficult as you need to remember specific format number, which you can use with CONVERT(). Like if you need to convert date 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 these conversion 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


------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box

Thursday, August 4, 2011

SQL Server Denali: Get Date/Time from Parts


SQL Server Denali has made developers life quite easy by introducing new useful functions. We have already discussed EOMONTH() in an early 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 day inputs. DATEFROMPARTS() takes three input parameters YEAR, MONTH and DAY and returns value in DATE format.
DATEFROMPARTS ( year, month, day )









DATEFROMPARTS() is not only function to get DATETIME values from parts but 5 other functions are also introduced.
  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 )



------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box



Wednesday, August 3, 2011

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

 A new function is introduced in SQL Server Denali CTP3, though which you can easily find out last date of any month. Prior to this 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 DateTime format or in string format and it will return last date of required month.
SELECT EOMONTH (GETDATE()) AS Result;

Or you can add number months to get last date.
SELECT EOMONTH (GETDATE(),3) AS Result;

















------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         IIFLogical Function

·         A New More Flexible Create Index Dialog box

Tuesday, August 2, 2011

SQL Server Denali: IIF Logical Function

If you have developed some sort of applications using Microsoft 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” as this 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. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_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 the CASE statement for Boolean expressions, null handling, and return types also apply to IIF.
The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function. Since CASE statements can nested only up to the level of 10, IIF statements can also be nested only up to the maximum level of 10. Also, IIF is remoted to other servers as a semantically equivalent CASE statement, with all the behaviors of a remoted CASE statement.”

Let’s try it with simple example.
 
(Only applicable for SQL Server Denali CTP 3)
DECLARE @weather VARCHAR(50) = 'Rainy', -- Rain/Sunny
              @umbrella BIT = 1 --1= Yes we have, 0=No we don't have
--Single IIF
SELECT IIF(@weather ='Rainy','Oh! its raining','Sun is shinning..Enjoy')
--Multiple IIF            
SELECT IIF(
       @weather = 'Rainy',IIF(
                           @umbrella = 1,'Its raining but you can take umbrella with you'
                                        ,'Its raining, stay inside')
                          ,'Sun is shining..Enjoy')



------------------------------------------------------------------------------------
Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         Changing Backup Files Default Path is More Easy Now

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         A New More Flexible Create Index Dialog box

Monday, August 1, 2011

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

After donwloding SQL Server Denali from here, I thought, it will take only few mintues to install but unfortunatly it took 3 hours. I tried every 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
  2. SQLFULL_x86_ENU_Core.box
  3. SQLFULL_x86_ENU_Install
  4. SQLFULL_x86_ENU_Lang.box
For x64 name of files is same, just replace x86 with x64.

I found that “Data is invalid” error occurs when you have not downloaded “SQLFULL_x86_ENU_Core.box” properly. So I you are also facing same error, repeat your download process for only “SQLFULL_x86_ENU_Core.box” and re-execute SQLFULL_x86_ENU_Install.exe.