Decimal, money, numeric, ints and floats oh my.

When you are choosing a data type to store your numerics, there are some questions you need to ask yourself.
1) Do you want your numeric to remain exact?
2) How precise do you want your numeric to be?
3) How much storage are you willing to give to your data type?


Exactness versus Approximation
There are two types of numerics in SQL Server, Exact numerics and Approximate numerics. Exact numerics garauntee the value will remain the same without losing precision do due rounding. Approximate numerics provide less precision.

There are two types of approximate numerics in SQL Server, float and real.

All other numerics in SQL Server are exact: decimal, number, money, small money, big int, int, small int, tiny int and bit.

If you are storing numerics for financial purposes, you would use an exact numeric data type.

Below is a link on approximate data types in SQL Server, specifically, float. http://sqlblogcasts.com/blogs/grumpyolddba/archive/2007/10/26/do-you-use-the-float-datatype.aspx

Precision & Scale
The decimal and numeric data types have a fixed precision and scale. I want to quickly mention that there is no difference between the decimal and numeric data types.

Precision is ther number of digits that can be stored to the left and right of the decimal point. Scale is the number of digits that can be stored to the right of the decimal.

If you need scale, then you would not use any of the whole number data types (big, int, small, tiny, bit).

Both the money and small money data types provide scale to 4 decimal places. If you need further digit representation beyond a scale of 4, you should use either decimal or numeric.

Storage
When planning for very large databases, you need to carefully choose the kinds of data types to use to store your numerics.

decimal and numeric can store up to 17 bytes
float 8 bytes
real 4 bytes
money 8 bytes
small money 4 bytes
bit int 8 bytes
int 4 bytes
small int 2 bytes
tiny int 1 byte
bit (1-8 bits = 1 byte)
Enjoy!

Data Type Conversion Chart

Below is a helpful data type conversion chart. I did not create this, but I wanted to share it because it is very useful. The chart and full article can be found @ http://msdn.microsoft.com/en-us/library/ms187928.aspx

More Version Detection

I discovered a couple of more ways to detect version information while studying for a certification exam.

xp_msver

select @@MICROSOFTVERSION

Most of you reading this were probably already aware, but I'm a career student of SQL Server and there's always more to learn. Enjoy.

Session and Request detail dump

Below is a query I use to retrieve all session and request details on a given sql server. This provides me a nice dump with lots of columns to sort through.

select
c.most_recent_session_id as ConnectionMostRecentSessionID,
c.connect_time as ConnectionConnectTime,
c.net_transport as ConnectionTransport,
c.protocol_type as ConnectionProtocolType,
c.protocol_version as ConnectionProtocolVersion,
c.endpoint_id as ConnectionEndPoint,
c.encrypt_option as ConnectionEncryptOption,
c.auth_scheme as ConnectionAuthScheme,
c.node_affinity as ConnectionNodeAffinity,
c.num_reads as ConnectionNumberReads,
c.num_writes as ConnectionNumberWrites,
c.last_read as ConnectionLastRead,
c.last_write as ConnectionLastWrite,
c.client_net_address as ConnectionClientNetAddress,
c.connection_id as ConnectionID,
c.parent_connection_id as ConnectionParentConnectionID,
c.most_recent_session_id as ConnectionMostRecentSessionID,
s.session_id, s.login_time as SessionLoginTime, s.host_name as SessionHostName,
s.program_name as SessionProgramName, s.login_name as SessionLoginName,
s.nt_domain as SessionNTDomain, s.nt_user_name as SessionUserName, s.status as SessionStatus,
s.cpu_time as SessionCPUTime, s.memory_usage as SessionMemoryUsage,
s.total_scheduled_time as SessionTotalScheduledTime,
s.total_elapsed_time as SessionTotalElapsedTime,
s.last_request_start_time as SessionLastRequestStartTime,
s.last_request_end_time as SessionLastRequestEndTime,
s.reads as SessionReads, s.writes as SessionWrites,
s.logical_reads as SessionLogicalReads, s.is_user_process as SessionIsUserProcess,
r.request_id as Requestid,r.start_time as RequestStartTime,r.status as RequestStatus,
r.command as RequestCommand,
r.sql_handle as RequestSqlHandle, r.plan_handle as RequestPlanJandle,
r.database_id as RequestDatabaseID, r.user_id as RequestUserID,
r.connection_id as RequestConnectionID, r.blocking_session_id as RequestBlockingSessionID,
r.wait_type as RequestWaitType, r.wait_time as RequestWaitTime, r.last_wait_type as RequestLastWaitType,
r.wait_resource as RequestWaitResource, r.open_transaction_count as RequestOpenTransactionCount,
r.open_resultset_count as RequestOpenResultsetCount, r.transaction_id as RequestTransactionID,
r.context_info as RequestContextInfo, r.percent_complete as RequestPercentComplete,
r.estimated_completion_time as RequestEstimatedCompletionTime, r.cpu_time as RequestCPUTime,
r.total_elapsed_time as RequestTotalElapsedTIme, r.scheduler_id as RequestSchedulerID,
r.reads as RequestReads, r.writes as RequestWrites, r.logical_reads as RequestLogicalReads,
t.text as SQLStatement

