A Fine Slice Of SQL Server
Kevin Wilkie plays duck-duck-goose with columns:
With Snowflake, we could do many different things that we’re not used to seeing with a SELECT statement. We’re all used to seeing this – SELECT * and it shows all kinds of columns.
With Snowflake, we can tell Snowflake NOT to show certain columns by using the EXCLUDE operator.
Read on to see how it works and specific requirements around operation. In addition, Kevin shows a way to perform aliasing.
Comments closedKevin Wilkie punctuates the statement:
With our last blog post, we started discussing Snowflake and the SELECT statement. Now, if you remember, there is this great thing called a semi-colon.
The main reason you should use the semicolon is to terminate all of your queries. Snowflake does this great thing by default, letting you run one query at a time.
I remember back when Microsoft deprecated T-SQL statements which did not end with semi-colons. It was fun speculating for about 5 minutes regarding the carnage which would happen if they carried out the deprecation notice, not least of which we’d find in Microsoft-developed code.
Comments closedLouis Davidson explains how bit operations work in T-SQL:
I expect that 99% of the people reading this looks at this probably would expect there to be a status table that contained the values of status. Seeing that this is a base 2 number, you may be in that 1% that thinks this might be a bitmask. but unless you have and eidetic memory, you probably don’t know what all of the bits mean.
A bitmask is a type of denormalization of values where instead of having a set of columns that have on or off values (no Null values), you encode it like:
Bitmasks make me break out the angry nun ruler. You can almost guarantee you’re doing something wrong if you design a bitmask as a column in a table.
Comments closedBill Fellows runs into an issue:
Perfect, now I have a row for each second from midnight to approximately 5.5 hours later. What if my duration need to vary because I’m going to compute these ranges for a number of different scenarios? I should make that 19565 into a variable and let’s overengineer this by making it a bigint.
Things don’t work out quite the way you might have expected there. Read on and see what Bill found and how you can circumvent the problem.
Comments closedAdam Zegelin takes us through full-text search options in PostgreSQL:
Full-text Search is a PostgreSQL® feature that facilitates the indexing of natural language text documents, and in the identification of indexed documents that match a given query. Matching documents can be sorted based on their relevance to the query, and document excerpts can be generated with the matching terms highlighted. A set of SQL data types, operators, and functions are provided to assist with the indexing, querying, and ranking of documents.
PostgreSQL uses the term document to mean any fragment of natural language text— essentially, strings containing human-readable words separated by whitespace and punctuation. Documents are often stored as text columns but can also be generated dynamically—such as by concatenating multiple columns together (even from multiple tables).
Click through for the tutorial.
Comments closedRobert Sheldon burns it all down:
In the last few articles in this series, you learned about three important data manipulation language (DML) statements: SELECT, INSERT, and UPDATE. The statements make it possible to retrieve, add, and modify data in a MySQL database. Another DML statement that is just as important is DELETE, which lets you remove one or more rows from a table, including temporary tables. In this article, I focus exclusively on the DELETE statement to help round out our discussion on the core DML statements in MySQL. Overall, the DELETE statement is fairly basic, but one that’s no less necessary to have in your arsenal of DML tools.
Read on to see how the DELETE statement works and the minor differences from SQL Server.
Greg Larsen fills us in on an important command:
Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article “Updating SQL Server Data” I discussed using the
UPDATEstatement to change data in existing rows of a SQL Server table. In this article I will be demonstrating how to use theDELETEstatement to remove rows from a SQL Server Table.
This stays pretty simple but provides an effective overview of how to keep those tables tidy.
Comments closedLukas Eder shows off one of my favorite operators:
The SQL:1999 standard specifies the
<lateral derived table>, which is SQL’s way of allowing for a derived table (a subquery in theFROMclause) to access all the lexically preceding objects in theFROMclause. It’s a bit weird in terms of syntax, I personally think that Microsoft SQL Server has a much nicer solution for this concept viaAPPLY. Oracle supports both syntaxes (standard and T-SQL’s). Db2, Firebird, MySQL, PostgreSQL only haveLATERAL.
Click through to see how the operator works.
Comments closedChad Callihan doesn’t make two calls:
Are you familiar with the OUTPUT clause in SQL Server? It can be used with commands like INSERT, UPDATE, DELETE, and MERGE. It can also be used with setting variables in stored procedures. Using the tried and true StackOverflow2013, we’ll narrow it down today to focus on how INSERT/DELETE are typically used for logging table changes as well as an example of how to use OUTPUT with stored procedures.
For really busy transactional systems, this provides a nice boost over making an update and then selecting the new values.
Comments closedBalmukund Lakhani has an announcement:
Approximate query processing was introduced to enable operations across large data sets where responsiveness is more critical than absolute precision. Approximate operations can be used effectively for scenarios such as KPI and telemetry dashboards, data science exploration, anomaly detection, and big data analysis and visualization. Approximate query processing family has enabled a new market of big data HTAP customer scenarios, including fast-performing dashboard and data science exploration requirements.
Today, we are announcing preview of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. This function will calculate the approximated value at a provided percentile from a distribution of numeric values.
This is way faster than using the PERCENTILE_CONT() or PERCENTILE_DISC() window functions. For a decent-sized query, I was getting anywhere from 5-20x performance improvements, and the larger the dataset, the bigger the gains. It is important to note that the approximate percentiles are not window functions, so you don’t get one row back per row of input.