SQL Server

Extracting All The Words From a String In A SQL Server Database Query

It is easy enough to extract the first word from a string in a database query, but what if you need to separate all words in a given string into a list of separate words?

This is a job for Common Table Expressions (CTEs) as they enable us to repeatedly read a single record while changing it each time.

Here is a SQL Server example which takes a string variable and spearates it into inidividual words. We use a combination of CHARINDEX, LEFT and RIGHT functions to achieve it.

If you work with SQL Server based applications then probably quite a few!

No matter what the application you are working with (CRM, Sales Order Processing, Financial Accounting, Contact Management, Booking System (to name but a few!)) there is a database behind it. Most common applications will store their data in either a Microsoft SQL Server Database or an Oracle Database (of course there are other relational database platforms out there).

Plotting Charts With SQL Server R Service

Yesterday I posted a blog article about the R Service available in SQL Server 2016 and showed an example of using regular expressions with the stringr R library.

In this blog I am talking about how R can be implemented for chart plotting purposes in SQL Server.

One More Language to Learn!

So, we have had SQL, TSQL, MDX, DMX, DAX to keep us busy in the SQL Server world for years now so I guess it was about time another language came along to join them!

The SQL Server R Service

The R Service enables integration of R scripts into TSQl scripts. 

Using CROSS APPLY In SQL SELECT Statements

In this article I  take you through the following:

• CROSS APPLY
• OUTER APPLY
• CROSS APPLY with User Defined Table Valued Functions

When Should We Use The CROSS APPLY Statement

The CROSS APPLY statement behaves in a similar fashion to a correlated subquery, but allows us to use ORDER BY statements within the subquery. This is very useful where we wish to extract the top record from a sub query to use in an outer sub query.

Counting Members in an MDX Query

MDX is a challenging query language. It is completely different to the SQL query language despite having a SELECT statement!

MDX queries are all about hierarchies, measures and members and when it comes to carrying out tasks that are simple in SQL it may not be so simple in MDX.

Most of the time when we work with MDX queries we are interested in bringing out aggregate values that are stored in our cube, but occasionally we may want to derive values from the stored cube data.

SQL Server Locking & Blocking

I’m sure all of you who work with SQL Server systems will have experienced the frustration of trying to retrieve a set of records only to find that someone is updating/deleting/inserting one or more records that are matched by your query, and locks prevent you from accessing part or all of your record set.

SSMS 2016 New Feature – Compare Query Execution Plans

Management Studio in SQL Server 2016 has introduced a great new feature: Compare Showplan.

SQL Queries to Retrieve Records from Multiple Tables

Relational databases store data in tables and we can retrieve information with SQL SELECT statements accessing one or more tables.

Display SQL Server Index Details – Type, Key Columns, Included Columns

Have you ever wanted a query that would give you a list of all the indexes that exist in your database along with details of the index type and all the columns that are a part of the index key and all included columns?

Well have a look at this query. It might help: