Counting logical lines of T-Sql code in a DacPac

There are two general ways of counting lines of code, the first is basically just count the lines in each file that make up a project, the second is to count the logical statements as specified by the language.

Counting lines of T-SQL code is typically quite hard as, personally, I am interested in things like stored procedures and functions and their statements as a metric for lines of code rather than including columns and tables as, for me, they are separate.
I thought this would be a great example of how to use the DacFx API and the T-Sql script dom together to create a tool that I, and hopefully the other person who has visited this blog, would find useful!

What this tool does is count the logical lines of code in any stored procedure, function (scalar or table-valued) and dml triggers, if anyone thinks of other items of code then it is open source so feel free to check it out, add it in and submit a pull request, it is really trivial to add other types of code.

To count the number of statements, I use the T-Sql visitor class to break the each code file into statements.

Feel free to download the tool from:

https://raw.githubusercontent.com/GoEddie/DacFxApi/master/Download/CountLinesOfSqlCodeInDacPac.zip

The source code is in my DacFx Api github repro:

https://github.com/GoEddie/DacFxApi

How does this work?

The first step is to open the dacpac file:

var model = new TSqlModel(fileName, DacSchemaModelStorageType.File);

I then need to query the model for the Objects of the specific type I am looking for:

model.GetObjects(DacQueryScopes.Default, Procedure.TypeClass)
model.GetObjects(DacQueryScopes.Default, ScalarFunction.TypeClass)

Each of these return an IEnumerable<TSqlObject> which I pass to a helper function to query for the name and the script:

Name = codeUnit.Name.ToString()
var script = “”;
if (codeUnit.TryGetScript(out script))

return script;

Once I have the names and contents of all the scripts I am interested in, I need to parse the scripts and break them into statements. This is made really simple using the TSqlFragmentVisitor class.

The way the TSqlFragmentVisitor class works is that you create a class that inherits from it:

public class SqlVisitor : TSqlFragmentVisitor
{
}

There are then a set of methods you can override which are called everytime a statement of that type is encountered, so for example if you use:

public override void Visit(TSqlStatement node)
{
}

This will be called for every statement, it is what I use in this tool as I don’t care what type of statement it is, as long as it is a statement.

There are other override-able methods such as:

public override void ExplicitVisit(SelectStatement node)

This is only called for select statements so it really is powerful and so simple to use!

Once I have the statement count, I tally up the lines of code for all code units and I end up with the total lines of logical code for just code units in a dacpac file!

This is obviously really basic, if you have an example where the lines of code is incorrect, send it to me and I will fix the tool or fix it yourself and send a pull request on github!

Happy DaxFx + TransactSql ScriptDom’ing!

 

p.s. to see how many lines in each code unit, use the /detailed switch!

Dac Fx Api – Use it, write better tools for Sql Server!

The DacFx Api is an api for querying the object model that both DacPac files and SSDT use, it is a giant leap forward in the available tooling for Sql Server and hopefully there will be a raft of great tools to make T-SQL development better.

I really enjoy writing c# code. I typically use visual studio with Resharper and add NUnit and Moq to my test projects and this setup makes developing and testing really simple and removes a lot of the boring repetition you sometimes get. I also love developing T-SQL but the development environment is nowhere near as smooth.

When I first started looking at the DacFx api, I was a little confused as to how it worked, even after reading the introductory blog (http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.aspx) and when I finally realised what the implications were of the painless sounding sentence “The public model API is loosely typed: the TSqlModel contains loosely typed TSqlObjects that represent all the elements in your schema.” my heart sunk a little as it felt very similar to every object being returned as a System.Object and then having to cast it to the correct type (you don’t by the way, you leave it as the TsqlObject).

What this means is that all objects in the model, a table, schema, procedure etc are all the same type, a TSqlObject. To get any information such as what columns belong to what table or index or whether the index is clustered or not you need to query the objects and ask for the information you want, in reality what you have with the DacFx Api is a queryable set of classes, you just have to know what you have and what you want and although it is a different paradigm to pretty much every API I have ever seen, it is fairly straight forward. I have put an example application which opens a dacpac file and dumps out a list of indexes which gives an overview of how to use the api to get the information you want.

