Erland Sommarskog

You are sysadmin on a production server, and on this server, there are databases where there are users with power permissions such as the db_owner or db_ddladmin roles. They have no server-level permissions, but there may be rogues who want to perform actions beyond what their own permissions allow them to.

This session is about a security threat, you may or may not have considered. You are sysadmin on a production server, and an attacker could lure you to unknowingly run malicious code (ab)using your sysadmin permissions, performing operations advantageous to the attacker. That could be data...

A common requirement in database applications is that users want a function to search a set of data from a large set of possible search conditions. The challenge is to implement such searches in a way that is both maintainable and efficient in terms of performance.

Regular expressions have been used in computing environments for decades for advanced search and replace operations. In SQL Server we have had the LIKE operator forever, but anyone who has tried to make advanced pattern-matching has realised that its capabilities are quite limited.

Deadlocks happen in the best of families, and an occasional deadlock is nothing to be alarmed of. But too many deadlocks can hamper throughput or be irritating for the users who encounter them, and in this case you need to investigate the deadlocks to see how you can reduce the pain.

Regular expressions have been used in computing environments for decades for advanced search and replace operations. In SQL Server we have had the LIKE operator forever, but anyone who has tried to make advanced pattern-matching has realised that its capabilities are quite limited.

You are sysadmin on a production server, and on this server, there are databases where there are users with power permissions such as the db_owner or db_ddladmin roles. They have no server-level permissions, but there may be rogues who want to perform actions beyond what their own permissions allow them to.

Any SQL programmer with some experience knows that loops are bad and that you should work with all data at once in set-based statements. However, you may have experienced situations where this strategy did not work out well and you ran into problems like out-growing the transaction log or blocking other users.

Collations is a topic that tends to confuse SQL Server professionals. It is not that the implementation of collations in SQL Server is extremely confusing, but computer people are not always prepared for the surprises that human language can offer.

You have a result set in relational format, but you want to pivot the result set for presentational reasons, so that that the values in some column(s) in the result set become column in what the user sees. And in many cases, you don’t know these values beforehand, so the pivot operation has to be dynamic. This is a non-relational operation, which may be better done client-side. Nevertheless, being able to do a dynamic pivot in SQL is a valuable asset to have in your tool belt. To be able to write a dynamic pivot, you need two master two things: writing static pivot queries and dynamic SQL. In this session, we will start learning how to write static pivot queries. In case you already feel acquainted with the PIVOT operation in T-SQL, here is a teaser: we will not use that operator, but a much more flexible scheme. Next, we will move on to the basics of dynamic SQL. Dynamic SQL itself is quite an advanced feature, but we will only have time for the basics – and some words of caution. Finally, we will put these things together and learn how to write dynamic pivots. This is a level-200 session. Preferably, you should have been working with writing queries and stored procedures for two-three years to have full benefit of the session.

Any SQL programmer with some experience knows that loops are bad and that you should work with all data at once in set-based statements. However, you may have experienced situations where this strategy did not work out well and you ran into problems like out-growing the transaction log or blocking other users.

Deadlocks happens in the best of families, and an occasional deadlock is nothing to be alarmed of. But too many deadlocks can hamper throughput or be irritating for the users who encounter them, and in this case you need to investigate the deadlocks to see how you can reduce the pain.

You are sysadmin on a production server, and on this server, there are databases where there are users with power permissions such as the db_owner or db_ddladmin roles. They have no server-level permissions, but there may rogues who want to perform actions beyond what their own permissions allow them to.

Deadlocks happens in the best of families, and an occasional deadlock is nothing to be alarmed of. But too many deadlocks can hamper throughput or be irritating for the users who encounter them, and in this case you need to investigate the deadlocks to see how you can reduce the pain.

Any SQL programmer with some experience knows that loops are bad and that you should work with all data at once in set-based statements. However, you may have experienced situations where this strategy did not work out well and you ran into problems like out-growing the transaction log or blocking other users. You can solve this by operating on the data in chunks. Implementing chunking is not that difficult, but there are still pitfalls you can run into. In this session, I will discuss in what situations you may want to use chunking. I will give some best practices for how to implement chunking for good performance and I will highlight some things you need to keep in mind, for instance, recovering from interruptions. I will also look at how you can use chunking for error handling: You want process many rows and you want the good rows to succeed even if some rows yield errors – something you cannot do in a single set-based statement. This is a level 300/400 session for persons who have been working with T-SQL development for a few years.

Deadlocks happens in the best of families, and an occasional deadlock is nothing to be alarmed of. But too many deadlocks can hamper throughput or be irritating for the users who encounter them, and in this case you need to investigate the deadlocks to see how you can reduce the pain.

A common requirement in database applications is that users want a function to search a set of data from a large set of possible search conditions. The challenge is to implement such searches in a way that is both maintainable and efficient in terms of performance. This session looks at the two main techniques to implement such searches and highlights their strengths and limitations.

The basic mechanism when we want to give users access to data or actions in SQL Server in a controlled way through stored procedure is ownership chaining. This is something we use everyday as SQL workers, although we may not be aware of it.