I recently received one big file with many T-SQL batches. It was a very big file and I was asked that this file was tested many times and it can run one transaction. I noticed the separator of the batches is not GO but it was EndBatch. I have followed two options to run the whole batch in one transaction. Let us learn how to change T-SQL Batch Separator.
SQLAuthority News – Limited Edition T-Shirts Arrived
I have received quite a few request for SQLAuthority.com T-shirts. Every day I receive lots of emails and suggestions. Many readers have great suggestions and have helped to improve content. First of all I express my gratitude to all of you. Few of my loyal and enthusiastic readers will receive…
Read MoreSQL SERVER – Disable Index – Enable Index – ALTER Index
There are few requirements in real world when Index on table needs to be disabled and re-enabled afterwards. e.g. DTS, BCP, BULK INSERT etc. Index can be dropped and recreated. I prefer to disable the Index if I am going to re-enable it again. USE AdventureWorks GO ----Diable Index ALTER INDEX [IX_StoreContact_ContactTypeID]…
Read MoreSQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
SQL SERVER – Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery
Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery Following statement will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” MS SQL Server doesn’t support it. USE PUBS GO SELECT AVG(COUNT(royalty)) RoyaltyAvg…
Read MoreSQL SERVER – Binary Sequence Generator – Truth Table Generator
Run following script in query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively. Script: USE AdventureWorks; DECLARE @Binary TABLE ( Digit bit) INSERT @Binary VALUES (0)…
Read MoreSQL SERVER – DBCC commands List – documented and undocumented
Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises. To learn about all the DBCC commands run following script in query…
Read MoreSQL SERVER – SQL Joke, SQL Humor, SQL Laugh – Photo
Pay attention to the last line of the ingredients. I found this entry at Worse Than Failure. I found it humorous. Reference : Pinal Dave (https://blog.sqlauthority.com)
Read MoreSQL SERVER – MS TechNet : Storage Top 10 Best Practices
This one of the very interesting article I read regarding SQL Server 2005 Storage. Please refer original article at MS TechNet here. Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application. More / faster spindles are better for performance. Try not to…
Read More
SQL SERVER – Query to Find First and Last Day of Current Month – Date Function
Following query will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.