All Tips & Questions

Tip of the day

Know your SQL Server Version by simple Select Query

Knowing your SQL Server version is easy by running simple SELECT command.

SELECT @@VERSION

Output:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )

Learn more about @@ Keywords (Transact-SQL) in SQL Server

- by

Question of the day

SQL Server: What is the difference between EXEC and sp_executesql?

sp_executesql (also known as "Forced Statement Caching")

  • Allows for statements to be parameterized.
  • Only allows parameters where SQL Server would normally allow parameters; however, this string can be built using forms of dynamic constructs.
  • Has strongly typed variables/parameters – and this can reduce injection and offer some performance benefits!
  • Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan

Syntax:
DECLARE @SQL_Command NVARCHAR(MAX);
SELECT @SQL_Command = 'SELECT * FROM CUSTOMERS WHERE FIRST_NAME LIKE @First_Name';
EXEC sp_executesql @SQL_Command, N'@First_Name nvarchar(50)', 'Venkat%';

EXEC (also known as "Dynamic String Execution" or DSE)
  • Allows *any* construct to be built.
  • Treats the statement similarly to an adhoc statement. This means that the statement goes through the same process that adHoc statements do – they are parsed, probably parameterized and possibly deemed “safe” for subsequent executions to re-use.
  • Does not have strongly typed parameters in the adhoc statement and therefore can cause problems when the statements are executed.
  • Does not force a plan to be cached.
    • This can be a pro in that SQL Server can create a plan for each execution.
    • This can be a con in that SQL Server needs to recompile/optimize for each execution.
Syntax:
EXEC ('SELECT * FROM CUSTOMERS WHERE FIRST_NAME LIKE ''Venkat%''')

- by


We are inviting Tips & Questions from you. Click Here to share your valuable Tips & Questions on this website.

  • Your Tips & Questions will be displayed on website, after validating the information you provided.
  • Tips & Questions will be refreshed on every day 12:00 AM IST.