Voice of the DBA

Tag: T-SQL

Changing a Computed Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was working with a computed column the other day, and realized I had the wrong definition. In this case, I was performing some large calculation, and the result was larger than an int. However the first part of the formula was an int, which resulted in an implicit conversion to an int.

I needed to change the formula, and then realized that plenty of people might not work with computed columns much, and not realize how you alter a computed column.

You don’t.

In fact, you need to drop the column and add it back. In my case, this was what I did. Here was my table:

CREATE TABLE SiteStats
(
StatID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
, StateDate DATE DEFAULT SYSDATETIME()
, StatMonth TINYINT
, StatYear int
, PageVisits INT
, TimeOnSite TIME
, Engagement AS (PageVisits * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite))
)
I wanted to cast the PageVisits part of the column to a bigint to solve the issue. I first needed to do this:

ALTER TABLE dbo.SiteStats
DROP COLUMN Engagement
Once that’s done, I can do this:

ALTER TABLE dbo.SiteStats
ADD Engagement AS (CAST(PageVisits AS BIGINT) * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite));
GO

Now I have a new definition that works great.

Some of you might realize that this could be an issue with columns in the middle of the table, and it is. However you shouldn’t worry about column order. Select the columns explicitly and you can order them anyway you want.

SQLNewBlogger

A quick post, five minutes. Even if you had to search for how this works, you could do this in 10-15 minutes, tops. Research, write why you did this and potential issues with your system.

May 18, 2016
Am I a sysadmin?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.

There’s this code, of course:

SELECT
ServerRole = rp.name,
PrincipalName = SP.name
FROM sys.server_role_members rm
Inner JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id
Inner JOIN sys.server_principals SP
ON rm.member_principal_id = SP.principal_id
where sp.name = SUSER_SNAME()
and rp.name = ‘sysadmin’

That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:

SELECT IS_SRVROLEMEMBER(‘sysadmin’);

If I run this, I get a 1 if I’m a member, or a 0 if I’m not.

Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.

SQLNewBlogger

This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you might use this, or where this type of check could come in handy in your code (maybe before taking some action).

April 28, 2016
Explicitly using tempdb with ##tables

I had someone ask me last night if this statement would create a permanent table in tempdb with a strange name or a global temp table:

CREATE TABLE tempdb..##mytable
( id int
);

My suspicion was that this would always be a temp table, but since I usually don’t include tempdb, I decided to test things. I quickly opened up SSMS and tried it. I got this message:

That seems to imply what I suspected. The ## override everything and determine the table type. When I look in the list of tables, I see my table there as a temporary one.

This is the behavior I’d expect, and it acts the same in SQL 2012, 2014, and 2016 (RC2).

I don’t think there’s an advantage to using tempdb..##mytable, or even creating a permanent table (until restart) using tempdb.dbo.mytable, but if there is, please note something in the comments.

April 25, 2016
#SQLNewBlogger–Adding Local Accounts
What do you do if you need a process running under Local Service to connect to your SQL Server? Most of the advice out there is to change the login account. I actually agree with that, but there are times you can’t, or don’t want to.

There are certainly times when I’ve seen some automated process use one of these accounts:
- NT Authority\Network Service
- NT Authority\Local Server
Often this is because someone doesn’t want to bother to learn how to enable other accounts for their application, which isn’t a good excuse. In my case, I had a local VSTS agent service running as part of a demo, where I had very limited rights. I couldn’t affect a change, and I needed to get a new login for SQL Server.

I searched a bit, but most advice said to just change the account, after all, if you had a process connecting from another machine, Local Service won’t work. However I found one item on Stack Overflow that helped.

Here’s my Login list. As you can see, I have Network Service, but not Local Service.

I the run this code:

CREATE LOGIN [NT AUTHORITY\LOCAL SERVICE] FROM WINDOWS;

This gives me a new login.

In my situation, I then had to add this to the dbcreator role, but I could treat this like any other login and assign the minimum privileges needed.

SQLNewBlogger

I had to solve this and decided to write about it. The writing took 10 minutes, the research was 15-20 minutes to find a good reference and experiment a bit.

A good learning exercise, and all of you should know how to do this. Prove it with your own blog.
April 4, 2016