Voice of the DBA

Tag: SQLNewBlogger

FORMATing Dates
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

FORMAT is a function that was introduced in SQL Server 2012. It is designed to format dates and numbers as date/times. It was added to try and reduce the complexity and cumbersome nature of CONVERT and CAST.

What I didn’t know, which I did like, is that the FORMAT command can be driven by language settings. For example, if I take a specific date, like today, I can reformat the date based on a language code.
```
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'
```
The results of this code are different, based on the cultural settings. Of course, the Chinese settings is really the only good way to show dates without confusion.

This is interesting in that if you can get the regional settings for the client, you can easily return data in a format that makes sense to the user. Of course, we typically don’t want to do too much formatting on the server.

The default language is that of the current session. For dumb uni-lingual people like me, this isn’t an issue, but it might matter for people that speak multiple languages and might move from system to system.

It is interesting in that you can easily format the data from a date with FORMAT. For example, I change orders to match the Chinese format:
```
DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'YYYY/MM/DD');
```
That gets me “2015/07/20”.

I can easily change to other formats, adding in times, or even partial times that might not make sense. For example, the result below named “Hours” has only the date. The “Date and hours” has the date and the hour only.
```
DECLARE @d DATETIME = GETDATE();

SELECT  'Default' ,
        FORMAT(@d, 'd')
UNION
SELECT  'Full Date' ,
        FORMAT(@d, 'YYYY/MM/DD hh:mm:ss.ffff t zzz')
UNION
SELECT  'Date and hours' ,
        FORMAT(@d, 'YYYY/MM/DD hh')
UNION
SELECT  'Date and minutes' ,
        FORMAT(@d, 'YYYY/MM/DD mm')
UNION
SELECT  'Hours' ,
        FORMAT(@d, 'hh');



--      ,FORMAT ( @d, 'yymmdd', 'en-gb' ) AS 'CleanUS'
```
The results are:

Date and hours    2015/11/20 04

Date and minutes 2015/11/20 11

Default           5/20/2015

Full Date         2015/11/20 04:11:28.9600 P -06:00

Hours             04

I think that there are lots of reporting queries where the formatting of dates would be handy and easier with FORMAT than CONVERT. It certainly is more intuitive to read than seeing something like “,110” in code.

I still have the habit of using CAST and CONVERT when I’m changing types, and I’ll continue to do that. Especially as FORMAT is really limited to date types. However when trying to get dates to render in proper formats, it’s a good choice.

SQLNewBlogger

This post came about while I was checking on another issue. I happened to run into the FORMAT command and hadn’t used it much, so I spent a few minutes messing around. This post came out of around 5 minutes of experimentation and 15-20 minutes of writing.

References

FORMAT – https://msdn.microsoft.com/en-us/library/hh213505.aspx

Formatting Types in the .NET Framework – https://msdn.microsoft.com/library/26etazsy.aspx
June 3, 2015
DB_Owner Querying for Database Options

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

What can a user with the db_owner database role do? I assumed anything inside of the database (create/alter objects, assign permissions, back up the database, etc). However someone asked recently about whether someone could read database properties. I’d assume they can, but I needed to check.

I decided to start by creating a new login. I have lots on my test instance, but I went with just building a new one. I used the GUI to add [DBOwnerTest] because it’s quicker. I assigned a password, set a default database, and mapped this user to db_owner in a database.

I then opened a query window and changed the connection:

Now I could easily run a query for properties and see the results:

Note the connection at the bottom of the image above.

Here’s where it’s a little interesting. I disconnected Object Explorer and connected back as DBOwnerTest. I see this:

Looks normal. According to the BOL documentation for permissions, db_owner gets View Any Database as well as control over their own database. I can see the properties of the Sandbox database (where I’m db_owner).

But I can’t see properties of other databases.

I also see my login, but not the couple dozen others I have, other than sa. That’s curious, and perhaps not good. However this isn’t the place to delve into that.

I have the ability to query through databasepropertyex(), which I’ve documented in another post.

Certainly db_owner conveys lots of rights to the user, and certainly the ability to see some of the outside of the database container, such as the options and properties, as well as other databases.

