Sql-Server

I often need to know if a date that I’m working with is a week day or a weekend. While T-SQL has some functions that help with that, you need to use a combination of them to get a reliable outcome that’s independent of the current session settings. (Based on Saturday / Sunday being weekends but easy for you to change if you have different weekends). To make it easy, we added two functions IsWeekday and IsWeekend to our free SDU Tools for developers and DBAs.

2019-06-12

We use comparison operators in T-SQL expressions. And the expressions are often used in WHERE clauses. For example:

WHERE CreditLimit = 10000

The = (equals) sign shown is a simple one but T-SQL has a number of operators.

Most people writing T-SQL code are familiar with these:

= Equals <> Does not equal < Less than > Greater than <= Less than or equals >= Greater than or equals

However, less people seem to be aware of the other operators added a while back:

2019-06-10

I was working in a bank a few weeks back. They were planning an upgrade from SQL Server 2008 R2 to SQL Server 2017. One of the things that I raised with them is why there were extended stored procedures that had been added to the master database. And they told me they were the result of a penetration test.

OK, so this had me intrigued.

The logic that was being applied was that the penetration testers required the bank to have SQL CLR disabled. No-one in the bank could tell me why; just that it was required to be off.

2019-06-06

Many of my friends have told me that they like how concise the PIVOT and UNPIVOT operators are, but they find the syntax confusing. I must admit that I wasn’t a big fan of these when they were added in SQL Server 2008 as they didn’t really add anything to the language that I couldn’t have done with CASE statements, etc. and there were so many other parts of the language that needed work.

2019-06-05

I showed earlier how we can retrieve data from a table using a SELECT clause. It can also be used to determine which columns are returned, and which table the data is being retrieved from. But we don’t always want all the rows to be returned. The WHERE clause fixes that.

It’s important to understand that the WHERE clause limits the rows returned to ones where the expression in the WHERE clause evaluates to a logical TRUE value.

2019-06-03

This is part 7 in a series of posts:

• Part 1 covered being sure there is a locking and blocking issue
• Part 2 covered the impact of RCSI
• Part 3 looked at the impacts of indexing on locking and blocking
• Part 4 looked at what deadlocks really are and how SQL Server handles them
• Part 5 looked at how applications should handle deadlocks
• Part 6 looked at how to avoid deadlocks in the first place

Today, though, I want to look at how to handle deadlocks if you must do that in T-SQL.

2019-05-30

I often need to document the access that various users have to content stored in SQL Server Reporting Services (SSRS). Surprisingly, I didn’t find any built-in way to do that.

We had started to add some Reporting Services related options to our free SDU Tools for developers and DBAs, and, just for this, we added a new procedure RSListUserAccessToContent.

It takes two parameters:

@IsOrderedByUserName bit -> Is the output ordered by user (default yes else by item) @RSDatabaseName sysname -> Reporting Services DB name (default is ReportServer)

2019-05-29

Apart from data just in a table SQL server can select other things like the ones shown here:

If I say SELECT 2, it just returns the value 2.

If I say SELECT ‘Hello’, it just returns Hello.

Both of those are examples of what’s called a literal value, which is an exact value that doesn’t change.

SELECT 4 + 5 is an example of an expression. This is where we can work something out to get the value that needs to be returned. No surprise, that will return 9 just as you’d.

2019-05-27

When I’m writing SQL Server scripts, I often need to store the name of SQL Server objects (like databases, schemas, tables, procedures, etc.) in variables.

That can also happen when I’m creating tables. I might need to store a SQL Server object name (like a login name or a user name) in a column of a table.

So which data type should be used? varchar(100), varchar(200), nvarchar(max), etc. etc. ??

2019-05-24

This is part 6 in a series of posts:

• Part 1 covered being sure there is a locking and blocking issue
• Part 2 covered the impact of RCSI
• Part 3 looked at the impacts of indexing on locking and blocking
• Part 4 looked at what deadlocks really are and how SQL Server handles them
• Part 5 looked at how applications should handle deadlocks

Today, though, I want to look at how to try to avoid deadlocks in the first place.

2019-05-23