SQL Server FAST TRACK for Novices - Tables

Explore table design and management in SQL Server Management Studio, covering column naming conventions, data types, null versus not null, and primary keys with auto-increment.

Explore table design and naming conventions for lookup tables, using a consistent prefix, auto increment key, and standard text fields such as display, description, visible, order, created, and modified.

Create a look up table in SQL Server Management Studio, define an auto-increment primary key, add name, description, and created date fields using getdate, and set visible and order.

Alter a table's schema in SSMS by adding and placing a comments field. Refresh to see changes and understand that existing data can influence adding or removing fields.

Learn to alter table schemas from the command line with ALTER TABLE, adding a comments column and, if needed, dropping it with the COLUMN keyword.

Master inserting records into a SQL Server table using direct inserts, table column defaults, and reusable scripts; build a stored procedure for inserts and practice with creation and sample scripts.

Learn how to insert records into a table using SSMS, populate the display field and rely on defaulted and auto number fields, and observe that missing descriptions remain empty.

Insert records into tables in SSMS while honoring not null fields and default values. Explore how not null constraints and schema changes affect inserts and require values to avoid errors.

Learn to insert records into a table when a not null field lacks value by using a default value like 'none', and recognize the difference between blank strings and nulls.

Learn to insert records with TSQL from the command line, inserting one and multiple records into a table (customer types), with fields and values, and understand readable versus uglier scripts.

insert records into a sql server table using tsql, writing only the fields you specify while defaults fill the rest, such as created and order values.

Demonstrates inserting records with a simple stored procedure: delete all rows in the customer types table, pass four parameters (display, description, visible, order), and insert them into the table.

Explore inserting records via a stored procedure for the customer types table, executing calls with default values for parameters and different patterns, including optional parameters and omitted values.

Update records in a table using a SQL update statement and update stored procedures, with careful data types, where clauses, and proper quoting, plus creating and running update scripts.

Update records directly in SQL Server Management Studio by editing all rows in the customer type table, changing numeric and boolean fields, and guarding against invalid values.

Update a table using t-sql from the command line to set a modified date for a specific record with a where clause and undo changes by resetting the value.

Discover updating tables with a saved T-SQL script run from the command line in SQL Server, updating all records or with a where clause, and reusing scripts for repeat tasks.

update records in a table using a stored procedure by passing a key and four field values, while automatically updating the modified date to the current date and time.

Learn to read and delete records in SQL Server tables using delete statements and where clauses, and safeguard data with transactions, backups, and reusable scripts and stored procedures.

Delete records directly from a table using the SSMS GUI by selecting a row and pressing delete, with a permanent confirmation.

Learn to delete records where ct_visible = 0 from a table via t-sql on the command line, using a where clause and a preceding select to verify results.

Delete records from a table using a TSQL script with a where clause, removing rows where the visible field is zero; preview with a select first.

Execute a stored procedure to delete a single record from a table by passing the key value, then verify the removal with a before-and-after select.

Explore advanced inserting and deleting in SQL Server, including identity value handling, identity insert, select into for loading data, and reseeding with DBCC CHECKIDENT, with cautions about existing data.

Learn to add and delete records in a table via sql server management studio, edit and overwrite existing records, save changes, and refresh to track updates.

Master inserting and updating records in the units table, using insert into with values or select, deleting with where, and updating with set clauses.

Use identity insert on and off to preserve auto number keys during inserts and deletes, preventing relationship corruption and avoiding disabling auto increment.

Use select into to copy a table's data into a new table, a quick, down and dirty backup method, but note it doesn't preserve primary keys or auto-number behavior.

Learn how to reseed a table with DBCC CHECKIDENT, delete and rebuild data, and reset identity values. The lecture warns against reseeding in live databases due to triggers and relationships.

Learn how to script table creation and data insertion to streamline development and production workflows, including generating recreate-table scripts and a dynamic procedure that creates tables with prefixes and defaults.

Script a table in SSMS to recreate its structure with all fields, identity, and primary key constraints, using a create script that handles constraints but not data.

Create and run data insertion scripts to populate a rebuilt table, recognize the dangers of drop-and-create, and apply identity insert for specific key values.

Use scripting as a template to quickly create a new table by duplicating an existing table and renaming identifiers with find-and-replace, enabling on-the-fly table creation.

Create tables efficiently with a base create table stored procedure. Pass the table name and a field-prefix to auto generate table scripts, including defaults and constraints.