A4-Modify data Flashcards
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.
