Voice of the DBA

Tag: T-SQL

Full Text Search – CONTAINS
I’ve been working on a new presentation for full text search and brushing up on some of my T-SQL operators. Part of my talk goes into the CONTAINS operator, which is one of the full text search keywords you need to know.

This operator is only used with full text indexes, so if you have a column that isn’t full-text indexed, it returns an error. If I issue this:
```
SELECT *
 FROM dbo.salary
  WHERE CONTAINS(empname, 'Steve')
```
I get this:

Msg 7601, Level 16, State 2, Line 3

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘dbo.salary’ because it is not full-text indexed.

I have a table that is full text indexed and I can issue a basic query, which looks like so many other T-SQL queries.
```
SELECT
 name
 FROM authordrafts
 WHERE CONTAINS(*, 'AlwaysOn')
 ;
 go
```
This returns me all the rows where the columns in the full-text index (I used the star, *), have the term “AlwaysOn” in them. In this case, I’m hitting a FileTable table with lots of whitepapers in there.

This query is essentially a LIKE search, but it isn’t doing character matching. Instead it is working with those keywords in the full text index. I’ve used a simple search above. I could replace the * with the column, in this case the file_stream column.
```
SELECT
 name
 FROM authordrafts
 WHERE CONTAINS(file_stream, 'AlwaysOn')
 ;
 go
```
I could also use a prefix term and the * wildcard, similar to LIKE.
```
SELECT
 name
 FROM authordrafts
 WHERE CONTAINS(file_stream, 'Always*')
 ;
 go
```
These match other rows where “always” is the document, which matches “always” as a standalone word as well as “alwayson” as a term.

I could also limit the search to particular columns, using parenthesis and commas to separate them out. The BOL example from the CONTAINS page does a nice job of showing this.
```
Use AdventureWorks2012;
GO
SELECT Name, Color
 FROM Production.Product
 WHERE CONTAINS((Name, Color), 'Red');
```
This is just a very basic look at CONTAINS. In another post, I’ll look at a few more possibilities with this term.
September 27, 2012
Transferring Table Types
An interesting idea. I saw this question asked after I was playing with table types a bit. “Can you move a table type between schemas?”

Suppose I had two schemas:
```
CREATE SCHEMA OldSchema
;
GO
CREATE SCHEMA NewSchema
;
GO
```
In one of them, I create a table type and a procedure:
```
CREATE TYPE OldSchema.MyTable AS TABLE
( IDCode INT
, Location VARCHAR(200)
)
;

CREATE PROCEDURE OldSchema.MyProc 
AS
 SELECT * FROM dbo.MyLogger
;
```
There’s nothing fancy here. Just two objects created in one schema. I now have the need to move these to the other schema. Perhaps it’s a mistake. Perhaps I have developers working in one schema and I do integration testing in the other schema. In any case, it’s easy to move the proc with the ALTER SCHEMA syntax:
```
ALTER SCHEMA NewSchema TRANSFER OldSchema.MyProc
;
```
I can easily script something to move multiple procs, but if I do this:
```
ALTER SCHEMA NewSchema TRANSFER OldSchema.MyTable
;
```
I get this:

Msg 15151, Level 16, State 1, Line 1

Cannot find the object ‘MyTable’, because it does not exist or you do not have permission.

I know it’s there; I just created it. What’s wrong?

The problem is that this isn’t an object per se, but a type. As a result, to move a type, I need to use a different syntax:
```
ALTER SCHEMA NewSchema TRANSFER type::OldSchema.MyTable
;
GO
```
That works fine and the type has moved. The class attribute of the notation is

CLASS::Schema.Object

I haven’t found good documentation of this, but there are numerous examples in BOL that show this is how you address various “types” in SQL Server.
September 25, 2012
Creating a User Defined Table Type
I saw a post about a user defined table type in SQL Server and I was sure it was a typo. I kept thinking the poster meant table variable, but when I searched the term in Books Online, I was surprised to find User-Defined Table Types as an entry.

These types are essentially templates that you can build for easier code reuse. They work in procedures and functions, or even as table variables. The CREATE TABLE syntax includes allowances for using these types.

