When we have the following table: CREATE TABLE Test1 (     Name NVARCHAR(50) NOT NULL     , Code SMALLINT NOT NULL )  With only 1 row for demo purpose: INSERT INTO Test1(Name, Code)  VALUES ( 'ABC', 1)  And the following queries: SELECT CONCAT(Name, ' - ', Code) FROM Test1  SELECT CONCAT(Name, ' - ', CONVERT(NVARCHAR(2), Code)) FROM Test1  SELECT CONCAT(Name, ' - ', CAST(Code AS NVARCHAR(2))) FROM Test1  SELECT CONCAT(Name, ' - ', FORMAT(Code, '0')) FROM Test1  The first 3 queries have a warning:  Type conversion in expression (...) may affect &quot;CardinaltiyEstimate&quot; in query plan choice.  But why is FORMAT behaving differently ? What happens behind the scenes to eliminate the implicit conversion? Is there any other way then format so you are not impacted by the implicit conversion?
71169
I maintain an archive database that stores historical data in partitioned views. The partitioning column is a datetime. Each table under the view stores one month of data.  We constraint the events on each table with a check constraint on the datetime column. This allows the optimizer to limit the tables that are searched for queries that filter on the event datetime column.  The names of the check constraints were generated by SQL Server, so it's hard to know what they do by looking at their name.  I want the constraint names to have the form 'CK_TableName_Partition'.  I can generate a rename script using this query and copying data from from sql_text column. The WHERE clause matches check constraints whose names look like they were generated by SQL Server:  SELECT   checks.name AS check_name,   tabs.name AS table_name,   skemas.name AS schema_name,   cols.name AS column_name,   N' EXECUTE sys.sp_rename   @objname = N''' + skemas.name + N'.' + checks.name + N''',   @newname = N''CK_' + tabs.name + N'_Partition'',   @objtype = ''OBJECT'';' AS sql_text FROM sys.check_constraints AS checks INNER JOIN sys.tables AS tabs ON   tabs.object_id = checks.parent_object_id INNER JOIN sys.schemas AS skemas ON   skemas.schema_id = tabs.schema_id INNER JOIN sys.columns AS cols ON   tabs.object_id = cols.object_id AND   cols.column_id = checks.parent_column_id WHERE checks.name LIKE (   N'CK__' + SUBSTRING(tabs.name, 1, 9) +   N'__' + SUBSTRING(cols.name, 1, 5) +   N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2 ) ORDER BY table_name;   The output looks like this:  check_name  table_name  schema_name column_name sql_text CK__tbAcquisi__Acqui__5C4299A5  tbAcquisitions_201301   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5',  @newname = N'CK_tbAcquisitions_201301_Partition',  @objtype = 'OBJECT'; CK__tbAcquisi__Acqui__76026BA8  tbAcquisitions_201302   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8',  @newname = N'CK_tbAcquisitions_201302_Partition',  @objtype = 'OBJECT'; CK__tbAcquisi__Acqui__7D6E8346  tbAcquisitions_201303   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346',  @newname = N'CK_tbAcquisitions_201303_Partition',  @objtype = 'OBJECT'; ... CK__tbRequest__Reque__60132A89  tbRequests_201301   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__60132A89',  @newname = N'CK_tbRequests_201301_Partition',  @objtype = 'OBJECT'; CK__tbRequest__Reque__1392CE8F  tbRequests_201302   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1392CE8F',  @newname = N'CK_tbRequests_201302_Partition',  @objtype = 'OBJECT'; CK__tbRequest__Reque__1AFEE62D  tbRequests_201303   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D',  @newname = N'CK_tbRequests_201303_Partition',  @objtype = 'OBJECT';   The result of the query seems to be correct and the server executes it quickly.  But the root node of the execution plan has a warning:     Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice   What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?
73953
I have been working with T-SQL in MS SQL for some time now and somehow whenever I have to insert data into a table I tend to use syntax:  INSERT INTO myTable &lt;something here&gt;   I understand that keyword INTO is optional here and I do not have to use it but somehow it grew into habit in my case.  My question is:    Are there any implications of using INSERT syntax versus INSERT INTO? Which one complies fully with the standard? Are they both valid in other implementations of SQL standard?
108095
Who can explain why   select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');   yields  ----------- ----------- ----------- ----------- ----------- 1           0           0           0           1   The funny consequence of this is that in   create table test ( val varchar(10) ); insert into test values( '' ); update test set val = ' ' where val = '';   the update will indeed replace the empty string by a blank, but the where clause keeps to be true and repeated executions of the update statement tell  (1 row(s) affected)
20339
the two bits of SQL below get the same result  SELECT c.name, o.product   FROM customer c, order o   WHERE c.id = o.cust_id   AND o.value = 150    SELECT c.name, o.product   FROM customer c   INNER JOIN order o on c.id = o.cust_id   WHERE o.value = 150   I've seen both styles used as standard at different companies. From what I've seen, the 2nd one is what most people recommend online. Is there any real reason for this other than style? Does using an Inner Join sometimes have better performance?  I've noticed Ingres and Oracle developers tend to use the first style, whereas Microsoft SQL Server users have tended to use the second, but that might just be a coincidence.  Thanks for any insight, I've wondered about this for a while.  Edit: I've changed the title from 'SQL Inner Join versus Cartesian Product' as I was using the incorrect terminlogy. Thanks for all the responses so far.
799
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.  The  say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even  to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).  As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.  It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?
61417
create table T(ID int identity primary key) insert into T default values insert into T default values  go  select cast(ID as varchar(10)) as ID from T where ID = 1   The query above has a warning in the query plan.  &lt;Warnings&gt;   &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[xx].[dbo].[T].[ID],0)" /&gt; &lt;/Warnings&gt;   Why does it have the warning?  How could a cast in the field list affect the cardinality estimate?
12285
Why would the following query return "Error converting data type varchar to bigint"?  Doesn't IsNumeric make the CAST safe?  I've tried every numeric datatype in the cast and get the same "Error converting..." error.  I don't believe the size of the resulting number is a problem because overflow is a different error.  The interesting thing is, in management studio, the results actually show up in the results pane for a split second before the error comes back.  SELECT CAST(myVarcharColumn AS bigint)   FROM myTable   WHERE IsNumeric(myVarcharColumn) = 1 AND myVarcharColumn IS NOT NULL   GROUP BY myVarcharColumn   Any thoughts?
54711
This question is related to . The below query was taking 10 to 15 seconds to execute:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))&gt;0)    In some articles I saw that using CAST and CHARINDEX will not benefit from indexing. There are also some articles that say using LIKE '%abc%' will not benefit from indexing while LIKE 'abc%' will:      In my case I can rewrite the query as:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id] FROM [company].dbo.[customer] WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'   This query gives the same output as the previous one. I have created a nonclustered index for column Phone no. When I execute this query it runs in just 1 second. This is a huge change compared with 14 seconds previously.  How does LIKE '%123456789%' benefit from indexing?  Why do the listed articles state that it will not improve performance?  I tried rewriting the query to use CHARINDEX, but performance is still slow. Why does CHARINDEX not benefit from the indexing as it appears the LIKE query does?  Query using CHARINDEX:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]  FROM [Company].dbo.[customer]  WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])&gt;0 )    Execution plan:    Query using LIKE:  SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]  FROM [Company].dbo.[customer]  WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'   Execution plan:
42242
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.  Ex: "one,two,three,four" would return a new table with only one column containing the following values:  one two three four   Does this code look error prone to you guys? When I test it with   SELECT * FROM utvf_Split('one,two,three,four',',')    it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.  Here is the function:  USE *myDBname* GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)  RETURNS @SplitValues TABLE (     Asset_ID VARCHAR(MAX) NOT NULL )  AS BEGIN             DECLARE @FoundIndex INT             DECLARE @ReturnValue VARCHAR(MAX)              SET @FoundIndex = CHARINDEX(@delimiter, @String)              WHILE (@FoundIndex &lt;&gt; 0)             BEGIN                   DECLARE @NextFoundIndex INT                   SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)                   SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)                   SET @FoundIndex = CHARINDEX(@delimiter, @String)                   INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)             END              RETURN END
47691
I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input.  How do you do this?  Do you get this automatically when using stored procedures?  So my understanding this is non-parameterized:    cmdText = String.Format("SELECT foo FROM bar WHERE baz = '{0}'", fuz)   Would this be parameterized?  cmdText = String.Format("EXEC foo_from_baz '{0}'", fuz)   Or do I need to do somethng more extensive like this in order to protect myself from SQL injection?  With command     .Parameters.Count = 1     .Parameters.Item(0).ParameterName = "@baz"     .Parameters.Item(0).Value = fuz End With   Are there other advantages to using parameterized queries besides the security considerations?  Update:  This great article was linked in one of the questions references by Grotok.
92996
So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.  For example, the following query doesn't return rows where value IS NULL but I want it to:  SELECT * FROM table WHERE   (value != 26)   AND date IS NULL   AND last_modified &gt;= '5/21/2014'   I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.  SELECT * FROM table WHERE   (value != 26 OR value is null)   AND date IS NULL   AND last_modified &gt;= '5/21/2014'   I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.  Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.  Edit:  My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.
12766
I have the following query:  select column_name, count(column_name) from table group by column_name having count(column_name) &gt; 1;   What would be the difference if I replaced all calls to count(column_name) to count(*)?  This question was inspired by .    To clarify the accepted answer (and maybe my question), replacing count(column_name) with count(*) would return an extra row in the result that contains a null and the count of null values in the column.
31131
Assuming String a and b:  a += b a = a.concat(b)   Under the hood, are they the same thing?  Here is concat decompiled as reference. I'd like to be able to decompile the + operator as well to see what that does.  public String concat(String s) {      int i = s.length();     if (i == 0) {         return this;     }     else {         char ac[] = new char[count + i];         getChars(0, count, ac, 0);         s.getChars(0, i, ac, count);         return new String(0, count + i, ac);     } }
13332
I am trying to use a  as input and then creating a query with it - so I need a .    I was surprised to find that it couldn't do the conversion implicitly or explicitly - but I don't even know how I would do this, as the Java API is still fairly new to me.
80631
What do the brackets do in a sql statement?  For example, in the statement:    insert into table1 ([columnname1], columnname2) values (val1, val2)  Also, what does it do if the table name is in brackets?
1681
When using SQL, are there any benefits of using = in a WHERE clause instead of LIKE?  Without any special operators, LIKE and = are the same, right?
36557
I am curious as to why an implicit cast fails in...  int? someValue = SomeCondition ? ResultOfSomeCalc() : null;   and why I have to perform an explicit cast instead  int? someValue = SomeCondition ? ResultofSomeCalc() : (int?)null;   It seems to me that the compiler has all the information it need to make an implicit casting decision, no?