LIKE vs PATINDEX

We had a discussion on some of the performance warnings in VSTF like using LIKE(with %) operator. I was just going through multiple options on how to avoid LIKE clause from the query andimprove performance as well. Generally when we use LIKE operator, it goes for table/clustered index scan and this is the costliest operation…

0

How much space are you wasting?

The Below script would provide us​ the space used by write only indexes. The usage stats from the indexes is pretty easy to get from ‘sys.dm_db_index_usage_stats’ and if you join the DMV ‘sys.dm_db_partition_stats’ onto that, you can from the column ‘used_page_count’ calculate the number of bytes that the particular indexes is wasting. Here is the…

0

LAG’n’LEAD – New T-SQL Features in SQL Server Denali CTP3

Are you tired of doing self joins, just to get the value of a column in the previus/subsequent row, or maybe even worse you are doing multiple self joins. This is where you will start smiling. Let’s test this functionality, how it is simple and gives better performance than self JOIN. CREATE TABLE MonthlyProfit(    yearid…

0

THROW in Error Handling – Denali

​Error handling is now easier with the introduction of the THROW command in SQL Server 2011. Legacy method (SQL 2005 onwards) In previous versions, RAISERROR was used to show an error message. RAISERROR requires a proper message number to be shown when raising any error. The message number should exist in sys.messages. RAISERROR cannot be…

0

Deprecated Database Engine Features in SQL Server 2012

​These features (http://technet.microsoft.com/en-us/library/ms143729(SQL.110).aspx) are scheduled to be removed in a future release of SQL Server. Deprecated features should not be used in new applications.

0

14 New Functions and 1 Changed Function in Denali

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) introduces 14 new built-in functions. These functions ease the path of migration for information workers by emulating functionality that is found in the expression languages of many desktop applications. However these functions will also be useful to experienced users of SQL Server. Those functions can be…

0

Dude! What is Microsoft SQL Server ?

Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a…

0

SQL Server 2012 – New Features – Contained Databases

A very common problem that has plagued both the DBAs and developers for a long time is migrating databases to different SQL Server instances for application development and testing purposes OR for various failover conditions. Databases are not really independent units. They have many server dependent objects like logins, SQL Server Agent jobs, linked servers,…

1

Parallel Data Warehouse Overview

Introduction SQL Server 2008 R2 Parallel Data Warehouse (PDW) is a highly scalable appliance for enterprise data warehousing.   The PDW works by using Massively Parallel Processing Software and Appliance hardware to control several physical servers each running their own instances of SQL Server 2008. This control process allows the PDW to scale out query processing…

3

Monitoring tempdb Transactions and Space usage

As the tempdb database is the common global resource for all the operations going on in SQL Server, so the DBA has to be bit cautious about the use of it. Because any unexpected operations by the applications running under the SQL Server instance or any adhoc query by the user can eat all the…

1