SQL SERVER – Weekly Series – Memory Lane – #033

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Spatial Database Definition and Research Documents
Here is the definition from Wikipedia about spatial database :
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types.

Select Only Date Part From DateTime – Best Practice
A very common question which I receive is how to only get Date or Time part from datetime value. In this blog post I explain the same in very simple words.

T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table
I have received few emails and comments about my post SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005. The main question was is this can be done using CTE? Absolutely! What about Performance? It is identical! Please refer above mentioned article for the history of paging.

SQL SERVER – Cannot resolve collation conflict for equal to operation
One of the very first error I ever encountered in my career was to resolve this conflict. I have blogged about it and I have realized that many others like me who are facing this error.

LEN and DATALENGTH of NULL Simple Example
Here is the question for you what is the LEN of NULL value? Well it is very easy – just read the blog.

Recovery Models and Selection
Very simple and easy explanation of the Database Backup Recovery Model and how to select the best option for you.

Explanation SQL SERVER Hash Join
Hash join gives best performance when two more join tables are joined and at-least one of them have no index or is not sorted. It is also expected that smaller of the either of table can be read in memory completely (though not necessary).

Easy Sequence of SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY

SELECT yourcolumns
FROM tablenames
JOIN tablenames
WHERE condition
GROUP BY yourcolumns
HAVING aggregatecolumn condition
ORDER BY yourcolumns

NorthWind Database or AdventureWorks Database – Samples Databases
In this blog post we learn how to install Northwind database. I also shared the source where one can download this database as that is used in many examples on MSDN help files.

sp_HelpText for sp_HelpText – Puzzle
A simple quick puzzle – do you know the answer of it? If not, go ahead and read the blog.

2008

SQL SERVER – 2008 – Step By Step Installation Guide With Images
When SQL Server 2008 was newly introduced lots of people had no clue how to install SQL Server 2008 and the amount of the question which I used to receive were so much. I wrote this blog post with the spirit that this will help all the newbies to install SQL Server 2008 with the help of images. Still today this blog post has been bible for all of the people who are confused with SQL Server installation.

Inline Variable Assignment
I loved this feature. I have always wanted this feature to be present in SQL Server. The last time when I met developers from Microsoft SQL Server, I had talked about this feature. I think this feature saves some time but make the code more readable.

Introduction to Policy Management – Enforcing Rules on SQL Server
If our company policy is to create all the Stored Procedure with prefix ‘usp’ that developers should be just prevented to create Stored Procedure with any other prefix. Let us see a small tutorial how to create conditions and policy which will prevent any future SP to be created with any other prefix.

2009

Performance Counters from System Views – By Kevin Mckenna
Many of you are not aware of this fact that access to performance information is readily available in SQL Server and that too without querying performance counters using a custom application or via perfmon. Till now, this fact has remained undisclosed but through this post I would like to explain you can easily access SQL Server performance counter information. Without putting much effort you will come across the system viewsys.dm_os_performance_counters. As the name suggests, this provides you easy access to the SQL Server performance counter information that is passed on to perfmon, but you can get at it via tsql.

Customize Toolbar – Remove Debug Button from Toolbar
I was fond of SQL Server Debugger feature in SQL Server 2000. To my utter disappointment, this feature was withdrawn from SQL Server 2005. The button of the debugger is similar to a play button and is used to run debugging commands of Visual Studio. Because of this reason, it gets very much infuriating for developers when they are developing on both – Visual Studio and SSMS. Let us now see how we can remove debugging button from SQL Server Management Studio.

Effect of Normalization on Index and Performance
A very interesting conversation which started from twitter. If you want to read one link this is the link I encourage you to read it.

SSMS Feature – Multi-server Queries
Using SQL Server Management Studio (SSMS) DBAs can now query multiple servers from one window. It is quite common for DBAs with large amount of servers to maintain and gather information from multiple SQL Servers and create report. This feature is a blessing for the DBAs, as they can now assemble all the information instantaneously without going anywhere.

Query Optimizer Hint ROBUST PLAN – Question to You
“ROBUST PLAN” is a kind of query hint which works quite differently than other hints. It does not improve join or force any indexes to use; it just makes sure that a query does not crash due to over the limit size of row. Let me elaborate upon it in the blog post.

2010

Do you really know the difference between various date functions available in SQL Server 2012?
Here is a three part story where we explored the same with examples:

Shrinking NDF and MDF Files – Readers’ Opinion
Shrinking Database always creates performance degradation and increases fragmentation in the database. I suggest that you keep that in mind before you start reading the following comment. If you are going to say Shrinking Database is bad and evil, here I am saying it first and loud. Now, the comment of Imran is written while keeping in mind only the process showing how the Shrinking Database Operation works. Imran has already explained his understanding and requests further explanation. I have removed the Best Practices section from Imran’s comments, as there are a few corrections.

2011

Solution – Puzzle – SELECT * vs SELECT COUNT(*)
This is very interesting question and I am very confident that not every one knows the answer to this question. Let me ask you again – Which will be faster SELECT* or SELECT COUNT (*) or do you think this is apples and oranges comparison.

2012

Service Broker and CAP_CPU_PERCENT – Limiting SQL Server Instances to CPU Usage
In SQL Server 2012 there are a few enhancements with regards to SQL Server Resource Governor. One of the enhancement is how the resources are allocated. Let me explain you with examples. Let us understand the entire discussion with the help of three different examples.

