Forced parameterization, inline table-valued functions, @ variables, UDFs

Question

Forced parameterization, inline table-valued functions, @ variables, UDFs

Golfer22

SSCrazy

Points: 2256
More actions
January 29, 2012 at 11:18 pm

#255573

I have four questions that have nothing to do with each other. They are small, so I hope it is ok to consolidate them.
#1
This link
http://msdn.microsoft.com/en-us/library/ms175037.aspx
says this:
"Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query."
What is the difference between literal constants and a parameter?
#2
What is the difference between inline table-valued functions and table-valued functions that are not inline? The term inline is elusive to me.
#3
Is there a command to know what all the @ variable statements do? I think these can be custom made. But by default, these seem to have a value in SQL Server.
#4
How is a custom-made stored procedure different from a UDF?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Rowles (1/29/2012)
I have four questions that have nothing to do with each other. They are small, so I hope it is ok to consolidate them.
#1
This link
http://msdn.microsoft.com/en-us/library/ms175037.aspx
says this:
"Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query."
What is the difference between literal constants and a parameter?

The difference is in the caching of the execution plan. Imagine you have a while loop in your code which creates dynamic sql statements such as "SELECT * FROM SomeTable WHERE Key=" and each iteration concatenates a different value to the statement (let's say 1,2,3...1000). For SQLServer, each statement is different from the previous one and has to calculate a different execution plan.

With forced parametrization, the literal values are replaced by a parameter and the statement executed internally by SQLServer is "SELECT * FROM SomeTable WHERE Key = @parameter", even if the statement passed by your code is "SELECT * FROM SomeTable WHERE Key = 5".

This way, the statement's execution plan can be cached and reused for each iteration.

Paul Randal and Kimberly Tripp have a better explanation here: http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/forced-parameterizationwhen-should-i-use-it-137149

#2
What is the difference between inline table-valued functions and table-valued functions that are not inline? The term inline is elusive to me.

Inline table-valued functions are functions that contain a single SELECT statement wrapped in a RETURN command. This kind of functions is particularly useful because SQLServer can merge the function's execution plan with the calling statement's execution plan.

Multi statement table-valued functions can contain multiple statements that retrieve data and return it to the calling statement by using INSERTs to a table variable declared in the RETURNS section of the function definition. This kind of function can create serious performance issues, because the execution plans are not merged and the function has to be invoked for each row in the input.

This post explains it pretty well: http://aboutsqlserver.com/2011/10/23/sunday-t-sql-tip-inline-vs-multi-statement-table-valued-functions/

#3
Is there a command to know what all the @ variable statements do? I think these can be custom made. But by default, these seem to have a value in SQL Server.

This question is unclear to me. Can you rephrase it or include an example?

#4
How is a custom-made stored procedure different from a UDF?

In many ways:

* A stored procedure can read and modify data, a UDF can only read data.

* UDFs can be used in SELECT statements in the FROM clause, while Stored Procedures cannot

* UDFs can have scalar return values or return tabular data. Stored procedures can return values with output parameters or return values, or issue a SELECT statement.

Here's a blog post on the subject: http://chiragrdarji.wordpress.com/2007/04/17/difference-between-user-defined-function-and-stored-procedure/

Hope this helps

Gianluca

-- Gianluca Sartori

Golfer22 SSCrazy Points: 2256 More actions · Answer 2

I understand system functions are often specified with an @@ at the beginning of them. What percentage of system functions have the @@ symbol at the beginning of them?

From this page, http://www.develop-one.net/blog/2011/07/08/ShrinkSQLServer2008R2LogFiles.aspx, I found this:

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Could LogFileLogicalName be replaced with "nonsense" or "TheBeatles"? If not, where do I find the reserved @ variables in SQL Server 2008? If so, what is "sysname"?

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Rowles (1/30/2012)
I understand system functions are often specified with an @@ at the beginning of them. What percentage of system functions have the @@ symbol at the beginning of them?

Not many:

@@CONNECTIONS

@@CPU_BUSY

@@IDLE

@@IO_BUSY

@@PACK_SENT

@@PACKET_ERRORS

@@TIMETICKS

@@TOTAL_ERRORS

@@TOTAL_READ

@@TOTAL_WRITE

@@ERROR

@@IDENTITY

@@PACK_RECEIVED

@@ROWCOUNT

@@TRANCOUNT

@@DBTS

@@LANGID

@@LANGUAGE

@@LOCK_TIMEOUT

@@MAX_CONNECTIONS

@@MAX_PRECISION

@@NESTLEVEL

@@OPTIONS

@@REMSERVER

@@SERVERNAME

@@SERVICENAME

@@SPID

@@TEXTSIZE

@@VERSION

@@CURSOR_ROWS

@@FETCH_STATUS

@@DATEFIRST

@@PROCID

From this page, http://www.develop-one.net/blog/2011/07/08/ShrinkSQLServer2008R2LogFiles.aspx, I found this:
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName
DBCC Shrinkfile(@LogFileLogicalName,100)
Could LogFileLogicalName be replaced with "nonsense" or "TheBeatles"?

Yes, given that you replace every single occurrence of @LogFileLogicalName with @TheBeatles. You choose the variable name.

If not, where do I find the reserved @ variables in SQL Server 2008? If so, what is "sysname"?

sysname is a whole different thing. It's a data type.

Well, not exactly: it's a synonym for nvarchar(128).

-- Gianluca Sartori