Add IDENTITY or Unique number to a table – T/SQL

I want to update my table records. However, my existing table does not have any unique columns. So, I need to append Identity column and update all the records based on that Identity column. For example, If my temp table has 1000 records without any unique column values. I need to number all these 1000 records and update the values. There is no need to do an UPDATE. The identity column is going to be populated when it is created….

Read More

How to Change Schema of Mssql Tables, Stored Procedures, and Views All at the Same Time

Sometimes changing database users changes who the database owner is for a table from dbo.tablename to badschema.tablename. This will also affect any stored procedures created under the tables with the incorect schema.  If you need to change the schema for these tables back to the default dbo schema follow the steps below. Open Microsoft SQL Server Management Studio and log in. Click the New Query button. Paste the following script into the New Query box changing oldschema to the name of the current schema:…

Read More

Find Duplicate Values with SQL Script

Let’s assume that data has been entered in a table and now you want to find if a table column have duplicated values. Here is an SQL script to join data from one table for comparison purpose. The table must have a primary key column (unique column) and the second column for comparison. Here is a sample script: Details: – DWDOCID is the primary key column – AIXFlag, is just a fixed value (optional) – SAMPLEEN is the table…

Read More

FORMAT Function for Transact-SQL – T-SQL

Returns a value formatted with the specified format and optional culture in SQL Server 2016. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. FORMAT ( value, format ) Return Types:  nvarchar or null The length of the return value is determined by the format. Remarks:   FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not…

Read More

T/SQL Splint String/Text

One common task performed in database applications is given a full name, how can this be split into the first name and last name.  In this article, three different methods are discussed to perform this task using three different functions in SQL Server.  It is assumed in this article that the full name is just made up of one first name and one last name. Using SUBSTRING Function The first method is the use of the SUBSTRING string function,…

Read More

T/SQL Generate Random Numbers

SQL Server has a built-in function that generates a random number, the RAND() mathematical function.  The RAND math function returns a random float value from 0 through 1.  It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value. To use it, you can simply do a simple SELECT, as follows: SELECT RAND() AS The result generated by this SELECT statement is as follows (note that…

Read More

T-SQL Comma Delimited Output

One of the common tasks performed when retrieving data from a SQL Server database is returning the result as a comma-delimited output instead of a result set.  This task can be performed by using a CURSOR selecting the column to be concatenated together.  Each row returned by the CURSOR is then concatenated together into a variable separating each one by a comma. Here’s how the script will look like using the table in the Northwind database. A sample…

Read More

Get Numbers from Alpha Numeric String – Get Numeric Numbers Only

So, someone had stored thousands of SSN numbers in a table but now they have an issue because some of them are incomplete or are not correctly formatted. Thank you to blog.sqlauthority.com and little creativity I have come up with a SQL script that fixes this issue. Here is the T/SQL script:

Read More