This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are the advantages of User defined Function?
- You can create the function once, store it in the database, and call it any number of times in your program.
- Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions.
- An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function.
What is a scalar function in SQL Server?
A scalar-valued function (SVF) returns a single value, such as a string, integer, or bit value.You can create scalar-valued user-defined functions in managed code using any .NET Framework programming language.
Can you write dynamic SQL in User defined Function?
No: we cannot perform dynamic SQL in a User defined function.
We should re-write our function to not require dynamic SQL.
Have you created DDL Trigger?
Yes:
DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.
In which scenarios, you require to create DDL Triggers?
- Prevent certain changes to your database schema.
- Have something occur in the database in response to a change in your database schema.
- Record changes or events in the database schema.
What are the basic system tables to find information about the Triggers?
- sys.trigger_event_types
- sys.triggers
- sys.triggers
- sys.server_triggers
What is a DML trigger?
DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
What is an AFTER TRIGGER?
AFTER triggers are executed after the action of the INSERT, UPDATE, MERGE, or DELETE statement is performed. AFTER triggers are never executed if a constraint violation occurs; therefore, these triggers cannot be used for any processing that might prevent constraint violations.
What is an INSTEAD OF TRIGGER?
INSTEAD OF triggers override the standard actions of the triggering statement. Therefore, they can be used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows.
The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates.
Another advantage of INSTEAD OF triggers is that they enable you to code logic that can reject parts of a batch while letting other parts of a batch to succeed.
What is a Logon Triggers?
Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.
T-SQL Script to disable/enable the triggers.
To Disable:
To Enable:
What is a Linked Server?
Another question that can tell you if the candidate has experience with distributed databases is “What is a Linked Server?” A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.
What is a four-part name?
This question can help you get a sense for whether the candidate ever crosses database boundaries with his queries. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. for example, linkd1.db_name.dbo.table_name.
What does a cardinality of zero mean?
Well, if a column has a cardinality of zero, it means that the column has no unique values. This could potentially happen if the column has all NULL’s – which means that the column was never really used anyways.
How can you delete duplicate records using SET ROWCOUNT?
Use the SET ROWCOUNT command. For instance, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0.
What is Delegation in SQL Server?
SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL Server of forwarding the credentials of an authenticated Windows user.
This arrangement is known as delegation.
What is Character Set and Collation?
How to find total Row Count of All Tables in a Database?
SQL Server: Script to find total Row Count of All Tables in a Database
How to find the last Executed Query using SPID?
SQL Server: Script to find the last Executed Query using SPID
