Chapter 3. Program databases Flashcards
View
Is a reusable named query, or table expression whose definition is stored in the database. They are accessible to users who have the permission to query it. You can also modify data in underlying tables trough it.
SCHEMABINDING
Prevents structural changes to dependent tables and columns while the view exists.
If you want to change the tables, you need to delete and then to recreate the view
Indexed Views
You need to first add a clustered index for you to add a non-clustered index
User-defined functions
is a routine that accepts parameters, applies calculations, and returns either a scalar-valued or table valued result.
Its an alternative to a view with parameters, since views dont support parameters
User-defined functions - limitations and restrictions
UDF you cannot: Use error handling Modify data Use data definition language (DDL) Use temporary tables Use dynamic SQL Invoke NEWID or RAND within UDF
Scalar-User defined functions
accepts parameters and returns a single value.
It must invoke a RETURN clause to return the value
Types of User defined functions
Scalar UDF
Inline table valued functions
Multistatement table valued functions
UDF requirements - deterministic vs non-deterministic
In order to use a user-defined function in a persisted computed column or in an indexed view, the function needs to be deterministic and it needs to be defined with SCHEMABINDING
Inline table valued user defined function
is very similar in concept to a view in the sense that it is based on a single query and you interact with it like a table expression, but unlike a view it supports input parameters. Paramaterized view
Multistatement table-valued user-defined functions
A multi-statement table-valued function or MSTVF is a table-valued function that returns the result of multiple statements.
Stored Procedures
Are routines that support flow with multiple steps. They can modify data and even make changes to the database DDL, error handling, using temporary tables and more.
They cannot be embedded in queries
Stored procedures - pros
Their results are cached to save time
They are easier to change with a simple ALTER PROC than to change the application
They simplify security handling
Less traffic because it only needs to send the name of the procedure and input parameters
How to make an input parameter optional?
You define them with a default NULL
CREATE OR ALTER PROC dbo.GetOrders
@orderid AS INT = NULL,
@orderdate AS DATE = NULL,
@custid AS INT = NULL,
@empid AS INT = NULL
AS
XACT_ABORT option
determines the effect on run time errors raised by T-Sql statements.
When this is OFF (default in most cases) some errors cause an open transaction to roll back, whereas other errors leave the transaction open. Best practice is to be set ON
NOCOUNT option
Indicates how many rows were affected - they degrade query performance
EXEC procedure - format
EXEC dbo.GetOrders @orderid = 42;
dynamic SQL
building a batch of code as a charachter string usually in a variable and then telling SQL server to excecute the code that resides in that variable - it is not ideal for query performance
One way to get efficient plans is to add the RECOMPILE option
OPTION(RECOMPILE);
create User
CREATE LOGIN login1 WITH PASSWORD = ‘J345#$)thb’;
GO
CREATE USER user1 FOR LOGIN login1;
GO
Grant permission to a user
GRANT EXEC ON dbo.GetOrders TO user1;
EXECUTE AS
you can define the stored procedure with an EXECUTE AS clause, to impersonate the security context of the procedure’s execution to that of the specified entity. For example, using the option EXECUTE AS OWNER
OUTPUT or OUT
you need to provide a local variable to accept the returned value, and indicate the keyword OUTPUT or OUT again in assignment, otherwise the parameter is actually treated as an input one.
Cursors
Cursors allow you to iterate through rows of some query result one at a time
@@TRANCOUNT
a way to know if you’re currently in an open transaction or not. If you’re in an open transaction the function returns a value greater than zero, otherwise it returns zero
Type of statements that are transactional
DML statements (INSERT UPDATE, DELETE, TRUNCATE, MERGE) GRANT, REVOKE and others
SET IMPLICIT_TRANSACTIONS OFF;
Having implicit transactions turned on is usually a bad idea. It increases the likelihood that transactions stay open for long periods, or worse, just stay open until someone realizes that that’s the case and manually intervenes. Long open transactions can cause performance problems in the system, including blocking and others.
ERROR_NUMBER()
provides the error number.
ERROR_MESSAGE()
ERROR_MESSAGE() provides the error message.
ERROR_SEVERITY()
ERROR_SEVERITY() provides the error severity. You can catch errors with severity 11 to 19. Errors with severity 20 and up are so severe that when they happen, SQL Server terminates your connection. Any error handling code that you may have doesn’t really have a chance to run. Messages with severity 0 to 9 are considered informational and are always passed to the client; they’re not accessible to SQL Server. Messages with severity 10 are also informational; for compatibility reasons, SQL Server converts those to severity 0 internally.
ERROR_STATE()
ERROR_STATE() provides the error state. The error state is an integer in the range 1 to 255. It can be used for different custom purposes such as indicating where the error originated in cases where the error can happen in different places in the SQL Server engine’s code.
ERROR_LINE()
ERROR_LINE() provides the line number where the error happened.
ERROR_PROCEDURE()
ERROR_PROCEDURE() provides the name of the stored procedure where the error happened. If the error did not happen in a stored procedure, this function returns a NULL.
IMPORTANT for Error functions
- If you invoke these functions not within a CATCH block, they all return NULLs.
- If you nest TRY-CATCH constructs, whether directly or indirectly when you call one procedure from another, these functions return error information about the innermost error.
- Some failures generate a chain of errors. These functions only return information about the last error in the chain.
RAISERROR - format
RAISERROR ( { message | messageid }, severity, state [, arguments for parameter markers
] ) [ WITH options ];
RAISERROR( ‘This is a user-define error with a string parameter %s and an integer
parameter %d.’, 16, 1, ‘ABC’, 123 );
THROW
It has 2 supported syntaxes
One without parameters allows you to re-throw the arroe that originally activated that CATCH block
THROW always aborts the batch irrespective of the error and bubbles up until you catch it with a catch block
THROW with parameters
THROW errornumber, message, state;
The first parameter is a user specified integer error number that must be greater than or equal to 50000. The error number does not need to be recorded anywhere previously.
The THROW command does not support specifying a severity. It always generates an error with severity 16.
Catchable Error severities
0-11 not catchable
11-19 Catchable
20-25 terminate the connection
To raise an error with severity 19 and up you have to add the option WITH LOG, which logs the error in the SQL Server error log as well as the Windows application log. To use the WITH LOG option you must be a member of the sysadmin role or have the ALTER TRACE permission.
NOWAIT
The NOWAIT option causes SQL Server to send the message immediately to the client without waiting for its internal buffer to first fill up. Developers often use this option when running long scripts to report the progress of the code in intermediate milestones.
What is 5/2 ?
For example, the result of the expression 5 / 2 in T-SQL is the INT value 2 and not the NUMERIC value 2.5, because both operands are integers, and therefore the result is an integer. If you were dealing with two integer columns, like col1 / col2, and wanted the division to be NUMERIC, you would need to convert the columns explicitly, as in CAST(col1 AS NUMERIC(12, 2)) / CAST(col2 AS NUMERIC(12, 2)).
ISNULL
ISNULL ( check_expression , replacement_value )
COALESCE
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, ‘third_value’, ‘fourth_value’); returns the third value because the third value is the first value that is not null.
Multistatement Table-Valued Function - format
CREATE FUNCTION FunctionName (InputParamenters) RETURNS @TableName TABLE (Columns) AS BEGIN; Code to populate table variable RETURN; END;
CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but not yet committed.
READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions
SNAPSHOT
Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
SERIALIZABLE
Specifies the following:
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Used with tables that are not used frequently
XACT_STATE
Is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction
XACT_STATE = 1
The current request has an active user transaction.The request can perform any actions, including writing data and committing the transaction.
XACT_STATE = 0
There is no active user transaction for the current request.
XACT_STATE = -1
The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.
Difference between @@TRANCOUNT and XACT_STATE
Both can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.
What is a covering index?
A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. To be considered as a covering index, all columns referenced by the query must be contained in the index. So this means all columns in the SELECT, JOIN, WHERE, GROUP BY, HAVING etc etc.
WITH CHECK OPTION
The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts to rows for which the WHERE clause in the select_statement is not true.
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
WITH ENCRYPTION
Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure.
@@ERROR
returns 0 if the previous statement executed correctly
@@ROWCOUNT
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
