SQL SERVER – Weekly Series – Memory Lane – #053 – Final Post in Series

memory lane SQL SERVER   Weekly Series   Memory Lane   #053   Final Post in SeriesIt has been a fantastic journey to write memory lane series for an entire year. This series gave me the opportunity to go back and see what I have contributed to this blog throughout the last 7 years. This was indeed fantastic series as this provided me the opportunity to witness how technology has grown throughout the year and how I have progressed in my career while writing this blog post.

This series was indeed fantastic experience readers as many joined during the last few years and were not sure what they have missed in recent years.

Let us continue with the final episode of the Memory Lane Series.

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

Get Current User – Get Logged In User
Here is the straight script which list logged in SQL Server users.

Disable All Triggers on a Database – Disable All Triggers on All Servers
Question : How to disable all the triggers for a database? Additionally, how to disable all the triggers for all servers? For answer execute the script in the blog post.

Importance of Master Database for SQL Server Startup
I have received following questions many times. I will list all the questions here and answer them together.

What is the purpose of Master database?
Should our backup Master database?
Which database is must have database for SQL Server for startup?
Which are the default system database created when SQL Server 2005 is installed for the first time?
What happens if Master database is corrupted?

Answers to all of the questions are very much related.

2008

DECLARE Multiple Variables in One Statement
SQL Server is a great product and it has many features which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.

2009

How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’
Many times I have seen that the index is disabled when there is a large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.

2010

List of all the Views from Database
Many emails I received suggesting that they have hundreds of the view and now have no clue what is going on and how many of them have indexes and how many does not have an index. Some even asked me if there is any way they can get a list of the views with the property of Index along with it.

Here is the quick script which does exactly the same. You can also include many other columns from the same view.

Minimum Maximum Memory – Server Memory Options
I was recently reading about SQL Server Memory Options over here. While reading this one line really caught my attention is minimum value allowed for maximum memory options. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 16 megabytes (MB).

2011

Fundamentals of Columnstore Index
There are two kinds of storage in a database. Row Store and Column Store. Row store does exactly as the name suggests – stores rows of data on a page – and column store stores all the data in a column on the same page. These columns are much easier to search – instead of a query searching all the data in an entire row whether the data are relevant or not, column store queries need only to search a much lesser number of the columns.

columnstore SQL SERVER   Weekly Series   Memory Lane   #053   Final Post in Series

How to Ignore Columnstore Index Usage in Query
In summary the question in simple words “How can we ignore using the column store index in selective queries?” Very interesting question – you can use I can understand there may be the cases when the column store index is not ideal and needs to be ignored the same. You can use the query hint IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX to ignore the column store index. The SQL Server Engine will use any other index which is best after ignoring the column store index.

2012

Storing Variable Values in Temporary Array or Temporary List
SQL Server does not support arrays or a dynamic length storage mechanism like list. Absolutely there are some clever workarounds and few extra-ordinary solutions but everybody can;t come up with such solution. Additionally, sometime the requirements are very simple that doing extraordinary coding is not required. Here is the simple case.

Move Database Files MDF and LDF to Another Location
It is not common to keep the Database on the same location where OS is installed. Usually Database files are in SAN, Separate Disk Array or on SSDs. This is done usually for performance reason and manageability perspective. Now the challenges comes up when database which was installed at not preferred default location and needs to move to a different location. Here is the quick tutorial how you can do it.

UNION ALL and ORDER BY – How to Order Table Separately While Using UNION ALL
If your requirement is such that you want your top and bottom query of the UNION resultset independently sorted but in the same result set you can add an additional static column and order by that column. Let us re-create the same scenario.

Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video

http://www.youtube.com/watch?v=FVWIA-ACMNo

thanks SQL SERVER   Weekly Series   Memory Lane   #053   Final Post in Series

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

SQL SERVER – Weekly Series – Memory Lane – #052

memory lane SQL SERVER   Weekly Series   Memory Lane   #052

Let us continue with the final episode of the Memory Lane Series.

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

Set Server Level FILLFACTOR Using T-SQL Script
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.

Limitation of Online Index Rebuld Operation
Online operation means when online operations are happening in the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to the database.

Get Permissions of My Username / Userlogin on Server / Database
A few days ago, I was invited to one of the largest database company. I was asked to review database schema and propose changes to it. There was special username or user logic was created for me, so I can review their database. I was very much interested to know what kind of permissions I was assigned per server level and database level. I did not feel like asking Sr. DBA the question about permissions.

Simple Example of WHILE Loop With CONTINUE and BREAK Keywords
This question is one of those questions which is very simple and most of the users get it correct, however few users find it confusing for the first time. I have tried to explain the usage of simple WHILE loop in the first example. BREAK keyword will exit the stop the while loop and control is moved to the next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to the first statement of while loop.

Forced Parameterization and Simple Parameterization – T-SQL and SSMS
When the PARAMETERIZATION option is set to FORCED, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement is converted to a parameter during query compilation. When the PARAMETERIZATION database option is SET to SIMPLE, the SQL Server query optimizer may choose to parameterize the queries.

2008

Transaction and Local Variables – Swap Variables – Update All At Once Concept
Summary : Transaction have no effect over memory variables. When UPDATE statement is applied over any table (physical or memory) all the updates are applied at one time together when the statement is committed.

First of all I suggest that you read the article listed above about the effect of transaction on local variant. As seen there local variables are independent of any transaction effect.

Simulate INNER JOIN using LEFT JOIN statement – Performance Analysis
Just a day ago, while I was working with JOINs I find one interesting observation, which has prompted me to create following example. Before we continue further let me make very clear that INNER JOIN should be used where it cannot be used and simulating INNER JOIN using any other JOINs will degrade the performance. If there are scopes to convert any OUTER JOIN to INNER JOIN it should be done with priority.

2009

Introduction to Business Intelligence – Important Terms & Definitions
Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data from various data sources, thus providing enterprise users with reliable and timely information and analysis for improved decision making.

Difference Between Candidate Keys and Primary Key
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

2010

Taking Multiple Backup of Database in Single Command – Mirrored Database Backup
I recently had a very interesting experience. In one of my recent consultancy works, I was told by our client that they are going to take the backup of the database and will also a copy of it at the same time. I expressed that it was surely possible if they were going to use a mirror command. In addition, they told me that whenever they take two copies of the database, the size of the database, is always reduced. Now this was something not clear to me, I said it was not possible and so I asked them to show me the script.

Corrupted Backup File and Unsuccessful Restore
The CTO, who was also present at the location, got very upset with this situation. He then asked when the last successful restore test was done. As expected, the answer was NEVER.There were no successful restore tests done before. During that time, I was present and I could clearly see the stress, confusion, carelessness and anger around me. I did not appreciate the feeling and I was pretty sure that no one in there wanted the atmosphere like me.

