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.
Category: SQL Server
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.
“Cannot drop the trigger” Error when Trying to Drop a Logon Trigger? Try This.
Are you’re trying to drop a logon trigger in SQL Server, but you’re getting an error like the following?
“Cannot drop the trigger ‘trigger_name’, because it does not exist or you do not have permission.”
It could be because you’re missing the ON ALL SERVER argument.
SQL Server IF vs IIF(): What’s the Difference?
Students learning a new programming language will often start their first lesson with an IF statement, where their statement will return a value only if the expression is true.
They might then progress to an IF... ELSE statement, where they can determine another value to return if the expression is false. So therefore, return one value if the expression is true, and another value if it’s false.
SQL Server certainly includes the IF... ELSE statement in its T-SQL toolbox.
SQL Server also includes the IIF() function, which does a similar thing, but with a more concise syntax.
But there are some subtle differences.
Continue readingHow IIF() Works in SQL Server
In SQL Server, the IIF() function (not to be confused with the IF statement) is a conditional function that returns the second or third argument based on the evaluation of the first argument.
It’s a shorthand way for writing a CASE expression. It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END assuming IIF(X, Y, Z).
IIF() is an abbreviation for Immediate IF.
How the IF Statement Works in SQL Server
Most (probably all) programming languages include some form of the IF statement that allows programmers to write conditional code. That is, code that will execute only if a certain condition is true.
It’s a very simple concept. Basically it goes like this:
“If this, do that.”
Most languages simply call it IF, but some have their own twist on the name (for example, in ColdFusion/CFML, it’s called CFIF).
In any case, they essentially do the same thing.
In SQL Server (or more precisely, its programming language T-SQL) it’s called IF.
SQL Server’s Equivalent to Sleep(): The WAITFOR Statement
In SQL Server, you can use the WAITFOR statement to delay the execution of a batch, stored procedure, or transaction.
It works similar to MySQL‘s sleep() function.
Actually, it’s probably more like a combination of Postgres‘s three “sleep” functions: pg_sleep(), pg_sleep_for(), and pg_sleep_until().
I say this, because SQL Server’s WAITFOR statement gives you the option of specifying either a time delay, or an actual fixed time before execution continues.
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.