The difference is COUNT(*) includes NULL values while COUNT(column_reference) doesn’t. As an example consider the two below queries for Northwind database that find the number of orders placed by each Northwind customer: WRONG: RIGHT:
What is CTE (Common Table Expressions) A common table expression (CTE) is a temporary result set accessible within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE does not hold any physical space in database like a derived table. But Unlike a derived table, a CTE can be self-referencing
What is a join? Before starting discuss on joins, first we should know what is join? SQL Server is a RDBMS(Relation database management system), i.e. it has tables which holds rows and columns and relationship between multiple tables using keys. So when we have to get data from more than one table, we use joins to
Problem: Last night i was uninstalling SQL Server 2008 from my machine and got stuck with an issue “Restart Computer Failure”. I tried several time but after goggling, i got the solution and now sharing with you all Solution : Start Regedit Go to the key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager” Delete “PendingFileRenameOperations” entry and try again
Restart Computer Failure issue during SQL Server 2008 uninstall Read More »
New features added in SQL 2014 (Headings only) Below are the enhancement points list : Database Engine Feature Enhancements o Memory-Optimized Tables o SQL Server Data Files in Windows Azure o Host a SQL Server Database in a Windows Azure Virtual Machine o Backup and Restore Enhancements o New Design for Cardinality Estimation o Delayed Durability
New features in SQL Server 2014 (In Database Engine) Read More »
In this post, we are going to learn “how we can convert a comma separated list into a table using a recursive CTE”. Below is the code: Why OPTION MAXRECURSION? Hint OPTION(MAXRECURSION 32767) is a query hint which instructs to compiler to iterate 32767 times. Default, max recursion of a loop in SQL is 100 and in case we
Problem Detail In the morning at your workplace, your manager or any colleague comes to you and asks to get the list of all the dates in a comma separated list. Now the first and the quickest way of getting the output which will knock us is using a loop to get the list. In
Avoid Use of While Loops to get a Comma Seperated list of rows Read More »
ISNULL vs COALESCE in SQL Server In our T-SQL programming, we mostly use ISNULL function to replace the null value of a column with another value. The same can be achieved using COALESCE function too. In this blog post, we are going to see a significant difference between NULL and COALESCE functions. We are going to
