What’s wrong with this query?

Tags

query, sub-query, table alias

Recently there was a question on one of the MS SQL forum, the query which user had posted was pretty simple but was behaving strangely.
Here I’m demonstrating you all the scenario which user had posted.

Create two tables and inserted few records in both the tables.

DECLARE @Header Table (EmpId Int , FirstName Varchar(50) , LastName Varchar(50))
DECLARE @Detail Table (id Int , Department Varchar(50))

INSERT INTO @Header Values(1 , 'ABC' , 'CBA'), (2 , 'BCD' , 'DCB'),(3 , 'CDE' , 'EDC')
INSERT INTO @Detail Values(7 , 'IT'), (8 , 'HR'),(9 , 'Admin')

Now execute below query, which pulls records from Header table for matching EmpId exists in Detail table.

SELECT * FROM @Header WHERE EmpId IN (SELECT EMPID From @Detail)

Following is the result set generated by above query.

Is this query pulling out right data?

Apparently, yes it does, but wait a minute and carefully observe the sub-query.

SELECT EMPID From @Detail

If you carefully check the Detail table definition it doesn’t have any EMPID column defined, infact it has column name ID, but still entire query runs successfully and produces the result.

The EMPID in the sub-query does refer to the outer-query (Header) EMPID column and we are getting the result set.

Alter the above query introduce table alias and run it again.

SELECT * FROM @Header H WHERE H.EmpId IN (SELECT D.EMPID From @Detail D)

Now, the query fails with the error message.

Msg 207, Level 16, State 1, Line 7
Invalid column name ‘EMPID’.

Many a times we do commit this kind of coding errors in our queries without realising it, so it’s always recommended to use table alias’s in your queries to overcome this.

Hope this helps.!!!

SQL Server – Finding Last Backup & Restore Date for All Database

Tags

last backup, last restore, SQL Server

Many a times we need to find when my database last backed up or when it was last restored.
Below is the quick script i use to find last backup and restore dates.

Script to find last backup date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.backupset bus ON bus.database_name = db.name
GROUP BY
db.Name

Script to find last restored date
SELECT
db.Name AS DatabaseName,
CONVERT(VARCHAR(12), MAX(rh.restore_date), 101) AS LastBackUpDate
FROM
sys.sysdatabases db
LEFT JOIN msdb.dbo.restorehistory rh ON rh.destination_database_name = db.name
GROUP BY db.Name

Hope this helps…!

Magical Object Explorer Details pane

Tags

SQL Server Object Explorer

In this blog I just show you how effectively we can make use of SSMS object Explorer Detail pane.

Once you are in SSMS, go to View-> Object Explorer Details alternatively you can use shortcut key F7 to access Object Explorer window as shown below.

This brings you the Object Explorer Detail window in the same location where we normally have the Query window.

Now you can navigate to any node in object explorer or click any node in object explorer and it will give you the related detail on Object Explorer Detail pane as shown in the above screen. Here I have selected SQL Server Agent node on Object Explorer and in Object Explorer Detail pane we can see all the objects related to SQL Server Agent.
Now I will show you how can we make best you of Object Explorer Detail pane to script out all the Jobs in a single click.
Navigate to SQL Server Agent -> Jobs, Object Explorer Detail pane will display all the jobs, now select any job and say Ctrl+A all jobs will be selected, now right click then say Script Job As -> New Query Editor Window as shown below.

Now you have script out all your jobs in a single query window, no need to go to each job to generate script. Same technique can be used to generate script of various other objects like tables, views, procedures etc.

Will explore some more features of Object Explorer Detail pane in coming days.

Hope this helps.!!!