Category: Database

String Literals as Column Aliases are Deprecated

Something that’s still found in SQL scripts in enterprise environment, even with newly written scripts is the use of string literals as column aliases.

SELECT record_id AS ID, account_number AS 'Account Number'
FROM RequestTable

Although this feature has been deprecated since 2012, old habits die hard.

So what’s the right way to write it?

You can either use alias without a whitespace or use a double quote character. Either of these examples below is acceptable.

SELECT record_id AS ID, account_number AS Account_Number
FROM RequestTable

SELECT record_id AS ID, account_number AS "Account Number"
FROM RequestTable

You can read more about this deprecated feature from the links below.

How to reset SA password on Microsoft SQL Server

If you happen to forget your sa password, you can still recover it as long as you have access to the server. Here are the steps to do it.

Steps

Launch Sql Configuration Manager under Configuration Tools folder.
Look for your SQL Server instance (the default is MSSQLSERVER) and stop the service. You can click the stop button while having the SQL Server (MSSQLSERVER) row highlighted or you can right-click on it and select Stop.
Launch the Command Prompt.
Next, we want to run the SQL Server in a single-user mode by adding “/m” parameter with the client application name:
net start MSSQLSERVER /m"SQLCMD"
Then we need to connect to the database on the machine using a trusted connection:
sqlcmd -E -S localhost
If you’re connecting to a database on a local machine, you can substitute “localhost” with a “.” (dot), which makes it look like so:
sqlcmd -E -S . or sqlcmd -E -S.
The two are identical except the former is easier to read. Another note is if you’re using SQL Server Express, you need to add “\SQLEXPRESS” after the period. You can see the difference on the example below.
After starting the sqlcmd, type the following SQL statement after the prompt. There is a difference in role assignment between SQL Server 2008 and SQL Server 2012:
For SQL Server 2008 or Older
```
                CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                GO
                sp_addsrvrolemember 'tempUser', 'sysadmin'
                GO
                
```
For SQL Server 2012 or Later
```
                CREATE LOGIN tempUser WITH PASSWORD = 'N3wPa$$1'
                GO
                ALTER SERVER ROLE sysadmin ADD MEMBER tempUser
                GO
                
```
For SQL Server 2012 or newer, use ALTER SERVER ROLE should be used instead of sp_addsrvrolemember as this system stored procedure will be removed in a future version of Microsoft SQL Server.
You can type “exit” to quit SQLCMD.
Restart the Sql Server service to get out of the single-user mode:
net stop MSSQLSERVER followed by net start MSSQLSERVER
Launch SQL Server Management Studio and connect to the local database using the new login you just created.
Expand on Security, then expand on Logins.
Right-click on user sa and select Properties. Enter the new password and click OK. And you’re done.

Now you can login to the database using the sa login and the new password you set. For security purpose, make sure you delete the tempUser afterwards.

Ways to Upsert a Record in SQL Server

To continue the previous post, this article demonstrates ways to do Upsert (update and insert) and how MERGE statement in SQL Server 2008 (or later) can be more efficient to perform the two operations at once.

First we’ll create a table for this demo.

CREATE TABLE dbo.GroupInfo (
    Id    int unique not null
  , App   varchar(100)
  , DB    bit
)

We want to do update if the Id is found on the table and insert if it’s a new Id number.

1. Conventional way of doing it is by using IF EXISTS statement.

CREATE PROCEDURE [dbo].[p_UPSERT1]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM dbo.GroupInfo WHERE Id = @ID)
  UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
  WHERE Id = @ID
ELSE
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
        @ID
    , @APP
    , @DB
  )
  SET NOCOUNT OFF;

2. Second way of doing it is by taking advantage of the @@ROWCOUNT.

CREATE PROCEDURE [dbo].[p_UPSERT2]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
WHERE Id = @ID
IF @@ROWCOUNT = 0
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  )
SET NOCOUNT OFF;

3. The third and probaby the best way by using MERGE to perform INSERT and UPDATE operations on a table in a single statement.

CREATE PROCEDURE [dbo].[p_UPSERT3]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
MERGE INTO dbo.GroupInfo AS tgt
USING
  (SELECT @ID) AS src (id)
  ON tgt.Id = src.id
WHEN MATCHED THEN
  UPDATE        
    SET
        App = @APP
      , DB = @DB
WHEN NOT MATCHED THEN
  INSERT (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  );
SET NOCOUNT OFF;

Now, you can analyze the execution plan of each stored procedure on your own to compare them.

EXEC p_UPSERT1
  @ID = 1
, @APP = 'App 1'
, @DB = 0
GO

EXEC p_UPSERT2
  @ID = 2
, @APP = 'App 2'
, @DB = 0
GO

EXEC p_UPSERT3
  @ID = 3
, @APP = 'App 3'
, @DB = 0
GO

How to Find a Column Name in SQL Server Database

This query was originally appeared from SQL Authority Blog and I find it really useful when you need to find a column in any tables in a database.

SELECT t.name AS "Table Name",
  SCHEMA_NAME(schema_id) AS "Schema",
  c.name AS "Column Name"
FROM sys.tables AS t
  INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ColumnName%'
ORDER BY 'Schema', 'Column Name';

If you need to find all column names that are in the database, just comment out or delete the highlighted line from the SQL command above.

Using Connection Coloring in SSMS Tools Pack

From previous posting, we demonstrated a way to use custom color coding using SQL Server Management Studio’s (SSMS) Registered Servers properties. But using the feature can be very dangerous if you don’t have the discipline to follow a strict process of opening new query from the registered servers pane. For example, if you use Change Connection button, the color will stay the same as the last environment you were connected to the first time via the registered server panel. That’s where SSMS Tools Pack comes in handy. Don’t get it wrong, this SSMS add-on software is more than just a connection color coding tool, but to add on from our last posting, you can do connection color coding quite easily by using its Window Connection Coloring feature. The tool is free for SSMS 2008 or earlier. You only have to pay when use version 2012 or higher. So here are the steps to use color connection with SSMS Tools Pack:

After you installed the tool, you should see it displayed on your SSMS’ menu (SSMS Tools). Click on it and select Window Connection Coloring, then Options…
This part is self-explanatory:
1. First, you may want to increase the thickness (in pixels) of the colored bar that will be displayed. I found the default a bit too thin for my taste.
2. Unlike default SSMS custom color, with SSMS Tools Pack you can choose where you want the bar displayed on either one of the four sides of your query window.
3. Click on the color bar to pick your custom color. Click OK twice when done.

That’s all there is to it. And this is what it looks like when it’s done.

Note: What’s displayed here is a combination of SSMS’ own default custom color on the bottom bar and SSMS Tools Pack’s color bar on the top. I personally like this setup as it’s harder to miss and where the two color disagree, I know the one from SSMS Tools Pack (top bar) is the one to follow.

Download

SSMS Tools Pack

String Literals as Column Aliases are Deprecated

Further Reading

How to reset SA password on Microsoft SQL Server

Steps

For SQL Server 2008 or Older

For SQL Server 2012 or Later

Further Reading

Ways to Upsert a Record in SQL Server

Further Reading

How to Find a Column Name in SQL Server Database

Further Reading

Using Connection Coloring in SSMS Tools Pack

Download

Further Reading

Recent Posts

Further Reading

Steps

For SQL Server 2008 or Older

For SQL Server 2012 or Later

Further Reading

Further Reading

Further Reading

Download

Further Reading

Recent Posts

Tags