SQL Server Express and Reporting Services

I'm a fan of SQL Server Express edition. It's a fabulously functional free database that lets you work with up to 10GB of data per database. One aspect that's commonly misunderstood though, is that it also supports a limited version of SQL Server Reporting Services.

There are many scenarios where Express edition is the perfect fit. The Microsoft sales and marketing teams have had an ongoing strange relationship with Express edition because it's free. Much of the documentation tried to suggest that it was for hobbyists, and para-professionals. But Express edition has a great role to play in professional systems too. For example, if you needed a small database to run on a series of point of sale devices, it could well be the right answer, particularly if it's then connected to one of the higher editions as a central SQL Server system.

I used to use it many years ago as an easy way to provide a system test system for clients using one of our applications. That allowed them to get established quickly without SQL Server licensing. When they had been using it for a while, and their databases grew in size, that was the easiest upgrade you'd ever convince customers to do.

Reporting

What wasn't understood well early on, is that reporting was also needed. I remember arguing passionately about why Express edition needed to include some form of Reporting Services. The marketing teams didn't at first understand that if a software house needed a reporting solution, they weren't going to use something different on Express to what they used on other editions. They'd simply use a different reporting tool where they only had to write the code once.

Express edition regularly helped us to sell paid SQL Server licenses.

Fortunately, the teams eventually saw the light on this one, and a version of Express with Advanced Services was released. It included a limited form of Reporting Services. It was perfectly functional, but didn't allow you to report on any data, except from the Express edition it was running from. (This avoided Microsoft providing a free reporting tool for the world). I thought that was a great option.

So where it is now?

Lately though, I've been asked where Reporting Services for Express went. It's not in that Advanced Services option any more. Other good things are, like full text, but not Reporting Services.

The reason for that, is that since SQL Server 2016, Reporting Services has become a separate install. It's not directly bundled with SQL Server. And that applies to Express edition as well.

You need to install Reporting Services separately. It still understands deployment against Express edition.

You'll find details of installing it here.

One thing to note, is that since the 2016 change, multiple instances of Reporting Services on a single machine are not supported.  (You could do that previously).

Isn't it all about Power BI now?

Power BI is outstanding, but there are many scenarios where Reporting Services is still the best tool. Standard reports from Express edition is a great example.

If you need to learn more about Reporting Services, we've got a course to help you learn to use it properly. You'll find details of it, and our other courses, here: https://training.sqldownunder.com.

 

SDU Tools: Date of Orthodox Easter in SQL Server T-SQL

Some time back, we added DateOfEasterSunday to our free SDU Tools for developers and DBAs. Given it was the Christian Easter Sunday, almost immediately, I got a request for the Greek Orthodox Easter. That date isn't of course just the Greek one, so we've added a new function DateOfOrthodoxEaster.

It's based on a concept from Antonios Chatzipavlis. Thanks !

It takes a single parameter:

@Year int – the year to find the date for

And the return value is a date.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

T-SQL 101: #84 Avoiding data type conversion errors with TRY_CAST, TRY_CONVERT and TRY_PARSE

In a previous post, I showed how to use CAST and CONVERT. What I didn't mention before though, is what happens when the conversion will fail. If I try to convert the string 'hello' to an int, that just isn't going to work. Of course, what does happen, is the statement returns an error.  Same thing happens if I try to convert the 30th February 2016 to a date. There aren't 30 days in February. Again, an error will be returned.

SQL Server 2012 though, added the ability to try to do a conversion, if it works, return the value, and if it fails, just return NULL. And so we got three versions of this:

TRY_CAST

TRY_CONVERT

TRY_PARSE

You can see in the example, that the first string isn't a valid date, so it returns NULL. The second one is valid, so we get back the converted value.

Now a common mistake that people make when writing T-SQL, is to write code in this type of format:

The problem with this, is that you might still get errors saying that happened_when couldn't be converted to a date. You'd think the WHERE clause would be applied first and would avoid that. But that might not happen.

Now you can deal with this in a robust way, by writing:

Even better, this would probably optimize simpler by just writing:

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: Check if Lock Pages in Memory is Enabled on SQL Server

In our free SDU Tools for developers and DBAs, we have a lot of procedures and functions for checking system and database configuration details. One that we were asked for a function for, was the ability to check if the LockPagesInMemory (LPIM) setting was enabled, so we added that.

The function is called IsLockPagesInMemoryEnabled.

It takes no parameters, and returns a bit to indicate if the value is enabled or not.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

SQL: Global temporary tables are almost never the answer in SQL Server

I was doing some consulting recently and reviewing a developer's code. I was surprised to find extensive use of global temporary tables. Let me start by saying this: global temporary tables are almost never what you should be using.

When I asked why he'd used them, he told me that he wanted the temporary tables that he created in his procedure, to be available to other procedures that were run from within the procedure. So in PROC-A, he was running PROC-B and PROC-C. A temporary table was created in PROC-A and he wanted to be able to use it in PROC-B and PROC-C.

Local vs Global Temporary Tables

