A Fine Slice Of SQL Server
Madhivanan JR shows two methods for extracting the decimal value of a number:
One of the questions asked in a forum. “What are the different methods to extract decimal part from a number?”.
There can be many methods. Two simple methods are as shown below
Click through for the two methods. I knew method #1 but didn’t think about method #2 because I’ve always thought of the modulo with respect to integers, but it makes sense.
Comments closedManoj Pandey points out a new string function in SQL Server vNext:
Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.
This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.
The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.
It’s a small change, but I think a rather useful one. Do think about how you’d want to interpret NULL values, though, as CONCAT_WS() does not include separators for NULL values.
Comments closedLouis Davidson points out a potential query writing time-saver:
But then I saw Mike use the following syntax (sans PROFILE and XML):
SET STATISTICS IO, TIME, PROFILE, XML ON;
GO
SELECT *
FROM dbo.Table1;
GO
SET STATISTICS IO, TIME, PROFILE, XML OFF;Wow, that is a lot easier! (And yeah, using SQL Prompt I can set a snippet to take away typing, but this saves space in any case.)
Read on for more examples.
Comments closedTom Martens explains a class of generic data processing problems:
Subset and Apply means that I have a dataset of some rows where due to some conditions all the rows have to be put into a bucket and then a function has to be applied to each bucket.
The simple problem can be solved by a GROUP BY using T-SQL, the not so simple problem requires that all columns and rows of the dataset have to be retained for further processing, even if these columns are not used to subset or bucket the rows in your dataset.
One quick example of this is running totals of orders for each customer, which Tom answers using T-SQL, R, and Power BI. Click through for those three solutions.
Comments closedTomaz Kastrun shows how to feed a JSON data set into R and turn that into a proper data frame:
JSON has very powerful statements for converting to and from JSON for storing into / from SQL Server engine (FOR JSON and JSON VALUE, etc). And since it is gaining popularity for data exchange, I was curious to give it a try with R combination.
I will simply convert a system table into array using for json clause.
There’s an R library. There’s always an R library.
Comments closedRob Sewell shows how to run SQL Server queries using Visual Studio Code:
Reading this blog post by Shawn Melton Introduction of Visual Studio Code for DBAs reminded me that whilst I use Visual Studio Code (which I shall refer to as Code form here on) for writing PowerShell and Markdown and love how easily it interacts with Githuib I hadn’t tried T-SQL. If you are new to Code (or if you are not) go and read Shawns blog post but here are the steps I took to running T-SQL code using Code
I played around with an early version of this and my thought was that there were some nice improvements over Management Studio (like being able to filter and sort the result set grid without going back to the server), but that there are still too many nice things Management Studio does for me to take a serious look at it. Still, I’m hopeful that Microsoft moves in the direction of having a fully-featured querying tool for Linux so I can finally join the perpetual Year of the Linux Desktop.
Comments closedSteve Jones has a warning for when you create a stored procedure:
Why is my select code in there? That was designed to be a piece of test code. Shouldn’t the BEGIN..END after the AS define my procedure?
Actually it doesn’t. the procedure doesn’t end until the CREATE PROCEDURE statement is terminated. That termination comes by ending the batch. The CREATE PROCEDURE documentation has this limitation:
The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.
This means that anything else you have in that batch will be considered as part of the procedure, regardless of BEGIN..END.
Judicious usage of the GO statement can help keep you out of trouble.
Comments closedNate Johnson explains the nested sets model:
Put another way, the #3 rule is that you should always operate on the tree (CrUD ops) using stored-procedures and/or triggers that encapsulate all the nitty-gritty details of maintaining the correct position values during said
insert/update/deleteoperations. Of course, somebody is responsible for writing those stored-procs. Any volunteers? Easy now, don’t raise your hands all at once! Generally, this responsibility falls to the DBA(s) or DBDev(s).The problem at-hand, in my current situation, was that of “moving a sub-tree”, i.e. taking a node and all its descendants, and moving it to place it under another “parent” node. In some models, and/or in some languages, this is a simple recursive operation. However, SQL is not spectacular at recursion — after all, we’re working in a relational engine — so let’s try to play to its strengths:
This is a straightforward look at one of the major hierarchical models in relational design. Well worth a look.
1 CommentDev Nambi has a new open-source project:
Databases store text, and the best way to manipulate text is to use a regular expression (‘regex’). Using regular expressions in SQL queries has been possible in many database engines for decades.
Now you can use regular expressions in SQL Server queries, too. I’ve created an open-source project, sql-server-regex, that lets you run regular expressions in T-SQL queries using scalar and table-valued functions.
This is a set of CLR functions which use the built-in .NET regular expressions functionality. That makes it pretty easy to see how the code works.
Comments closedVladimir Oselsky builds a date dimension in T-SQL:
Before we get into discussing how to create it date dimension and how to use it, first let’s talk about what it is and why do we need it. Depending on who you talk to, people can refer to this concept as “Calendar table” or “Date Dimension,” which is usually found in Data Warehouse. No matter how it is called, at the end of the day, it is a table in SQL Server which is populated with different date/calendar related information to help speed up SQL queries which require specific parts of dates.
In my case, I have created it to be able to aggregate data by quarters, years and month. Depending on how large your requirements are it will add additional complexity to building it. Since I don’t care about holidays (for now at least), I will not be creating holiday schedule which can be complicated to populate.
I love date dimensions, even on non-warehouse databases, because it’s an easy way of providing additional context to time series data. Think about graphing orders per day in an industry with weekday-versus-weekend trends; a date dimension lets you strip out weekends (maybe plotting them separately) or even lets you build day-of-week analysis for each day, or looking at week of the month, etc. You might also be interested in computing holidays.
Comments closed