SQL Server Monday, 13 October 2014 SQL Management Studio – How to create read only users The following steps detail how to create a read-only user for an SQL database using Microsoft SQL Server Management Studio.... 1 SQL Management Studio – Change ‘Select Rows’ and ‘Edit Rows’ Amount When using SQL Management Studio you can right-click on a database table or view and select or edit, however by... SQL Query – Find numbers with more than two decimal places As a database administrator one simple health check you can perform on the information in the database is that dollars... 4 SQL – How to insert new line/line break in string If you’re building a string using an SQL statement, you can use CHAR() to insert the ASCII character for a... How to install SQL Management Studio 2008 R2 SQL Management Studio is the must use tool for SQL database administrators, however every time I’ve needed to install it... 6 SQL Query – Find invalid email addresses As a database administrator one of the important data health checks you can perform is checking that email addresses being... SQL – SELECT query where case insensitive and wildcard Below you’ll find two ways to search an SQL table column for a case insensitive string. Both examples use the... SQL Management Studio – How to generate change scripts SQL Management Studio allows you to open a table in design mode, make changes then generate a “change script”. The... SQL – Return TRUE/FALSE if query rows returned Here’s a little trick you can use to return TRUE/FALSE if a query has returned results. For example if a... SQL – How to count unique values in database The following shows how use a simple SQL statement to create a list of unique values and a count of... How to use a REGEX in an SQL SELECT query The following example shows how to use a regular expression (a regex) to search for information in an SQL database.... SQL – How to concatenate/combine column values into comma separated text string The following SQL statement shows how to combine a column into a single text string – separated by a comma.... SQL – How to decode HTML entities when using FOR XML PATH When building complicated SQL statements often I will make use of a SELECT – FOR XML PATH statement to concatenate... SQL Server – How to set a datetime field to automatically use current date/time Senario You have a ‘datetime’ field in an SQL server database table and need it to automatically use the current... SQL – How to convert datetime to formatted date string dd-mm-yyyy When you have a date or datetime column in an SQL database, it will output a value that looks like 2017-01-01... SQL – How to remove spaces before and after database values The following SQL can be used to remove leading and trailing spaces from a columns values. Ideally the system that... T-SQL – How to use where filter with column alias Problem If you try to use a column alias in a where statement, you recieve an error message that reads... SQL – How to get current financial year using SQL The following SQL shows how to get the current financial year using SQL. This assumes that the financial year for... SQL – How to format decimal to space delimited currency $ 1 234 567.89 The following SQL statement shows how to take a decimal value, e.g. 1234567.89 and convert it to a space delimited... T-SQL – How to output day name from date The following T-SQL statement shows how output the name of a day from a date. For example, 16 March 2019... T-SQL – How to reset auto increment to next available number The following T-SQL shows how to reset a tables identity auto increment back the last available number. This may be... SQL – Quick and easy way to performance test SQL statements There are many commercial tools to help you design and test SQL statements, as well as the execution tests in... SQL – How to select current year from lookup table The following SQL shows how to select a row from a table using the current year as the WHERE condition.... SQL Management Studio – unable to delete row ‘String or binary data would be truncated’ Problem When attempting to delete a database table row using SQL Management Studio you receive the following error message: No... MS SQL Server 2008 – How to update value using SELECT results The following T-SQL shows how to update a value using the results of a SELECT query. This applies to Microsoft... SQL -Find and replace text in string The following T-SQL for Microsoft SQL Server shows how to find and replace text in a string. In this example we’ll change... SQL – How to copy column data to another column The following SQL statement shows how to copy the data from one column to another. It will replace any existing... SQL – How to search with accented characters The following T-SQL for Microsoft SQL Server shows how to search for accented text without having to use the accented characters in... T-SQL – How to reset auto increment to 1 The following T-SQL shows how to reset a tables identity auto increment back to 1. This may be useful if... T-SQL – How to output ordinal number – 1ST 2ND 3RD 4TH The following T-SQL statement shows how output a number as an ordinal number (st, nd, rd, th). For example, 3... SQL – Divide by zero error Problem When running an SQL view using SQL Management Studio you experience the following message: MSG 8134, Level 16, State... SQL – How to select current financial year from lookup table The following SQL shows how to select a row from a table using the current financial year as the WHERE... T-SQL – How to output ordinal day from date – 1ST 2ND 3RD 4TH The following T-SQL statement shows how output the day from a date as an ordinal number (st, nd, rd, th).... SQL Management Studio – ‘Saving changes is not permitted’ message when changing table Problem When designing a table you’re unable to save the changes. When you try you receive the following message: Saving... 1 SQL – How to convert line separated text to comma separated The following T-SQL for Microsoft SQL Server shows how to convert line separated text to comma separated text. For example, if we... SQL Management Studio – How to change varchar(n) to varchar(MAX) The following SQL command can be used in SQL Management Studio to change a column from varchar(x) to varchar(MAX). ALTER... T-SQL – How to output day number from date The following T-SQL statement shows how output the day from a date. For example, 16 March 2019 would output 16.... T-SQL – How to output name of month The following T-SQL statement shows how output the name of the month from a date. For example, 16 March 2019...
SQL Management Studio – How to create read only users The following steps detail how to create a read-only user for an SQL database using Microsoft SQL Server Management Studio.... 1
SQL Management Studio – Change ‘Select Rows’ and ‘Edit Rows’ Amount When using SQL Management Studio you can right-click on a database table or view and select or edit, however by...
SQL Query – Find numbers with more than two decimal places As a database administrator one simple health check you can perform on the information in the database is that dollars... 4
SQL – How to insert new line/line break in string If you’re building a string using an SQL statement, you can use CHAR() to insert the ASCII character for a...
How to install SQL Management Studio 2008 R2 SQL Management Studio is the must use tool for SQL database administrators, however every time I’ve needed to install it... 6
SQL Query – Find invalid email addresses As a database administrator one of the important data health checks you can perform is checking that email addresses being...
SQL – SELECT query where case insensitive and wildcard Below you’ll find two ways to search an SQL table column for a case insensitive string. Both examples use the...
SQL Management Studio – How to generate change scripts SQL Management Studio allows you to open a table in design mode, make changes then generate a “change script”. The...
SQL – Return TRUE/FALSE if query rows returned Here’s a little trick you can use to return TRUE/FALSE if a query has returned results. For example if a...
SQL – How to count unique values in database The following shows how use a simple SQL statement to create a list of unique values and a count of...
How to use a REGEX in an SQL SELECT query The following example shows how to use a regular expression (a regex) to search for information in an SQL database....
SQL – How to concatenate/combine column values into comma separated text string The following SQL statement shows how to combine a column into a single text string – separated by a comma....
SQL – How to decode HTML entities when using FOR XML PATH When building complicated SQL statements often I will make use of a SELECT – FOR XML PATH statement to concatenate...
SQL Server – How to set a datetime field to automatically use current date/time Senario You have a ‘datetime’ field in an SQL server database table and need it to automatically use the current...
SQL – How to convert datetime to formatted date string dd-mm-yyyy When you have a date or datetime column in an SQL database, it will output a value that looks like 2017-01-01...
SQL – How to remove spaces before and after database values The following SQL can be used to remove leading and trailing spaces from a columns values. Ideally the system that...
T-SQL – How to use where filter with column alias Problem If you try to use a column alias in a where statement, you recieve an error message that reads...
SQL – How to get current financial year using SQL The following SQL shows how to get the current financial year using SQL. This assumes that the financial year for...
SQL – How to format decimal to space delimited currency $ 1 234 567.89 The following SQL statement shows how to take a decimal value, e.g. 1234567.89 and convert it to a space delimited...
T-SQL – How to output day name from date The following T-SQL statement shows how output the name of a day from a date. For example, 16 March 2019...
T-SQL – How to reset auto increment to next available number The following T-SQL shows how to reset a tables identity auto increment back the last available number. This may be...
SQL – Quick and easy way to performance test SQL statements There are many commercial tools to help you design and test SQL statements, as well as the execution tests in...
SQL – How to select current year from lookup table The following SQL shows how to select a row from a table using the current year as the WHERE condition....
SQL Management Studio – unable to delete row ‘String or binary data would be truncated’ Problem When attempting to delete a database table row using SQL Management Studio you receive the following error message: No...
MS SQL Server 2008 – How to update value using SELECT results The following T-SQL shows how to update a value using the results of a SELECT query. This applies to Microsoft...
SQL -Find and replace text in string The following T-SQL for Microsoft SQL Server shows how to find and replace text in a string. In this example we’ll change...
SQL – How to copy column data to another column The following SQL statement shows how to copy the data from one column to another. It will replace any existing...
SQL – How to search with accented characters The following T-SQL for Microsoft SQL Server shows how to search for accented text without having to use the accented characters in...
T-SQL – How to reset auto increment to 1 The following T-SQL shows how to reset a tables identity auto increment back to 1. This may be useful if...
T-SQL – How to output ordinal number – 1ST 2ND 3RD 4TH The following T-SQL statement shows how output a number as an ordinal number (st, nd, rd, th). For example, 3...
SQL – Divide by zero error Problem When running an SQL view using SQL Management Studio you experience the following message: MSG 8134, Level 16, State...
SQL – How to select current financial year from lookup table The following SQL shows how to select a row from a table using the current financial year as the WHERE...
T-SQL – How to output ordinal day from date – 1ST 2ND 3RD 4TH The following T-SQL statement shows how output the day from a date as an ordinal number (st, nd, rd, th)....
SQL Management Studio – ‘Saving changes is not permitted’ message when changing table Problem When designing a table you’re unable to save the changes. When you try you receive the following message: Saving... 1
SQL – How to convert line separated text to comma separated The following T-SQL for Microsoft SQL Server shows how to convert line separated text to comma separated text. For example, if we...
SQL Management Studio – How to change varchar(n) to varchar(MAX) The following SQL command can be used in SQL Management Studio to change a column from varchar(x) to varchar(MAX). ALTER...
T-SQL – How to output day number from date The following T-SQL statement shows how output the day from a date. For example, 16 March 2019 would output 16....
T-SQL – How to output name of month The following T-SQL statement shows how output the name of the month from a date. For example, 16 March 2019...