SQL SERVER – UDF, UPDATE and More – Memory Lane – #047

This is the 47th episode of Memory Lane. 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. My favorite blog posts are about UDF and the difference between UPDATE and UPDATE (). 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

• SQL SERVER – 2008 – Interview Questions and Answers – Part 1
• SQL SERVER – 2008 – Interview Questions and Answers – Part 2
• SQL SERVER – 2008 – Interview Questions and Answers – Part 3
• SQL SERVER – 2008 – Interview Questions and Answers – Part 4
• SQL SERVER – 2008 – Interview Questions and Answers – Part 5
• SQL SERVER – 2008 – Interview Questions and Answers – Part 6
• SQL SERVER – 2008 – Interview Questions and Answers – Part 7
• SQL SERVER – 2008 – Interview Questions and Answers – Part 8

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.

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

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 (https://blog.sqlauthority.com)