2011

TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution
SQL Trace is a SQL Server database engine technology which monitors specific events generated when various actions occur in the database engine. When any event is fired it goes through various stages as well various routes. One of the routes is Trace I/O Provider, which sends data to its final destination either as a file or rowset.

DATEDIFF – Accuracy of Various Dateparts
If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it in minutes.

Dedicated Access Control for SQL Server Express Edition

http://www.youtube.com/watch?v=1k00z82u4OI

booksign4 SQL SERVER   Weekly Series   Memory Lane   #052

Book Signing at SQLPASS

2012

Who I Am And How I Got Here – True Story as Blog Post
If there was a shortcut to success – I want to know. I learnt SQL Server hard way and I am still learning. There are so many things, I have to learn. There is not enough time to learn everything which we want to learn. I am constantly working on it every day. I welcome you to join my journey as well. Please join me in my journey to learn SQL Server – more the merrier.

Vacation, Travel and Study – A New Concept
Even those who have advanced degrees and went to college for years, or even decades, find studying hard.  There is a difference between studying for a career and studying for a certification.  At least to get a degree there is a variety of subjects, with labs, exams, and practice problems to make things more interesting.

Order By Numeric Values Formatted as String
We have a table which has a column containing alphanumeric data. The data always has first as an integer and later part as a string. The business need is to order the data based on the first part of the alphanumeric data which is an integer. Now the problem is that no matter how we use ORDER BY the result is not produced as expected. Let us understand this with an example.

Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video
One of the most famous errors related to SQL Server is about connecting to SQL Server itself. Here is how it goes, most of the time developers have worked with SQL Server and knows pretty much every error which they face during development language. However, hardly they install fresh SQL Server. As the installation of the SQL Server is a rare occasion unless you are a DBA who is responsible for such an instance – the error faced during installations are pretty rare as well.

http://www.youtube.com/watch?v=1k00z82u4OI

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

SQL SERVER – Weekly Series – Memory Lane – #051

memory lane SQL SERVER   Weekly Series   Memory Lane   #051Here 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

Explanation and Understanding NOT NULL Constraint
NOT NULL is integrity CONSTRAINT. It does not allow creating of the row where column contains NULL value. Most discussed questions about NULL is what is NULL? I will not go in depth analysis it. Simply put NULL is unknown or missing data. When NULL is present in database columns, it can affect the integrity of the database. I really do not prefer NULL in the database unless they are absolutely necessary.

Three T-SQL Script to Create Primary Keys on Table
I have always enjoyed writing about three topics Constraint and Keys, Backup and Restore and Datetime Functions. Primary Keys constraints prevent duplicate values for columns and provides a unique identifier to each column, as well it creates clustered index on the columns.

2008

Get Numeric Value From Alpha Numeric String – UDF for Get Numeric Numbers Only
SQL is great with String operations. Many times, I use T-SQL to do my string operation. Let us see User Defined Function, which I wrote a few days ago, which will return only Numeric values from Alpha Numeric values.

Introduction and Example of UNION and UNION ALL
It is very much interesting when I get requests from blog reader to re-write my previous articles. I have received few requests to rewrite my article SQL SERVER – Union vs. Union All – Which is better for performance? with examples. I request you to read my previous article first to understand what is the concept and read this article to understand the same concept with an example.

Downgrade Database for Previous Version
The main questions is how they can downgrade the from SQL Server 2005 to SQL Server 2000? The answer is : Not Possible.

Get Common Records From Two Tables Without Using Join
Following is my scenario, Suppose Table 1 and Table 2 has same column e.g. Column1 Following is the query,

1. Select column1,column2 From Table1
2. Select column1 From Table2

I want to find common records from these tables, but I don’t want to use the Join clause because for that I need to specify the column name for Join condition. Will you help me to get common records without using Join condition? I am using SQL Server 2005.

Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2
A year ago I wrote a post about SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice where I have discussed two different methods of getting the date part from datetime.

Introduction to CLR – Simple Example of CLR Stored Procedure
CLR is an abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish tasks which are not possible by T-SQL or can use lots of resources. The CLR can be usually implemented where there is an intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to the Extended Stored Procedure.

2009

Comic Slow Query – SQL Joke

Before Presentation

slowquery SQL SERVER   Weekly Series   Memory Lane   #051

After Presentation

slowquery1 SQL SERVER   Weekly Series   Memory Lane   #051

Enable Automatic Statistic Update on Database
In one of the recent projects, I found out that despite putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Recently Executed T-SQL Query
Please refer to blog post  query to recently executed T-SQL query on database.

Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script
At some time in your DBA career, you may find yourself in a position when you sit back and realize that your database collations have somehow run amuck, or are faced with the ever annoying CANNOT RESOLVE COLLATION message when trying to join data of varying collation settings.

2010

Visiting Alma Mater – Delivering Session on Database Performance and Career – Nirma Institute of Technology
Everyone always dreams of visiting their school and college, where they have studied once. It is a great feeling to see the college once again – where you have spent the wonderful golden years of your time. College time is filled with studies, education, emotions and several plans to build a future. I consider myself fortunate as I got the opportunity to study at some of the best places in the world.

Nirma%20(6) SQL SERVER   Weekly Series   Memory Lane   #051

Change Column DataTypes
There are times when I feel like writing that I am a day older in SQL Server. In fact, there are many who are looking for a solution that is simple enough. Have you ever searched online for something very simple. I often do and enjoy doing things which are straight forward and easy to change.

2011

Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry
In this blog post we will see three new DMVs which are introduced in Denali. The DMVs are very simple and there is not much to describe them. So here is the simple game. I will be asking a question back to you after seeing the result of the each of the DMV and you help me to complete this blog post.

A Simple Quiz – T-SQL Brain Trick
If you have some time, I strongly suggest you try this quiz out as it is for sure twists your brain.

2012

List All The Column With Specific Data Types in Database
5 years ago I wrote script SQL SERVER – 2005 – List All The Column With Specific Data Types, when I read it again, it is very much relevant and I liked it. This is one of the script which every developer would like to keep it handy. I have upgraded the script bit more. I have included few additional information which I believe I should have added from the beginning. It is difficult to visualize the final script when we are writing it first time.

Find First Non-Numeric Character from String
The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character.

Finding Different ColumnName From Almost Identitical Tables
Well here is the interesting example of how we can use sys.column catalogue views and get the details of the newly added column. I have previously written about EXCEPT over here which is very similar to MINUS of Oracle.

