Latest Event Updates

OPENQUERY Vs LinkedServer

Posted on Updated on

What’s the differences between OPENQUERY vs. Linked Server?

  1. OPENQUERY uses the source server’s processing power to query the data
  2. OPENQUERY returns the result set specific to the WHERE clause making it faster
  3. LinkedServer uses the local server processing power to query all of the source’s data and then reprocesses the data to return the result set
  4. LinkedServer is not a direct call to the source server

How to use OPENQUERY and LinkedServer

ORACLE represents the Providers name under the Linked Servers, Server Objects in SQL MSSMS. (MSSMS -> Object Explorer -> Database -> Server Objects -> Linked Servers – > Providers)

OPENQUERY

SELECT * FROM OPENQUERY(ORACLE,’SELECT * FROM PAYMASTR WHERE CHECK_ID = 1′)

LinkedServer

SELECT * FROM ORACLE.DATABASENAME.dbo.PAYMASTR WHERE CHECK_ID = 1

Creating a Temp Table on the Fly

Posted on Updated on

Sometimes we need to create a temporary table on the fly to hold our data for whatever reasons. Here is one approach in creating a temporary table on the fly.

–LOCAL SCOPE
–ONLY YOU CAN SEE THIS TEMP TABLE

SELECT *
INTO #forMe
FROM Customer

SELECT * FROM #forMe

–GLOBAL SCOPE
–EVERYONE CAN SEE THIS TEMP TABLE

SELECT *
INTO ##forEveryone
FROM Customer

SELECT * FROM ##forEveryone

DROP TABLE #forMe
DROP TABLE ##forEveryone

Recover a Deleted Stored Procedure

Posted on Updated on

To recover a deleted object, the SQL Service MUST NOT be restarted. If the SQL Service has been restarted then you won’t be able to find your deleted Stored Procedure.

SELECT A.objtype, B.text, C.query_plan
FROM sys.dm_exec_cached_plans A
CROSS APPLY sys.dm_exec_sql_text ( A.plan_handle ) B
CROSS APPLY sys.dm_exec_query_plan ( A.plan_handle ) C
WHERE B.text LIKE ‘%Alter%’

Check if TABLE exist

Posted on Updated on

Check if the table exists before dropping the table.

IF OBJECT_ID(‘dbo.Customer‘) IS NOT NULL
     DROP TABLE dbo.Customer
ELSE
     PRINTTable already exist

Find SQL Version installed

Posted on Updated on

SQL Versions: 2005, 2008, 2008 R2, 2012, 2014

If you need to see the specific version of SQL you’re running.

SELECT @@VERSIONN

Find current Service Pack installed

Posted on

SQL Versions: 2005, 2008, 2008 R2, 2012, 2014

If you need to see the specific version of SQL Service Pack installed. This SP is not the same as the Windows OS SP.

SELECT SERVERPROPERTY(‘ProductLevel‘)