Multiple Query Plans

As my last post, this is again something I got from MongoDB.

Execution plans are vital to measure performance of your queries. How many times, you are puzzled with why that index is not using? why it is doing a scan?

In MongoDB, there is a option to view all the execution plans with query engine considered and what was used eventually.

syntax for that is db.users.find({UserID:14049}).explain(true)

explain() will tell you what is the query plan used and true parameter will give you all the query plans considered for the execution.

However, though this is a great feature to have, in case of SQL Server implementation may not be easy. In MongoDB anyway queries have one collection only and query plan is not complex

Dropping Duplicates

If you search for "Duplicate rows sql server", in Google you will get about 136,000 results. This will tell how series the problem is. I am not going to add another post to above huge number of articles. What I going to explain here is how some other database system does this.

I was able to attend to a MongoDB conference last month and came across few features which are nice to have features in SQL Server.

In MongoDB, there are few ways for creating indexes, one is stated below.

db.users.ensureIndex({UserID:1},{"unique":true, "dropDups":true})

Just to tell you what the above script is, users is the "table" (Collection in MongoDB) and UserID is the "column" (Attribute in MongoDB") and ensureIndex is the command to create the index.

so, in this statement, unique:true will create a unique index BUT if there are duplicates records, it will retain only the first "record" ( Document in MongoDB" ) and others will be deleted because of the dropDups:true statment.

Now, this is very efficient way of deleting duplicates, but you need to pay more attention before issuing this command.

Re-generating SQL Server Logins

Migrating database servers is quite a frequent task which is carried out by a DBA. In migrating, it is very important to transfer the logins. When transfering logins and users, you need to make sure that you transfer roles and password as well.
By querying the system tables, database administrators can produce a comprehensive report for login permission including original password, the server and database roles assigned, down to granular level object permission.
This article describes how to achive this and other information behind this.

Tips & Tricks for DBAs Writing Their First Article

I have been writing articles from 2003 (only 7+ years not much, still more to go) in some SQL sites like www.SQLServerCentral.com ,www.sql-server-performance.com , www.sqlserveruniverse.com etc.

Brad Mc Gehee is one of few who inspired me to write articles.Now he has comeup with some tips and tricksfor writing articles.

Hope you will take a leaf out of this and start writing. Good luck.

SQLskills Free Online MCM Training

These videos are designed to give an overview of the breadth of subject knowledge required, plus some indication of the depth to which you should know it.

SQL Server Data Mining with Microsoft Office Excel

Data mining is made easier with Excel. This is my first article with data mining.

Importing dbf files

dbf files (dbase 3+, clipper or foxpro) to SQL Server is something that you won't get frequently. However, it might not be as simple as importing other data sources.

This faq by me, tells you how to do that.

Sending Email to Different Domains using Database Mail

This FAQ tells you how to send mail to difference domains using Database Mail.

SEQUENCE in SQL Server 2011

SEQUENCE is a core new feature of SQL Server 2011 (Denali). It is a more performant, flexible alternative to the INDENTITY attribute. This article introduces sequence and demonstrates how to use it and its performance advantage.

Read my first article on Denali

Ten Techniques to Guarantee a Successful DW/BI Solution

The ingredients to a successful Data Warehouse / Business Intelligence deployment include good project management, effective communication and using DW/BI tools to their full potential. Denise Rogers presents her top 10 techniques to guarantee a successful DW/BI deployment.

The ingredients to a successful Data Warehouse / Business Intelligence deployment include good project management, effective communication and using DW/BI tools to their full potential. A Data Warehouse / Business Intelligence application is being built, NOT an OLTP application. So use the ETL solution and its features in support of the solution, the Business Intelligence software to its full potential, the DBMS and its Data Warehouse functionality. Other essential ingredients include getting the resources trained or experienced hires or both that know how to exploit the features and functionality that each of the components of the solution has to offer.

Read more at here