SQL SERVER – Weekly Series – Memory Lane – #051
- by Pinal Dave
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
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
After Presentation
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.
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)
Filed under: Memory Lane, PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