Storing Data and Files in Cloud – Dropbox – Personal Technology Tip
I thought long and hard about doing a Personal Technology Tips series for this blog.  I have so many tips I’d like to share.  I am on my computer almost all day, every day, so I have a treasure trove of interesting tidbits I like to share if given the chance.  The only thing holding me back – which tip to share first?  The first tip obviously has the weight of seeming like the most important.  But this would mean choosing amongst my favorite tricks and shortcuts.  This is a hard task.

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

SQL SERVER – Weekly Series – Memory Lane – #050

memory lane SQL SERVER   Weekly Series   Memory Lane   #050Here 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

Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server
In this example we see two different methods of how to call Stored Procedures remotely. 

Connection Property of SQL Server Management Studio SSMS
A very simple example of the how to build connection properties for SQL Server with the help of SSMS.

Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE
SQL Server has a total of 4 ranking functions. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

T-SQL Script to Add Clustered Primary Key
Jr. DBA asked me three times in a day, how to create Clustered Primary Key. I gave him following sample example. That was the last time he asked “How to create Clustered Primary Key to table?”

2008

2008 – TRIM() Function – User Defined Function
SQL Server does not have functions which can trim leading or trailing spaces of any string at the same time. SQL does have LTRIM() and RTRIM() which can trim leading and trailing spaces respectively. SQL Server 2008 also does not have TRIM() function. User can easily use LTRIM() and RTRIM() together and simulate TRIM() functionality.

http://www.youtube.com/watch?v=1-hhApy6MHM

2009

Earlier I have written two different articles on the subject Remove Bookmark Lookup. This article is as part 3 of original article. Please read the first two articles here before continuing reading this article.

Interesting Observation – Query Hint – FORCE ORDER
SQL Server never stops to amaze me. As regular readers of this blog already know that besides conducting corporate training, I work on large-scale projects on query optimizations and server tuning projects. In one of the recent projects, I have noticed that a Junior Database Developer used the query hint Force Order; when I asked for details, I found out that the basic concept was not properly understood by him.

Queries Waiting for Memory Allocation to Execute
In one of the recent projects, I was asked to create a report of queries that are waiting for memory allocation. The reason was that we were doubtful regarding whether the memory was sufficient for the application. The following query can be useful in similar cases. Queries that do not have to wait on a memory grant will not appear in the result set of following query.

2010

Quickest Way to Identify Blocking Query and Resolution – Dirty Solution
As the title suggests, this is quite a dirty solution; it’s not as elegant as you expect. However, it works totally fine.

Simple Explanation of Data Type Precedence
While I was working on creating a question for SQL SERVER – SQL Quiz – The View, The Table and The Clustered Index Confusion, I had actually created yet another question along with this question. However, I felt that the one which is posted on the SQL Quiz is much better than this one because what makes that more challenging question is that it has a multiple answer.

Encrypted Stored Procedure and Activity Monitor I recently had received questionable if any stored procedure is encrypted can we see its definition in Activity Monitor.Answer is – No. Let us do a quick test. Let us create following Stored Procedure and then launch the Activity Monitor and check the text.

Indexed View always Use Index on Table
A single table can have maximum 249 non clustered indexes and 1 clustered index. In SQL Server 2008, a single table can have maximum 999 non clustered indexes and 1 clustered index. It is widely believed that a table can have only 1 clustered index, and this belief is true. I have some questions for all of you. Let us assume that I am creating view from the table itself and then create a clustered index on it. In my view, I am selecting the complete table itself.

2011

Detecting Database Case Sensitive Property using fn_helpcollations()
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.

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 ComparisonSQL 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 installWebmatrix 4.0 and download sample database. Once done you can run following script.

Why I am Going to Attend PASS Summit Unite 2011
The four-day event will be marked by a lot of learningsharing, and networking, which will help me increase both my knowledge and contacts. Every year, PASS Summit provides me a golden opportunity to build my network as well as to identify and meet potential customers or employees.

2012

Manage Help Settings – CTRL + ALT + F1
This is very interesting read as my daughter once accidently came across a screen in SQL Server Management Studio. It took me 2-3 minutes to figure out how she has created the same screen.

Recover the Accidentally Renamed Table
“I accidentally renamed table in my SSMS. I was scrolling very fast and I made mistakes. It was either because I double clicked or clicked on F2 (shortcut key for renaming). However, I have made the mistake and now I have no idea how to fix this. If you have renamed the table, I think you pretty much is out of luck. Here are few things which you can do which can give you an idea about what your table name can be if you are lucky.

Identify Numbers of Non Clustered Index on Tables for Entire Database
Here is the script which will give you numbers of non clustered indexes on any table in entire database.

Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video
Here is the complete complete script which I have used in the SQL in Sixty Seconds Video. Thanks Harsh for important Tip in the comment.

http://www.youtube.com/watch?v=3kDHC_Tjrns

Advanced Data Quality Services with Melissa Data – Azure Data Market
For the purposes of the review, I used a database I had in an Excel spreadsheet with name and address information. Upon a cursory inspection, there are miscellaneous problems with these records; some addresses are missing ZIP codes, others missing a city, and some records are slightly misspelled or have unparsed suites. With DQS, I can easily add a knowledge base to help standardize my values, such as for state abbreviations. But how do I know that my address is correct?

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

SQL SERVER – Weekly Series – Memory Lane – #049

memory lane SQL SERVER   Weekly Series   Memory Lane   #049Here 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

Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS
@@CONNECTIONS

Returns the number of attempted connections, either successful or unsuccessful since SQL Server was last started.

@@MAX_CONNECTIONS
Returns the maximum number of simultaneous user connections allowed on an instance of SQL Server. The number returned is not necessarily the number currently configured.

Query Editor – Microsoft SQL Server Management Studio
This post may be very simple for most of the users of SQL Server 2005. Earlier this year, I have received one question many times – Where is Query Analyzer in SQL Server 2005? I wrote small post about it and pointed many users to that post – SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio. Recently I have been receiving similar question.

OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE
SQL Server 2005 has a new OUTPUT clause, which is quite useful. OUTPUT clause has access to insert and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements. OUTPUT clause can generate a table variable, a permanent table, or temporary table. Even though, @@Identity will still work with SQL Server 2005, however I find the OUTPUT clause very easy and powerful to use. Let us understand the OUTPUT clause using an example.

Find Name of The SQL Server Instance
Based on database server stored procedures has to run different logic. We came up with two different solutions. 1) When database schema is very much changed, we wrote completely new stored procedure and deprecated older version once it was not needed. 2) When logic depended on Server Name we used global variable @@SERVERNAME. It was very convenient while writing migrating script which depended on the server name for the same database.

