20

One way might be to: Add a NULLable NVARCHAR column Using batches, update a number of rows at a time (e.g. 1000 or 10000 rows) Backup the log, checkpoint, what have you in between batches When all the rows have been updated, drop the old column and rename the new one Rebuild indexes This won't be faster in the long run, and still requires a maintenance ...


16

By default, as documented in MSDN, if no length is specified for varchar it will default to 30 when using CAST or CONVERT and will default to 1 when declared as a variable. To demonstrate, try this : DECLARE @WithLength varchar(3),@WithoutLength varchar; SET @WithLength = '123'; SET @WithoutLength = '123'; SELECT @WithLength,@WithoutLength This is very ...


15

As an alternative to RDC, I'd just skip converting the data types in SSIS and explicitly cast them as nvarchar in my source query. Usage In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length. Instead of SELECT E.BusinessEntityID , E....


13

Well, first off, you should fix your table and store date/time data using the right kind of column, and not breaking it up for reasons unknown. Whose decision was it to store a time as a CHAR(6)? Can you think of a single good reason for that? Where do you store the date? Was that 1 PM today, last Tuesday, or October 2012 sometime? This really should be a ...


13

VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102)); Starting from the inside, the ODBC escape sequence {D '2019-06-30'} returns a datetime. (ignoring the redundant convert to datetime) You're then converting that to a string with 102 style (rather than 105 for Italian). You're then relying on an implicit conversion back to datetime to match ...


12

This warning was new for SQL Server 2012. From New "Type Conversion in Expression....." warning in SQL2012 ,to noisy to practical use I see what you mean. While I agree that this is noise in most cases, it is low priority for us to fix. We will look at it if we get more feedback. For now I have closed this by design. Connect was killed and it doesn’...


12

In SQL Server 2008, converting binary to a character representation became a lot faster and easier: CREATE TABLE dbo.X ( pk integer PRIMARY KEY, c1 integer NOT NULL, rv rowversion NOT NULL, rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1) ); Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to ...


11

(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.) You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then ...


11

What version of SQL Server are you using? From SQL Server 2012 onwards you can use TRY_PARSE with its USING culture argument. You can also use PARSE, the difference being PARSE will fail if the conversion fails and TRY_PARSE will return a NULL, eg DECLARE @t TABLE ( x VARCHAR(10) ) INSERT INTO @t VALUES ( '7.000,45' ), ( 'xxx' ) SELECT x, TRY_PARSE(...


11

To reproduce the problem: SELECT *, (CASE WHEN IsGun=1 THEN CEILING(Price1Avg) ELSE Price1 END) FROM ( SELECT UPC, IsGun, Price1, AVG(CAST(Price1 AS numeric(8, 2))) OVER (PARTITION BY UPC) AS Price1Avg FROM ( VALUES ('A', 0, 14.99), ('B', 0, 29.99), ('C', 1, 319.00), ('D', 1, ...


11

The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of the CAST operation. The issue is that the CONVERT operation is being done ...


11

The implicit conversions are caused by the computed column AltKey: CREATE TABLE dbo.Test ( [sessionid] [decimal](18, 0) NOT NULL, [sessionseqnum] [smallint] NOT NULL, [nodeid] [smallint] NOT NULL, [profileid] [int] NOT NULL, [AltKey] AS CONCAT ( [sessionid], [sessionseqnum], [nodeid], ...


10

Eithers of these 3 should work: SELECT color , HEX_1 = '#' + CONVERT(varchar(6), CAST(ABS(color) as varbinary(1)) + CAST(ABS(color/256) as varbinary(1)) + CAST(ABS(color/256/256) as varbinary(1)) , 2) , HEX_2 = '#'+ +CONVERT(varchar(2), CAST(ABS(color) as varbinary(1)), 2) +CONVERT(...


10

Use for xml path to create XML instead of casting. select @YourVariable for xml path(''), type Empty string in the path expression and the absence of an alias on the returned column will give you your string back as xml.


10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


10

My question is why SQL uses '201701' as a number but uses implicit conversion on the second value: '201702A' what I'm interested in is the internal mechanism how SQL server looks into these two values. Does it know the first one is a number and the second one is not? SQL Server attempts to convert both strings to the correct type (according to the data type ...


10

Your parameters have a lower Data Type Precedence than the columns they are compared to. In this case the parameter values will be implicitly converted. This is a good thing. Converting the column values to the parameter’s type is problematic and will trigger the warning.


10

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX). You could try converting the values there to get rid of the implicit conversion warnings. Another possible improvement would be to alter the NonClustereIndex-Cardholder index on ...


9

Is this an intentional feature? Yes, implicit type conversion is an intentional feature. There are pros and cons either way but this is how postgres is designed to work: In many cases a user does not need to understand the details of the type conversion mechanism. However, implicit conversions done by PostgreSQL can affect the results of a query. When ...


9

The issue is that your query through the view is the same as doing SELECT * FROM SomeTable WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1' Pretty much any CAST of a column in a predicate will render that predicate unsargable. The only exceptions that I am aware of are a CAST of datetime column to date and VARCHAR to NVARCHAR under some collations. There ...


9

You can get a list using the psql client with \dCS This will show all casts defined between system data types. In addition, all types can be cast to and from text using the type input and output functions.


9

There's nothing to fix, really. The message refers to this computed column in the Sales.SalesOrderHeader table: [SalesOrderNumber] AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')), The warning is informational in this case, to be sure you're aware in case it was a problem, as discussed in this Q&A: Type conversion in ...


8

There is a way. Given a table t and a function f() that returns an anonymous record that would match that table type: CREATE TABLE t (id int, d date); You cannot just cast the anonymous record, since a column definition list is required for SELECT * FROM f() Quoting the manual on the SELECT command: If the function has been defined as returning the ...


8

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format. Try this: select count(*) from dbo.profile where [created] between convert(datetime,'2014-11-01T00:...


8

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime The literals you are providing for comparison to the Created column are strings. To compare those literals with the datetime column, SQL Server attempts to convert the strings to datetime types, according to the rules of data type precedence. ...


8

It looks like your varbinary string got injected with some nonsense. All of the sequences of two 00 values are null characters, so this is why your string is terminating upon conversion. The first character is 0x24 (dollar sign), which is why the output is just a dollar sign. SELECT CONVERT(varchar(60), 0x2400....anything....); -- is equivalent to: SELECT ...


8

It's converting your variables, because bigint is what OFFSET ... FETCH is expecting for those values. It probably makes sense to DECLARE @paramPageNumber AS BIGINT, @paramPageSize AS BIGINT; to avoid any possible performance issues this may cause. Your id column in your table, however, should still be fine as an int. From the documentation for ...


8

What @Jack said. Plus, to be precise, the cast in your INSERT is "implicit" in a common sense: insert into d values ( 42 ); But it's an assignment cast, not an implicit cast in Postgres terminology. That's an important difference, implicit casts have far more applications. See: Generate series of dates - using date type as input Assignment or implicit ...


8

When there is a mismatch between the data types of the column and variable, SQL Server cannot directly use the seeking ability of a b-tree index to locate the correct range of values. When the rules of data type precedence mean that the column data would have to be converted to the data type of the variable, this would mean scanning the whole table or index,...


8

The type of the result is determined by the rules set out in Precision, scale, and Length (Transact-SQL): The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when either: Both expressions are decimal. One expression is decimal and the other ...


Only top voted, non community-wiki answers of a minimum length are eligible