SQL: Just like tables, IN clauses have no default order

In Stack Overflow, I saw a poster with a query like this:

And he was frustrated that he couldn't reliably get an ordered output like this:

The problem is that he was assuming that the IN clause had a specified order that was then obeyed by the SELECT statement.

It doesn't.

If you want a SELECT statement to return rows in a particular order, you need to use an ORDER BY clause, and, in this case, you need something to order the rows on in the first place.

I think the easiest way to do that is with a table variable that has an IDENTITY column like this:

I think that's about the simplest way to do it, particularly if there are a lot of values that could be in that IN clause.

Hope that helps someone.

 

SDU Tools: List user access to Reporting Services content items

Finishing up my recent theme of running queries against the SQL Server Reporting Services catalog. I often need to list which users have access to which items in the SSRS catalog.

So, in our free SDU Tools for developers and DBAs, we added a procedure that does just that. It's called RSListUserAccessToContent.

It takes two optional parameters:

@RSDatabaseName sysname – the name of your SSRS database

@IsOrderedByUserName bit – should the output be ordered by user name?

The database name will default to ReportServer which is the name that SSRS uses during install if you don't change it.

Find out more

You can see it in action in the main image above, and in the video here:

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

 

Opinion: If you don't like answering questions, leave the forums

Over the years, I have long periods where I avoid Q&A forums. Lately, I've been spending a bit of time back in some forums. And once again, I've seen the sorts of behavior that make me think about leaving again.

Here's a simple message: If you don't like answering questions in Q&A forums, then don't. Leave. You might think it's all about you and your attempts to gain reputation in the forums. It's not.

It's about the people who need help.

There are a few personality types that I really want to call out:

The "you didn't search well enough before asking the question" type.

These people will berate others for asking questions that have been asked before. Get over it. People will ask the same questions again and again. Not every one of them is a search whiz. And who cares anyway? Just answer their question and let them get on with their lives. If you don't want to do that, just stop doing it.

Worse, I've seen examples lately where questions are closed as duplicates, and the person who jumped into to close it quickly, has totally misunderstood that it's really not actually a duplicate.

The "you're doing a dumb thing; I'm really clever but you should go back to school" type.

These people are more intent on trying to make themselves look clever, belittling the questioner, and basically telling them they're stupid for what they're trying to do. Yet in 99% of cases, they don't give them an answer to their question, or point them in the right direction. Just give the questioner a break; do a bit extra work in showing them what they should be doing.

The "I'm so bored with answering questions that I really can't be bothered" type.

These people will often have compiled a massive FAQ, and the answer to every question is that people will find the answer (somewhere) in the FAQ. If you have the answers so easily at hand, just copy and paste it in, to help the questioner. Don't make them play Where's Wally to find the answer.

Look, if you aren't spending time in the forums with a genuine desire to help people, thank you for your previous efforts but might be time for you to just take a break.

And one for the forum people too: Is it really necessary to remove human interaction and warmth from the responses? When I finish a response with something like "Hope this helps" as I normally would when talking to a human, invariably one of the rules enforcers edits my post and removes that comment. Really? I can see why many people find the forums pretty toxic.

 

 

T-SQL 101: #41 Using string literals in SQL Server

When we write string literal values, we need to surround them in single quotes. There are options in some SQL products we could make that double quotes. In general though, don't do that. Just use single quotes.

If you have multi byte characters, they need to be preceded by the N prefix. I've previously mentioned that it stands for National Character Set. If I don't use the N, when I execute the second query, I'd see this:

My multi-byte Chinese characters get replaced by question marks.

However, if I include the N, I see this:

Without the N, we just get the ANSI characters, basically our normal English alphabet and numbers and punctuation and so on. The minute you need to use a language that has way more characters than we do, then you can't just use single quotes or the values won't be interpreted correctly.

One other thing worth noting is that sometimes you need to put a quote in the middle of a string. To do that, you use two quotes like this:

Notice that when I execute this, only one quote is output.

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.

Snowflake for SQL Server Users – Part 12 – Parallelism when loading data from files into tables

When you are loading data into Snowflake, it's really important to achieve the maximum parallelism that you can. You want as many files loading in parallel as you have. I mentioned in earlier posts that the number of servers that you have, and the size of each of those servers, will determine the number of processor threads that are available to you from your virtual warehouses.

It would be pointless to have 32 processor threads waiting to load your data, and you provide Snowflake with one large file to load.

Instead, you should consider having at least as many smaller files to load as you have available processor threads. This means that you should favour many small files, rather than a smaller number of larger files.

Breaking up large files

This means that it can be desirable to break up existing large files. When I was doing Snowflake training, a number of people asked about good options for breaking up large text files. We couldn't find a good option so I wrote one.

You can find details of SDU_FileSplit here. As well as breaking up the files, it can transfer the header rows into the split files and more.

On Linux, you could just use the split command line utility.

File Sizes

In terms of how large the files should be, the Snowflake documentation recommends 10MB to 100MB of compressed data. (Most people load zip files).

That also means that if your files are much smaller, you should aggregate them before loading them, to get them into the target file size.

 

 

SQL: Accessing SQL Server Express localdb from another computer

I saw a discussion on a forum the other day about whether or not you could access the localdb version of SQL Server Express from another computer. Lots of people were saying it's only a local tool and you can't access it from elsewhere.

But that's not true.

You actually can connect to a localdb instance from other machines. It's a perfectly valid configuration. (Although, if you're really running it like a service, you'd be better off with SQL Server Express Edition instead).

localdb is a deployment method for SQL Server Express edition, where instead of being started as a service, the database is started as a child process to another process (or application).

First, you need to make sure that the localdb instance is fired up. localdb is normally started by an application that it's attached to, like the way it can be used with Visual Studio to provide a local version of a SQL Server database, that's easier to deploy than SQL Server Express.

Now that can be your application, if you only need others to connect while your application is running. Alternately, it can be via the SqlLocalDB Utility. See here: https://docs.microsoft.com/en-us/sql/tools/sqllocaldb-utility

The SqlLocalDB utility can be the host process that launches localdb. It has options to start and stop it along with a few additional options.

Second, you need to make sure that TCP is enabled. Details of setting up a shared instance are described here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb

Hope that helps someone.

SDU Tools: List user access to Reporting Services

Continuing my recent theme of running queries against the SQL Server Reporting Services catalog, I often need to list which users have access to the service.

So, in our free SDU Tools for developers and DBAs, we added a procedure that does just that. It's called RSListUserAccess.

It takes two optional parameters:

@RSDatabaseName sysname – the name of your SSRS database

@IsOrderedByUserName bit – should the output be ordered by user name?

The database name will default to ReportServer which is the name that SSRS uses during install if you don't change it.

Find out more

You can see it in action in the main image above, and in the video here:

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

 

T-SQL 101: #40 String data types in SQL Server

You'll notice when we discuss strings that there are many data types compared to what we had with numbers:

char was the original data character data type. By fixed length, I mean that if it's defined as 10 characters long, it always returns 10 characters, even if there are only 5 characters stored in the string.

Prior to SQL Server 2019, char could only be used with ANSI characters (i.e. single byte characters). From 2019 onwards, char can also be used to store multi-byte characters by using UTF-8 encoding. I'll write more about that another day.

nchar was the same thing except that it stored multi-byte characters to allow it to work with the full Unicode character set. Originally these were only 2 bytes per character, but in SQL Server 2012, an option for 4 byte characters was also added.

varchar and nvarchar are the variable length versions of char and nchar. When you select data from them, they only return the characters that are stored i.e. if there are 5 characters stored in an nvarchar(10) string, when you query it, you only get 5 characters back.

Originally, Sybase had a text data type that was used for long ANSI characters, and so it became part of SQL Server too. It allowed up to 2GB of data in a single value. ntext was the Unicode equivalent.

Nowadays, you shouldn't use text or ntext, and should use varchar(max) or nvarchar(max) instead.

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.

Snowflake for SQL Server Users – Part 11 – GET, PUT and SnowSQL for working with local files

In a previous post, I talked about stages. They are internal or external cloud storage locations that you can use the COPY command to copy data into database tables from or use the COPY command to export data from database tables.

Now if you are using external stages, they're just standard storage accounts in AWS (S3), Azure Storage, or Google (GCS). You can use whatever tools you want to get files from other locations (like your local file system) to/from these accounts.

But if you want to get data in or out of the internal stages, you need to talk to the Snowflake API. While you could write your own code to do that, the easiest way to do that is by using a tool called SnowSQL.

SnowSQL

Snowflake has a number of connectors. SnowSQL runs on 64bit versions of Windows, macOS, and Linux. It's built using the Snowflake Connector for Python. That connector is just straight Python coding. On Windows, for Python v2, you need 2.7.9 or higher. For Python v3, you need 3.5.0 or higher.

SnowSQL is a command line client that you can run interactively as a shell, or you can run in batch mode. Commands can be referenced via a -f parameter (common on Windows) or redirected into it via stdin (common on Linux and macOS).

Using SnowSQL is very much like using OSQL or SQLCMD. Like SQLCMD though, it has a rich command language of its own with variables, auto-complete, command line history, and variable substitution.

SnowSQL Commands

You can run SQL commands through SnowSQL but most people would use other tools for that. The commands that most people will use SnowSQL for are the GET and PUT commands. These are not SQL commands but SnowSQL commands.

GET is used to retrieve files from the internal stages.

PUT is used to upload files into the internal stages.

Other useful (and related) commands are:

LIST is used to list the files in either internal or external stages.

REMOVE is used to delete files from the internal stages.

 

 

 

 

SQL: How to find primary key column names in SQL Server

Another question that I answered on a forum recently was about how to find the primary key column (or columns) for a table.

Here's an example of the code required:

I hope that helps someone.