Explanation of TRYCATCH and ERROR Handling With RAISEERROR Function
One of the developers at my company thought that we can not use the RAISEERROR function in new feature of SQL Server 2005 TRY… CATCH. When asked for an explanation he suggested SQL SERVER – 2005 Explanation of TRY… CATCH and ERROR Handling article as excuse suggesting that I did not give example of RAISEERROR with TRYCATCH. We all thought it was funny. Just to keep records straight, TRY… CATCH can sure use RAISEERROR function.

Different Types of Cache Objects
Serveral kinds of objects can be stored in the procedure cache:

Compiled Plans: When the query optimizer finishes compiling a query plan, the principal output is compiled plan.

Execution contexts: While executing a compiled plan, SQL Server has to keep track of information about the state of execution.

Cursors: Cursors track the execution state of server-side cursors, including the cursor’s current location within a resultset.

Algebrizer trees: The Algebrizer’s job is to produce an algebrizer tree, which represents the logic structure of a query.

Open SSMS From Command Prompt – sqlwb.exe Example
This article is written by request and suggestion of Sr. Web Developer at my organization. Due to the nature of this article most of the content is referred from Book On-Line. sqlwbcommand prompt utility which opens SQL Server Management Studio. Squib command does not run queries from the command prompt. sqlcmd utility runs queries from command prompt, read for more information.

2008

Puzzle – Solution – Computed Columns Datatype Explanation
Just a day before I wrote article SQL SERVER – Puzzle – Computed Columns Datatype Explanation which was inspired by SQL Server MVP Jacob Sebastian. I suggest that before continuing this article read the original puzzle question SQL SERVER – Puzzle – Computed Columns Datatype Explanation.The question was if the computed column was of datatype TINYINT how to create a Computed Column of datatype INT?

2008 – Find If Index is Being Used in Database
It is very often I get a query that how to find if any index is being used in the database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If the number of indices are higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.

2009

Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries
If you want to see what’s going on here, I think you need to shift your point of view from an implementation-centric view to an ANSI point of view. ANSI does not guarantee processing the order. Figure 2 is interesting, but it will be potentially misleading if you don’t understand the ANSI rule-set SQL Server operates under in most cases. Implementation thinking can certainly be useful at times when you really need that multi-million row query to finish before the backup fire off, but in this case, it’s counterproductive to understanding what is going on.

SQL Server Management Studio and Client Statistics
Client Statistics are very important. Many a times, people relate queries execution plan to query cost. This is not a good comparison. Both parameters are different, and they are not always related. It is possible that the query cost of any statement is less, but the amount of the data returned is considerably larger, which is causing any query to run slow. How do we know if any query is retrieving a large amount data or very little data?

2010

I encourage all of you to go through complete series and write your own on the subject. If you write an article and send it to me, I will publish it on this blog with due credit to you. If you write on your own blog, I will update this blog post pointing to your blog post.

Limited View

SQL SERVER – Get Query Running in Session
I was recently looking for syntax where I needed a query running in any particular session. I always remembered the syntax and ha d actually written it down before, but somehow it was not coming to mind quickly this time. I searched online and I ended up on my own article written last year SQL SERVER – Get Last Running Query Based on SPID. I felt that I am getting old because I forgot this really simple syntax.

Find Total Number of Transaction on Interval
In one of my recent Performance Tuning assignments I was asked how do someone know how many transactions are happening on a server during certain interval. I had a handy script for the same. Following script displays transactions happened on the server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.

2011

Here are two DMV’s which are newly introduced in SQL Server 2012 and provides vital information about SQL Server.

SQL Backup and FTP – A Quick and Handy Tool
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.

Quick Note about JOIN – Common Questions and Simple Answers
In this blog post 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 a few of the same over here. Most of them are basic, but note that the basics are of great importance.

2012

Importance of User Without Login
Question: “In recent version of SQL Server we can create user without login. What is the use of it?”

Great question indeed. Let me first attempt to answer this question but after reading my answer I need your help. I want you to help him as well with adding more value to it.

Preserve Leading Zero While Coping to Excel from SSMS
Earlier I wrote two articles about how to efficiently copy data from SSMS to Excel. Since I wrote that post there are plenty of interest generated on this subject. There are a few questions I keep on getting over this subject. One of the question is how to get the leading zero preserved while copying the data from SSMS to Excel. Well it is almost the same way as my earlier post SQL SERVER – Excel Losing Decimal Values When Value Pasted from SSMS ResultSet. The key here is in EXCEL and not in SQL Server.

Solution – 2 T-SQL Puzzles – Display Star and Shortest Code to Display 1
Earlier on this blog we had asked two puzzles. The response from all of you is nothing but Amazing. I have received 350+ responses. Many are valid and many were indeed something I had not thought about it.

I strongly suggest you read all the puzzles and their answers here – trust me if you start reading the comments you will not stop till you read every single comment. Seriously trust me on it. Personally I have learned a lot from it.

Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video

http://www.youtube.com/watch?v=TvlYy-TGaaA

Importance of User Without Login – T-SQL Demo Script
Earlier I wrote a blog post about SQL SERVER – Importance of User Without Login and my friend and SQL Expert Vinod Kumar has written excellent follow up blog post about Contained Databases inside SQL Server 2012. Now lots of people asked me if I can also explain the same concept again so here is the small demonstration for it. Let me show you how login without user can help. Before we continue on this subject I strongly recommend that you read my earlier blog post here.

In following demo I am going to demonstrate following situation.

  1. Login using the System Admin account
  2. Create a user without login
  3. Checking Access
  4. Impersonate the user without login
  5. Checking Access
  6. Revert Impersonation
  7. Give Permission to user without login
  8. Impersonate the user without login
  9. Checking Access
  10. Revert Impersonation
  11. Clean up

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

SQL SERVER – Weekly Series – Memory Lane – #048

memory lane SQL SERVER   Weekly Series   Memory Lane   #048Here 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

Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used
Above theory is true in most of the cases. However SQL Server does not use that logic when returning the resultset. SQL Server always returns the resultset which it can return fastest.In most of the cases the resultset which can be returned fastest is the resultset which is returned using clustered index.

Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT
One of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech Lead. When I came back from meeting both of them were looking for me. They said they are confused. I quickly wrote down following example for them.

2008

SQL SERVER – Guidelines and Coding Standards Complete List Download
Coding standards and guidelines are very important for any developer on the path of a successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where they should not prevent best practices for coding. They are basically the guidelines that one should follow for better understanding.

Download Guidelines and Coding Standards complete List Download

Get Answer in Float When Dividing of Two Integer
Many times we have requirements of some calculations amongst different fields in Tables. One of the software developers here was trying to calculate some fields having integer values and divide it which gave incorrect results in integer where accurate results including decimals was expected.