from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.Plan_handle) t
inner join sys.dm_exec_connections c on r.session_id = c.session_id
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
where r.session_id > 50

Simple Parallelism Testing

I recently started doing research on the CXPacket wait type and I needed a simple sql statement to test the MAX Degree of Parallelism and Cost Threshold for Parallelism settings. I wanted a simple statement that would not have alot of complexity that could be run on a simple workstation or laptop while at the same time invoking parallelization. My goal was to see if the parallelized query would run faster than the non parallelized query.

My machine configuration
Dell Precision M4500
Dual Socket Quad Core Intel(R) Core(TM) i7 CPU x 940 @ 2.13GHz
SQL Server sees 8 total CPU's
4GB of Ram
64-bit Windows 7 Professional Service Pack 1
SQL Server 2008 R2 Enterprise SP1

I borrowed the query below from Pinal Dave.

SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

The query produces the following execution plan with the MAX Degree of Parallelism setting of 0 and Cost Threshold for Parallelism set to 5. This query took 5 seconds to complete, regardless of a clean cache.

My main objective is to see if I take half of my cpu's (4) and allow them to be used for parallelism, what's the hight cost threshold I can set before parallelism is not used. I found that if I set the Cost Threshold for Parallelism to 20, that is the highest threshold in which the query is considered for parallelism. When setting the threshold to 21, parallelism is not used. Look at the query plan below. Note, the query took 3 seconds to run, regardless of a clean cache.

What's really interesting, is that the query actually takes longer when the optimizer decides to use parallelism. This is a crucial point when researching CXPacket wait types.

SQL Server 2012 Licensing

Microsoft recently released new licensing information for SQL Server 2012. To understand how SQL Server will be licensed, we first must quickly review how Microsoft has partitioned their editions of SQL Server. There will be three editions to license, Enterprise, Business Intelligence and Standard.

Enterprise
The Enterprise edition includes AlwaysOn, Columnstore, Max virtualization and the Business Intelligence capabilities.
Enterprise licenses will be core-based.

Business Intelligence
The new edition, Business Intelligence, will include Power View, Reporting, Analytics, Data Quality Services, Master Data Services and the Standard editions capabilities.
Business Intelligence licenses will be Server and CAL based.

Standard
The Standard Edition offers the basics of database services, reporting and analytics.
Standard licensing will be both core-based and Server and CAL based.

For more information, Microsoft's SQL 2012 website.

Operator Icons

When I'm documenting execution plans, I always find myself copying, pasting and cropping. I've started saving these operators so that I can re-use them. Below are icons for you to use and I will continue to add to these as I encounter reasons for me to document them.