Tag: SQLNewBlogger

We recently published an article on CHOOSE at SQL Server Central. I thought it was a good intro, but as someone noted in the comments, how do you use CHOOSE? Do you have to hard code choices?

This post shows you don’t.

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

A Scenario

I have a table that contains some data. In this case, about beer. I like beer, and this was a fun little demo. I’m not recreating the DDL because, well, you might like different beers.

In any case, this is simple to set up.

If I wanted to choose some data from this table based on an index, I could do something like this. This code populates the first index in choose with beers and the second with brewers. CHOOSE is 1-based indexing.

DECLARE @i INT = 1;
SELECT
   CHOOSE (@i, beername, brewer)
FROM dbo.Beer AS b2;

This returns me the beers.

If I changed the value to 2, I get brewers. I show both below.

How would I use this? Maybe a user is asking to edit either a home or shipping address. I can index these by returning the column data as index 1 or 2, and linking the user suggestion to the index. They choose home, we pass in 1. If we qualify the query with a WHERE clause to one customer, they get just their data to edit.

I could even do something silly, like getting values from different places. For example, here I’ll use string_split on a value.

DECLARE @i INT = 2;
DECLARE @s VARCHAR(20) = 'Vodka,Tequila,Bourbon'

; WITH a (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 1
),
  b (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 2
),
  c (value)
AS
(SELECT a.value FROM STRING_SPLIT(@s, ',', 1) AS a
  WHERE a.ordinal = 3
) 
SELECT
   CHOOSE (@i, a.value, b.value, c.value)
  FROM a, b, c

This is silly, but it does return an acceptable answer.

I don’t know that there are many places that I’d use CHOOSE, but as I play with it, I can see that it could be a handy tool at times with a little creativity.

SQL New Blogger

This post took me about 15 minutes to write after I saw a comment. I set up a scenario and posted a reply, then took that code to structure this post. The STRING_SPLIT piece was the longest, as I had to futz with code, but I show some use of a new feature and how I might incorporate this into an application.

You could write your own creative blog on this, probably in 30 minutes or less. I bet you’d get asked about this in an interview as it’s kind of funny.

This was an interesting thing I saw in a Question of the Day submission. I hadn’t thought about the issue, but apparently DATEADD truncates values rather than rounding them. I’m not sure why that is the case, but it is.

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

The Scenario

Imagine that I have someone enter a value for the number of hours to include in a report. I enter 5 and the report divides this in half to go back 2.5 hours and forward 2.5 hours. I run this code at the top of my code block:

DECLARE @hours NUMERIC(4, 2) = 5;
DECLARE @start DATETIME, @end datetime
SET @start = DATEADD (hour, -@hours / 2, GETDATE ())
SET @end = DATEADD (hour, @hours / 2, GETDATE ())

Now, what do you think are the resulting start and end times? I’d assume this works and the function sorts out how much of an hour is .5 or .4 or whatever.

Here’s the interesting result. Look at the time interval in the end result.

It’s 4. I entered 5 hours, but I get 4 hours. I bet a lot of us would let this bug slip through as reading the datetimes we’d miss this wasn’t actually 5 hours.

Apparently DATEADD actually truncates a non-integer value. The parameter notes that the 2nd parameter, the number to add to the date value, resolves to an integer. It also notes that DATEAD truncates, not rounds, values that have a decimal fraction.

Those are two very important distinctions. That could result in calculations that are way off from what people expect if you are trying to include data in a query and you are trying to do parts of time. You might need to separately calculate all your different date/time parts.

If you need to do fractional work with dates, you can’t use DATEADD.

To me that seems lacy, but is it? Let me know.

SQL New Blogger

This is a short example of something that a person pointed out to me, and I never knew. I decided to make a quick test (the code above) and then write about this. I could have included other examples, or shown how this might mess up different situations in my code.

You could do the same thing in 30 minutes or less and point out an interesting piece of knowledge that your future employers might find interesting. They might even want to interview someone that learns things like this.

Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

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

The Scenario

I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

For my friend, imagine a table like this one:

CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)

If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:

CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)

If I run this, then I have a better named PK.

Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

SQL New Blogger

This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

Write this and maybe someone asks you how to do this in an interview.

I saw a post where a developer was trying to read the Information Schema views to create a copy of a view as a “real” table, a user table. This posts shows an easy way to do this.

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

The Scenario

Imagine you have a view, for example, I have this one:

CREATE   VIEW [dbo].[City] 

AS

SELECT TOP 10 
  cn.CityNameID, cn.CityName
  FROM dbo.CityName AS cn
  WITH CHECK OPTION

GO

The structure of the underlying table is:

I have data being returned from this view as well, as you can see here:

If I want a copy of this view, I can certainly look in the information_schema views and see some data. Below, I have the column information for this view, which can be used to structure a create table statement.

However, there’s a better way.

Quickly Copying a View

The INTO clause is very valuable and helpful here. Many of us use this to copy a table or part of a table, but it work with views. Here is how I create an empty copy of my view.

SELECT * 
  INTO dbo.MyCities 
  FROM dbo.city
  WHERE 1 = 0;

This will actually create a new table, as you can see in my Table list when I refresh after running the command.

The table looks like the structure of the view above. The PK isn’t set, but there isn’t necessarily a PK in a view as it can combine data from multiple tables. If I wanted data, I can run the same statement above without the WHERE clause. I’ve done that below and then selected data from the new table so show this.

If I needed to add some constraints or other items, I could easily add those with ALTER TABLE statements.

SQL New Blogger

This post required about 20 minutes for me to setup a demo, test, and then write with some screenshots. It wasn’t a hard post to write, but it shows a quick technique for doing something I’ve commonly seen from others.

This is the type of post you can write that might get an interviewer interested in you and perhaps ask you a question. You could add some context as to why you did this, or why you like (or don’t like) this technique.