What staments are required when modifying an identity column?
SET INDENTITY INSERT ON schema.tablename
INSERT INTO schema.tablename (columnname,..)
VALUES (Val1,…)
SET INDENTITY INSERT OFF schema.tablename
Mention one INSERT Restrictions.
You cannot have SET statement immediately after an INSERT
What is SET IDENTITY_INTERT and what are some of it’s restrictions?
Def: Allows explicit values to be inserted into the identity column of a table
Restrictions:
When IDENTITY_INSERT is ON, you must provide the complete list of columns, including the identity column;
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
What are Constraints?
- Constraints let you define the way the Database Engine automatically enforces the integrity of a database.
- Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.
- Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer also uses constraint definitions to build high-performance query execution plans.
What are the classes of constraints?
- NOT NULL specifies that the column does not accept NULL values;
- CHECK constraints enforce domain integrity by limiting the values that can be put in a column;
- A CHECK constraint specifies a Boolean (evaluates to TRUE, FALSE, or unknown) search condition that is applied to all values that are entered for the column
- UNIQUE constraints enforce the uniqueness of the values in a set of columns;
- PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table;
- FOREIGN KEY constraints identify and enforce the relationships between tables.
DBCC CHECKIDENT (def, syntax, remarks)
def: Checks the current identity value for the specified table in SQL Server 2019 and, if it’s needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.
syntax:
- DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value] } } ] )
remarks:
- The specific corrections made to the current identity value depend on the parameter specifications.
- With NORSEED, it checks the current identity value for a given table and if necessary changes the identity value.
- With RESEED, it checks the current identity value for the table and changes that identity value.
CREATE SEQUENCE def, syntax
def:
Creates a sequence object and specifies its properties. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created.
- The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.
- Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value.
- The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.
syntax:
CREATE SEQUENCE [schema_name .] sequence_name
[AS [ built_in_integer_type | user-defined_integer_type] ]
[START WITH]
[INCREMENT BY]
[{ MINVALUE [] } | { NO MINVALUE } ]
[{ MAXVALUE [] } | { NO MAXVALUE } ]
[CYCLE | { NO CYCLE }]
[{ CACHE [] } | { NO CACHE } ] [;]
CREATE SEQUENCE remarks
- Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back. Duplicate validation only occurs once a record is fully populated. This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate. If this occurs and other autonumber values have been applied to subsequent records, this can result in a gap between autonumber values and is expected behavior.
- It is required to specify a table name for the sequence object.
- By default sequence increments by 1 because the default value for INCREMENT BY is 1.
DIFFERENCE-def, syntax, remarks
def: this function returns an integer value measuring the difference between soundex() values of two different character expressions.
syntax: DIFFERENCE ( character_expression , character_expression )
Remarks:
- DIFFERENCE compares two different SOUNDEX values, and returns an integer value.
- Built in function not an operator and cannot be used to compare two queries.
TRUNCATE TABLE def, syntax, remarks
def: Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
syntax:
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
[WITH ( PARTITIONS ( { | } [ , …n] ) ) ]
[;]
Remarks:
Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:
- Less transaction log space is used.
- Fewer locks are typically used.
- Without exception, zero pages are left in the table
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.
Cannot be used with WHERE clause
DELETE def, syntax, best practices
def: Removes one or more rows from a table or view in SQL Server.
syntax: DELETE
Best Practices:
To delete all the rows in a table, use TRUNCATE TABLE. TRUNCATE TABLE is faster than DELETE and uses fewer system and transaction log resources. TRUNCATE TABLE has restrictions, for example, the table cannot participate in replication. For more information, see TRUNCATE TABLE (Transact-SQL)
Use the @@ROWCOUNT function to return the number of deleted rows to the client application. For more information, see @@ROWCOUNT (Transact-SQL).
UPDATE def
def: Changes existing data in a table or view in SQL Server 2019
some remarks:
- You cannot UPDATE an statement with the NOT MATCHED keywords
- UPDATE statements that include OUTPUT need to be written in the following order: UPDATE, SET, OUTPUT, WHERE.
ALTER TABLE def
Modifies a table definition by altering, adding, or dropping columns and constraints. ALTER TABLE also reassigns and rebuilds partitions, or disables and enables constraints and triggers.
ADD-usage, limitations, best practice, restrictions, syntax example
Used with the ALTER TABLE statement to add columns to a table automatically it adds those columns to the end of the table. If you want the columns in a specific order in the table, use SQL Server Management Studio. However, note that this is not a database design best practice.
Best practice- is to specify the order in which the columns are returned at the application and query level. You should not rely on the use of SELECT * to return all columns in an expected order based on the order in which they are defined in the table.
Restriction: Always specify the columns by name in your queries and applications in the order in which you would like them to appear.
Syntax example:
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;
STUFF def, syntax, remarks
def: The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
syntax: STUFF ( character_expression , start , length , replaceWith_expression )
remarks:
If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.
An error is raised if the resulting value is larger than the maximum supported by the return type.
- Creates a list of interests that have unwanted leading spaces. LTRIM correctly strips out the spaces from the string.
STRING_SPLIT def, syntax, remarks
def: A table-valued function that splits a string into rows of substrings, based on a specified separator character.
syntax: STRING_SPLIT ( string , separator )
remarks:
STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function:
STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT outputs a single-column table whose rows contain the substrings. The name of the output column is value.
EX:SELECT value FROM STRING_SPLIT(‘Lorem ipsum dolor sit amet.’, ‘ ‘)
OUTPUT def, syntax
def:Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Syntax:
<OUTPUT_CLAUSE> ::=
{ [ OUTPUT INTO { @table_variable | output_table }
[( column_list )] ] [OUTPUT] }
::= { | scalar_expression } [[AS] column_alias_identifier ] [,…n]
::= { DELETED | INSERTED | from_table_name } . { * | column_name } | $action
Output Remarks
Remarks:
The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.
When a computed column is included in the , the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.
There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.
If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.
Where is OUTPUT clause not supported?
- DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
- INSERT statements that contain an EXECUTE statement.
- Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
- The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
- A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
What references can’t OUTPUT clause contain in order to prevent nondeterministic behaviour?
- A subquery;
- A user-defined function that performs user or system data access, or is assumed to perform such access;
- A computed column that contains a user-defined function that performs user or system data access in its definition.
NEWID def, syntax, remarks
def: Creates a unique value of type uniqueidentifier.
syntax: NEWID ( ).
reamrks: NEWID() is compliant with RFC4122.
NEWSEQUENTIALID def, syntax, remarks
def:Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
Important: If privacy is a concern, do not use this function. It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID.
syntax: NEWSEQUENTIALID ( )
remarks: NEWSEQUENTIALID() can only be used with DEFAULT constraints on table columns of type uniqueidentifier
-
A1-Create Transact-SQL Select queries17
-
A2-Query multiple tables by using joins7
-
A3-Implement functions and aggregate data35
-
A4-Modify data22
-
B1-Query data by using the subqueries and APPLY9
-
B2-Query data by using table expressions16
-
B3-Group and pivot data by using queries13
-
B4-Query temporal data non-temporal-relational data34
-
C1-Create database programmability objects by using Transact-SQL54
-
C2-Implement error handling and transactions21
-
C3-Implement data types and NULLs13
-
General12