Writing

This was based on a question I saw posted at SQLServerCentral. It took me about 5 minutes to set up a test login and query for information. I had to perform a few searches and try some queries. I spent a few minutes researching databasepropertyex(), which became another post.

All told, this was about a 15 minute post.

You can do this. Join the #SQLNewBlogger group and start documenting your career. You can see all my posts that fall into this area by looking through the SQLNewBlogger tag here.

References

Permissions of Fixed Database Roles – https://technet.microsoft.com/en-us/library/ms189612%28v=sql.90%29.aspx

May 21, 2015
Getting Database Properties – DatabasePropertyEx()
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had someone ask me a question about security recently, and while working through the answer, I ran across something I didn’t know well: databasepropertyex(). Here’s a few notes.

Each database in SQL Server has all sorts of options and settings. Most people (including me) get in the habit of checking here for the information. First we right click the database (after starting SSMS if it isn’t running)

Then you get this dialog, with lots of stuff.

And more on the other tabs.

Time consuming, and error prone. As I get older, I find that trying to decide which selection is set for which option becomes harder. I find my finger tracing across the screen. It’s entirely possible I’d make a mistake when glancing at this to check the ArithAbort setting.

Use T-SQL

Scripting and querying is usually better. Not the sp_configure scripting where you get a whole list of options, but looking for a particular item. That’s where DatabasePropertyEx() comes in. This lets you query for a database property.

The problem comes in when you run it. If you run the function, you get little information.

Certainly you can go look at BOL to get more data, but that’s annoying. If I run sp_configure, I get data. However here, nothing. Even if I do what I think would be helpful, with a NULL parameter, I don’t get a list of stuff. SQL Prompt alerted me to the fact that the first parameter is the database, and the second is the property, but that doesn’t work.

Fortunately, I have SQL Prompt, so I get this when I put in quotes for the second parameter.

As you can see, the parameters don’t map to the properties, though you can figure out what they mean if you see them. They tend to follow the conventions that most application programmers use (IsAutoClose).

That’s fine, and it just means you need to have a reference handy for properties to query. I wish MS would give all properties with a NULL parameter, or a link to BOL.

Writing

This one took a bit longer to write. Once I realized I didn’t know databasepropertyex() very well, I had to read about it (5 minutes) and experiment a bit. I took some screen shots, which is always cumbersome. As I wrote this, I had to change the wording and ordering a few times to try and convey a simple message. I was originally going to look at more details, but decided to keep this simple and talk about just querying properties.

This was about 30-40 minutes for me.

You can do this. Join the #SQLNewBlogger group and start documenting your career. You can see all my posts that fall into this area by looking through the SQLNewBlogger tag here.

References
- https://msdn.microsoft.com/en-us/library/ms186823.aspx
May 14, 2015
April Blogger Challenge 4–Filtered Index Limitations
The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

Filtered Index Limits

I ran across a limitation recently with filtered indexes and was surprised. Apparently, you can’t make complex filters in your definitions.

In the last post, I wrote about creating a filtered index on gender, looking for non-null columns in a table. That code looked like this:
```
CREATE INDEX Users_Gender_Filtered
  ON Users (gender)
  WHERE gender IS NOT NULL;
```
However, what if I find that I have lots of NULL values, but also lots of spaces, ‘ ‘, in my table because this is an optional field, and the data entry code changed at some point. Neither of these rows actually helps me in finding the rows with an ‘M’ or a ‘F’.

I tried creating an index like this:
```
CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IS NOT NULL OR gender = ' ';
```
That didn’t work, so I tried parenthesis.
```
CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');
```
That didn’t work either. No matter how I moved parens around, they didn’t allow a complex (if two criteria are “complex”) filtered index.

This is a limitation of a filtered index right now. I’m not sure if it will be changed, though I would like it to be. There are other limitations, so read the docs carefully, and think about what might work well in your environment.

References

A few places I used to research this post.
- https://msdn.microsoft.com/en-us/library/cc280372.aspx
- https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/
Quick and Easy Blogging

This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

Look for the other posts in the April challenge.
April 30, 2015