Sql-Server

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).

2019-10-24

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?

2019-10-23

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.

2019-10-21

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.

2019-10-18

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:

SELECT ic.index_column_id AS ColumnID, 
       c.[name] AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id 
INNER JOIN sys.columns AS c
    ON i.object_id = c.object_id
    AND ic.column_id = c.column_id
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id 
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id 
WHERE i.is_primary_key <> 0
    AND t.[name] = N'TestTable' -- table name
    AND s.[name] = N'dbo' -- schema name
ORDER BY ColumnID;

I hope that helps someone.

2019-10-17

I mentioned last week that I’ve been needing to write queries against the SQL Server Reporting Services catalog. I often need to list the items that are contained 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 RSListContentItems.

It takes two optional parameters:

@RSDatabaseName sysname - the name of your SSRS database

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

2019-10-16

Today, I’m continuing the discussion on the variety of data types supported by SQL Server. I’ll round out the discussion by looking at the operators that are used with the numeric data types. Here are the operators:

Some of these are fairly obvious but even then, they can have hidden complexity.

We use the plus sign + to add numbers, and we use the minus sign (or dash) - to subtract numbers. No surprise there.

2019-10-14

One thing that I quite like about Snowflake is the way it cleanly works with a wide variety of file formats.

Obviously this changes over time but at the time of writing, you could COPY from the following source file formats:

• CSV
• JSON
• AVRO
• ORC
• PARQUET
• XML

There are also quite a number of options for configuring how these are used. Apart from the obvious options like record and field delimiters, skipping rows, etc, one of the most important of these options is compression. You can currently choose these options for compression:

2019-10-11

There was an interesting question in the forums the other day. The poster wanted to be able to put entries in a table to determine who could access data in the other tables in the database.

There are two basic ways to do this. If you want an error thrown, you’d be best just using GRANT/DENY/REVOKE as permissions and not using your own table to control it. However, if you want no error, but just no data, then the Row Level Security (RLS) added in SQL Server 2016 could do the job. Let’s take a look:

2019-10-10

This post relates to another question I got on Stack Overflow recently.

The poster was saying that he was having no luck inserting data from teh Gujarati language, even though he was using nvarchar as a data type.

The most common problem that I see when people aren’t getting the outcome they want when inserting into an nvarchar column is that they aren’t putting N in front of their string values. (N is National Characters Set). Imagine a table like this:

2019-10-10