I can see this as being valuable when you have a structure that you want to pass into a module of some sort in multiple places and don’t want to have to include the code each time. I’m not sure it’s a great benefit, but it does prevent subtle mismatches like one module using varchar(50) for a column and another using varchar(200).

A simple create for this type would be:
```
CREATE TYPE StateTbl AS TABLE
( StateID INT
, StateCode VARCHAR(2)
, StateName VARCHAR(200)
)
;
```
This gives me a template I can use. Note that I can’t add rows to this table:
```
INSERT StateTbl SELECT 1, 'CO', 'Colorado';
```
I get this error:

Msg 208, Level 16, State 1, Line 1

Invalid object name ‘StateTbl’.

It’s not an object yet. I need to instantiate an object based on this template. I can do that in a procedure:
```
CREATE PROCEDURE SortStates
  @S StateTbl READONLY
 as

SELECT StateName
 FROM @s
 ORDER BY StateName
RETURN 0
;
GO
```
Fairly simple stuff. I can easily call this procedure, but I need a set of parameters first.
```
DECLARE @p TABLE (id INT, scode VARCHAR(3), sname VARCHAR(20))

INSERT @p
 VALUES (1, 'NC', 'North Carolina')
      , (2, 'VA', 'Virginia')
      , (3, 'CO', 'Colorado')
; 
EXEC SortStates @p
```
However this doesn’t work. The table isn’t compatible (I did that on purpose). Let’s clean it up.
```
DECLARE @p TABLE StateTbl
   (StateID INT
   , StateCode VARCHAR(2)
   , StateName VARCHAR(200))

INSERT @p
 VALUES (1, 'NC', 'North Carolina')
      , (2, 'VA', 'Virginia')
      , (3, 'CO', 'Colorado')
; 
EXEC SortStates @p
```
It still doesn’t work. There’s a binding here. I need to use the (cleaner) AS syntax for declaration.
```
DECLARE @p as StateTbl

INSERT @p
 VALUES (1, 'NC', 'North Carolina')
      , (2, 'VA', 'Virginia')
      , (3, 'CO', 'Colorado')
; 
EXEC SortStates @p
```
This returns results:

This means that you can use these types to create cleaner code, and enforce some standards (preventing things like people declaring columns with different lengths. However it also means that you have another “type” to manage and ensure everyone is using.

I’m not sure how useful this is, but it is a neat little construct.
September 19, 2012
Finding DDL Triggers
Triggers are the types of objects in SQL Server that are easy to lose track of. There isn’t an obvious way to tell that a table has a trigger on it and since most tables don’t have triggers, this is one of the things people often miss when troubleshooting unexpected results.

DDL triggers are worse, since they aren’t tied to particular tables, but rather events. How can you find DDL triggers in your environment?

There are a few ways. I’ll show you visually and in code.

The GUI

I like the Management Studio GUI to find information, and to quickly get code written. With SQL Prompt installed, I can get great intellisense that makes it easy to find parameters, names, objects, etc. I don’t like to run the actions from SSMS, but rather use the Script button and save the code, and execute it in a query window.

In looking for server-side triggers, there is a “Server Objects” folder in the tree.

Here is where you find your backup devices, endpoints, linked servers, and server level triggers. In this case, I can expand the folder (shown above) and find the trigger I created recently.

At the database level, there’s a similar structure. Inside of a database, we find there is a programmability folder, which contains all the code items I can create in a database.

In here we can see there is a Database Triggers item, and inside there are two triggers that I setup inside this database.

You have to go look for these triggers, but if you’re wondering if they exist, you can find them here.

Code

The best way to look for triggers quickly is with code. Without resorting to BOL, I suspected there was some DMV that contained trigger code. As you can see below, I was right as typing SSF (a shortcut in Prompt), followed by “master.sys.server_t” got me this result:

If I then examine the results from the server_triggers table, I get my one trigger at the server level.

This is only part of the information needed as the server_trigger_events table has the events that will fire this trigger. I can query that to see I only have one event here:

If I join in the events, then I can clean this up and get this:
```
select
  t.name
, t.object_id
, t.is_disabled
, te.type_desc
 FROM master.sys.server_triggers t
   INNER JOIN master.sys.server_trigger_events te
     ON t.object_id = te.object_id
```
Which shows me the trigger, its ID, and the event’s.
September 13, 2012