Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL 761 > Chapter 3. Program databases > Flashcards

Chapter 3. Program databases Flashcards

1
Q

View

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

SCHEMABINDING

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Indexed Views

A

You need to first add a clustered index for you to add a non-clustered index

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

User-defined functions

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

User-defined functions - limitations and restrictions

A 
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Scalar-User defined functions

A

accepts parameters and returns a single value.

It must invoke a RETURN clause to return the value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Types of User defined functions

A

Scalar UDF
Inline table valued functions
Multistatement table valued functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

UDF requirements - deterministic vs non-deterministic

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Inline table valued user defined function

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Multistatement table-valued user-defined functions

A

A multi-statement table-valued function or MSTVF is a table-valued function that returns the result of multiple statements.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Stored Procedures

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Stored procedures - pros

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How to make an input parameter optional?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

XACT_ABORT option

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

NOCOUNT option

A

Indicates how many rows were affected - they degrade query performance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

EXEC procedure - format

A

EXEC dbo.GetOrders @orderid = 42;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

dynamic SQL

A

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);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

create User

A

CREATE LOGIN login1 WITH PASSWORD = ‘J345#$)thb’;

GO

CREATE USER user1 FOR LOGIN login1;

GO

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Grant permission to a user

A

GRANT EXEC ON dbo.GetOrders TO user1;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

EXECUTE AS

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

OUTPUT or OUT

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Cursors

A

Cursors allow you to iterate through rows of some query result one at a time

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

@@TRANCOUNT

A

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

24
Q

Type of statements that are transactional

A 
DML statements (INSERT UPDATE, DELETE, TRUNCATE, MERGE)
GRANT, REVOKE and others
25
Q

SET IMPLICIT_TRANSACTIONS OFF;

A

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.

26
Q

ERROR_NUMBER()

A

provides the error number.

27
Q

ERROR_MESSAGE()

A

ERROR_MESSAGE() provides the error message.

28
Q

ERROR_SEVERITY()

A

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.

29
Q

ERROR_STATE()

A

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.

30
Q

ERROR_LINE()

A

ERROR_LINE() provides the line number where the error happened.

31
Q

ERROR_PROCEDURE()

A

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.

32
Q

IMPORTANT for Error functions

A
  1. If you invoke these functions not within a CATCH block, they all return NULLs.
  2. 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.
  3. Some failures generate a chain of errors. These functions only return information about the last error in the chain.
33
Q

RAISERROR - format

A

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 );

34
Q

THROW

A

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

35
Q

THROW with parameters

A

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.

36
Q

Catchable Error severities

A

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.

37
Q

NOWAIT

A

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.

38
Q

What is 5/2 ?

A

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)).

39
Q

ISNULL

A

ISNULL ( check_expression , replacement_value )

40
Q

COALESCE

A

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.

41
Q

Multistatement Table-Valued Function - format

A 
CREATE FUNCTION FunctionName (InputParamenters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN;
 Code to populate table variable
 RETURN;
END;
42
Q

CHECK Constraint

A

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)
);
43
Q

READ UNCOMMITTED

A

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

44
Q

READ COMMITTED

A

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.

45
Q

REPEATABLE READ

A

Specifies that statements cannot read data that has been modified but not yet committed by other transactions

46
Q

SNAPSHOT

A

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.

47
Q

SERIALIZABLE

A

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

48
Q

XACT_STATE

A

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

49
Q

XACT_STATE = 1

A

The current request has an active user transaction.The request can perform any actions, including writing data and committing the transaction.

50
Q

XACT_STATE = 0

A

There is no active user transaction for the current request.

51
Q

XACT_STATE = -1

A

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.

52
Q

Difference between @@TRANCOUNT and XACT_STATE

A

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.

53
Q

What is a covering index?

A

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.

54
Q

WITH CHECK OPTION

A

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;

55
Q

WITH ENCRYPTION

A

Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure.

56
Q

@@ERROR

A

returns 0 if the previous statement executed correctly

57
Q

@@ROWCOUNT

A

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

SQL 761 (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions