User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
What is the use of COALESCE in SQL Server?
COALESCE accepts "n" no of parameters and returns first not null values from the parameters passed to the function.
Read more in detail with example...
What is Row_Number()?
ROW_NUMBER() is used to generate a unique row number in the result set.
Read more in detail with example...
What is Apply Operator?
The Apply operator acts like a Join without the ON clause. The Apply function in a query allows you to join a table to a table-valued function in such a way that function is invoked for each row returned from the table
Read more in detail with example...
What is the STUFF function and how does it differ from the REPLACE function?
STUFF function is used to overwrite existing characters.
Syntax
STUFF (string_expression, start, length, replacement_characters)
- string_expression is the string that will have characters substituted
- start is the starting position
- length is the number of characters in the string that are substituted
- replacement_characters are the new characters interjected into the string.
- REPLACE function is used to replace existing characters of all occurrences.
REPLACE (string_expression, search_string, replacement_string)Every incidence of search_string found in the string_expression will be replaced with replacement_string.
What is a Stored Procedure?
Stored Procedure in nothing but a set of T-SQL statements combined to perform several set of statements as a single task.
What are the advantages of using Stored Procedures?
- Stored procedure can reduced network traffic and latency, boosting application performance.
- Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
- Stored procedures help promote code reuse.
- Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
- Stored procedures provide better security to your data.
Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?
1024
What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.
From within Query Analyzer is an option called "Show Execution Plan"
(located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.
What is the use of Keyword WITH ENCRYPTION keyword in SQL Server?
WITH ENCRYPTION keyword is used to encrypt the text of the Stored Procedure.
Read more in detail with example..
How to pass data table to stored procedure as parameter in SQL Server 2005 (Write stored procedure) ?
Refer this post : Passing Data Table to Stored Procedure
How to achieve paging from SQL side.
Refer this post : Custom Paging
No comments:
Write Comments