Voice of the DBA

Tag: T-SQL

Setting up a Full Text Index–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a question recently on Full Text Search. I knew the answer, but to test some code, I had to reset up an index, which took just a minute, but I decided to write about it. This post gets the basics of setting an index.

Setup

A full text index allows you to search a little more freely than standard T-SQL with a LIKE or wildcards. It’s useful for going through large amounts of text, mainly hundreds or thousands of words.

To get started, you need to know a few things. First, this system in modern SQL Server (2008+) is set up on all instances. You don’t enabled FTS like you would for In-Memory OLTP tables or FILESTREAM.

Next, you need a catalog for the FTS indexes, which is a logical container.

Next, a table with data.

Finally, you create the index. In this post, I’ll look at SSMS and the GUI. In another one, I’ll look at the T-SQL itself.

Using SSMS

The quick way to get started is to right click your table. As noted, the database is already enabled for FTS. In the right click menu, there is a Full-Text index section, and under there there is a “define” choice, as shown here.

Click that and the wizard will start. The intro screen appears, and you can click next.

A unique index is required, and the next step let’s you pick the one you want to use. This allows the various FTS query items to return the key value used here in the index. In my case, I only have a PK, but if you have unique indexes, you can choose any one.

The column(s) you want to index need to be selected. I only have one here, but you can choose any, or multiple, character or image based columns. Image would be binary columns that might contain something like a Word document.

The statistical semantics are used to extract key phrases from documents. For basic FTS of character data, you wouldn’t use this, but if you are searching things like PDFs, Word, etc., you might enable this.

If you want to track changes as the data changes and update the index, choose auto or manual.

The next page is where you assign this to a catalog. This is where you can create one if necessary. You can also choose a different filegroup for storing the index, and set the sensitivity for accents and choose a stop list. If you don’t know these terms, something to look up (and blog about).

The next step is the population schedule. This is the place where you decide when the index is updated. You can allow the system to run when it detects changes if this isn’t a lot of data, or you can schedule this. Large indexes with lots of data can take time and consume resources, so some instances need this scheduled during low workload hours.

I tend to ignore this for demos.

You get a final summary of everything. You can check each item and go back if necessary to fix something. Or click finish.

For my small demo table, this completed quickly.

From here, I can run queries using CONTAINS() or other terms, as shown below.

If you want to follow along, here’s the table setup I used.
```
CREATE TABLE FTSTest (

myid INT NOT NULL IDENTITY(1,1) CONSTRAINT FTSTestPK PRIMARY KEY

, mydata VARCHAR(MAX)

)

GO

INSERT dbo.FTSTest (mydata) 

VALUES ('Now is the time for all good men to come to the aid of their country'),

('there are a number of men who are good in the world'),

('good for men that help others'),

('If there are men who others might consider good, we should support them'),

('Good is a concept that is sometimes hard for men to comprehend'),

('Good is a concept that is sometimes hard for anyone to comprehend')

GO
```
That’s a quick setup. I’ll look at queries and the T-SQL setup in another post.

SQLNewBlogger

This post was the first of a few that I made after solving a problem for someone. I took my 10 minutes of code writing and added about 10 minutes each for a few posts, including this one.

A good way to break down a problem into a few posts and get a few weeks worth of content that shows your knowledge and learning.
June 9, 2021
2020 Advent of Code Day 6
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 6. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

This is another weird string grouping issue. The data load is a mess, meaning that there are groups of data I need to consider, and the groups are separated by blank lines. However, each group has multiple lines.

Ugh.

Easier in Python, where I can load the data line by line and break things. I do that with this code:
```
for answers in open("2020\day6\day6_data.txt").read().split("\n\n"):
```
In SQL, it’s harder. I bulk load into a table, the cursor through the data.
```
DECLARE pcurs CURSOR FOR SELECT lineval FROM Day6 ORDER BY linekey;

DECLARE
     @val VARCHAR(1000) = ''
   , @groups VARCHAR(1000);

OPEN pcurs;

FETCH NEXT FROM pcurs

INTO @val;

SET @groups = '';

WHILE @@FETCH_STATUS = 0

BEGIN
     IF @val > ''
         SELECT @groups += ' ' + @val;
     ELSE
     BEGIN
         INSERT dbo.Day6_Groups (groupanswers) VALUES (@groups);
         SET @groups = '';
     END;
     FETCH NEXT FROM pcurs
     INTO @val;

END;
     INSERT dbo.Day6_Groups(groupanswers) VALUES (@groups);

DEALLOCATE pcurs;

GO
```
Once that is done, I again have to do things differently. Python is easy, where I count the values and add them up.
```
answers = set(answers.replace("\n",""))
part1 += len(answers)
```
In SQL, I need to distinctly find the values, for which I need a function of some sort.
```
UPDATE dbo.Day6_Groups
  SET deduppedanswers =  DBO.REMOVE_DUPLICATE_INSTR(1,groupanswers)
```
Once that’s done the answer is the sum of lengths.

Part 2

More complex here. Now I need to match up the common answers among the groups. In Python, this isn’t bad. I use the intersection method to find out what matches between the groups.
```
for answers in open("2020\day6\day6_data.txt").read().split("\n\n"):
    matches = set.intersection(*[set(answer) for answer in answers.split()])
    part2 += len(matches)
print("Part 2: ", part2)
```
Fairly simple here, with the grouping of the answers in a set.