Finding Size of a Columnstore Index Using DMVs
One of the very common question I often see is need of the list of columnstore index along with their size and corresponding table name. I quickly re-wrote a script using DMVs sys.indexes and sys.dm_db_partition_stats. This script gives the size of the columnstore index on disk only. I am sure there will be advanced script to retrieve details related to components associated with the columnstore index. However, I believe following script is sufficient to start getting an idea of columnstore index size.

Developer Training Resources and Summary Roundup

Developer Training – Importance and Significance – Part 1

In this part we discussed the importance of training in the real world. The most important and valuable resource any company is its employee. Employees who have been well-trained will be better at their jobs and produce a better product.  An employee who is well trained obviously knows more about their job and all the technical aspects. I have a very high opinion about training employees and it is the most important task.

Developer Training – Employee Morals and Ethics – Part 2

In this part we discussed the most crucial components of training. Often employees are expecting the company to pay for their training and the company expresses no interest in training the employee. Quite often training expenses are the real issue for both the employee and employer.

Developer Training – Difficult Questions and Alternative Perspective – Part 3

This part was the most difficult to write as I tried to address a few difficult questions and answers. Training is such a sensitive issue that many developers when not receiving chance for training think about leaving the organization.

Developer Training – Various Options for Developer Training – Part 4

In this part I tried to explore a few methods and options for training. The generic feedback I received on this blog post was short and I should have explored each of the subject of the training in details. I believe there are two big buckets of training 1) Instructor Lead Training and 2) Self Lead Training.

Developer Training – A Conclusive Summary- Part 5

There is no better motivation than a personal desire to learn new technology. Honestly there is nothing more personal learning. That “change is the only constant” and “adapt & overcome” are the essential lessons of life. One cannot stop the learning and resist the change. In the IT industry “ego of knowing all” and the “resistance to change” are the most challenging issues.

A Quick Look at Logging and Ideas around Logging
Question: What is the first thing comes to your mind when you hear the word “Logging”? Strange enough I got a different answer every single time. Let me just list what answer I got from my friends. Let us go over them one by one.

Beginning Performance Tuning with SQL Server Execution Plan

Solution of Puzzle – Swap Value of Column Without Case Statement
Earlier this week I asked a question where I asked how to Swap Values of the column without using CASE Statement. Read here: SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement. I have proposed 3 different solutions in the blog posts itself. I had requested the help of the community to come up with alternate solutions and honestly I am stunned and amazed by the qualified entries.

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

SQL SERVER – Weekly Series – Memory Lane – #032

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download

Explanation and Example – SELF JOIN
When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.

Insert Multiple Records Using One Insert Statement – Use of UNION ALL
This is very interesting question I have received from new developer. How can I insert multiple values in table using only one insert? Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.

Function to Display Current Week Date and Day – Weekly Calendar
Straight blog post with script to find current week date and day based on the parameters passed in the function. 

2008

In my beginning years, I have almost same confusion as many of the developer had in their earlier years. Here are two of the interesting question which I have attempted to answer in my early year. Even if you are experienced developer may be you will still like to read following two questions:

Example of DISTINCT in Aggregate Functions
Have you ever used DISTINCT with the Aggregation Function? Here is a simple example about how users can do it.

Create a Comma Delimited List Using SELECT Clause From Table Column
Straight to script example where I explained how to do something easy and quickly.

Compound Assignment Operators
SQL SERVER 2008 has introduced new concept of Compound Assignment Operators. Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators is operator where variables are operated upon and assigned on the same line.

PIVOT and UNPIVOT Table Examples
Here is a very interesting question – the answer to the question can be YES or NO both.

“If we PIVOT any table and UNPIVOT that table do we get our original table?”

Read the blog post to get the explanation of the question above.

2009

What is Interim Table – Simple Definition of Interim Table
The interim table is a table that is generated by joining two tables and not the final result table. In other words, when two tables are joined they create an interim table as resultset but the resultset is not final yet. It may be possible that more tables are about to join on the interim table, and more operations are still to be applied on that table (e.g. Order By, Having etc). Besides, it may be possible that there is no interim table; sometimes final table is what is generated when the query is run.

2010

Stored Procedure and Transactions
If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Generate Database Script for SQL Azure
When talking about SQL Azure the most common complaint I hear is that the script generated from stand-along SQL Server database is not compatible with SQL Azure. This was true for some time for sure but not any more. If you have SQL Server 2008 R2 installed you can follow the guideline below to generate a script which is compatible with SQL Azure.

Convert IN to EXISTS – Performance Talk
It is NOT necessary that every time when IN is replaced by EXISTS it gives better performance. However, in our case listed above it does for sure give better performance. You can read about this subject in the associated blog post.

Subquery or Join – Various Options – SQL Server Engine Knows the Best
Every single time whenever there is a performance tuning exercise, I hear the conversation from developer where some prefer subquery and some prefer join. In this two part blog post, I explain the same in the detail with examples.

Part 1 | Part 2

Merge Operations – Insert, Update, Delete in Single Execution
MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted.

2011

Puzzle – Statistics are not updated but are Created Once

Here is the quick scenario about my setup.

  • Create Table
  • Insert 1000 Records
  • Check the Statistics
  • Now insert 10 times more 10,000 indexes
  • Check the Statistics – it will be NOT updated – WHY?

Question to You – When to use Function and When to use Stored Procedure
Personally, I believe that they are both different things ‑ they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question.

2012

In year 2012 I had two interesting series ran on the blog. If there is no fun in learning, the learning becomes a burden. For the same reason, I had decided to build a three part quiz around SEQUENCE. The quiz was to identify the next value of the sequence. I encourage all of you to take part in this fun quiz.

Simple Example to Configure Resource Governor – Introduction to Resource Governor
Resource Governor is a feature which can manage SQL Server Workload and System Resource Consumption. We can limit the amount of CPU and memory consumption by limiting /governing /throttling on the SQL Server. If there are different workloads running on SQL Server and each of the workload needs different resources or when workloads are competing for resources with each other and affecting the performance of the whole server resource governor is a very important task.

Tricks to Replace SELECT * with Column Names – SQL in Sixty Seconds #017 – Video

  1.  Retrieves unnecessary columns and increases network traffic
  2. When a new columns are added views needs to be refreshed manually
  3. Leads to usage of sub-optimal execution plan
  4. Uses clustered index in most of the cases instead of using optimal index
  5. It is difficult to debug

SQL SERVER – Load Generator – Free Tool From CodePlex
The best part of this SQL Server Load Generator is that users can run multiple simultaneous queries again SQL Server using different login account and different application name. The interface of the tool is extremely easy to use and very intuitive as well.

A Puzzle – Swap Value of Column Without Case Statement
Let us assume there is a single column in the table called Gender. The challenge is to write a single update statement which will flip or swap the value in the column. For example if the value in the gender column is ‘male’ swap it with ‘female’ and if the value is ‘female’ swap it with ‘male’.

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

SQL SERVER – Weekly Series – Memory Lane – #031

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Find Table without Clustered Index – Find Table with no Primary Key
Clustered index is very important concept for any table. They impact the performance very heavily. Here is a quick script to find tables without a clustered index.

Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types
Question: “Is VARCHAR (MAX) big enough to store the TEXT field?”
Answer: “Yes, VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in a future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommended to use new data types which are VARHCAR (MAX), NVARCHAR (MAX) and VARBINARY (MAX).”

Limiting Result Sets by Using TABLESAMPLE – Examples
Introduced in SQL Server 2005, TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

User Defined Functions (UDF) Limitations
UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF). However, this blog post is a good read to know what are the limitations of UDF.

Change Database Compatible Level – Backward Compatibility
For a long time SQL Server stayed on the compatibility level of 80 which is of SQL Server 2000. However, as soon as SQL Server 2005 introduced the issue of compatibility was quite a major issue. Since that time MS has been releasing the versions at every 2-3 years, changing compatibility is a ever popular topic. In this blog post, we learn how we can do the same using T-SQL. We can also do the same using SSMS and here is the blog post for the same: Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio.

Constraint on VARCHAR(MAX) Field To Limit It Certain Length
How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowed 12500 characters. First of all – this requirement is bit strange but if someone wants to do the same, they can do it as described in this blog post.

2008

UNPIVOT Table Example
Understanding UNPIVOT can be very complicated at times. In this blog post, I have attempted to explain the same concept in very simple words.

Create Default Constraint Over Table Column
A simple straight to script blog post – I still use this blog quite many times for my own reference.

UDF – Get the Day of the Week Function
It took me 4 iteration to find this very simple function which can immediately get the day of the week in a single line.

2009

Find Hostname and Current Logged In User Name
There are two tricks listed in this blog post where users can find out the hostname and current logged user name immediately and very easily.

Interesting Observation of Logon Trigger On All Servers
When I was doing a project, I made an interesting observation of executing a logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it did it multiple times on different threads – indeed an eccentric phenomenon at first sight!

Difference Between Candidate Keys and Primary Key
One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.

Create Multiple Filegroup For Single Database
Why should one create multiple file group for any database and what are the advantages of the same. In this blog post, I explain the same in detail.

List All Objects Created on All Filegroups in Database
In this blog post we discuss the essential question – “How can I find which object belongs to which filegroup. Is there any way to know this?”

2010

DATE and TIME in SQL Server 2008
When DATE is converted to DATETIME it adds the of midnight. When TIME is converted to DATETIME it adds the date of 1900 and it is something one wants to consider if you are going to run scripts from SQL Server 2008 to earlier version with CONVERT.

Disabled Index and Update Statistics
If you do not need a nonclustered index, I suggest you to drop it as keeping them disabled is an overhead on your system. This is because every time the statistics are updated for system all the statistics for disabled indexes are also updated.

Precision of SMALLDATETIME – A 1 Minute Precision
The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.

2011

Getting Columns Headers without Result Data – SET FMTONLY ON
SET FMTONLY ON returns only metadata to the client. It can be used to test the format of the response without actually running the query. When this setting is ON the resultset only have headers of the results but no data.

Copy Database from Instance to Another Instance – Copy Paste in SQL Server
SQL Server has a feature which copy database from one database to another database and it can be automated as well using SSIS. Make sure you have SQL Server Agent Turned on as this feature will create a job.

Puzzle – SELECT * vs SELECT COUNT(*)
If you have ever wondered SELECT * gives error when executed alone but SELECT COUNT(*) does not. Why? in that case, you should read this blog post.

