Section 1
Preview this deck
SCOPE_IDENTITY
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (115)
Section 1
(50 cards)
SCOPE_IDENTITY
will return the IDENTITY value inserted into an identity column within the scope of the stored procedure and will prevent the wrong value from being written to the
> (Greater Than)
Greater than
Between .. and returns
row version for a specified period
quarter
qq, q
Identity (1,1)
IDENTITY [ (seed , increment) ]
LEFT JOIN
This would return the last three sales regardless of the employee found in the Employee table. The three last sales would be calculated first and then matched to information from the Employee table.
EOMONTH ( start_date [, month_to_add ] )
--returns end of month date as data_type DATE
SELECT - FOR Clause
1.Allow updates while viewing query results in a browse mode cursor by specifying FOR BROWSE. 2.Format query results as XML by specifying FOR XML. 3.Format query results as JSON by specifying FOR JSON.
= (Equals)
Equal to
To be SARGable, a predicate must, on one side
have a column, not an expression on a column. So, <column> <operator> <expression>
week
wk, ww
month
mm, m
DATEFROMPARTS()
Year,Month,day
weekday
dw, w
day
dd, d
millisecond
ms
Contained in returns
only rows in a specified period
!< (Not Less Than)
Not less than (not ISO standard)
< (Less Than)
Less than
minute
mi, n
A function cannot be used to
insert a row into a table
from .... to
returns row versions for a specified version
THROW
re-raises the same error that caused control to be given to the CATCH block. You should not use the RAISERROR(1205, 'Deadlock', 16, 1); statement. RAISERROR cannot be used to raise system errors. System errors have error numbers less than 50000.
INNER JOIN
This would return the last three sales, regardless of the employee found in the Employee table. The three last sales would be calculated first and then matched to information from the Employee table.
!< or >=
values that are not less than the limit
nanosecond
ns
IDENT_CURRENT
returns the last value generated for an IDENTITY column for a specified table or view.
cross join
This would return the last three sales regardless of the employee found in the Employee table. These three rows would be then attached to every employee from the Employee table, which produces incorrect results.
as of
returns the state before forecasts were added
second
ss, s
<> (Not Equal To)
Not equal to
a view returns
a result set and cannot be used to insert a row into a table
@@IDENTITY
We should use which function to get the number of rows affected by the last statement; This always returns the last identity value inserted into an identity column anywhere in the system. This could therefore write to the wrong identity value to the SalesInvoiceLine table if an IDENTITY value has been created elsewhere in the system.
Select Statement
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ] SELECT select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set.
RAISERROR();
always requires parameters and cannot be used to raise system errors.
RAISERROR(1205, 'Deadlock', 16, 1)
RAISERROR(1205, 'Deadlock', 16, 1); statement. RAISERROR cannot be used to raise system errors. System errors have error numbers less than 50000.
Dateadd syntax
DATEADD (datepart , number , date )
!= (Not Equal To)
Not equal to (not ISO standard)
hour
hh
year
yy, yyyy
<= (Less Than or Equal To)
Less than or equal to
>= (Greater Than or Equal To)
Greater than or equal to
datetime data type
Although @dt is declared with the datetime data type, SQL Server will implicitly convert the date into a datetime2 data type. Datetime must be in increments of 0.000, 0.003, or 0.007 for the value to be correctly compared. Otherwise, it will be rounded. In this case, the millisecond value ends in 0, and so the correct results are returned.
dayofyear
dy, y
cross apply
This part of the query will calculate the top three latest sales for every employee. The data from the Employee table acts as a driver for the calculation that is performed in the CROSS APPLY statement.
!> (Not Greater Than)
Not greater than (not ISO standard)
microsecond
mcs
sequence syntax
CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ] [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ <constant> ] } | { NO CACHE } ] [ ; ]
EOMONTH
The function of _____ returns the serial number of the last day of the month before or after a specified number of months.
The other requirement for a predicate to be SARGable, for SQL Server at least, is that the column and expression are
of the same data type or, if the data types differ, such that the expression will be implicitly converted to the data type of the column.
Section 2
(50 cards)
Original_Login()
returns the underlying Windows identity regardless of the security context or impersonation level that is being used
Truncate table is the
fastest way of removing all from a table
Where CustomerID = SOME
SOME compares a value to a set of values in the subquery and returns TRUE when any one row matches.
Cross Apply
used to invoke a table-valued function and return only rows from the function that match to rows in the StateProvince table. States that do not have any cities with sales are not included.
CTE Scope
the scope of a single statement such as SELECT, INSERT, UPDATE, or DELETE and so could not be used several times within a batch.
Truncate table cannot be used with a
where clause
Set xact_abort on changes
statement-terminating errors to batch-terminating errors but has no impact here because divide by zero is already a statement-terminating error
Procedures
Accept input parameters and return multiple values in the form of output parameters to the calling program. Contain programming statements that perform operations in the database. These include calling other procedures. Return a status value to a calling program to indicate success or failure (and the reason for failure).
When you are using a table-valued functin, should you use Cross Join or Cross Apply
Cross apply
Table Variables Scope
The contents of a table variable will persist within a batch
Json_query
allows for lists
When you are in an international system, what should you use for characters?
nvarchar()
WITH Schemabinding is required for
indexed views. prevents underlying tables from being modified in any way that would affect the view defintion
RAW
generates a single <row> element per row in the rowset that is returned by the SELECT statement. You can generate XML hierarchy by writing nested FOR XML queries.
Provide Json path when
When you call OPENJSON to create a relational view of JSON data. For more info, see OPENJSON (Transact-SQL). When you call JSON_VALUE to extract a value from JSON text. For more info, see JSON_VALUE (Transact-SQL). When you call JSON_QUERY to extract a JSON object or an array. For more info, see JSON_QUERY (Transact-SQL). When you call JSON_MODIFY to update the value of a property in a JSON string. For more info, see JSON_MODIFY (Transact-SQL).
When to use a unique constraint instead of a primary key constraint
1. you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key. 2. as with any value participating in a UNIQUE constraint, only one null value is allowed per column.
table-valued functions
1. pass a parameter to a table-valued function 2. use a table-valued function in a view 3. cannot return multiple result sets from a table valued function 4. can use results from a table-valued function in a SELECT statement
SQL-92 syntax
1. produces code that is easier to understand (separates join conditions from where clause) 2. current bes tpractice 3. does not produce more efficient query plans 4. requires more typing 5. less error prone
DECLARE @json NVARCHAR(MAX) SET @json=N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' SELECT * FROM OPENJSON(@json,N'lax $.info')
Results Key Value Type type 1 0 address { "town":"Bristol", "county":"Avon", "country":"England" } 5 tags [ "Sport", "Water polo" ] 4
table-valued function
Selects data and returns the results as a table. Cannot modify any permanent tables.
int
approximately 2 billion customerIDs and is stored in 4 bytes
CTE can only be used
within the current statement and so cannot be used in different places
Predicates with LIKE are only SARGable if
the wildcard is not at the start of the string.
user-defined inline function
returns a table data type and will give the best performance
= operator
Requires one value
stored procedures
1. Reduced server/client network traffic 2. stronger security (do not have direct permissions on those underlying objects; Execute as) 3. can be encrypted (not the underlining data) 4. Easier maintenance 5. improved performance
TRY_Parse('2012-31-07 11:33:00' as date USING 'en-us') what would the expression evaluate to?
The expression evaluates to NULL as a date. The date expression provided is invalid for en-us culture (USA). The result of TRY_PARSE for invalid dates is a NULL. The data type will match the requested type of date.
geography type holds
latitude, longitude
Identity function takes in
property of the column, takes seed, and increment as its arguments
ISNULL function
A function that tests a field value or an expression for a null value; if the field value or expression is null, the result is true; otherwise, the result is false; only limited to two parameters
tinyint
0 to 255
cannot use truncate table if a
column in the table is referenced by a foreign key constraint and so you should use DELETE to remove records
derived table can only be used
within the current statement and so cannot be used in different places
Path mode
1. In lax mode, the function returns empty values if the path expression contains an error. 2. In strict mode, the function raises an error if the path expression contains an error.
stored procedure scope
cannot be invoked from within a SELECT statement.
In a FOR XML clause, you specify one of these modes:
RAW AUTO EXPLICIT PATH
The property path
a set of path steps. Path steps can contain the following elements and operators. Key names. For example, $.name and $."first name". If the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes. Array elements. For example, $.product[3]. Arrays are zero-based. The dot operator (.) indicates a member of an object. For example, in $.people[1].surname, surname is a child of people.
A table-valued user-defined function can be used
anywhere a table or view could be used
Cast(createddate as date) is sargable: True or False
True
Concat Syntax
CONCAT ( string_value1, string_value2 [, string_valueN ] )
Insert Sales.ProductPrices (productID, UnitPrice) Values (1,25.30), (2,33.25) Does this solution meet the goal? Why?
* ProductPriceID is an IDENTITY column and will be automatically calculated. * ProductID has a foreign key reference, the value is supplied, and the ProductID values in the Sales.Product table already exist. * EffectiveDateTime is defined as NOT NULL, but the default value derived from SYSDATETIME() will be used. * UnitPrice is defined as NOT NULL but is supplied. * PricingComment is defined as NOT NULL and will be left NULL because it was not supplied. The query is a multi-row INSERT. Two rows will be inserted.
bigint
takes 8 bytes to store
A divide by zero error and set xact_abort on
How to check if a function is deterministic
Select OBJECT_PROPERTY (OBJECT_ID('dbo.FullName'),'IsDeterministic')
Insert Sales.ProductPrices (ProductPriceID, EffectiveDateTime, UnitPrice, PricingComment) Values (3,'20170901',4.54,'Old pricing applied'); Does this solution meet the goal? Why?
The insert will not succeed because an attempt is being made to insert a value for the identity column ProductPriceID when Identity_insert is not enabled
Derived Table Scope
You should not use a derived table. The scope of a derived table is local to the statement and so could not be used several times in the batch.
Set identity insert has been turned on requires what also?
a column list must be supplied in the INSERT statement
IIF function
evaluates a condition and returns one value if the condition is true and another value if the condition is false
The AUTO mode
generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. You have minimal control over the shape of the XML generated. The nested FOR XML queries can be written to generate XML hierarchy beyond the XML shape that is generated by AUTO mode heuristics.
IFF( , ' ', ' ')
Returns text
Section 3
(15 cards)