SQL SERVER – Weekly Series – Memory Lane – #034
- 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
UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression
The UDF used in the blog does fantastic task – it scans entire HTML text and removes all the HTML tags. It keeps only valid text data without HTML task. This is one of the quite commonly requested tasks many developers have to face everyday.
De-fragmentation of Database at Operating System to Improve Performance
Operating system skips MDF file while defragging the entire filesystem of the operating system. It is absolutely fine and there is no impact of the same on performance. Read the entire blog post for my conversation with our network engineers.
Delay Function – WAITFOR clause – Delay Execution of Commands
How do you delay execution of the commands in SQL Server – ofcourse by using WAITFOR keyword. In this blog post, I explain the same with the help of T-SQL script.
Find Length of Text Field
To measure the length of TEXT fields the function is DATALENGTH(textfield). Len will not work for text field. As of SQL Server 2005, developers should migrate all the text fields to VARCHAR(MAX) as that is the way forward.
Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
There are three ways to retrieve the current datetime in SQL SERVER. CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}
Explanation and Comparison of NULLIF and ISNULL
An interesting observation is NULLIF returns null if it comparison is successful, whereas ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.
Here is my question to you - How to create infinite loop using NULLIF and ISNULL? If this is even possible?
2008
Introduction to SERVERPROPERTY and example
SERVERPROPERTY is a very interesting system function. It returns many of the system values. I use it very frequently to get different server values like Server Collation, Server Name etc.
SQL Server Start Time
We can use DMV to find out what is the start time of SQL Server in 2008 and later version. In this blog you can see how you can do the same.
Find Current Identity of Table
Many times we need to know what is the current identity of the column. I have found one of my developers using aggregated function MAX () to find the current identity. However, I prefer following DBCC command to figure out current identity.
Create Check Constraint on Column
Some time we just need to create a simple constraint over the table but I have noticed that developers do many different things to make table column follow rules than just creating constraint. I suggest constraint is a very useful concept and every SQL Developer should pay good attention to this subject.
2009
List Schema Name and Table Name for Database
This is one of the blog post where I straight forward display script. One of the kind of blog posts, which I still love to read and write.
Clustered Index on Separate Drive From Table Location
A table devoid of primary key index is called heap, and here data is not arranged in a particular order, which gives rise to issues that adversely affect performance. Data must be stored in some kind of order. If we put clustered index on it then the order will be forced by that index and the data will be stored in that particular order.
Understanding Table Hints with Examples
Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.
2010
Data Pages in Buffer Pool – Data Stored in Memory Cache
One of my earlier year article, which I still read it many times and point developers to read it again. It is clear from the Resultset that when more than one index is used, datapages related to both or all of the indexes are stored in Memory Cache separately.
TRANSACTION, DML and Schema Locks
Can you create a situation where you can see Schema Lock? Well, this is a very simple question, however during the interview I notice over 50 candidates failed to come up with the scenario. In this blog post, I have demonstrated the situation where we can see the schema lock in database.
2011
Solution – Puzzle – Statistics are not updated but are Created Once
In this example I have created following situation:
Create Table
Insert 1000 Records
Check the Statistics
Now insert 10 times more 10,000 indexes
Check the Statistics – it will be NOT updated
Auto Update Statistics and Auto Create Statistics for database is TRUE
Now I have requested two things in the example 1) Why this is happening? 2) How to fix this issue?
Selecting Domain from Email Address
This is a straight to script blog post where I explain how to select only domain name from entire email address.
Solution – Generating Zero Without using Any Numbers in T-SQL
How to get zero digit without using any digit? This is indeed a very interesting question and the answer is even interesting. Try to come up with answer in next 10 minutes and if you can’t come up with the answer the blog post read this post for solution.
2012
Simple Explanation and Puzzle with SOUNDEX Function and DIFFERENCE Function
In simple words - SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. DIFFERENCE function returns an integer value. The integer returned is the number of characters in the SOUNDEX values that are the same.
Read Only Files and SQL Server Management Studio (SSMS)
I have come across a very interesting feature in SSMS related to “Read Only” files. I believe it is a little unknown feature as well so decided to write a blog about the same.
Identifying Column Data Type of uniqueidentifier without Querying System Tables
How do I know if any table has a uniqueidentifier column and what is its value without using any DMV or System Catalogues? Only information you know is the table name and you are allowed to return any kind of error if the table does not have uniqueidentifier column. Read the blog post to find the answer.
Solution – User Not Able to See Any User Created Object in Tables – Security and Permissions Issue
Interesting question – “When I try to connect to SQL Server, it lets me connect just fine as well let me open and explore the database. I noticed that I do not see any user created instances but when my colleague attempts to connect to the server, he is able to explore the database as well see all the user created tables and other objects. Can you help me fix it?”
Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
Here is interesting small 60 second video on how to import CSV file into Database.
ColumnStore Index – Batch Mode vs Row Mode
Here is the logic behind when Columnstore Index uses Batch Mode and when it uses Row Mode. A batch typically represents about 1000 rows of data. Batch mode processing also uses algorithms that are optimized for the multicore CPUs and increased memory throughput.
Follow up – Usage of $rowguid and $IDENTITY
This is an excellent follow up blog post of my earlier blog post where I explain where to use $rowguid and $identity. If you do not know the difference between them, this is a blog with a script example.
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
