SQLAuthority News – Milestone – 1900th Post and 31 Million Views – Thank You!

I really never thought that I would be writing this post – honestly! After 1900 posts and almost 5 years, this has been a journey and lots of learning. I get to write a 100 “mile stone” post 3-4 times a year so I am happy to be writing this one. I am eagerly looking forward to my 2000th blog post as well.

n1900 SQLAuthority News   Milestone   1900th Post and 31 Million Views   Thank You!

Let us play today a very simple game –  frequently asked questions and answers. These questions were prepared by my lovely wife Nupur Dave.

sitestats SQLAuthority News   Milestone   1900th Post and 31 Million Views   Thank You!

Click to Enlarge

Why did you start this blog?
It began just as a bookmark for myself. I tried to collect all the frequently used scripts at one place and that is how this blog started.

What is the original name of SQLAuthority.com?
I think for this answer you need to read my earlier blog post – Origin of Blog Name Revealed. This post was written almost a year ago.

What is one thing you want to highlight in the last 100 posts?
There are two things – I am now a published author and have two books with my contribution.

SQL Wait Stats – A book on the unique subject of Wait Types and Queues
[Amazon] | [Flipkart]

SQL Server Programming – A book explaining various SQL Server Concepts in easy to read language
[Amazon] | [Flipkart]

Who is Rick Morelan?
Rick is a great SQL enthusiast. He has co-authored two books with me and during the course I learned a lot of things from him. He has the talent to explain any complex idea in the simplest way. Writing a book with him was a real pleasure and excellent learning experience.

What are your plans for the next 100 blog posts?
Well, the first of November is my blog’s anniversary. We are going to have a fifth anniversary celebration of this blog. I am thinking something big – something really big for all of us. I am not sure yet but all of the readers will have blast on the anniversary of this blog.

What is the next big surprise?
Watch the blog post of Nov 1, 2011

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – System Center Monitoring Pack for Microsoft SQL Server 2008 R2 Parallel Data Warehouse Appliance

Microsoft is continuously releasing something new in database related space and it is hard to keep it up what is new coming up. Every Sunday I will post all the new updates in the database and its related world. I will post here what is new every week and this post help us as a ready reference.

System Center Monitoring Pack for Microsoft SQL Server 2008 R2 Parallel Data Warehouse Appliance

This Monitoring Pack for Microsoft SQL Server 2008 R2 Parallel Data Warehouse (PDW) Appliance provides capabilities to discover, monitor, and manage your SQL Server PDW appliances. The management pack simplifies the monitoring process for PDW.

In addition to health monitoring capabilities, this monitoring pack includes a custom diagram view to visualize appliance health. The custom view has detailed knowledge about the health states for the hardware and software components. The custom view enables near real-time diagnosis and resolution of detected issues.

Note: Description is taken from Microsoft page.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server Quiz 2011 – All was well few moments before all went wrong – Reasons and Resolutions

j2pwait SQLAuthority News   SQL Server Quiz 2011   All was well few moments before all went wrong   Reasons and ResolutionsI earlier wrote about DBA Quiz at All was well few moments before all went wrong – Reasons and Resolutions. I have even announced that I will give away one print book of SQL Wait Stats book. SQL Programming Joes 2 Pros (vol 4) [Amazon] | [Flipkart]- Chapter 13 has few interesting hints. However, I want to announce one more thing today.

I will give giving away not one but 2 copies of the SQL Wait Stats books [Amazon] | [Flipkart] . SQL Wait Stats book is available for very low cost on Kindle at this moment. This is special promotion for October Month.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Server Side Paging in SQL Server CE (Compact Edition)

SQL Server Denali is coming up with new T-SQL of Paging. I have written about the same earlier.

SQL SERVER – Server Side Paging in SQL Server Denali – A Better Alternative
SQL SERVER – Server Side Paging in SQL Server Denali Performance Comparison
SQL SERVER – Server Side Paging in SQL Server Denali – Part2

What is very interesting is that SQL Server CE 4.0 have the same feature introduced. Here is the quick example of the same. To run the script in the example, you will have to do install Webmatrix 4.0 and download sample database. Once done you can run following script.

SELECT *
FROM [Order Details]
ORDER BY [Order ID]
OFFSET 20 ROWS
FETCH NEXT 7 ROWS ONLY

pagingce SQL SERVER   Server Side Paging in SQL Server CE (Compact Edition)

Above script returns records 21 to 27 from the tables [Order Details]. OFFSET clause will skip the first 20 rows and then returns 7 rows. Please note, this T-SQL will throw error if ORDER BY is not used in the query.

I have found exactly similar post written by Jacob Sebastian on BeyondRelational.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations()

In my recent Office Hours, I received a question on how to determine the case sensitivity of the database.

The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.

SELECT *
FROM fn_helpcollations()

helpcollations SQL SERVER   Detecting Database Case Sensitive Property using fn helpcollations()

The method shown above is the most recommended method and I suggest using the same.

When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.

SELECT 1
WHERE 'SQL' = 'sql'

If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().

Another interesting suggestion was from Dave Dustin who is SQL Server MVP from New Zealand. He has provided the following script:

SELECT 1
FROM sys.Databases
WHERE name='<databasename>'
AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')

Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.

It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Wait Stats Book – Available as Kindle eBook – October Special

Get SQL Wait Stats – Kindle Edition

j2pwait s SQLAuthority News   SQL Wait Stats Book   Available as Kindle eBook   October SpecialLast month I released my SQL Wait Stats  book. This book is the beginning of my journey in wait stats. It has been extremely popular and so far in India it has sold all the print copies twice on Flipkart. This book is available in the United States on Amazon and it has gotten a tremendous response as well. What is special about this book is that it gives you the opportunity to start on performance tuning instantly after receiving the book. The scripts are very simple and they are all available online on this blog. Additionally, the book is open for improvement and suggestions. There are so many different kinds of wait stats that it is not possible to cover all of them, but I have attempted to cover almost all of the most important ones and most frequently encountered.

So far this book was available in India and the United States. However, here is the good news for worldwide readers and everybody who was waiting for the eBook version of SQL Wait Stats. We have an eBook version out for sale! Looking at your response for the month of October we have a special price as well. The price for the eBook in the United States is USD 3.99 – you can spend less than a large coffee and gain the knowledge to find bottlenecks and improve the performance of your SQL Server. The price for the rest of the world is between 3.99 to 5.99, based on your country location. In any case, now there is no excuse to not read this book. If you do not own a Kindle device, there is no need to buy one, you can download Kindle for PC right now and get your SQL Wait Stats eBook.

Please note, there are still 25 more days in October. After October the price will reset to the regular higher price.

SQL Wait Stats – Kindle Edition

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Quick Note about JOIN – Common Questions and Simple Answers

TSQL2sDay SQL SERVER   Quick Note about JOIN   Common Questions and Simple Answers

This blog post is written in response to the T-SQL Tuesday post of JOIN. This is a very interesting subject. Years ago, I wrote my article about SQL SERVER – Introduction to JOINs – Basic of JOINs, ‑ till date, it is my most favorite article on the blog.

Today we are going to talk about join and lots of things related to the JOIN. I recently started office hours to answer questions and issues of the community. I receive so many questions that are related to JOIN. I will share few of the same over here. Most of them are basic, but note that the basics are of great importance.

Without further ado, let me continue with the question and answers.

Q: Which one of the following is a better method?
Method 1:
SELECT t1.*, t2.*
FROM t1,t2
WHERE t1.col1 = t2.col1

Method 2:
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1

A: The answer to this question will bring some interesting conversation. I strongly prefer method 2 because it is much cleaner to understand and if I have to use table level hints or so on, it is much convenient to do the same. I would suggest going ahead with method 2. Currently with regard to performance and execution plan, both the methods show the same (most of the time). However, with respect to standard and future innovation, method 2 is the way to go.

When I have to perform a performance tuning task, and if I see method 1, I usually ask the developer to convert it to method 2 as I feel much more comfortable with method 2. Additionally, when you have to work with OUTER JOIN, you will have to do so.

Q: What is better ‑ subquery or JOIN?
Subquery:
SELECT t1.*
FROM t1
WHERE t1.col1 IN (SELECT t2.col1 FROM t2)

Join:
SELECT t1.*
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1

In this case, there is no right answer. You should use the one that gives you optimal performance. I have seen cases when the subquery gives optimal performance as well join giving optimal performance when compared to each other. I have seen either of them performing so well that I think one has to test out both the methods before selecting one. If you are facing situation where you are not sure which method you should select, I suggest that you go with your intuition. I still prefer JOIN over any other method, but in this case, I will suggest you to test your options.

Q: How to simulate Join?
A: I get this question a lot of times, and I have no answer. Here, I want your help as I do not even understand this question.

Q: How can I change my LEFT JOIN to RIGHT JOIN and get the same answer?
A: Sure. Here is quick example of the same:

Left Join:
SELECT t1.col1, t2.col2
FROM t1 LEFT JOIN t2 ON ON t1.col1 = t2.col1

