Hi Friends, I have written a couple of blog posts on new T-SQL features and enhancements in SQL Server 2012 (DENALI). This post summarizes some of them: SQL Server 2012 DENALI series-THROW statement http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/365/sql-server-2012-denali-series-throw-statement SQL Server 2012 DENALI series: SEQUENCE OBJECT http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/346/sql-server-2012-denali-series-sequence-object SQL Server 2012 DENALI series: S...
Read More
Hi Friends, Continuing with my DENALI series, today I would like to talk about the new EXECUTION PLAN warnings. There are many new warnings that have introduced in execution plans, primarily, to help DBAs & developers alike to identify issues in their T-SQL code. In this post, I want to talk about implicit conversion warnings that may lead to incorrect cardinality estimation: Let us create a temporary table in tempDB which will have some records from the customers table in Northwind2 databas...
Dear All, As I mentioned in my last post, I have started exploring new DMVs and some enhancements to existing DMVs in SQL Server 2012 (DENALI). In my last post I talked about sys.dm_exec_query_stats Today, I will talk about sys.dm_os_volume_stats – this DMV returns information about the operating system volume on which the specified databases and files are stored. Before I show a demo, let me clarify that this DMV is not new in SQL Server 2012 (DENALI). It is available in SQL Server 2008 R...
DAX Query Part 1 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the COUNT Function . In this post let us write out first DAX query. Fairly easy! Please note that I am using the AdventureWorksDWDenali as my database. EVALUATE() : Evaluate the context which is passed as an arg...
Hi Friends, Continuing with my DENALI series, today I would like to talk about the new THROW statement. T-SQL TRY/CATCH were available since SQL Server 2005 but THROW and FINALLY were missing. We have THROW now, but FINALLY is yet to come :) The syntax as follows: --THROW [ { error_number | @local_variable }, -- { message | @local_variable }, -- { state | @local_variable } -- ] [ ; ] A simple example could be as follows: Another example of THROW, this time inside CATCH block: USE tempdb; GO CREA...
Dear All, I have started exploring new DMVs and some enhancements to existing DMVs in SQL Server 2012 (DENALI). Today, I will talk about sys.dm_exec_query_stats – this DMV is heavily used to troubleshoot long running queries. Four new columns have been added to this DMV which are as follows: Description of columns extracted from MSDN) total_rows bigint Total number of rows returned by the query. Cannot be null. last_rows bigint Number of rows returned by the last execution of the query. Ca...
Keep your system up to date with the most recent cumulative update package for SQL Server 2008 SP2. This package contains all of the SQL Server 2008 hotfixes that have been released since the initial release of SQL Server 2008 SP2. Abstract from: http://blogs.msdn.com/b/sqlreleaseservices/archive/2011/11/21/cumulative-update-7-for-sql-server-2008-service-pack-2.aspx @SQLServerGeeks http://www.FaceBook.com/SQLServerGeeks
The Microsoft SQL Server Sustained Engineering team is proud to announce the release of SQL Server 2008 SP3 Cumulative Update 2. Cumulative Update 2 contains a roll-up of hotfixes released since the initial release of SQL Server 2008 SP3. Abstract from: http://blogs.msdn.com/b/sqlreleaseservices/archive/2011/11/21/cumulative-update-2-for-sql-server-2008-service-pack-3.aspx @SQLServerGeeks http://www.FaceBook.com/SQLServerGeeks
COUNT Function in BISM Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the RELATED FUNCTION . In this post I plan to showcase examples for COUNT and related functions in DAX. We have 5 different COUNT related functions available for data manipulations. Let us have a small wal...
Hi Readers, Friends, MVPs, Colleagues, Customers & Partners, My precious little one new born has arrived; Amit Bansal Jr. - and I feel the very special feeling - God has been very very kind :) - My wife and I have begun enjoying parenthood ! I am enjoying every bit of a new life (feel like I am re-born). Sleepless nights have already started :). Obviously, I don’t sleep when he is not sleeping and definitely not when he is sleeping as I love watching him sleep :) And yes, blogging will...
SSAS Tabular Project Part 2 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which can be found here . In this post we shall have a walkthrough on how to create perspectives in a tabular project. A perspective can be literally resolved to a common term – VIEW as in the SQL terminology. ...
Hi Friends, Here is part 3 of my sequence of blogs on SEQUENCE object :) In part 1, I blogged on how SEQUENCE objects in DENALI behave within transactions. You can see that here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/346/sql-server-2012-denali-series-sequence-object In part 2, I blogged on some basics on how to get started with SEQUENCE object and how to use it in table definitions: you can find that blog here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/3...
EOMONTH() and BOMONTH? Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the RELATED FUNCTION . Consider the syntax as stated below using which one can calculate the last day of the month SQL syntax to calculate last day of month for SQL Server 2008R2 SELECT CONVERT(VARCHAR(10)...
DAX Functions for Querying BISM tabular models Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes how to configure SSAS Tabular Project . Consider the SQL as stated below: select DimProduct.EnglishProductName ,DimProductCategory.EnglishProductCategoryName ,DimProductSubcategory....
SSAS Tabular Project Part 1 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes how to configure SSAS in Tabular Mode and create tabular model. In this post we shall have a walkthrough on how to create a tabular project which later can be used for data analysis. The first task af...
Hello Folks, FileStream data type has been introduced with the arrival of SQL Server 2008. Before that, we had to use the BOLB data type of SQL Server which allows us to store the data up to 2GB. But one disadvantage with that is file streaming becomes slow and performance of the file stream can be affected very badly. Well I have made some keynotes about FileStream data type: FILESTREAM allows large binary data (documents, images, videos, etc.) to be stored directly in the Windows file system. ...
Configuring SQL Server 2012 Denali SSAS Engine in tabular mode Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which described one of the enhanced features of SSIS in Denali In this post we shall preview configuration of SSAS engine in tabular mode There already has been a lot of buzz about ...
SQL Server 2012 - Denali SSIS Enhancement Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which can be found here In this post, we shall have a walk through on the changes made in Annotation Annotation: A note by way of explanation or comment added to a text or diagram Before I begin I would...
Hi Friends, Good News. SQL Server 2012 RC0 is now available to download. Donwload Link: http://www.microsoft.com/download/en/details.aspx?id=28145 Start downloading and enjoy the cloud ready SQL. If you liked this post, like us on Facebook @ www.facebook.com\SQLServerGeeks Thanks, Sarabpreet Singh
SQL Server 2012 - Denali SSIS Enhancement Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which described one of the SQL Server T-SQL Enhancement In this post we shall preview Group and Connection Managers Group This is one of many new features added to the latest community release of SSIS. ...
Hi Friends, My bad; what I should be blogging as Part 1, I am doing as PART 2. Yesterday, I blogged on how SEQUENCE objects in DENALI behave within transactions. You can see that here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/346/sql-server-2012-denali-series-sequence-object Today, I want to show you how you can use SEQUENCE numbers instead of IDENTITY columns to insert global serial values in your tables. First create the SEQUENCE object: USE tempdb GO CREATE SEQUENCE dbo.Te...
Hi Friends, Have you ever noticed, while taking a compressed backup the file which gets created initially has a different size than the final file size when the backup completes. To be more precise, the file which gets created initially allocates more space than the final backup file size. But the question is why? Actually the size of the backup file depends upon the compression settings applied while taking the backup. Now since SQL Server is programmed to get maximum performance, SQL Server ca...
T-SQL New feature for OFFSET and FETCH Please note the table is completely fictitious and all data is just for presentation purpose Consider a scenario below: English Product Name Reorder Point Dealer price Item 1 100 1000 Item 2 200 2000 Item 3 150 3000 Item 4 250 4000 Item 5 190 5000 Item 6 200 6000 Item 7 300 7000 Item 8 190 8000 Item 9 300 9000 Item 10 200 10000 This represents a limited data set. Assume you have a wide array of data with you. Now what if you were to list the rows from Row 1...
Hi Friends, At Kolkata DevCon 2011, I was demonstrating SEQUENCE OBJECT, and one participant asked if SEQUENCE OBJECTS would respect a transaction. The answer is NO. First, what is a SEQUENCE OBJECT? From Books Online: A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (rep...
Contention is the word that you need to concentrate in resolving the performance issue, page latches contention is common in multi-CPU systems due to the nature of w hen multiple threads concurrently attempt to acquire incompatible latches to the same in-memory structure. Going back to basiscs a latch is an internal mechanism that is used by SQL DB engine automaticatlly to determine when to use it. By design they are deterministic and any behavior of schema design can affect the issue.One of the...