SQL is hard.I reloaded the data, and separated each group by a comma. This gave me data I could split up, keeping each group in a group.

From here, I had a CTE for this, another to put these into groups of 2 strings by groupID. I then did a comparison for common characters across each group of 2 strings. This gave me partial matches, and I then compared all these in a group, taking the minimum number of matches. From here, I summed up the count of all the matches.
May 24, 2021
Computed Columns for Grouping–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran into someone trying to do some grouping for an accounting report. In this case, they had a number of criteria where certain accounts were used to produce groups of values. For example, I might have these criteria:
- Accounts from 400000 to 490000 are Operating Expenses
- Accounts from 500000 to 502999 are Personal Expenses
- Accounts from 503000 to 503999 are Materials and Services
There would be other items, but if I have accounts and values, how do I sum and group in these ways?

There are a few possibilities, but since I might move accounts around, I thought that computed columns might help with grouping here. This post looks are a way you can do this.

Imagine I have some values like this:
```
CREATE TABLE BudgettoActual

(accountid INT

, budget NUMERIC(10,2)

, actual NUMERIC(10,2)

)

GO

INSERT dbo.BudgettoActual
     (accountid, budget, actual)

VALUES
     (400010, 300, 299),
     (501010, 100, 102),
     (502010, 200, 150),
     (503010, 400, 150),
     (507010, 800, 150)

GO
```
Now I can use a SUM with a CASE, but that makes a complex query. One way to simplify this for others is to use a computed column in the table that might include my criteria. I can use a CASE statement to create my groupings.
```
ALTER TABLE dbo.BudgettoActual 

ADD AccountGroup AS CASE 
    WHEN accountid>= 400000 AND accountid < 489999 THEN 1
    WHEN accountid>= 501000 AND accountid < 503000 THEN 2
    WHEN accountid>= 503000 AND accountid < 504000 THEN 3
    WHEN accountid>= 507000 AND accountid < 508000 THEN 4
  ELSE 5
  END
```
With this, I see this in my table:

Now I have these different groups that I can use in a query and group by them. For example, I can get a quick look at my different categories with this code. I’ve put the categories in a CASE in the column list, but it could come from another table.
```
SELECT
          CASE
              WHEN ba.AccountGroup = 1 THEN
                  'Operating Revenues'
              WHEN ba.AccountGroup = 2 THEN
                  'Personal Expenses'
              WHEN ba.AccountGroup = 3 THEN
                  'Materials and Services'
              WHEN ba.AccountGroup = 4 THEN
                  'Reserves'
          END 'Object'
        , SUM (ba.budget) AS Budget
        , SUM (ba.actual) AS actual

FROM     dbo.BudgettoActual AS ba

GROUP BY ba.AccountGroup;
```
This gives me a look at my financial numbers quickly.

Rather than numbers, I could have used the title in the computed column, but that causes issues with ordering. With these numbers, I can choose numbers that are the order I need them in for a report (which can matter for financial reporting).

I’d prefer to use a separate table mapping the AccountGroup to a title and then joining that in my report.

This isn’t the only way to do this, but it is one way to handle complex grouping in a way that can make it easier for clients that might need to query this data.

SQLNewBlogger

This post took me about 10 minutes to write, but about 15 minutes to setup, which might be most of a writing session for a blog. However, it’s a good showcase of a creative way to solve an issue.

Any of you could put together a similar post on a query issue you’ve run into and want to share.
May 19, 2021
2020 Advent of Code – Day 5
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 5. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

This is an interesting problem, and one that’s simpler than it appeared at first. I started down the path of some hash bucket thing, moving to calculate rows before I got to the end and realized this is really a binary problem.

As a result, after I loaded the data, I started here:
```
SELECT 
   (SUBSTRING(d.SeatCode, 1, 1) * 64) +
   (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
   (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
   (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
   (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
   (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
   (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
   (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
   (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
   (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
  FROM dbo.Day5 AS d
```
Here you can see I broke this into two binary sections. The first 7 characters get you a row code from 0 to 127. The last 3 values get you a 0 to 7 value. I should have been clued in when I saw the 0s here. In any case, this gets me the two binary values.

The seat code is the row multiplied by 8 and then adding the seat. I took the above query, wrapped it with a CTE and then ordered by seat codes. This gave me the highest value, which solved the problem.
```
WITH cteAirplane( ROW, seat)

AS

(SELECT 
   (SUBSTRING(d.SeatCode, 1, 1) * 64) +
   (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
   (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
   (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
   (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
   (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
   (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
   (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
   (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
   (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
  FROM dbo.Day5 AS d
  --ORDER BY row desc
  )
  SELECT (row * 8)+seat AS seatID
  FROM cteAirplane
  ORDER BY seatID DESC
```
Part 2

The second part is a different problem. Now I need the seat codes, but I’m looking for a gap here. Meaning a missing seat code.

I decided to use LAG here. I altered my first CTE to calculate the seat code directly rather than returning the row and seat. Then I added this CTE:
```
cteValues (SeatID, diff)

AS

(

SELECT seatid, SeatID - LAG(SeatID,1) OVER (ORDER BY SeatID) AS diff

FROM cteAirplane

) 
```
This CTE found the difference between each subsequent Seat codes using the OVER() clause. My final query was looking for a diff > 1, which returned 1 row. That was the answer.
May 17, 2021