All Articles - SQL Authority with Pinal Dave

Interview Question of the Week #007 – How to Reindex Every Table of the Database?

February 15, 2015

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

Some questions are extremely popular questions and they never get old. Here is one such question which I see very often asked to DBAs in their early career. Question: How to re-index every table of the database? Answer: Well, The answer of this question can be only given in the form of the…

SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

February 14, 2015

Pinal Dave

SQL Tips and Tricks

SQL Datatype, SQL Error Messages, SQL Scripts, SQL Server

Earlier this week, I have blogged about how to suppress Warning: Null value is eliminated by an aggregate or other SET operation SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation. Let us learn about the error STring or Binary Data would be truncated.

SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation

February 13, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages

Working with NULL’s are always a challenge – not many developers understand how NULL works sometimes. In a related note, long back I wrote below blog in which I explained two set options Quoted Identifier and ANSI NULL. SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation In future blogs,…

SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067

February 12, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Notes from the Field, SQL Backup and Restore, SSIS

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications. In this episode of the Notes from the…

SQL SERVER – Script to Find Leap Year

February 11, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

A leap year has 366 days. A leap year has 29 days for February month. Suppose you want to find if year is Leap year or not, you can use many methods. But this is one of the simplest methods. In our example I have passed the year 2000 as…

SQL SERVER – Error: Fix for Error Msg 3906 – Failed to update database because the database is read-only

February 10, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages

Some error messages are a great source of troubleshooting when things go wrong. I always request my blog readers to read what the error messages are conveying. Most of the solutions are very much documented as part of the message. I must agree that over the years the error messages…

SQL SERVER – Viewing User Configurable Info in SQL Server

February 9, 2015

Pinal Dave

SQL Tips and Tricks

SQL Scripts

Most of the blog post as I say is revisiting something I wrote a while back in this blog. Recently one my blog readers asked me about user defined counters and he seems to have bumped into the blog: How to use Procedure sp_user_counter1 to sp_user_counter10 and was thinking if…

Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

February 8, 2015

Pinal Dave

SQL, SQL Interview Questions and Answers, SQL Server, SQL Tips and Tricks

Shrinking Database

Here is the most debated Interview Question – Is Shrinking Database Good or Bad? I will try to answer this in a single statement – “Shrinking Database is bad practice for performance as it increases fragmentation. It should be used in rare cases of running out of space on drive.” I believe above…

SQL SERVER – What are the Different Ways to Script Procedure with T-SQL

February 7, 2015

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Stored Procedure

Ever wondered how a simple task can be done multiple ways. You can script a stored procedure in many ways. You can use SSMS tool and use Generate Script option. However, if you want to do it in T-SQL, there are four ways. Let us create this simple procedure CREATE…

SQL SERVER – One Trick of Handling Dynamic SQL to Avoid SQL Injection Attack?

February 6, 2015

Pinal Dave

SQL Tips and Tricks

MySQL, SQL Scripts, SQL Server, SQL Server Security

SQL Server has so many things to learn and I always find it amazing. My conversations with customers often come up with security questions esp around SQL Injection. Many have claimed SQL Injection is a SQL Server problem. It takes quite some time for me to let them know there is nothing about SQL Server and SQL Injection. SQL Injection is an outcome of wrong coding practices. One of the recommendations I give is about not using Dynamic SQL. There might be some situations where you can’t avoid it. My only advice would be, avoid if possible. In this blog, I would demonstrate a SQL Injection problem due to dynamic SQL and a possible solution you can have.