There are two different functions that can be used when converting an integer to a string. One is CAST and the other is CONVERT. Either of these functions can be used with the exact same result when converting to a string. The only difference with the CONVERT function is that it takes an extra optional parameter for style which can affect the way certain data types are displayed after they are converted (an example is with date/time formats). The common need to convert an INT to a string is to then concatenate it with either another int or an existing string. Here is a simple example: [cc lang=”sql”] SELECT CAST(12345 AS VARCHAR(11)) [/cc] And the output: Here we are casting the int to a varchar(11). This is a safe value for us to convert to because the maximum integer value is -2147483648. We see if we try to convert an integer to a smaller string, it returns back an asterisks (*) meaning an error has occurred. [cc lang=”sql”] SELECT CAST(-2147483648 AS VARCHAR(10)) [/cc] So it is important to choose a data type that is large enough. With that said, I have seen the following work as well, however I personally would not run this in production in case it is deprecated in a future version of SQL. [cc lang=”sql”] SELECT CAST(-2147483648 AS VARCHAR) [/cc]
Continue reading ...
This is one of those topics that will get people fired up. But here goes. I am mostly an explicit temp table developer. By contrast, I am not an implicit temp table developer. What I mean by that is – in writing SQL, you simply cannot avoid the use of tempdb. Either you write to tempdb by breaking queries out and intentionally create temp tables, or you write to tempdb by not breaking queries out (keeping them as long convoluted statements with a long convoluted query plan) and let the optimizer create “worktables”. In either case you are writing to tempdb whether you like it or not. Yet.. the difference is this: Breaking them out: You can control the size of the result set being written to disk You can ensure that the execution plan is simple You can utilize the materialized temp table data throughout the entire procedure Temp tables contain statistics and can be indexed To compare temp table development to CTE development is somewhat of an apples and oranges comparison. A CTE uses nothing special on the back end. It is simply a (potentially) clean way to write a query. The difference is this however. With a CTE, the execution plan of the main query becomes intertwined with the CTE, leaving more room for the optimizer to get confused. By contrast, when a temp table divides two queries, the optimizer is not intertwined with that which created the temp table and the execution plans stay simple and […]
Continue reading ...
Over the years I’ve ran across certain situations that cause errors in SQL that error out the calling application if they are not correctly trapped. As unsuspecting as these can be, you end up learning your lesson when you get a call late at night that a web page is erroring out. Then to follow you get a deep sinking feeling in your gut while you think, “Why didn’t I think of that?”. In some of these cases I’ve often wondered why SQL doesn’t simply return a NULL or an invalid result. And often I wish there was an option that could set that because while it’s true that all errors should be handled, in truth they are not. Hopefully this list can save you a bit of heartache. divide by zero string or binary data would be truncated Invalid length parameter passed to the LEFT or SUBSTRING function These three errors are very common and all of them at one time or another have bit me in the pants – most of the time after deploying code and even worse, hours/days/months after deploying the code. Not only that, they can occur on an intermittent basis which is always fun to troubleshoot. Divide by Zero This innocent division operation turns ugly when your divisor hits the dead even zero. This is because of the rules of math. You simply cannot divide by zero. Reproducing this issue is simple enough. [cc lang=”sql”] SELECT 8 / 0 [/cc] The workaround is to […]
Continue reading ...
Two of the most common ways to check for the existence of records is to use a WHERE EXISTS or to perform a LEFT JOIN WHERE values are NOT NULL. My personal choice is usually to use the EXISTS method where possible. Call me a prude, but I think just by virtue of EXISTS being the keyword for this method, it’s a safer and more optimization friendly method. If someone asked me how to check for existence I would tell them to use exists. However I do think there are many more considerations that need to be outlined before making a final decision. Let’s look at examples of the two methods to illustrate: [cc lang=”sql”] — using EXISTS to check for existence SELECT P.ProductID FROM Production.Product p WHERE EXISTS ( SELECT 1 FROM Sales.SalesOrderDetail sod WHERE sod.ProductID = p.ProductID ) — Using LEFT JOIN to check for existence GO SELECT DISTINCT P.ProductID FROM Production.Product p LEFT JOIN Sales.SalesOrderDetail sod ON sod.ProductID = p.ProductID WHERE sod.SalesOrderDetailID IS NOT NULL [/cc] Since these examples are straightforward, the optimizer realizes the end result we are looking for and gives us the same execution plan for both. However we don’t always want to rely on the optimizer’s brains. This is because as the execution plan gets more complex (by adding additional tables), it’s less likely to make good suggestions. This brings up a separate point. Anatomy of LEFT JOIN WHERE NOT NULL While there is a chance both methods can take the same execution […]
Continue reading ...
The debate whether to use temp tables or table variables is an old debate that goes back since they were first introduced. The real answer to knowing the difference lies in what is going on under the hood and correlating those specifics to your situation. In this article we’ll touch on (hopefully all) the differences between the two. #Temp tables are just regular SQL tables that are defined and stored in TempDB. The only difference between them and a permanent table is they are not allowed to have foreign keys. You can view the temp tables currently defined via SSMS by going to TempDB and Expanding Temp Tables. Let’s look at a matrix of specific differences then we’ll touch on a few of the key differences below. Item #Temp Tables @Table Variables Can participate in a transaction Writes to Log File Writes only to memory (not disk) Can Qualify for Parallelism Allows creation of statistics Does not affect recompilation Allows nonclustered indexes Allows clustered indexes Can perform SELECT INTO Can access in nested stored procedures Can define globally Can use in user defined functions Can insert from EXEC Allows TRUNCATE Allows ALTER TABLE Scope #Temp tables can be either #locally defined (within the scope of the session) or ##globally defined (within a database). When they are locally defined, the table can be used by whatever is executed within that session (or SPID). In other words, if you define a #temp table in procedure A, then procedure A calls procedure B, […]
Continue reading ...