Local temporary tables (i.e. tables with names starting with a single #) are available where they are created, and in any other procedures run from within that same scope. You don't need a global temporary table to do that.

Local temporary tables are dropped when they go out of scope (i.e. the session or context where they were created is dropped). And of course you can manually drop them with a DROP statement as well.

Ideally, you'd always drop them in your code rather than waiting for them to go out of scope. tempdb is a shared resource and you don't want it clogged up with unnecessary data for too long.

Global temporary tables (i.e. tables with names starting with ##) are visible to all users. These are dropped dropped when the session that created the table ends and all other tasks have stopped referencing them (ie. when other queries that are using them have completed). And yes, you can manually drop them with a DROP statement.

I'm not a fan of creating user objects in tempdb but if you really wanted a temporary table that stayed until the server restarts, one curious option would be to create a user table in tempdb automatically every time the system starts up. You could do that with either an Agent job (easiest) or by assigning a startup procedure (more reliable but also requires a configuration change).

Abstraction

One thing you need to decide early on though, is if temporary tables are the right way for you to pass data from one proc to another. They're certainly easy, and importantly, the data doesn't get duplicated when the sub-procedure is called.

However, for me the jury is out on whether this is a good idea or not. What you end up doing is making the sub-procedure much harder to test. It ends up referring to a table that doesn't exist anywhere in the procedure's code. That just breaks all concepts of abstraction pretty solidly.

As I said though, people like it for performance. You aren't copying the data.

An option to consider instead is creating a real table and putting the temporary data there, keyed off your session ID. Then the only issue is how it gets emptied again. That wouldn't have an automatic clean-up, and that's important if you have people creating temporary data and never removing it themselves; just letting it go out of scope.

Dropping and Recreating

Before creating temporary tables, it's common to make sure they don't already exist. In the past, you'd often see this type of code:

Now you can just use:

NOTE: When you create these tables, you don't provide a schema name. In fact if you do, it's ignored.

When are global temp tables needed or useful ?

I almost never use them. However, they do have uses. For example, if I wanted to "snoop" on the data that's in a temporary table in another session, I could use a global temporary table for that.

I've seen them used for licensing schemes i.e. counting the number of active sessions. But nowadays, if you can, an in-memory non-durable table would probably work better.

In most cases that I see them used, a "real" table would actually be a better solution.

 

 

T-SQL 101: #83 Determining if a string is a number or date with ISNUMERIC and ISDATE

Sometimes we need to determine whether a string is a date or whether it is a number.

In the first example above, I'm asking if the string '20190229' is a valid date. You can see from the response (0) that it isn't. That's because even though it's a valid date format, February in 2019 doesn't have a 29th day. It's not a leap year.

The value returned from the ISDATE function is a zero or a 1.  Curiously, the return value is of data type int. You'd think that a function that starts with Is and tests something would return a bit data type instead. But that's just one of the curiosities of T-SQL.

In the second example, I'm asking if the string 'hello' is a date. Clearly it's not, so the return value is 0. And in the third example, '20190228' is a date.

Similarly, we might also need to check if a string is a valid number. The ISNUMERIC function is used to do that.

However, that function might work a bit differently to what you'd expect. ISNUMERIC returns 1 for some strings that contain values that aren't numbers like plus (+), minus (-), and currency symbols like ($).

If that's not suitable for you, you might need to use a different function. A TRY_CAST or TRY_COVERT might be better, depending upon what you're trying to achieve. I'll talk about them soon in an upcoming blog post.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: COBOL-CASE in SQL Server T-SQL

I've had a lot of good feedback about the options that we've provided in our free SDU Tools for developers and DBAs for formatting strings. Someone recently asked for a format with all capitals and dashes in between. I realised we didn't have that, and we've added it. Generally, in the industry, this is referred to as Cobol Case.

So, we added a function CobolCase.

It takes one parameter:

@InputString – the string to be formatted

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

SQL: Work arounds for multi-column IN queries in T-SQL

We all use IN when writing queries:

A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:

Size        Color 
370ml   Blue
370ml   Red
220ml   Blue

How do I find those when using IN?

Other databases do allow you to have pairs:

I wish T-SQL had that option but it doesn't. It would be particularly useful when those values in the IN clause are coming from a sub-query.

Using CONCAT

One option is to use CONCAT to concatenate the strings. It takes a list of values, ignores NULL values, implicitly casts all values to strings, and concatenates the results.

That's the solution that looks closer to what you were trying to achieve, but it does more work than needed.

Using VALUES and a join

The other way is to just create a table expression using row constructors and to then join to it:

Using EXISTS

Tiago Rente reminded me in the comments on LinkedIn that of course we could have used EXISTS rather than an INNER JOIN. I actually prefer the EXISTS as it keeps the filtering in a single predicate. It could look like this:

Using a CTE for Clarity

Anthony Duguid also mentioned in the LinkedIn comments that he'd like a CTE in there, to allow for a clearer name for the subquery. In fact, I think I'd like the combination of a CTE and EXISTS the best:

 

T-SQL 101: #82 Using PARSE to convert between data types

If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that's a US date format, but if it was only 5/4/2016, there's no way I could just easily tell that.

Conversion functions use your session settings to determine how to convert the values. When I'm converting strings to dates (and/or times), I certainly don't want to change my settings so that I run with US configuration.

What I want to do is just have a way of saying look just temporally, use US settings to parse that string. And that's what the T-SQL PARSE statement does!

In the example above, I'm asking SQL Server to convert the string '2/29/2016' to a datetime2, but no matter what settings I currently have, it should interpret it with 'en-US' culture i.e. US date format. (en-US is English USA)

You might be puzzled by the output of the statement. Keep in mind that what's happened, is that SQL Server has taken that string, converted it to a datetime2 using US format, and then sent a datetime2 back to the client (SQL Server Management Studio). SSMS has then just displayed the datetime2 value in its default format.

The main thing is that it's interpreted in the input string using US format without me changing my session settings.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: Nearest Weekday using SQL Server T-SQL

I spend a lot of time doing data conversion work. It's hard to imagine how much of that relates to working with dates and times. A requirement that I had recently was to find the nearest weekday to a given date i.e. find me the closest Thursday to a supplied date.

The NearestWeekday function in our free SDU Tools for developers and DBAs, does this.

It takes two parameters:

@TargetDate date – the date that we're aiming for
@DayOfWeek int – Sunday = 1, Monday = 2, Tuesday = 3, etc.
(values from 1 to 7)

Find out more

You can see itin action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.