Category Archives: 2008 – New Features

COLUMNSTORE Index

SQL Server’s indexes, clustered and nonclustered, are based on the B-tree. B-tree structure is great for finding data that match the condition on the basis of the primary key. They give us good results in the form of performance when all data scan is required. Then why we needed columnstore indexes? Before jumping to the [...]

How many Non Clustered Index can be created on a single table?

During my on of the training session, I used a term that we can multiple NON clustered Indexes, instantly one of the participants  was very specific about number and said, we can have upto 249 non clustered index in a table. I instantly said, Your information in not upto date, and you need a course [...]

SQL Script to Get Free Space Information where Database Files are located

Recently I working on a server, where I need to get free drive space information on the Server. I can check this information either by My Computer (Windows Explorer) or by master..xp_fixeddrives, but this will give information about all drives, regardless whether SQL Server database files are hosted on those drives or not as Show [...]

How to Setup Mirroring in SQL Server ?(Screen Shots)

What is Mirroring in SQL Server ? Database mirroring is an alternative high-availability solution to failover clustering in SQL Server. Database mirroring supports automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering. In this Article, we will implement Mirroring with witness Server and will Automatic Failover [...]

Database mirroring must be removed before you drop SQL Server database

Error Message TITLE: Microsoft SQL Server Management Studio —————————— Drop failed for Database ‘Test_SQLServer_Mirroring’.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Database&LinkId=20476 —————————— ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) —————————— The database ‘Test_SQLServer_Mirroring’ is enabled for database mirroring. Database mirroring must be removed before you drop the database. (Microsoft SQL Server, [...]

List of DMV and DMF availability across all SQL Server Versions

This is the extensive list of ALL Dynamic Management Views (DMV) and Dynamic Management Functions (DMF) which are available across Versions and Editions. This List Answers: DMV in SQL Server 2005 DMV newly added in SQL Server 2008 DMV removed in SQL Server 2008 DMV newly added in SQL Server 2008 R2 DMV removed in [...]

What are T-SQL Enhancements done in SQL Server 2008

What are T-SQL Enhancements done in SQL Server 2008 ? SQL Server 2008’s new statements, extend the T-SQL feature and functionality. Let’s understand What are T-SQL Enhancements done in SQL Server 2008 ? Declare and Initialize Variables Compound Assignment Operators Row Constructors CONVERT Enhancements Composable DML Object Dependencies MERGE Statement TABLE Value constructor GROUPING SET [...]