Puzzle – Computed Columns Datatype Explanation
SQL Server automatically does a cast to the data type having the highest precedence. So the result of INT and INT will be INT, but INT and FLOAT will be FLOAT because FLOAT has a higher precedence. If you want a different data type, you need to do an EXPLICIT cast.

Renaming SP is Not Good Idea – Renaming Stored Procedure Does Not Update sys.procedures
I have written many articles about renaming a tables, columns and procedures SQL SERVER – How to Rename a Column Name or Table Name, here I found something interesting about renaming the stored procedures and felt like sharing it with you all. The interesting fact is that when we rename a stored procedure using SP_Rename command, the Stored Procedure is successfully renamed. But when we try to test the procedure using sp_helptext, the procedure will be having the old name instead of new names.

2009

Insert Values of Stored Procedure in Table – Use Table Valued Function
It is clear from the result set that , where I have converted stored procedure logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. The performance of the stored procedure is “usually” better than that of functions.

Interesting Observation – Index on Index View Used in Similar Query
Recently, I was working on an optimization project for one of the largest organizations. While working on one of the queries, we came across a very interesting observation. We found that there was a query on the base table and when the query was run, it used the index, which did not exist in the base table. On careful examination, we found that the query was using the index that was on another view. This was very interesting as I have personally never experienced a scenario like this. In simple words, “Query on the base table can use the index created on the indexed view of the same base table.”

Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries
Working with SQL Server has never seemed to be monotonous – no matter how long one has worked with it. Quite often, I come across some excellent comments that I feel like acknowledging them as blog posts. Recently, I wrote an article on SQL SERVER – Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location, which is well received in the community.

2010

I encourage all of you to go through complete series and write your own on the subject. If you write an article and send it to me, I will publish it on this blog with due credit to you. If you write on your own blog, I will update this blog post pointing to your blog post.

Limited View

2011

Startup Parameters Easy to Configure
If you are a regular reader of this blog, you must be aware that I have written about SQL Server Denali recently. Here is the quickest way to reach into the screen where we can change the startup parameters. Go to SQL Server Configuration Manager >> SQL Server Services >> Right Click on the Server >> Properties >> Startup Parameters

2012

Validating Unique Columnname Across Whole Database
I sometimes come across very strange requirements and often I do not receive a proper explanation of the same. Here is the one of those examples. For example “Our business requirement is when we add new column we want it unique across current database.” Read the solution to this strange request in this blog post.

Excel Losing Decimal Values When Value Pasted from SSMS ResultSet
It is very common when users are coping the resultset to Excel, the floating point or decimals are missed. The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that.

Basic Calculation and PEMDAS Order of Operation
Read this interesting blog post for fantastic conversation about the subject.

equ SQL SERVER   Weekly Series   Memory Lane   #048

Copy Column Headers from Resultset – SQL in Sixty Seconds #027 – Video

http://www.youtube.com/watch?v=x_-3tLqTRv0

Delete From Multiple Table – Update Multiple Table in Single Statement

There are two questions which I get every single day multiple times. In my gmail, I have created standard canned reply for them. Let us see the questions here.

  • I want to delete from multiple table in a single statement how will I do it?
  • I want to update multiple table in a single statement how will I do it?

Read the answer in the blog post.

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

SQL SERVER – Weekly Series – Memory Lane – #047

memory lane SQL SERVER   Weekly Series   Memory Lane   #047Here 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 Constraint of Database – Find Primary Key and Foreign Key Constraint in Database
The script listed in the blog is very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database.

Difference Between UPDATE and UPDATE()
What is the difference between UPDATE and UPDATE()? The UPDATE is the syntax used to update the database tables or database views. UPDATE() is used in triggers to check update/insert to the database tables or database views.

UDF – Validate Positive Integer Function – Validate Natural Integer Function
Earlier I wrote SQL SERVER – UDF – Validate Integer Function. It was very interesting to write this and developers at my company started to use it. One Jr. DBA modified this function to validate only positive integers.

Rename Database to New Name Using Stored Procedure by Changing to Single User Mode
There are a few interesting facts to note when the database is renamed.

  • When renamed the database, filegroup name or filename (. mdf,. ldf) are not changed.
  • User with SA privilege can rename the database with following script when the context of the database is master database.

2008

2009

Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location
The best practice is to avoid the usage of function in ORDER BY clause when string concatenation operations are executed.

The reason for this behavior is that the use of function in ORDER BY clause will change the order of query execution and create an unexpected output.

Introduction to Service Broker and Sample Script
The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption. Let us understand Service Broker with a simple script. The script contains necessary comments to explain what exactly script is doing.

2010

SELECT * and Adding Column Issue in View – Limitation of the View 4
In this blog we talk about a very well known limitation of the view. Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

How to Stop Growing Log File Too Big
With this method, you can restore your database at Point of Time if a disaster ever happens on your server.

Let us run an example to demonstrate this. In this case, I have done the following steps:

  1. Create Sample Database in FULL RECOVERY Model
  2. Take Full Backup (full backup is a must for taking subsequent backup)
  3. Repeat Following Operation
    1. Take Log Backup
    2. Insert Some rows
    3. Check the size of Log File
  4. Clean Up

COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5
One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example in blog.

2011

I have put up a quick guide here where I am writing all the 14 new functions linking them to my blog post as well Book On-Line for a quick reference.

SQLAuthority.com Book On-Line
Conversion functions
PARSE PARSE
TRY_CONVERT TRY_CONVERT
TRY_PARSE TRY_PARSE
Date and time functions
DATEFROMPARTS DATEFROMPARTS
DATETIME2FROMPARTS DATETIME2FROMPARTS
DATETIMEFROMPARTS DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS
EOMONTH EOMONTH
SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS
TIMEFROMPARTS TIMEFROMPARTS
Logical functions
CHOOSE CHOOSE
IIF IIF
String functions
CONCAT CONCAT
FORMAT FORMAT

I have personally bookmarked this post for my future reference.

2012

SSMS Automatically Generates TOP (100) PERCENT in Query Designer
Question: I am trying to create a view in Query Designer (not in the New Query Window). Every time I am trying to create a view it always adds  TOP (100) PERCENT automatically on the T-SQL script. No matter what I do, it always automatically adds the TOP (100) PERCENT to the script. I have attempted to copy paste from notepad, build a query and a few other things – there is no success. I am really not sure what I am doing wrong with Query Designer.

Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video

http://www.youtube.com/watch?v=zPmL0z_aspo

SQL SERVER – 2 T-SQL Puzzles 

Why following code when executed in SSMS displays result as a * (Star)?

SELECT CAST(634 AS VARCHAR(2))

Replace a Column Name in Multiple Stored Procedure all together
How to replace a column name in multiple stored procedure efficiently and quickly? This blog tries to answer the same question.

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

SQL SERVER – Weekly Series – Memory Lane – #046