Creating All New Database with Full Recovery Model
This blog post is very based on very interesting story where the user wants to do something by default for every single new database created. Model database is a secret weapon which should be used very carefully and with proper evalution. If used carefully this can be a very much beneficiary when we need a newly created database behave in certain fashion.

2012

In year 2012 I had two interesting series ran on the blog. If there is no fun in learning, the learning becomes a burden. For the same reason, I had decided to build a three part quiz around SEQUENCE. The quiz was to identify the next value of the sequence. I encourage all of you to take part in this fun quiz.

Can anyone remember their final day of schooling?  This is probably a silly question because – of course you can!  Many people mark this as the most exciting, happiest day of their life.  It marks the end of testing, the end of following rules set by teachers, and the beginning of finally being able to earn money and work in your chosen field.

Read five part series on developer training subject

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

SQL SERVER – Weekly Series – Memory Lane – #030

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

ASCII to Decimal and Decimal to ASCII
I still use this script many times in my daily work. It works and it is cool.

Script/Function to Find Last Day of Month
A simple trick but we are often lazy to write, a script like this from the archives can immediately help.

Comparison EXCEPT operator vs. NOT IN
The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there is no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that do not also return from the right query.

2005 Explanation Left Semi Join Showplan Operator and Other Operator
The Left Semi Join operator returns each row from the first (top) input when there is a matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row. The Left Anti Semi Join operator returns each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row.

NorthWind Database or AdventureWorks Database – Samples Databases
Where can you find Northwind database and how can you install it – this is very simple basic blog which can do the same.

Disadvantages (Problems) of Triggers
I personally do not prefer to use triggers in my daily work as there are many disadvantages associated with it. I only use it in the case of debugging. In this blog post, I have listed few of the disadvantages of triggers.

Change Default Fill Factor For Index
Fill factor is one of the most important parts of the index and I have seen many database getting slower or under performing due to they have very incorrect fill factor. In this blog post I explain how one can change fill factor to good value.

2008

SQL SERVER – PIVOT Table Example
In this blog in very simple words I have attempted to explain how PIVOT works and also have included the script along with it.

T-SQL Script to Devide One Column into Two Column
This is indeed a strange question – why would you divide one column into two columns but there are reasons for doing the same. We often faced a situation where one column in the database contained two values which were separated by comma. We wanted to separate these two values in their own columns. It was interesting that the value of the column was variable and something dynamic needed to be written.

2009

Download PDF SQL Server Cheat Sheet
You can download SQL Server Cheat Sheet and print it on a glossy paper yourself. I request you all to spread the words and pass this cheat sheet to your friends. Bookmark it, reprint it, and distribute it! Due to copyright issues, you are not allowed to host it online anywhere other than its original location listed below.

Fix connection error to SQL Server
There are many reasons for user not able to connect to SQL Server. In this blog post, I went over in detail about the reasons how user can’t connect to SQL Server and what is the workaround for the same. I also built a video for the same, where I demonstrate how one can resolve it in 60 seconds.

2010

Simple Example of Snapshot Isolation – Reduce the Blocking Transactions
Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands.

2011

Connecting to Server Using Windows Authentication by SQLCMD
In this simple blog post I explain how to connect to SQL Server using SQLCMD.

Running SSIS Package in Scheduled Job
Can we run SSIS package from schedule Job? Absolutely, we can. Additionally we can also run an SSIS package from the command line.

2012

Saturday Fun Puzzle with SQL Server DATETIME2 and CAST
Here is the puzzle with DATETIME2

Renaming Index – Index Naming Conventions
Is NCI and CI prefixed required to additionally describe the index names. I have once received suggestion to even add fill factor in the index name – which I do not recommend at all. What do you think should be an ideal name of the index, so it explains all the most important properties? Additionally, you are welcome to vote if you believe changing the name of the index is just a waste of time and energy.

SmallDateTime and Precision – A Continuous Confusion
This question once again brings up another ancient question:  “Do we need a database designer?” I often come across databases which are facing major performance problems or have redundant data. Normalization is often ignored when a database is built fast under a very tight deadline. Often I come across a database which has table with unnecessary columns and performance problems. While working as Developer Lead in my earlier jobs, I have seen developers adding columns to tables without anybody’s consent and retrieving them as SELECT *.

Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video

A Puzzle – Fun with NULL – Fix Error 8117
It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense.

A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value
See if you can figure out why there is an error due to UNION.

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

SQL SERVER – Weekly Series – Memory Lane – #029

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

List all the database
A Simple script which list all the database from the server.

Function to Parse AlphaNumeric Characters from String
Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is a very handy function when working with Alphanumeric String only. I have used this many times.

Query to Find First and Last Day of Current Month
The blog contains the query, which will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

Binary Sequence Generator – Truth Table Generator
Run script from the blog in the query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively.

Disable Index – Enable Index – ALTER Index
A straight to script blog where I demonstrate how to enable and disable index.

2008

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2
There is lots of confusing going on about difference between TempTable and TempVariable. In this blog I take another attempt about how to find difference between TempTable and TempVariable.

SQL SERVER Database Administrator Job Description
I have received quite a lot of requests to update it or post something similar. Writing SQL Articles are easier than writing Job description for DBA. I have read many job description and job posting at Best SQL Jobs and found the following job description.