To open a dacpac file and get a TSqlModel object which is the entry point to the API you just need to call:

var model = new TSqlModel(filePath, DacSchemaModelStorageType.File);

When you have the model you can then query it for all the objects you would like, if you want all the indexes in the model just do:

model.GetObjects(DacQueryScopes.Default, Index.TypeClass)

GetObjects searches for and returns a list of specific object types, the query scope means all objects, you can limit it to just system objects or just user objects etc. Finally the TypeClass causes a list of just Indexes to be returned.

The way the TypeClass works is that every object such as a table, index, column etc has a TypeClass and also on the static class that has the TypeClass such as Index or Table there are a number of properties which allow you to access the actual properties you want. If this sounds a little strange, I think it is, but it is fairly straight-forward with an example! We will call the class that holds the TypeClass and other property classes the container class as that is what msdn seems to call them. If you retrieve a list of all the indexes and you have a TSqlObject that you got by using the Index.TypeClass (as above) then you can query the index for its properties. The Index container class has, amongst others, these properties:

Index.TypeClass

Index.AllowPageLocks

Index.Clustered

You can then use these properties to get the value you want from your index TSqlObject instance, so if you do:

foreach (TSqlObject index in model.GetObjects(DacQueryScopes.Default, Index.TypeClass ){

var isClustered = index.GetProperty<bool?>(Index.Clustered)

}

You will get a nullable bool saying whether the index is clustered or not. If you do not know the type of the object you want you can call the non-generic version index.GetProperty(Index.Clustered). The example at: https://github.com/GoEddie/DacFxApi/tree/master/DumpIndexProperties (repo: https://github.com/GoEddie/DacFxApi/) shows some extra information like how to query the relationships to get the columns and included columns that are on the index (in, on, used by??) and how to generate the T-SQL script to deploy the index.

I strongly believe that as Sql Server developers we have the ability to really help move our tooling forward and if you throw into the mix the TSql scripting stuff, the next few years are really going to be exciting times for us Sql devs!

Setting breakpoints in SSMS

Setting breakpoints in SSMS is difficult and a bit of a pain, if you use the object explorer to open a procedure or function and set a breakpoint, SSMS will happily show it as being set but when you debug the code, you get nothing. The reason is that what you need to set the breakpoint on is a temporary file which only SSMS knows about. The file you have set the breakpoint, although looks right is like creating a csharp file on your desktop with the same text as in a code file in a solution, setting a breakpoint and then running your solution and wondering why the debugger didn’t stop in the right place.

This shows a breakpoint which looks like it is set but it is not actually set:

breakpoint will never be hit :(

breakpoint will never be hit 😦

Breakpoint will not be hit, notice the name of the file SQLQuery1.sql.

There are a couple of ways to set the breakpoint in the actual file, the first is to debug your code and step into or F11 into the place where you want the breakpoint, when you do that, SSMS opens the code file for you but instead of using a name like SQLQuery1.sql it opens a mssql:// path:

breakpoint will always be set :)

breakpoint will always be set 🙂

If you notice the name of the tab it shows the file name as the cryptic: “mssql://WIN-UJM5DI37BMD/test/?/=261575970”.

The file name may well look cryptic but it is quite simple, it is the name of the instance then the database, in this case “test”, followed by the object id of the item you want the breakpoint set it.

If we look at the breakpoints window, we can see the same file name and also do some cool stuff like set conditional breakpoints or jump to the breakpoint. This takes you to the file where you can set more breakpoints of course. To get to the breakpoint menu, go to Debug –> Windows –> Breakpoints. You need to have at least one active script window open, I am not sure why, you just do.

breakpoint window

 

Setting your own breakpoints

To set your own breakpoints either get SSMS to show you the debuggable version of the code or add the breakpoint manually, I normally get SSMS to set one and then copy it and modify it to get it set to the object and location I like. To set one manually, in the breakpoint window, choose New —> Break at Function and enter the filename and line and column (I know, more pain!). If you try to set a breakpoint at an invalid location you will get a failure message.

 

Use the strange naming to set a breakpoint

Use the strange naming to set a breakpoint

I hope this makes debugging in SSMS easier, if anyone has any other tips please share!

Speed up TDD in SSDT (and other acronymns)

tldr: I have written a free vs package to deploy files quickly from SSDT to sql server, to speed up the red-green tdd cycle.

I find that writing code for sql server using TDD can be a little frustrating, the process I have is that I use SSDT to manage and deploy database projects including tests written using tSQLt, using the standard red-green cycle:

  1. In SSDT, Visual Studio 2013 currently, write a tSQLt test
  2. In SSDT I write an nunit test to call tSQLt.Run passing in the name of the new test.
  3. I then deploy the tSQLt test and run it and expect it to fail (Red)
  4. I then write the code which satisfies the test
  5. I then deploy the code and watch for the test to pass (Green)
  6. If the test doesn’t pass, I either change the code and re-deploy or change the test and re-deploy that
  7. The cycle continues until the test is passing correctly.

The frustrating part, for me is that building the project and deploying, although I have automated it takes quite a long time (45 seconds to 1 minute for a reasonably sized project).

To help this process I have written the Sql Tdd extension for visual studio. If you install it from:

http://goo.gl/U3s0il

When you right click on a .sql file, you are given two new options, the first option is to “Generate Deploy Script” which creates a .sql file which includes a drop and create script.

The second option, generates the script in the background and deploys it to your test sql server (there is a configurable connection string).

The two options replace any $(Sql Cmd Variables) that have been configured in the project using the default values.

This isn’t intended to replace the build + publish but supplement it in that you can quickly deploy single files to help speed up the red-green cycle.

Once you have the test working I would certainly recommend, building, re-deploying and re-running your tests to verify that the whole project is correct but the time you spend waiting for ssdt to update it’s internal model and deploying is greatly reduced.

Not convinced?

To compare working with ssdt with and without this plugin, here is a comparison.

To deploy a file in ssdt:

  1. Write the changes
  2. Build the solution
  3. Fix any build errors, anywhere in the solution
  4. Perform a schema comparison
  5. Decide what you want to include in the update
  6. Update the destination
  7. Re-perform the schema comparison

You could just have done a publish at step 4 but that wouldn’t have been as compelling!

To deploy a file with this plugin:

  1. Write the changes
  2. Build the solution if you want to validate the model or just save the files
  3. Right click the files in solution explorer and choose “deploy file”

simple!

This is a really simple plugin, so don’t expect bells and whistles like only activating on certain projects I am afraid (maybe a later version) – all messages are sent to the output window so check that if you want to see what is going on.

This is published on github (http://github.com/GOEddie/SSDTExtension), if you like it and want it to do more, either ask me or fork it and do it!

There are a few articles and blogs on SSDT but if you have any tips on making the most of it, please please do share with everyone!

 

Disclaimer: I run it on my work machine and home machine and seems to work, please don’t cry if you get any issues, let me know and I will try to fix them!

How does “select getdate()” get the time?

getdate is an interesting function to look at as it is some data that sql has to ask the o/s for, it isn’t something that it can (or should) store itself.

If we look at the windows api’s that it could possible use, we have these possibilities:

 

GetLocalTime

GetSystemTime

GetSystemTimeAsFileTime (Sounds unlikely)

GetTickCount (Unlikely but would probably be used if sql stores the startup time and the current time is based from that – let’s hope not!)

 

Using cdb, we get this stack trace (Sql 2012 32 bit):

KERNELBASE!GetLocalTime (FPO: [1,17,4])
sqllang!utgettime+0x10f (FPO: [2,23,4])
sqllang!D8GetDate+0x10 (FPO: [0,0,4])
sqlTsEs!CEsExec::GeneralEval4+0xbc (FPO: [Non-Fpo])
sqllang!CXStmtAssignBase::XretExecute+0x153 (FPO: [Non-Fpo])
sqllang!CXStmtSelectWithoutQuery::XretExecute+0x129 (FPO: [Non-Fpo])
sqllang!CMsqlExecContext::ExecuteStmts<1,1>+0x352 (FPO: [Non-Fpo])
sqllang!CMsqlExecContext::FExecute+0x878 (FPO: [Non-Fpo])
sqllang!CSQLSource::Execute+0x7d5 (FPO: [3,41,0])
sqllang!process_request+0x3fa (FPO: [Non-Fpo])
sqllang!process_commands+0x38c (FPO: [Non-Fpo])
sqldk!SOS_Task::Param::Execute+0x292 (FPO: [Non-Fpo])
sqldk!SOS_Scheduler::RunTask+0xa2 (FPO: [Non-Fpo])
sqldk!SOS_Scheduler::ProcessTasks+0x316 (FPO: [2,15,0])
sqldk!SchedulerManager::WorkerEntryPoint+0x2e7 (FPO: [Non-Fpo])
sqldk!SystemThread::RunWorker+0xae (FPO: [Non-Fpo])
sqldk!SystemThreadDispatcher::ProcessWorker+0x2fe (FPO: [2,21,0])
sqldk!SchedulerManager::ThreadEntryPoint+0x20b (FPO: [Non-Fpo])
KERNEL32!BaseThreadInitThunk+0xe (FPO: [Non-Fpo])
ntdll!__RtlUserThreadStart+0x20 (FPO: [SEH])
ntdll!_RtlUserThreadStart+0x1b (FPO: [Non-Fpo])

 

Interesting things here:

sqllang!CXStmtSelectWithoutQuery::XretExecute – Run a select without an actual query.

sqllang!D8GetDate – This makes sense as it returns a datetime which is 8 bytes, I couldn’t find any other D ?? GetDate functions using the public symbols.

There are two variants of GetDate in sqllang, D8GetDate and D8GetDateUTC – they are small functions and D8GetDate passes a 0 to sqllang!utgettime, D8GetDateUTC passes a 1. (There is also a D8GetDateOnly which also passes a 1 so asks for the UTC date).

utgettime takes a pointer to a SQLDATE (SQLDATE*) and an int, the int (as mentioned) determines which date to ask the o/s for.

utgettime uses the 1 or 0 to determine whether it calls GetLocalTime (0 non UTC) or GetSystemTime (1 UTC)

select getutcdate() obviously resolves to utgettime( &date, 1)

 

So there you have it, GetDate() uses the windows kernelbase (forwarded from kernel32) GetLocalTime and GetUTCDate() uses GetSystemTime

CXVariantPerformConvert::Convert

DLL: sqlTsEs.dll

Class: CXVariantPerformConvert<Type1, Type2>

Section: Types

Description: There is a group of classes (probably c++ templates) which take two types and convert from one to another, these are called when the sql CAST is used – some conversions like smallint to int happen without calling these functions. The full list of types is:

CXVariantPerformConvert<108,104>::Convert
CXVariantPerformConvert<108,48>::Convert
CXVariantPerformConvert<108,52>::Convert
CXVariantPerformConvert<122,127>::Convert
CXVariantPerformConvert<122,48>::Convert
CXVariantPerformConvert<122,52>::Convert
CXVariantPerformConvert<122,56>::Convert
CXVariantPerformConvert<127,104>::Convert
CXVariantPerformConvert<127,122>::Convert
CXVariantPerformConvert<127,48>::Convert
CXVariantPerformConvert<127,52>::Convert
CXVariantPerformConvert<127,56>::Convert
CXVariantPerformConvert<127,60>::Convert
CXVariantPerformConvert<127,62>::Convert
CXVariantPerformConvert<165,104>::Convert
CXVariantPerformConvert<165,36>::Convert
CXVariantPerformConvert<165,48>::Convert
CXVariantPerformConvert<165,52>::Convert
CXVariantPerformConvert<167,104>::Convert
CXVariantPerformConvert<167,127>::Convert
CXVariantPerformConvert<167,48>::Convert
CXVariantPerformConvert<167,52>::Convert
CXVariantPerformConvert<167,56>::Convert
CXVariantPerformConvert<189,52>::Convert
CXVariantPerformConvert<189,56>::Convert
CXVariantPerformConvert<189,60>::Convert
CXVariantPerformConvert<231,104>::Convert
CXVariantPerformConvert<231,48>::Convert
CXVariantPerformConvert<231,52>::Convert
CXVariantPerformConvert<231,56>::Convert
CXVariantPerformConvert<231,62>::Convert
CXVariantPerformConvert<40,58>::Convert
CXVariantPerformConvert<40,61>::Convert
CXVariantPerformConvert<48,127>::Convert
CXVariantPerformConvert<48,62>::Convert
CXVariantPerformConvert<52,127>::Convert
CXVariantPerformConvert<52,48>::Convert
CXVariantPerformConvert<52,62>::Convert
CXVariantPerformConvert<56,127>::Convert
CXVariantPerformConvert<56,48>::Convert
CXVariantPerformConvert<56,52>::Convert
CXVariantPerformConvert<56,62>::Convert
CXVariantPerformConvert<58,104>::Convert
CXVariantPerformConvert<58,127>::Convert
CXVariantPerformConvert<58,40>::Convert
CXVariantPerformConvert<58,48>::Convert
CXVariantPerformConvert<58,52>::Convert
CXVariantPerformConvert<58,56>::Convert
CXVariantPerformConvert<58,59>::Convert
CXVariantPerformConvert<58,62>::Convert
CXVariantPerformConvert<59,104>::Convert
CXVariantPerformConvert<59,127>::Convert
CXVariantPerformConvert<59,48>::Convert
CXVariantPerformConvert<59,52>::Convert
CXVariantPerformConvert<59,60>::Convert
CXVariantPerformConvert<59,62>::Convert
CXVariantPerformConvert<60,104>::Convert
CXVariantPerformConvert<60,48>::Convert
CXVariantPerformConvert<60,52>::Convert
CXVariantPerformConvert<61,104>::Convert
CXVariantPerformConvert<61,127>::Convert
CXVariantPerformConvert<61,40>::Convert
CXVariantPerformConvert<61,48>::Convert
CXVariantPerformConvert<61,52>::Convert
CXVariantPerformConvert<62,104>::Convert
CXVariantPerformConvert<62,122>::Convert
CXVariantPerformConvert<62,127>::Convert
CXVariantPerformConvert<62,48>::Convert
CXVariantPerformConvert<62,52>::Convert
CXVariantPerformConvert<62,56>::Convert
CXVariantPerformConvert<62,59>::Convert
CXVariantPerformConvert<62,60>::Convert
CXvariantPerformConvertFromBHToBH::Convert
CXVariantPerformConvertFromBHToString<165,167,0>::Convert
CXVariantPerformConvertFromBHToString<165,231,0>::Convert
CXVariantPerformConvertFromBHToString<167,165,0>::Convert
CXVariantPerformConvertFromBHToString<231,165,0>::Convert
CXVariantPerformConvertFromBHToString<231,231,1>::Convert
CXVariantPerformConvertFromUdtToBinary
CXVariantPerformConvertToByt<127>::Convert
CXVariantPerformConvertToByt<165>::Convert
CXVariantPerformConvertToByt<34>::Convert
CXVariantPerformConvertToByt<48>::Convert
CXVariantPerformConvertToByt<52>::Convert
CXVariantPerformConvertToByt<62>::Convert
CXVariantPerformConvertToDate<127,58>::Convert
CXVariantPerformConvertToDate<127,61>::Convert
CXVariantPerformConvertToDate<165,58>::Convert
CXVariantPerformConvertToDate<167,58>::Convert
CXVariantPerformConvertToDate<167,61>::Convert
CXVariantPerformConvertToDate<189,58>::Convert
CXVariantPerformConvertToDate<189,61>::Convert
CXVariantPerformConvertToDate<231,61>::Convert
CXVariantPerformConvertToDate<52,58>::Convert
CXVariantPerformConvertToDate<56,58>::Convert
CXVariantPerformConvertToDate<56,61>::Convert
CXVariantPerformConvertToDate<59,58>::Convert
CXVariantPerformConvertToDate<62,58>::Convert
CXVariantPerformConvertToDate<62,61>::Convert
CXVariantPerformConvertToNm<122>::Convert
CXVariantPerformConvertToNm<127>::Convert
CXVariantPerformConvertToNm<231>::Convert
CXVariantPerformConvertToNm<59>::Convert
CXVariantPerformConvertToNm<62>::Convert
CXVariantPerformConvertToSsVariant<104>::Convert
CXVariantPerformConvertToSsVariant<106>::Convert
CXVariantPerformConvertToSsVariant<108>::Convert
CXVariantPerformConvertToSsVariant<122>::Convert
CXVariantPerformConvertToSsVariant<127>::Convert
CXVariantPerformConvertToSsVariant<165>::Convert
CXVariantPerformConvertToSsVariant<167>::Convert
CXVariantPerformConvertToSsVariant<231>::Convert
CXVariantPerformConvertToSsVariant<36>::Convert
CXVariantPerformConvertToSsVariant<40>::Convert
CXVariantPerformConvertToSsVariant<48>::Convert
CXVariantPerformConvertToSsVariant<52>::Convert
CXVariantPerformConvertToSsVariant<56>::Convert
CXVariantPerformConvertToSsVariant<58>::Convert
CXVariantPerformConvertToSsVariant<59>::Convert
CXVariantPerformConvertToSsVariant<60>::Convert
CXVariantPerformConvertToSsVariant<61>::Convert
CXVariantPerformConvertToSsVariant<62>::Convert
CXVariantPerformConvertToStr<127>::Convert
CXVariantPerformConvertToStr<165>::Convert
CXVariantPerformConvertToStr<167>::Convert
CXVariantPerformConvertToStr<34>::Convert
CXVariantPerformConvertToStr<35>::Convert
CXVariantPerformConvertToStr<40>::Convert
CXVariantPerformConvertToStr<58>::Convert
CXVariantPerformConvertToStr<60>::Convert
CXVariantPerformConvertToStr<61>::Convert
CXVariantPerformConvertToStr<62>::Convert
CXVariantPerformConvertToWstr<127,0>::Convert
CXVariantPerformConvertToWstr<165,0>::Convert
CXVariantPerformConvertToWstr<231,0>::Convert
CXVariantPerformConvertToWstr<231,1>::Convert
CXVariantPerformConvertToWstr<34,0>::Convert
CXVariantPerformConvertToWstr<40,0>::Convert
CXVariantPerformConvertToWstr<58,0>::Convert
CXVariantPerformConvertToWstr<61,0>::Convert
CXVariantPerformConvertToWstr<62,0>::Convert
CXVariantPerformConvertToWstr<99,0>::Convert
CXVariantPerformConvertVarTime<40,42>::Convert
CXVariantPerformConvertVarTime<40,43>::Convert
CXVariantPerformConvertVarTime<41,41>::Convert
CXVariantPerformConvertVarTime<41,42>::Convert
CXVariantPerformConvertVarTime<41,43>::Convert
CXVariantPerformConvertVarTime<41,58>::Convert
CXVariantPerformConvertVarTime<41,61>::Convert
CXVariantPerformConvertVarTime<42,40>::Convert
CXVariantPerformConvertVarTime<42,41>::Convert
CXVariantPerformConvertVarTime<42,42>::Convert
CXVariantPerformConvertVarTime<42,43>::Convert
CXVariantPerformConvertVarTime<42,58>::Convert
CXVariantPerformConvertVarTime<42,61>::Convert
CXVariantPerformConvertVarTime<43,40>::Convert
CXVariantPerformConvertVarTime<43,41>::Convert
CXVariantPerformConvertVarTime<43,42>::Convert
CXVariantPerformConvertVarTime<43,43>::Convert
CXVariantPerformConvertVarTime<43,58>::Convert
CXVariantPerformConvertVarTime<43,61>::Convert
CXVariantPerformConvertVarTime<58,41>::Convert
CXVariantPerformConvertVarTime<58,42>::Convert
CXVariantPerformConvertVarTime<58,43>::Convert
CXVariantPerformConvertVarTime<61,41>::Convert
CXVariantPerformConvertVarTime<61,42>::Convert
CXVariantPerformConvertVarTime<61,43>::Convert
CXVariantPerformConvertWithFullTi<40,62>::Convert
CXVariantPerformConvertWithFullTi<41,62>::Convert
CXVariantPerformConvertWithFullTi<42,62>::Convert
CXVariantPerformConvertWithFullTi<43,62>::Convert
CXVariantPerformConvertWithFullTi<62,40>::Convert
CXVariantPerformConvertWithFullTi<62,41>::Convert
CXVariantPerformConvertWithFullTi<62,42>::Convert
CXVariantPerformConvertWithFullTi<62,43>::Convert

I don’t know what he type numbers match up to, will try to figure it out, it should be fairly simple by issuing a query like SELECT CAST(@int as bigint) and seeing what function it hits but that is slow and painful as there are loads of them!

Update:

Some of the types:

56 int

127 bigint

62 float

Update 2:

The types match up to the types in the [ms-tds] spec so:

 

NULLTYPE  0x1F  Null
INT1TYPE  0x30  TinyInt
BITTYPE  0x32  Bit
INT2TYPE  0x34  SmallInt
INT4TYPE  0x38  Int
DATETIM4TYPE  0x3A  SmallDateTime
FLT4TYPE  0x3B  Real
MONEYTYPE  0x3C  Money
DATETIMETYPE  0x3D  DateTime
FLT8TYPE  0x3E  Float
MONEY4TYPE  0x7A  SmallMoney
INT8TYPE  0x7F  BigInt
GUIDTYPE  0x24  UniqueIdentifier
INTNTYPE  0x26  (see below)
DECIMALTYPE  0x37  Decimal (legacy support)
NUMERICTYPE  0x3F  Numeric (legacy support)
BITNTYPE  0x68  (see below)
DECIMALNTYPE  0x6A  Decimal
NUMERICNTYPE  0x6C  Numeric
FLTNTYPE  0x6D  (see below)
MONEYNTYPE  0x6E  (see below)
DATETIMNTYPE  0x6F  (see below)
DATENTYPE  0x28  (introduced in TDS 7.3)
TIMENTYPE  0x29  (introduced in TDS 7.3)
DATETIME2NTYPE  0x2A  (introduced in TDS 7.3)
DATETIMEOFFSETNTYPE  0x2B  (introduced in TDS 7.3)
CHARTYPE  0x2F  Char (legacy support)
VARCHARTYPE  0x27  VarChar (legacy support)
BINARYTYPE  0x2D  Binary (legacy support)
VARBINARYTYPE  0x25  VarBinary (legacy support)
BIGVARBINTYPE  0xA5  VarBinary
BIGVARCHRTYPE  0xA7  VarChar
BIGBINARYTYPE  0xAD  Binary
BIGCHARTYPE  0xAF  Char
NVARCHARTYPE  0xE7  NVarChar
NCHARTYPE  0xEF  NChar
XMLTYPE  0xF1  XML (introduced in TDS 7.2)
UDTTYPE  0xF0  CLR-UDT (introduced in TDS 7.2)
TEXTTYPE  0x23  Text
IMAGETYPE  0x22  Image
NTEXTTYPE  0x63  NText
SSVARIANTTYPE  0x62  Sql_Variant (introduced in TDS 7.2)

 

To help confirm this list, I used the “SQL Server Data Type Conversion Chart” from http://www.microsoft.com/en-us/download/details.aspx?id=35834 and they do indeed match.

CQScanTableScanNew

DLL: sqlmin.dll

Class: CQScanTableScanNew

Section: Query Operators (?)

Description: The operator that is responsible for returning rows from a table when a table scan is performed. This maps  to a “Table Scan” in an execution plan. It is used for parallel and non-parallel table scans.

Methods:

Constructor: (CExecContext*, CXteTableScan*)

CreateVal(CValRefOrdinal*)

CreateVal(CValRefRow*)

GetRow(ulong*, ulong*)

IRSIndex(void)

Open(ulong*)

Calls: GetRow uses CQScanRowsetNew::GetRowWithPrefetch to retrieve the data from the heap (table scans happen on heaps), ultimately HeapDataSetSession uses the HeapRowScanner & HeapRowObject to retrieve data from the actual pages.