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.
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.
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.
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.
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.
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.
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?
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.
5 Highest Scores 5 Highest 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?
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?
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?
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)?
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?
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?
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 (http://blog.sqlauthority.com)