2009

This year, I have attended my very first TechEd and presented very first session ever. Here is a photo with my wife Nupur at this event.

How to Drop Temp Table – Check Existence of Temp Table
A quick direct to script blog post.

2010

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
The difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD along with the disabled clustered index. Instead of writing a big theory, we will go over the demo right away. Here are the steps that we intend to follow.

1) Create Clustered and Non-clustered Index
2) Disable Clustered and Non-clustered Index
3) Enable – a) All Indexes, b) Clustered Index

Find Most Expensive Queries Using DMV
I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online.

List All the DMV and DMF on Server
“How many DMVs and DVFs are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Trainings. The answer is very simple in this blog post.

2011

Import CSV File into Database Table Using SSIS
It is a very frequent request to upload CSV file to database or Import CSV file into a database. I have previously written an article how one can do this using T-SQL over here  Import CSV File Into SQL Server Using Bulk Insert. One of the request which I quite often encounter is how I can do the same using SSIS package and automate it. Today we will understand the same using images.

Restart Remote Computer – Shutdown Remote Computer
Here is a quick trick which I use almost everyday. I have more than one computer at my desk and I manage multiple instances from a single monitor. Some of my computers are in a different location and I have to physically walk there. In this blog post, I demonstrated a quick method where I can use CMD prompt to restart and shutdown remote computer.

2012

Columnstore Index and sys.dm_db_index_usage_stats
Regular SQL Server DMV does not give information for usage stats for columnstore indexes and for that one has to use different DMV. In this blog post I explain the same in detail with DMV script. I also describe my journey how I come across this DMV.

Get Schema Name from Object ID using OBJECT_SCHEMA_NAME
Sometime a simple solution have even simpler solutions but we often do not practice it as we do not see the value in it or find it useful. Well, today’s blog post is also about something which I have seen not practiced much in codes. We are so much comfortable with an alternative usage that we do not feel like switching how we query the data.

SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series
Here are the five videos which Rick and I have built for Joes 2 Pros series.

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

SQL SERVER – Weekly Series – Memory Lane – #028

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

UDF – Function to Convert List to Table
Article contains UDF written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR. It will convert the list to table.

Creating Comma Separate Values List from Table – UDF – SP
Script contained in the blog will create common separate values (CSV) or common separate list from tables, convert list to table. Following script is written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR (MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

Examples and Explanation for GOTO
GOTO statement can be used anywhere within a procedure, batch, or function. GOTO can be nested as well. GOTO can be executed by any valid user on SQL SERVER. GOTO can co-exists with other control of flow statements (IFELSE, WHILE). GOTO can only go(jump) to label in the same batch, it can not go to label outside of the batch.

Index Optimization CheckList
Index optimization is always interesting subject to me. Every time I receive request to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.

Explanation SQL Command GO
GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

2008

Here are two straight to script blog posts where I explain how to do something quickly and easily.

2009

Find Last Date Time Updated for Any Table
If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys. dm_db_index_usage_stats and easily figure out when the table was updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.

Questions and Answers with Database Administrators
Interesting conversation – I recommend everyone to read this.

Q. According to you what goes into making the best Database Administrator?

A. The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

2010

Get Latest SQL Query for Sessions – DMV
A straight to script blog which is my favorite as well.

Size of Index Table for Each Index – Solution 2
Another blog post where I explain how to find size of index using T-SQL script.

Spatial Database Queries – What About BLOB
In this blog post I explain how to find distance between two cities using Spatial Database.

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
Not everyone knows the difference between ALTER INDEX ALL REBUILD and disabled Clustered index. In this blog post, I have tried to explain the same with example.

2011

Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
One of the most popular blog post because it is the basic requirement of the developer. Developers like to do things their own way on their database. This blog post explains how developers can absolutely create another replica of the database and later do practice over it.

What Kind of Lock WITH (NOLOCK) Hint Takes on Object?
Do we know what kind of lock WITH(NOLOCK) hint takes on object. Answer is Schema Lock. Yes, WITH(NOLOCK) hint takes Schema Lock on the object which is accessed. In this blog post we have working example where I demonstrate the same.

Resource Database ID – 32767
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

2012

In year 2012 in a single week, I had come up with the Quiz based on the Joes 2 Pros Book and here are the five questions asked in the quiz. Just like every other quiz, this quiz also had a video hints. Something very unique and never been attempted.

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

SQL SERVER – Weekly Series – Memory Lane – #027

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Good, Better and Best Programming Techniques
Well, here is my note which I prepared to discuss in my earlier meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.

Explanation SQL SERVER Merge Join
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

Random Number Generator Script – SQL Query
One of the most popular blog post where I demonstrate how many different ways one can generate random numbers. I have also built a SQL in Sixty Seconds Video on this subject as well.

SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query
SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views and when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause. SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

Collate – Case Sensitive SQL Query Search
Here is a quick script which does case sensitive SQL Query Search in the database.

Copy Column Headers in Query Analyzers in Result Set
Another very popular request which I transformed into SQL in Sixty Seconds Video.

User Defined Functions (UDF) to Reverse String – UDF_ReverseString
UDF_ReverseString User Defined Functions returns the Reversed String starting from certain positions. First parameters take the string to be reversed. Second parameters take the position from where the string starts reversing.

TOP Improvements/EnhancementsSQL Server 2005 introduces two enhancements to the TOP clause.
1) User can specify an expression as an input to the TOP keyword.
2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE).

