SQL SERVER – Online Index Operations, NEWID and More – Memory Lane #042

This is the 42nd edition of Memory Lane weekly 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. My favorite article is about Online Index Operations and NEWID. Let me know which one of the following is your favorite article from memory lane.

2007

Validate Integer Function
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). Now this is an issue with ISNUMERIC () function. It even suggest variable as numeric when it is not. I use following function to validate instead of ISNUMERIC () to validate if the number is numeric or not.

Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Difference and Similarity Between NEWSEQUENTIALID() and NEWID()
NEWSEQUENTIALID() and NEWID() both generates the GUID of datatype of uniqueidentifier. NEWID () generates the GUID in random order, whereas NEWSEQUENTIALID () generates the GUID in sequential order.

Explanation and Script for Online Index Operations – Create, Rebuild, Drop
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. In case of Online Indexing Operations, when Index operations (create, rebuild, dropping) are occuring they do not require exclusive access to database, they do not lock any database tables. This is a major important upgrade in SQL Server from previous versions.

2008

Get Date Time in Any Format – UDF – User Defined Functions
A very comprehensive function which returns date time in any format.

Introduction to Online Indexing Operation
SQL Server 2005 and later versions have provided feature called “Online Indexing”. Everytime index is updated it puts a lock on a table where index operations are happening. Depending on the situation SQL Server puts schema lock or shared lock while the index is modified. If a feature of “Online Indexing” is used SQL Server will behave normally when the Index is modified.

2009

Backup master Database Interval – master Database Best Practices

• The master database is the most important database of all and the most recent version of the backup should be available in the case of disaster.
• Backup of the master database should be made for:
  • Changing server-level configuration settings;
  • Changing database-level configuration settings; and
  • Changing any logon accounts details.

Measure CPU Pressure – CPU Business
Let us understand the simple meaning of CPU pressure. CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.

2010

SHRINKDATABASE For Every Database in the SQL Server
Shrinking the database is indeed bad and it reduces the performance of the server. In this blog post, I clearly demonstrate the real world scenario for the same.

Why SQL Server is better than any other RDBMS Applications?
Very interesting arguments on this subject in this blog. I really enjoyed and I am very sure everybody who loves SQL Server will enjoy this blog post.

Finding the Occurrence of Character in String
A straight to script blog where I explained how to find the occurrence of character in any string.

2011

Tips from the SQL Joes 2 Pros Development Series – Advanced Aggregates with the Over Clause – Day 11 of 35
Q) You have a table named CurrentProducts. The table contains a column named Category. You need to make a T-SQL statement that calculates the percentage (with decimals) of products in each product Category. Which query should you use?

1. SELECT DISTINCT Category,
   COUNT(*) OVER (PARTITION BY Category)*100/ COUNT(*) OVER() as PctCategory
   FROM CurrentProducts
2. SELECT DISTINCT Category,
   COUNT(*) OVER (PARTITION BY Category)*100.0/ COUNT(*) OVER() as PctCategory
   FROM CurrentProducts
3. SELECT DISTINCT Category,
   COUNT(*) OVER ( )*100.0/ COUNT(*) OVER(PARTITION BY Category) as PctCategory
   FROM CurrentProducts

Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – RANK( ), DENSE_RANK( ), and ROW_NUMBER( ) – Day 12 of 35
Q) The figure below shows the scores of 6 contest winners. Tom is the highest

and Eric made 6th place. There were 6 people, but only 5 distinct scores.

You are writing a query to list the 5 highest distinct scores. The Ranked field should be called ScoreRating. You have written the following code.

SELECT * FROM
(SELECT  *  More code here.
FROM [Contestants])  AS ContestantFinal
WHERE ScoreRating <= 5

What code will achieve this goal?

1. SUM(*) OVER(ORDER BY Score DESC) as ScoreRating
2. RANK( ) OVER(ORDER BY Score DESC) as ScoreRating
3. COUNT(*) OVER(ORDER BY Score DESC) as ScoreRating
4. DENSE_RANK( ) OVER(ORDER BY Score DESC) as ScoreRating

Tips from the SQL Joes 2 Pros Development Series – Ranking Functions – Advanced NTILE in Detail – Day 13 of 35
Q) You want to find the top 2% of all students’ Grade Point Averages (GPA). Which NTILE would you use?

1. NTILE(1) OVER(ORDER BY GPA DESC)
2. NTILE(2) OVER(ORDER BY GPA DESC)
3. NTILE(25) OVER(ORDER BY GPA DESC)
4. NTILE(50) OVER(ORDER BY GPA DESC)

Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples – Day 14 of 35
Q) You have an HourlyPay table and are giving all hourly employees a $1 raise. When you run the update statement you want to see the EmpID, OldPay, and NewPay. What code will achieve this result?

1. UPDATE HourlyPay SET Hourly = Hourly + 1
   OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Updated.Hourly as NewPay
   WHERE Hourly IS NOT NULL
2. UPDATE HourlyPay SET Hourly = Hourly + 1
   OUTPUT Deleted.EmpID , Updated.Hourly as OldPay, Deleted.Hourly as NewPay
   WHERE Hourly IS NOT NULL
3. UPDATE HourlyPay SET Hourly = Hourly + 1
   OUTPUT Deleted.EmpID , Inserted.Hourly as OldPay, Deleted.Hourly as NewPay
   WHERE Hourly IS NOT NULL
4. UPDATE HourlyPay SET Hourly = Hourly + 1
   OUTPUT Deleted.EmpID , Deleted.Hourly as OldPay, Inserted.Hourly as NewPay
   WHERE Hourly IS NOT NULL

Tips from the SQL Joes 2 Pros Development Series – Data Row Space Usage and NULL Storage – Day 15 of 35
Q) You have three variable length data fields. What are the rules that go into the calculation of how large the variable block will be (Choose two)?

1. You will allocate 2 bytes to the creation of the variable block
2. You will allocate 3 bytes to the creation of the variable block
3. You will allocate 2 more bytes for each of the three variable fields
4. You will allocate 1 byte for every eight columns in the table.

Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35
Q) Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?

1. GETDATE( )
2. SYSDATETIME( )
3. GETUTCDATE( )
4. SYSUTCDATETIME( )

Tips from the SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data – Day 17 of 35
Q) You have two fields, of INT and MONEY, in your Bonus table. You have 1000 records and all instances of the money column are null. When you set up the money field, you used the Sparse option. How much space are the 1000 rows of the money field using?

1. None
2. 4000 bytes
3. 8000 bytes

2012

Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – Part 1 of 2
In simple words – due to ON DELETE CASCASE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.

Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2
My friend was confused as there was no delete was firing over ProductsDetails Table still there was a delete happening. The reason was because there is a foreign key created between Products and ProductsDetails Table with the keywords ON DELETE CASCADE. Due to ON DELETE CASCADE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well.

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