I thought it would be interesting to run a few quick queries to see how various formatting strings affect the formatting of date/time values.
mssql
Create a “Last Modified” Column in SQL Server
Some database tables include a “last modified” column, which stores the date and time that the row was last updated. Each time the row is updated, the date is updated to reflect the date and time of that update.
In SQL Server, you can use a trigger to perform this update.
A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.
You can use the CREATE TRIGGER statement to create a trigger when using T-SQL. This statement can be used to create a DML, DDL, or logon trigger.
How to Use GOTO in SQL Server
In SQL Server, you can use GOTO to alter the flow of execution. You can use it to “jump” to another part in the T-SQL code.
The way it works is, you create a label, then you can use GOTO to jump to that label. Any code between GOTO and the label are skipped, and processing continues at the label.
GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. They can also be nested.
Difference Between sys.sql_modules, sys.system_sql_modules, & sys.all_sql_modules in SQL Server
In SQL Server the sys.sql_modules, sys.system_sql_modules, and sys.all_sql_modules system catalog views return metadata about SQL language-defined modules in SQL Server.
However, there is a difference between them.
Difference Between Local and Global Temporary Tables in SQL Server
When you create a temporary table in SQL Server, you have the option of making it a local or global temporary table.
Here’s a quick outline of the main differences between local temporary tables and global temporary tables.
5 Ways to List Temporary Tables using T-SQL
In SQL Server, you can use any of the following five ways to return a list of temporary tables using Transact-SQL.
These return both local and global temporary tables.
Create a Temporary Table Based on Another Table in SQL Server
In SQL Server you can create a temporary table based on another table by using the SELECT... INTO syntax.
You can create the table with or without data. In other words, you can copy data from the original table if you wish, or you can create the table without any data.
Difference Between sys.parameters, sys.system_parameters, & sys.all_parameters in SQL Server
If you’re looking for a catalog view to return parameter information in SQL Server, you have a choice. In particular, you can get parameter information from sys.parameters, sys.system_parameters, and sys.all_parameters.
However, you will probably only want to use one of these views, as there are differences between them.
Return All Disabled Constraints in SQL Server (T-SQL Example)
Here’s some T-SQL code you can use to get a list of all CHECK and foreign key constraints in a SQL Server database.
How to make RAND() Deterministic in SQL Server
The RAND() function in SQL Server returns a pseudo-random float value from 0 through 1, exclusive.
This function can be deterministic or nondeterministic, depending on how it’s invoked.