Enable Login – Disable Login using ALTER LOGIC – Change name of the ‘SA’
One of the most important but ignored SQL Security Tip. A must read for everyone!

2008

Find Table in Every Database of SQL Server
This is one of the most asked T-SQL Script where user want to know where the table is used in every single database on the server. It is a two part story. Do not miss to read Part 1 and Part 2.

Delete Duplicate Records – Rows – Readers Contribution
A quick script from reader very important article but very less explored by users. Let me know your thoughts about this one.

2009

Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script
SQL Server 2005 and later versions provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.

Solution to Puzzle – Shortest Code to Perform SSN Validation
Write the shortest T-SQL Code that removes invalid SSN values and returns a result set with only valid SSN values. This blog post was winner of the T-SQL Challenges on the same subject. If will be interesting to see if you can come up with a better optimized solution.

Example of DDL, DML, DCL and TCL Commands
Data Manipulation Language, Data Definition Language, Data Control Language, Transactional Control Language explained in simple words.

Add or Remove Identity Property on Column
How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action? For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table. However, there is an easy way to accomplish this action. It can be done through SSMS.

Roadmap of Microsoft Certifications – SQL Server Certifications
There are several benefits you can gain after clearing certification exams. After passing the first exam, developers earn credential of Microsoft Certified Professional commonly known as MCP. Once credentials are achieved developers get access to an official transcript, logos and certificates, and have their own landing page on Microsoft.com! According to Microsoft’s official site, real MCPs get real benefits of  Reward, Respect and Recognition.

2010

GUID vs INT – Your Opinion
This is an age old problem and I want to compile the list stating the advantages and disadvantages of using GUID and INT as a Primary Key or Clustered Index or Both (the usual case). The epic intense debate is happening on this particular topic till today after 3 years. You can see there is a wealth of information on this blog which one can just learn reading the original blog post as well as comments associated with the blog post.

Disable Clustered Index and Data Insert
Should we disable the clustered index while we insert the large data or we should drop the clustered index while we insert large data. Honestly, the answer is very simple, drop the clustered index. If we disable clustered index the impact of it is much different than we expect. Check out this blog post which describes the same issue with working demo.

Here is one of my old photo of TechEd 2010 – I had great time presenting on the stage in year 2010.

What is AdventureWorks?
This question is always there in my mailbox. Till today, everyday multiple times. Here is the blog post where I describe where one can get AdventureWorks as well I explain how they can install it as well.

SHRINKFILE and TRUNCATE Log File in SQL Server 2008
There are few absolute No-No’s in SQL Server environment. There should be no need to shrink and truncate log files in daily routine. Though this blog post was written keeping SQL Server 2008 in mind, this is also valid in SQL Server 2008 R2 and SQL Server 2012. If you are going to shrink your database, I strongly suggest that you read this blog post before it.

2011

Executing Stored Procedure with Result Sets

Q: Why are you using User Defined Function instead of Stored Procedure?

A: I cannot SELECT from SP, but I can from UDF.

The SQL Server 2012 version has come up with a very interesting feature called WITH RESULT SET.

Read here the follow up article.

CTAS – Create Table As SELECT – What is CTAS?
CTAS stands for ‘Create Table As Select’. This method is used when table was not created earlier and needs to be created when data from one table needs to be inserted into a newly created table from another table. The new table is created with the same data types as that of the selected columns.

Performance Improvement with of Executing Stored Procedure with Result Sets in SQL Server 2012
This is one of the article which really explains why one should use any feature for example. One should use the feature of Executing Stored Procedure with Resulset because it gives better performance over traditional methods.

2012

Microsoft Certification – SQL Server 2012
Read this blog post which describes how new Microsoft Certification have changed with the release of SQL Server 2012.

Video – Step by Step Installation of SQL Server 2012
Here is a video tutorial for how to install SQL Server 2012.

A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function
Write a select statement using a single table, using single table single time only without using join keywords, which generate an execution plan with 2 join operators. Use AdventureWorks as a sample database.

Identify Columnstore Index Usage from Execution Plan
How do I know if columnstore index is used by query through execution plan? Here is the detail blog post about how to identify if columnstore index is used in the query or not.

Here are the steps which are to be followed:

  • Create a sample table
  • Insert some data
  • Create clustered index on it
  • Create nonclustered Columnstore Index on it
  • Enable execution plan in SSMS
  • Run two SELECT statement together with using clustered index and columnstore index (use hint if needed)

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

SQL SERVER – Weekly Series – Memory Lane – #026

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

SQL Server Interview Questions and Answers Complete List Download
SQL Server interview questions and answers is very crucial for any beginners. Some use this as a reference for future and some use it for refreshing the technology. Well, anyway, this is one of the most popular download on this blog.

@@DATEFIRST and SET DATEFIRST Relations and Usage
The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as the first day of the week using DATEFIRST.

Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index
In SQL Server 2005, the functionality of non-clustered indexes is extended by adding non-key columns to the leaf level of the non-clustered index. Non-key columns, can help to create cover indexes.By including non-key columns, you can create non-clustered indexes that cover more queries. The Database Engine does not consider non-key columns when calculating the number of index key columns or index key size.

