Sometimes the Cache of SSMS gets “corrupted” or fails to update, this can lead to Red squiggles under column namnes, table names, basiacally anything (Also SSMS will claim the name does not exist with for instance the error message invalid column namn).
When this error lies in SSMS, then refreshing the cache is the solution, and this is done with “Ctrl-Shitf-R“
Category Archives: SQLServer
MS SQL Truncating seconds (and milliseconds) from a datetime
By converting a DateTime to a char(16) the seconds and milliseconds are truncated from the DateTime.
DECLARE @d datetime SELECT @d = GETDATE() SELECT @d, CONVERT(CHAR(16), @d, 121) --2012-12-04 11:50:42.160 2012-12-04 11:50 |
MS SQL Update WHERE value is NULL
This example replaces null with an empty string in SomeTable.SomeColum
UPDATE SomeTable SET SomeColumn = '' WHERE SomeColumn IS NULL |
The important thing to note here is that the check to find the value that is NULL is based on “IS NULL” (it is not possible to use the equals sign to check for NULL)
MS SQL SELECT WHERE value is NULL
Here is how to do a select where a column have NULL as the stored value.
SELECT * FROM SomeTable WHERE SomeColumn IS NULL |
Equals sign does not work for null, trying to use the equals operator generates the result Unknown (it is not true nor false)
IF i = NULL THEN SELECT 'Result is True' ELSEIF NOT(i = NULL) THEN SELECT 'Result is False' ELSE SELECT 'Result is Unknown'; --This will end with Result is Unknown |
This is based on the fact that null was introduced to represent “missing information and inapplicable information” in the database model and based on that statement using equals null was not implemented.
SQLServer Adding a column to a table
Adding a column to existing table is not that hard. One thing to keep in mind, if the column is not nullable then a default value is mandatory.
ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE} |
Looking at the database structure (in MSSQL)
This query will show how the current database looks (what columns exists in what tables etc), the select might be a * instead but this is the info I find interesting.
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION