SQL / T-SQL

Inserting data The INSERT VALUES statement The INSERT SELECT statement The SELECT INTO statement The BULK INSERT statement The identity property and the sequence object Identity Sequence Deleting data The DELETE statement The TRUNCATE statement DELETE based on a join Updating Data The UPDATE statement UPDATE based on a join Assignment UPDATE Merging data Modifying…

HASHBYTE and CHECKSUM CHECKSUM and HASHBYTES are both commonly used to determine whether field values in two records are the same. HASHBYTES and CHECKSUM are typically used in data warehouse loading scenarios, especially when we need to compare many columns from the source table to many columns in the destination table. This allows us to…

Error handling The main tool used for error handling in SQL Server is the TRY…CATCH construct. SQL Server also provides functions we can use to get information about the error. We place the usual T-SQL code in a TRY block (between the BEGIN TRY and END TRY keywords) and place all the error-handling code in…

Theoretical background A relational database management system (RDBMS) is based on the relational model, which in turn is based on two branches of mathematics: Set Theory and Predicate Logic. SQL Data in a RDBMS can be managed and queried using the SQL language, and Microsoft’s dialect and extension of SQL is T-SQL. SQL is a…

Theoretical background A relational database management system (RDBMS) is based on the relational model, which in turn is based on two branches of mathematics: Set Theory and Predicate Logic. SQL Data in a RDBMS can be managed and queried using the SQL language, and Microsoft’s dialect and extension of SQL is T-SQL. SQL is a…

Cursors Creating a Cursor Cursor Sensitivity More example uses-cases for Cursors Scope of Cursor Updating columns using a cursor Restricting which columns can be updated using a cursor How optimistic locking works Scrolling around a cursor Why Database Cursors are used Performance considerations and best practices (INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM SQL COOKBOOK,…

Dynamic SQL The EXEC command The sp_executesql stored procedure Using Pivot with Dynamic SQL SQL Injections (INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM SQL COOKBOOK, Udemy) (Include notes from here: http://www.sommarskog.se/dynamic_sql.html#good_practices  and http://www.benkotips.com/pages/DynamicSQL.aspx) Dynamic SQL Dynamic SQL is a batch of T-SQL code that is generated and executed on-the-fly at run-time. It is the capability…

Theoretical background A relational database management system (RDBMS) is based on the relational model, which in turn is based on two branches of mathematics: Set Theory and Predicate Logic. SQL Data in a RDBMS can be managed and queried using the SQL language, and Microsoft’s dialect and extension of SQL is T-SQL. SQL is a…

Triggers DML Triggers DDL Triggers (INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM Advanced SQL book, PRAGMATIC WORKS INTRO AND ADVANCED, DATABASESTAR ACADEMY, SQL COOKBOOK, Udemy, WENZEL) (COMPARE WITH ADVANCED SQL BOOK ONE LAST TIME) Triggers Triggers are stored procedures that cannot be executed explicitly. Instead it’s attached to an event, meaning whenever an event occurs…

(INCLUDE SYNTAX WITH EVERYTHING) (INCLUDE NOTES FROM Advanced SQL book, PRAGMATIC WORKS INTRO AND ADVANCED, DATABASESTAR ACADEMY, SQL COOKBOOK, Udemy, WENZEL) (COMPARE WITH ADVANCED SQL BOOK ONE LAST TIME) Stored procedures A stored procedure is code that can have input and output parameters, and they return the result sets of queries. Stored procedures are allowed…