Query to Find Seed Values, Increment Values and Current Identity Column value of the table
Script in the blog will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table.

TRIM() Function – UDF TRIM()
A very interesting blog post how we can trim any column value in database.

Take Off Line or Detach Database
Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds.

Difference Between Unique Index vs Unique Constraint
There is no difference between Unique Index and Unique Constraint. Even though their syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys.

SELECT vs. SET Performance Comparison
SET is the ANSI standard for variable assignment, SELECT is not. SET can only assign one variable at a time, SELECT can make multiple assignments at once – that gives SELECT slight speed advantage over SET.

Query to Retrieve the Nth Maximum Value
A very popular script on my blog. I am sure you have faced the similar situation in future.

2008

Better Performance – LEFT JOIN or NOT IN?
First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing a query? The answer is : It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN.

Optimization Rules of Thumb – Best Practices
There are few rules for optimizing slow running query. Let us look at them one by one to see how it can help. I started with first six suggestions and later on I asked users to come up with the seventh suggestion. One of the readers actually came up with an entire blog post based on my earlier article. Read that here: Optimization Rules of Thumb – Best Practices – Reader’s Article

2009

Starting the SQL Journey – How Did I Get Started With SQL?
This is one of the most interesting blog post, I keep on reading it again and again. I started my career from Las Vegas and currently I am in Bangalore. My journey has been extremely long and it is almost 7 years old journey. I strongly suggest that everyone who is interested to know how I get here and what I have been doing, please read this blog post about me.

  

2010

Create Primary Key with Specific Name when Creating Table
Often primary keys are created with a default name and it is a good idea that we create primary keys with specific name so it helps readability and user can directly know lots of information if he/she is familiar with the naming convention.

Update Statistics are Sampled By Default
Question: Are the statistics sampled by default?
Answer: Yes. The sampling rate can be specified by the user and it can be anywhere between a very low value to 100%.

Attach mdf file without ldf file in Database
If you have only MDF file of the database it is absolutely possible to restore it without LDF file as well. Read this interesting story where I explain how we can do it.

2011

Prevent Constraint to Allow NULL
Here is a quick script which describes how to create a constraint which allows NULL.

Using Decode in SQL Server
There is no DECODE function in SQL Server, one has to use a CASE statement to simulate this one.

How to ALTER CONSTRAINT
Very simple straight to the script blog post.

2012

Working with FileTables in SQL Server 2012
This is a very interesting subject and I have written a three part blog series on this subject. I recommend everyone to read each of these three parts to understand the subject. Part 1 | Part 2 | Part 3

Do you want to learn SQL Server? Here are three excellent links about this subject where we have taken five different articles from Joes 2 Pros book series.

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

SQL SERVER – Weekly Series – Memory Lane – #025

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

CASE Statement/Expression Examples and Explanation
CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

This is very old series but very relevant none the less. I later on published a book on this subject and one of the most popular one. You can download the sample chapters over here

Sample Chapters

SQL Server Interview Questions and Answers -Introduction
SQL Server Interview Questions and Answers – Part 1
SQL Server Interview Questions and Answers – Part 2
SQL Server Interview Questions and Answers – Part 3
SQL Server Interview Questions and Answers – Part 4
SQL Server Interview Questions and Answers – Part 5
SQL Server Interview Questions and Answers – Part 6
SQL Server Interview Questions and Answers Complete List Download

2008

Row Constructors – Load Temp Tables From Stored Procedures
Though I use following feature every day now a days when it was newly introduced it was quite a novelty for me. This feature existed in other database but was not available with SQL Server was I was very much delighted to have it.

Generate Foreign Key Scripts For Database
This script generates all the foreign key addition script for your database. Many times there are situations where one need to drop all the foreign key and add them back. This SQL Script can be used for the same purpose.

2009

Check if Current Login is Part of Server Role Member
Most of the time, I get a login and DBA from my clients. However, sometimes I face login-related problems, primarily because my clients forget to confer admin rights on me. Anticipating this situation I perform a simple task, which saves me time and saves me from exasperation.  Whenever I receive a server login I run the following query to verify if  the client has assigned me the role of system admin or not.

2010

What is Spatial Database? – Developing with SQL Server Spatial and Deep Dive into Spatial Indexing
A spatial database is a database that is optimized to store and query data related to objects in space, including points, lines and polygons. While typical databases can understand various numeric and character types of data, additional functionality needs to be added for databases to process spatial data types.

SELECT TOP Shortcut in SQL Server Management Studio (SSMS)
This tool is pretty old, yet always comes as a handy tip. I had a great trip at TechEd in India. And, during one of my presentations, I was asked if there are any shortcuts to SELECT only TOP 100 records from SSMS. I immediately told him that if he explores the table in SSMS, he can just right click on it and SELECT TOP 1000 records. If he wanted only 100 records, then he could edit that 1000 to 100 by means of going to Options.

Find Most Active Database in SQL Server – DMV dm_io_virtual_file_stats
A quick DMV script which finds the most active database in SQL Server. A must bookmark.

Find Max Worker Count using DMV – 32 Bit and 64 Bit
To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:

For x86 (32-bit) upto 4 logical processors  max worker threads = 256
For x86 (32-bit) more than 4 logical processors  max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors  max worker threads = 512
For x64 (64-bit) more than 4 logical processors  max worker threads = 512+ ((# Procs – 4) * 8)

2011

Transaction Log Impact Detection Using DMV – dm_tran_database_transactions
A straight to script blog post where I explain using DMV how to detect transaction log.

Finding Location of Log File when Primary Datafile is Crashed
Quick Quiz:Do you need the primary data file available to backup your transaction log after a crash?

This question can have multiple answers. While he asked the question on blog, I was sitting very next to him and he asked what do I think about it. We had less than 10 minutes during the lunch break after which we had to get back on work.

Making Database to Read Only – Changing Database to Read/Write
A simple but effective script about making database read only.

Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction
How do I apply NOLOCK hint to my whole query. I know that I can use NOLOCK at every table level but I have many tables in my query and I want to apply the same to all the tables. Read the answer to the question in this blog post.

Sudden Death of SSD on my Laptop – A Warning for SSD Users
An entertaining story of how my SSD had sudden death and I struggled to get back online.

2012

Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment
Filestream is a very interesting feature, and an enhancement of FileTable with Filestream is equally exciting. Today in this post, we will learn how to set up the FileTable Environment in SQL Server. The major advantage of FileTable is it has Windows API compatibility for file data stored within an SQL Server database. In simpler words, FileTables remove a barrier so that SQL Server can be used for the storage and management of unstructured data that are currently residing as files on file servers. Another advantage is that the Windows Application Compatibility for their existing Windows applications enables to see these data as files in the file system.

Part 1 – Setting Up Environment  | Part 2 – Methods to Insert Data Into Table

I suggest you to read above two

Cheatsheet – Released for SQL Server 2012 Edition
SQL Server Cheatsheet has been extremely popular download from my blog. There is plenty of request for me to update it with SQL Server 2012 features. I have finally upgraded the cheat sheet with SQL Server 2012 features. The new cheat sheet has following updates

  • SSMS Shortcuts
  • Columnstore Index
  • SQL Server 2012 Datetime Functions
  • SQL Server Ranking Functions
  • SQL Server 2012 Analytic functions

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

SQL SERVER – Weekly Series – Memory Lane – #024

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Search Text Field – CHARINDEX vs PATINDEX
Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking for the pattern as the first (omit the first %) or last (omit the last %) characters in a column. For CHARINDEX, the pattern cannot include wildcard characters. The second argument is a character expression, usually a column name, in which Adaptive Server searches for the specified pattern.

Explanation of TRYCATCH and ERROR Handling
SQL Server provides the TRYCATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

UDF – User Defined Function to Extract Only Numbers From String
This is straight to script blog post where I had written about UDF which extracts only numbers from strings.

Disable Triggers – Drop Triggers
Till today I use it extensively when I have to disable or drop trigger.

Stored Procedures Advantages and Best Advantage
Here is a very old but still today relevant blog post where I discuss why one should use stored procedures. I just can’t believe that such a short article which I wrote 6 years ago is still very valid article and relevant.

2008

Change Order of Column In Database Tables
How to change the order of the column in a database table? It happens many times table with few columns is already created. After a while there is need to add new column to the previously existing table.

Restore Database Using Corrupt Datafiles (.mdf and .ldf) – Part 2
A straight to script kind of blog post!

2009

Maximum Columns per Primary Key
Maximum columns per Primary Key Index is 16. In fact, 16 is the limit for columns per Foreign Key and Index Key. You cannot have more than 16 columns per Index Key, Primary Key or Foreign Key. So, reduce the columns in those columns to less than or equal to 16 columns.

Restore or Attach Database Without .NDF or .MDF is Not Possible
All the .mdf and .ndf files are mandatory to attach or restore database successfully.  Even though  you have all the transactions stored in .ldf you will not be able to restore the database completely.

Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server.

2) sys.dm_tran_current_transaction – Returns a single row that displays the state information of the transaction in the current session.

Introduction to JOINs – Basic of JOINs
I have tried to explain the fundamentals of the join using following quick method. I used diagram and simple script which are quite popular.

2010

Configure Management Data Collection in Quick Steps – T-SQL Tuesday #005
The three most important components of any computer and server are the CPU, Memory, and Hard disk specification. This post talks about  how to get more details about these three most important components using the Management Data Collection. Management Data Collection generates the reports for the three said components by default. Configuring Data Collection is a very easy task and can be done very quickly.

2011

Add New Column With Default Value
Adding default value to column is very easy task but not everyone knows the secret and they often write long script do so. In this to the point script you can see how easy it is to do it.

Query to Recent Query on Server with Execution Plan Function to Get SQL
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output.

2012

DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module
DMV sys. dm_exec_describe_first_result_set_for_object returns a result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure.

Social Media Series – Twitter and Myself

Follow me on Twitter

Social Media Series – Facebook and Google+

Like me on Facebook |Add me to Google+ 

Social Media Series – YouTube and Movies

Subscribe us on YouTube

Social Media Series – LinkedIn and Professional Profile

Connect me with LinkedIn

Installing AdventureWorks Sample Database – SQL in Sixty Seconds #010 – Video
Many SQL Books and online blogs and articles there are scripts written by using AdventureWork database. I often received request that where people can get sample database as well how to restore sample database.

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