Right Join:
SELECT t1.col1, t2.col2
FROM t2 RIGHT JOIN t1 ON ON t1.col1 = t2.col1

Both of the above options will give you same result. However, the real question is why you want to do that. What is the reason that you want to change the left join to right join?

Q: Does it matter how I write tables in my join if I am using INNER JOIN only?
A: No it does not matter in case of INNER JOIN as the result will be the same, and the SQL Server Engine will figure out the optimal execution plans for your query. As your question clearly suggests that for any other kind of join (i.e., OUTER JOIN, CROSS JOIN), it will matter for sure. Additionally, there are cases with INNER JOIN ‑ when order is forced on them, they have shown a little performance enhancement. Here is a quick example of the same.

If you have attended my session of Virtual Tech Days few days ago, you would have seen the example of the how forceorder hint works.

Q: Is there a quick tutorial to Joins?
A: I have written an article on this subject earlier, and as I said earlier in this article, I personally like the same a lot. Here you can read about the same: Introduction to JOINs – Basic of JOINs.

Q: Is there any book available to learn T-SQL, which explains various concepts like this easily?
A: I am bit biased but you can read about my books over here.

Q: Is SELF JOIN is a type of INNER JOIN or OUTER JOIN?
A: In fact, it is both an inner as well as outer join. Self Join is a very interesting subject. Here is an interesting article that I have written earlier on this subject: SQL SERVER – The Self Join – Inner Join and Outer Join .

Q: In case of the OUTER JOIN, where should I put the condition?
A: This question requires a detailed answer, I have written a detailed blog post on this subject over here: How ON Clause Effects Resultset in LEFT JOIN .

Q: What is Optimal LEFT JOIN or NOT IN?
A: I personally prefer LEFT JOIN as I have seen LEFT JOIN doing better in many cases. Once again, I suggest you should test it with your query. Here is a quick example of the same: Differences Between Left Join and Left Outer Join.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – CE – 3 Links to Performance Tuning Compact Edition

Today, I am going to do webcast online on how to improve performance for SQL CE. Here are three articles which I am going to base my session.

Database Design and Performance (SQL Server Compact Edition)

  • Use Database Denormalization
  • Decide Between Variable and Fixed-length Columns
  • Create Smaller Row Lengths
  • Use Smaller Key Lengths
  • Publication Article Types and Options

Query Performance Tuning (SQL Server Compact Edition)

  • Improve Indexes
  • Choose What to Index
  • Use the Query Optimizer
  • Understand Response Time vs. Total Time
  • Rewrite Subqueries to Use JOIN
  • Use Parameterized Queries
  • Query Only When You Must

Optimizing Connectivity (SQL Server Compact Edition)

  • Synchronization Time-Out Properties
  • Time-out Optimization

I found above articles very interesting and useful, and looking forward to your opinion on the same.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – SQL Backup and FTP – A Quick and Handy Tool

Scroll down at the end of this post to win my SQL Wait Stats Book.

I have used this tool extensively since 2009 at numerous occasion and found it to be very impressive. What separates it from the crowd the most – it is it’s apparent simplicity and speed. When I install SQLBackupAndFTP and configure backups – all in 1 or 2 minutes, my clients are always impressed.

To put it simply, SQLBackupAndFTP is MS SQL Server backup software that performs these tasks:

  • Backup SQL Server Database
  • Zip the backups
  • Encrypt the backups
  • FTP the backups to remote FTP server
  • Move file to local area network
  • Send final status of tasks in email.
  • Allows to monitor the history of jobs through the web

SQLBackupAndFTP comes in Free and Paid versions (starting from $29) – see this version comparison chart, but even Free version is fully functional for unlimited ad hoc backups or for scheduled backups of up to two databases – it will be sufficient for many small customers.

But enough theory, let us now see how we can take backup and move it to the desired folder.

Image 1: You start from the main form and  all the settings are very easy as described in the following image. From database selection to job scheduling, you can configure everything on one screen.

On this form you would usually do the following:

  1. Press “Connect to SQL Server” button to connect to your SQL Server instance
  2. Check databases in the list to select databases to backup
  3. If you need to store backups in a Local/Network folder – fill out the path to this folder
  4. If you need to send backups to FTP – fill out your FTP server settings
  5. Enter you email to receive job notification e-mails
  6. Fill out the time when a scheduled daily backup job should run
  7. Press “Run Now” button to test your backups

Note that the above covers just the basic scenario when you need full backups once a day. If you need Differential or Transaction Log backups or a schedule more complicated than once per day – press “Advanced Schedule”.

image001 SQL SERVER   SQL Backup and FTP   A Quick and Handy Tool

Image 2: Advanced Backup Schedule. Again, what I love about this program – it is how simply it allows you to resolve even quite complicated tasks. Your can click the sample scenario link that matches you needs the most – like “Daily 4 times” and everything is set – you’ll get one full and 3 Differential backups per day. Or select any other scenario that suits you.

image003 SQL SERVER   SQL Backup and FTP   A Quick and Handy Tool

Image 3: Advanced Settings screen is optional and allows you set encryption, compression levels, backup file name, temporary folders, if you want to use web logs (more about it below) and more

image005 SQL SERVER   SQL Backup and FTP   A Quick and Handy Tool

Image 4: If you checked “Use web log” in Advanced Settings, the log of your jobs is being saved on the web on sqlbackupandftp.com site (see weblog demo) and you can see the history of these jobs (even collected from multiple servers) in a single page – very interesting feature that can be very handy for large organizations

image007 SQL SERVER   SQL Backup and FTP   A Quick and Handy Tool

A detailed configuration tutorial is available here if you are interested.

Now, a question that might baffle your mind is – how this tool is better than maintenance tasks available in SQL Server. Well, here goes the answer. Maintenance tasks are easy to set up for backups; however, SQLBackupAndFTP have integrated solution for encryption, FTP and email which make it superior to maintenance tasks in every aspect.

I would like to present my unbiased view about this tool. At the same time, I want to give my readers a broad perspective about backup tools in general.  On comparing this tool with other backup tools I have experienced that it is not the fanciest tool to manage backups or check their health. This is one tool that smoothly performs the job in a very few steps compared to native SQL Server backup, which takes numerous steps to execute the similar job.

Image 5: Along with SQLBackupAndFTP setup gives you the option to install “One-Click SQL Restore” (you can install it stand-alone too) – a basic tool for restoring just Full backups.

image009 SQL SERVER   SQL Backup and FTP   A Quick and Handy Tool

However basic, you can drag-and-drop on it the zip file created by SQLBackupAndFTP, it unzips the BAK file if necessary, connects to the SQL server on the start, selects the right database, it is smart enough to restart the server to drop open connections if necessary – very handy for developers who need to restore databases often.

While testing SQLBackupAndFTP I noticed some limitations. It only works reliably on local SQL Server instances. In other words it has to be installed on the SQL server itself. For remote servers it uses scripting which is less reliable. This limitations is actually inherent in SQL server itself as BACKUP DATABASE command  creates backup not on the client, but on the server itself.

This tool is compatible with almost all the known SQL Server versions. It works with SQL Server 2008 (all versions) and many of the previous versions. It is especially useful for SQL Server Express 2005 and SQL Server Express 2008, as they lack built in tools for backup.

SQLBackupAndFTP is rapidly evolving since I first started using it two years ago. I spoke to developers and it seems that their immediate goal is to include cloud storage support – users will be able to backup directly to Microsoft SkyDrive, Amazon S3, DropBox, Box.net and others – very exciting development.

I strongly recommend this tool to all the DBAs. They must absolutely try it as it is free and does exactly what it promises. You can download your free copy of the tool from here.

Please share your experience about using this tool. I am eager to receive your feedback regarding this article. I will also give print book of my SQL Server Wait Stats book to two individual who share their feedback about this product. The book will be sent to any country where Amazon delivers the book.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – CE – List of Information_Schema System Tables

Yesterday I wrote  blog post that I downloaded WebMatrix and it was very easy to install, after installing I noticed it has default database as SQL CE. I started to play with SQL CE and I was glad that it supports many of the Information_Schema. There is one important thing I need to mention. Yesterday I shared Sample Database of the SQL CE. Few of the readers tried to install that database in other versions and it give them error. Please note that SQL CE will only and will not work with any other version of the database.

Here are few of the Information_schema system tables which works in SQL CE.

-- Get all the columns of the database
SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS
-- Get all the indexes of the database
SELECT * 
FROM INFORMATION_SCHEMA.INDEXES
-- Get all the indexes and columns of the database
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-- Get all the datatypes of the database
SELECT * 
FROM INFORMATION_SCHEMA.PROVIDER_TYPES
-- Get all the tables of the database
SELECT * 
FROM INFORMATION_SCHEMA.TABLES
-- Get all the constraint of the database
SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
-- Get all the foreign keys of the database
SELECT * 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Here is my question – how many of you use SQL CE and how many of you use SQL Express? and for what purpose?

Reference: Pinal Dave (http://blog.SQLAuthority.com)