memory lane SQL SERVER   Weekly Series   Memory Lane   #046Here 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

User Defined Function – Get Number of Days in Month
A straight to script blog post where I find the number of days in month.

Start Stop Restart SQL Server From Command Prompt
Very frequently I use following command prompt script to start and stop a default instance of SQL Server. Our network admin loves these commands as this is very easy.

Frequency of SQL Server Reboot and Restart
This is a very interesting question. I will keep the answer of this question very simple. First of all there is no scientific research or white paper I can backup my results with. The answer contains part simple observation and part experience. There is no need to reboot SQL Server. Once it is on it is ON!

Scrum: Agile Software Development for Project Management
Scrum is organized around the following roles:

  • Product Owner – Determines what functionality is needed
  • ScrumMaster – Leads the Scrum and is primarily responsible for making sure the Scrum process is followed and removing impediments that keep the Team from working
  • The Team – Those who do the actual work that translates what the Product Owner has requested into usable functionality

Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always
Using EXEC or EXECUTE is good practice as it always executes the stored procedure, when not using EXEC can confuse SQL SERVER to misinterpret commands and may create errors.

2008

Creating Primary Key, Foreign Key and Default Constraint
Primary key, Foreign Key and Default constraint are the 3 main constraints that need to be considered while creating tables or even after that. It seems very easy to apply these constraints but still we have some confusions and problems while implementing it. So I tried to write about these constraints that can be created or added at different levels and in different ways or methods.

SharePoint Stop Working After Changing Server (Computer) Name
When you change the physical server name the SharePoint is already connected to the SQL instance of the old computer name (OldServerName/SQLInstance) and on changing the name the SharePoint will not able to connect the SQL Server  as now the SQL Server instance will run on new computer name (NewServerName/SQLInstance).

Steps To Create A Custom WebPart – Deploy It SharePoint Site
SharePoint does not allow custom coding for any of the webpart. It is possible to create a webpart in Visual Studio and integrate it with SharePoint. The process to create a webpart in .NET framework and make it work in SharePoint often fails due to lack of guidance on this subject on the internet.

2009

Difference between SQL Server Express and MySQL
Both SQL Server express and MySQL are two of the Relational Database Systems (RDBMS) available today. Both are freely available and meant for running smaller or embedded databases, yet there are also significant differences between them.

Plan Caching and Schema Change – An Interesting Observation
As per the white paper, “Schema change” is defined as follows:

  • Adding or dropping columns to a table or view.
  • Adding or dropping constraints, defaults, or rules to/from a table.
  • Adding an index to a table or an indexed view.
  • Dropping an index defined on a table or an indexed view (only if the index is used by the query plan in question).
  • Dropping a statistic (not creating or updating!) defined on a table will cause a correctness-related recompilation of any query plans that use that table. Such recompilations occur at that instant when the query plan in question begins execution. Updating a statistic (both manual and auto-update) will cause an optimality-related (data related) recompilation of any query plans that uses this statistic.

2010

Find Row Count in Table – Find Largest Table in Database – Part 2
Last Year I wrote articles on the subject SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. It is very good to see excellent participation there. In my script I had not taken care of table schema. SQL Server Expert Ameena has modified the same script to include the schema. Here is the new modified script.

Find Automatically Created Statistics – T-SQL
Earlier, I wrote about my experience at an organization here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script. This blog post briefly narrates another experience I had at the same organization.

What are Wait Types, Wait Stats and its Importance
 As per BOL, there are three types of wait types, namely:

  • Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
  • Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
  • External Waits. External waits occur when an SQL Server worker is waiting for an external event.

To check the wait types for any server, just run the following simple statistics:

SELECT *
FROM sys.dm_os_wait_stats

You can get the Wait Stats and identify which of the Wait Stats is causing the issue that troubles you.

Disabled Index and Index Levels and B-Tree
This blog post tries to answer a very important question – What will be the status of the B-Tree structure when the index is disabled?

In the following script, the following operations are to be done:

  • Create Table
  • Create Clustered Index
  • Check the Index Levels
  • Disable Index
  • Check the Index Levels

2011

I have put up a quick guide here where I am writing all the 14 new functions linking them to my blog post as well Book On-Line for a quick reference.

SQLAuthority.com Book On-Line
Conversion functions
PARSE PARSE
TRY_CONVERT TRY_CONVERT
TRY_PARSE TRY_PARSE
Date and time functions
DATEFROMPARTS DATEFROMPARTS
DATETIME2FROMPARTS DATETIME2FROMPARTS
DATETIMEFROMPARTS DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS
EOMONTH EOMONTH
SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS
TIMEFROMPARTS TIMEFROMPARTS
Logical functions
CHOOSE CHOOSE
IIF IIF
String functions
CONCAT CONCAT
FORMAT FORMAT

I have personally bookmarked this post for my future reference.

2012

Unable to DELETE Project in Data Quality Projects (DQS)
Here is the problem. I am not able to delete the project which I have created earlier. I am able to open it and play with it but the delete option is disabled and grayed out (see attached image). Now I believe there is nothing wrong with this project as it was just a test project. Would you please write to my manager that it is not harmful to leave that project there as it is? It is also not using any resources. I think he will believe you.”

Configuring Interactive Cleansing Suggestion Min Score for Suggestions in Data Quality Services (DQS) – Sensitivity of Suggestion
Would you please tell me how to increase the numbers of suggestion? I do understand this may not be preferable solution in many case but all the business cases go on it depends. There are cases when the high sensitivity required and there are cases when higher sensitivities are not required. I would like to seek your help here.

Why Do We Need Data Quality Services – Importance and Significance of Data Quality Services (DQS)
In a database, these sorts of anomalies are incredibly important.  Databases are often used by multiple people who rely on this data to be true and accurate, so data quality is key.  That is why the improved SQL Server features Master Data Management talks about Data Quality Services.  This service has the ability to recognize and flag anomalies like out of range numbers and similarities between data.  This allows a human brain with its pattern recognition abilities to double-check and ensure that P. Dave is the same as Pinal Dave.

Why Do We Need Master Data Management – Importance and Significance of Master Data Management (MDM)
Let me paint a picture of everyday life for you.  Let’s say you and your wife both have address books for your groups of friends.  There is definitely overlap between them, so that you both have the addresses for your mutual friends, and there are addresses that only you know, and some only she knows.  They also might be organized differently.  You might list your friend under “J” for “Joe” or even under “W” for “Work,” while she might list him under “S” for “Joe Smith” or under your name because he is your friend.  If you happened to trade, neither of you would be able to find anything!

Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video

http://www.youtube.com/watch?v=BL5GO-jH3HA

Core Concepts – Elasticity, Scalability and ACID Properties – Exploring NuoDB an Elastically Scalable Database System
The goal of this article is to answer following simple questions

  1. What is Elasticity?
  2. What is Scalability?
  3. How ACID properties vary from NOSQL Concepts?
  4. What are the prevailing problems in the current database system architectures?

Grouping by Multiple Columns to Single Column as A String
One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.

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

SQL SERVER – Weekly Series – Memory Lane – #045

memory lane SQL SERVER   Weekly Series   Memory Lane   #045Here 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

Here are three blog posts where I have written scripts to do various helpful task developers have to frequently.

Introduction and Explanation to sqlcmd
SQL Server 2005 has introduced new utility sqlcmd to run an ad hoc Transact-SQL statements and scripts from the command prompt. T-SQL commands are entered in command prompt window and result is displayed in the same window, unless result set are sent to output files. sqlcmd can execute single T-SQL statement as well as the batch file. sqlcmd utility can connect to earlier versions of SQL Server as well.

Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example
A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly. A noncorrelated subquery is subquery that is independent of the outer query and it can execute on its own without relying on the main outer query.

2008

Introduction to Filtered Index – Improve performance with Filtered Index
Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table, that means it applies a filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Creating Full Text Catalog and Full Text Search
Full Text Index helps to perform complex queries against character data.  These queries can include words or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns.

Explanation about Usage of Unique Index and Unique Constraint
I enjoy reading questions from blog readers and answering them. One of the other SQL enthusiastic is Imran who also regularly answer questions of users on this community blog. Recently he answered in detail about when to use Unique Index and when to use Unique Constraint.

2009

September 1st 2009 was a fantastic day. Checkout Why?

Mirrored Backup and Restore and Split File Backup
This article is based on a real life experience of the author while working with database backup and restore during his consultancy work for various organizations. We will go over the following important concepts of database backup and restore.

  1. Conventional Backup and Restore
  2. Spilt File Backup and Restore
  3. Mirror File Backup
  4. Understanding FORMAT Clause
  5. Miscellaneous details about Backup and Restore

What is Data Mining – A Simple Introductory Note
Data mining is defined as “the process of analyzing data to find hidden patterns using automatic methodologies.” Consider the following simple example that explains this concept. By analyzing the data on the items purchased from a supermarket or a chain of such stores, information on the products that are sold most can be obtained and accordingly supply of that particular product are increased and vice versa. Data mining, in short, is an analytical activity that studies the hidden patterns in a huge pile of data after appropriately classifying and sorting it.

Find Gaps in The Sequence
This is straight to script blog post where I explain how to find gaps in the sequence generated manually.

Importance of Database Schemas in SQL Server
The default schema for a user can be defined by using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER commands. If no default schema is defined for a user account, SQL Server will assume dbo is the default schema. It is important note that if the user is authenticated by SQL Server via the Windows operating system, no default schema will be associated with the user. Therefore if the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema, though not directly with the user.

2010

Soft Delete – IsDelete Column – Your Opinion
I had gone for performance tuning consultation and I was reviewing a large table. I spotted one table called Orders. Naturally, the size of the table was in millions of the rows. I thought – it is fine, a table can have that many rows. Then I checked another table called customers and it had under thousand records. The question which came to my mind was how come thousand customers ordered millions of items. I asked the local DBA coordinator the same question. He said oh, we just cleaned the customer table but the orders table is yet to clean – consider it as a small table.

Index Created on View not Used Often – Limitation of the View 3
I have heard many people saying that if they create a view and index on it, this will reduce the load on the original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.

Index Levels and Delete Operations – Page Level Observation
When data are deleted from any table, the SQL Server does not reduce the size of the table right away, but marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for some time background process de-allocates the pages and finally reducing the page size.  Follow the example below.

2011

Using Root With Auto XML Mode – Day 32 of 35

Q) You need to generate the following XML document from your CurrentProducts table:

<ProductExport>
<Product Price=”99″>Product1</Product>
<Product Price=”199″>Product2</Product>
<Product Price=”299″>Product3</Product>
<Product Price=”399″>Product4</Product>
</ProductExport>

Which query should you use?

  1. SELECT Price, ProductName
    FROM CurrentProducts AS ProductExport
    FOR XML PATH(‘Product’)
  2. SELECT Price, ProductName
    FROM CurrentProducts
    FOR XML AUTO, ROOT(ProductExport’)
  3. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts AS ProductExport
    FOR XML AUTO, ELEMENTS
  4. SELECT Price [@Price], ProductName AS [*]
    FROM CurrentProducts
    FOR XML PATH(‘Product’), ROOT(ProductExport’)

Shredding XML – Day 33 of 35

Q) What process will transform XML data to a rowset?

  1. Shredding
  2. Retrieving

Preparing XML in Memory – Day 34 of 35

Q) The sp_XML_PrepareDocument stored procedure requires a parameter that is an XML data type. What is the output parameter for?

  1. The handle as an INT
  2. The handle as an XML
  3. The handle as a Varchar

OpenXML Options – Day 35 of 35

Q) You have a table named Buildings that has an XML column named StoreHours. This column contains the opening and closing times:

<hours dayofWeek=“Monday” open=“8:00” closed= “18:00”/>
<hours dayofWeek=“Tuesday” open=“8:00” closed= “18:00”/>
<hours dayofWeek=“Wednesday” open=“8:00” closed= “18:00”/>

<hours dayofWeek=“Saturday” open=“9:00” closed= “17:00”/>

You need to write a query that returns a list of Buildings and their opening time for Wednesday.  Which code segment should you use?

  1. SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.value(‘/hours[1]/@dayofWeek’,’varchar(20)’) = @Day
  2. SELECT StoreName, StoreHours. value(‘/hours[1]/@open’,’time’)
    FROM Buildings
    WHERE StoreHours.exist(‘/hours[@dayofWeek=”Wednesday”]‘) = 1
  3. SELECT Storename, StoreHours.query(‘/hours[@dayofWeek=”Wednesday”]/@open’)
    FROM Buildings

Conversion Function – PARSE() – A Quick Introduction
PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result in an error. PARSE () function relies on the Common Language Runtime (CLR) to convert the string value.

2012

Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 – Introduction to DQS
Data Quality Services is a very important concept of SQL Server. I have recently started to explore the same and I am really learning some good concepts. Here are two very important blog posts which one should go over before continuing this blog post. This article is introduction to Data Quality Services for beginners.

Fun Post – Connecting Same SQL Server using Different Methods
I created a list of 5 different way but I am sure there are many more ways and I would like to document there here. Here is my setup. I am attempting to connect to the default instance of SQL Server from the same system where it is installed.

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

SQL SERVER – Weekly Series – Memory Lane – #044

