SQL Server

Spojení řetězců pomocí FOR XML PATH

13. 11. 2014

Spojení textových řetězců z více sloupců na řádek můžeme v SQL Serveru v edicích 2016 a nižších vyřešit pomocí FOR XML PATH a je to i vůbec nejpoužívanější způsob. V SQL Serveru 2017 dosáhneme stejného výsledku mnohem elegantnější cestou pomocí funkce STRING_AGG(), která ve všem plně nahradí FOR XML PATH.

Read more
DBA

Logging schema changes using DDL trigger

2. 10. 2014
Logging schema changes (DDL) in the database is a common requirement in many organizations or development teams. We can choose from many tools offering complex auditing or write our solution. Let’s build our one and keep it as simple as possible. Please remember that the event log can be manipulated…
SQL Server

Why to avoid functions in WHERE?

20. 9. 2014
It’s well known that we should use functions in the WHERE clause because they can have a negative impact or query execution because of the suboptimal execution plan is generated. I have prepared a really simple example of how to demonstrate this. It also demonstrates one rule that we should…
SQL Server

CONTEXT_INFO()

26. 8. 2014

Session context information enables applications to set binary values of up to 128 bytes that can be referenced in multiple batches, stored procedures, triggers, or user-defined functions operating on the same session. You can set a session context value by using the SET CONTEXT_INFO statement and retrieve it later by…

Read more
SQL Server

CONVERT() datetime to string - Table of output values

16. 8. 2014
I’m converting DATETIME values to string nearly every day when doing regular T-SQL development. After years I remember a lot of format parameter values to get the right string representation of input DATETIME value. But still time to time something specific is needed. This is why I have created a…
T-SQL

Rounding DATETIME values to midnight

5. 8. 2014
DATETIME data type precision for milliseconds is rounded to increments of .000, .003, or .007 which leads to some unexpected behavior from time to time. I.e. if we will try to extract at which day event logged with milliseconds occurred. The example below demonstrates it pretty: Event occurring at 20101205…
SQL Server

ISNUMERIC()? No, thank you!

2. 6. 2014

Can we trust ISNUMERIC() function when checking if it is safe cast string value to numeric data type? Definitely not, because it´s more or less unusable for this purpose, because we can´t be sure what is hidden in string to be converted. And trust me: there is lot of surprises…

Read more
T-SQL

Examples of TOP syntax

21. 5. 2014
This article is just a short summary of TOP clause variants we can use. Especially the last one with a subquery isn’t so common and can be something new for you. Consider official documentation for more details on TOP usage and if you have any other interesting example don’t hesitate…
SQL Server

Change in Query Plan Can Affect Query Result Set (Table Spool)

10. 5. 2014
It´s legitimate expectation that a SELECT statement will return consistent results independently on number of rows returned or an internal implementation of query processing. But it is not always true. I will demonstrate it on a simple query using NEWID() function. This function should return new value on every call…
T-SQL

Composable DML

27. 4. 2014

Composable DML is an extension SQL Server 2008 extension of the OUTPUT clause which one was originally published in SQL Server 2005. OUTPUT is generally returning all rows from the DML where it’s used without an option to filter the results set. Composable DML extends this concept for INSERT…SELECT scenarios…

Read more