memory lane SQL SERVER   Weekly Series   Memory Lane   #044Here 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

Use Always Outer Join Clause instead of (*= and =*)
Instead of using LEFT OUTER JOIN clause he was using *= and similarly instead of using RIGHT OUTER JOIN clause he was using =*. Once I replaced did necessary modification, queries run just fine.

Actual Execution Plan vs. Estimated Execution Plan
I always use the Actual Execution Plan as it is accurate. Why not Estimated Execution Plan? It is not accurate. Sometime it is easier or useful to to know the plan without running the query. I just run a query and have correct and accurate Execution Plan.

Shortcut for Display Estimated Execution Plan : CTRL + L
Shortcut for Include Actual Execution Plan : CTRL + M

Difference and Explanation among DECIMAL, FLOAT and NUMERIC
Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.

Find Database Collation Using T-SQL and SSMS
This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many readers asked me how to find the collation of the current database. There are two different ways to find out SQL Server database collation.

Find Database Status Using sys.databases or DATABASEPROPERTYEX
While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.

2008

How to Rename a Column Name or Table Name

Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Introduction to Table-Valued Parameters with Example
Table-valued parameters are declared using user-defined table types. To use a Table Valued Parameters we need follow the steps shown below:

  1. Create a table type and define the table structure
  2. Declare a stored procedure that has a parameter of the table type.
  3. Declare a table type variable and reference the table type.
  4. Using the INSERT statement and occupy the variable.
  5. We can now pass the variable to the procedure.

2009

SQL Server Express – A Complete Reference Guide
SQL Server Express is one of the most valuable products of Microsoft. Very often, I face many questions with regard to SQL Server Express. Today, we will be covering some of the most commonly asked questions.

2010

Does Order of Column in WHERE clause Matter?
Quick puzzle time – Does the order column used in WHERE clause matter for performance? Here are the rules for you –

  • You can use any numbers of the tables in your query
  • You can only change the order of columns in WHERE clause
  • You need to use either AND or OR clause between conditions of the WHERE clause
  • Performance will be measured using Actual Execution Plan and SET IO Statistics ON
  • The resultset returned from the query should be the same before changing order of columns in WHERE condition and after changing order of columns in WHERE condition.

Adding Column is Expensive by Joining Table Outside View – Limitation of the Views Part 2
In this episode of limitation of the View, we will see how adding an additional column outside the view can be very expensive, whereas the same situation does not happen with regular T-SQL query.

Plan Cache – Retrieve and Remove – A Simple Script
In this blog post we discuss about query that demonstrates cache plans which are ‘ad hoc’ or called only once in a life time. You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the another command to remove it.

2011

Table-Valued Store Procedure Parameters – Day 25 of 35

Q) You need to create a stored procedure which accepts a table-valued parameter named @Suppliers. What code will achieve this result?

  1. CREATE PROCEDURE AddSuppliers
    @Suppliers Float READONLY
  2. CREATE PROCEDURE AddSuppliers
    @Suppliers Int READONLY
  3. CREATE PROCEDURE AddSuppliers
    @Suppliers Money READONLY
  4. CREATE PROCEDURE AddSuppliers
    @Suppliers SupplierType READONLY
  5. CREATE PROCEDURE AddSuppliers
    @Suppliers GeographyType READONLY

Table-Valued Functions – Day 26 of 35

Q) You need to create two functions that will each return a scalar result of the number of hours each user has logged in: 1) the current day, and 2) month to date.  You will pass in the user ID as a parameter value. What two things must you do?

  1. Create a function that returns a list of values representing the login times for a given user.
  2. Create a function that returns a list of values representing the people who have logged more hours than the current user has logged.
  3. Create a function that returns a numeric value representing the number of hours that a user has logged for the current day.
  4. Create a function that returns a number value representing the number of hours that a user has logged for the current month.

SQL Server Error Messages – Day 27 of 35

Q) When does SQL Server always raise an error message? (Choose two)

  1. When a statement in SQL Server cannot run
  2. When multiple records are updated in one table
  3. When you issue a RAISERROR message

Structured Error Handling – Day 28 of 35

Q) You have tables named CurrentProducts and SalesInvoiceHeader. The CurrentProducts table has a foreign key relationship with the SalesInvoiceHeader table on the ProductID column. You are deleting ProductID 77 from the Product table and then trying to insert a sale for Product77 into the SalesInvoiceHeader table.

BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CurrentProducts  WHERE ProductID = 77
BEGIN TRANSACTION
INSERT INTO SalesInvoiceHeader VALUES ( 95894, 77, 2 )
COMMIT TRANSACTION
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH

What will be the outcome when you run this query?

  1. 1 The product will be deleted from the CurrentProducts table.
    2) The order details will be inserted into the SalesInvoiceHeader table.
  2. 1) The product will be deleted from the CurrentProducts table.
    2) The order details will not be inserted into the SalesInvoiceHeader table.
  3. 1) The product will not be deleted from the CurrentProducts table.
    2) The order details will be inserted into the SalesInvoiceHeader table.
  4. 1) The product will not be deleted from the CurrentProducts table.
    2) The order details will not be inserted into the SalesInvoiceHeader table.

What is XML? – Day 29 of 35

Q) If you don’t specify any option, then XML RAW will have your data streamed in…

  1. Element text.
  2. Attributes.

What is XML? – Day 30 of 35

Q) Without XSINIL, what happens to null values from your result set?

  1. They error out since XSINIL does not allow nulls.
  2. They appear as empty tags.
  3. No tags are present for null values.

Using Root With Auto XML Mode – Day 31 of 35

Q) You have a query which joins tables. You want to create a well-formed XML stream, which is attribute-based and nests the results in the table from the first field of the select list. Which code do you append to the SQL statement?

  1. FOR XML AUTO
  2. FOR XML RAW
  3. FOR XML AUTO, ROOT
  4. FOR XML RAW, ROOT

2012

Answer – Value of Identity Column after TRUNCATE command
Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time.

A Brief Note on SET TEXTSIZE
I do not think they can be directly comparable even though both of them give the exact same result while using SSMS. LEFT is applicable only on the column of a single SELECT statement. Where it is used but it SET TEXTSIZE applies to all the columns in the SELECT and follow up SELECT statements till the SET TEXTSIZE is not modified again in the session. Incomparable!

Three Methods to Insert Multiple Rows into Single Table – SQL in Sixty Seconds #024 – Video
One of the biggest ask I have always received from developers is that if there is any way to insert multiple rows into a single table in a single statement. Currently when developers have to insert any value into the table they have to write multiple insert statements. First of all this is not only boring it is also very much time consuming as well. Additionally, one has to repeat the same syntax so many times that the word boring becomes an understatement.

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