How can I fix the following cardinality estimation warning? while exists(select 1 from @images) begin    declare @imageid     int    declare @filename    varchar(max)    select TOP (1)       @imageid = [imageid],       @filename = concat([imageid], '.', [extension])    from       @images    order by       [imageid]    ...    delete @images where [imageid] = @imageid end   SQL Server Compatibility Level: SQL Server 2019 (150)
122950
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:      Type conversion in expression (CONVERT_IMPLICIT(nvarchar(50),[tempdb].[dbo].[#MyFunIntTable].[EvenCoolerColumn],0)) may affect "Cardinality Estimate" in query plan choice.   Being a concerned citizen, I checked all of the obvious suspects and eventually dug into the XML to confirm that it was actually warning about the insert into the table. The problem is, I can't figure out why this would ever affect cardinality estimates. If I were doing this in a join or somewhere with a little more logic it would make sense, but there shouldn't be a cardinality estimate mismatch for the actual insert operation, right?  I noticed that this happened when it was more than just a trivial query - as soon as more than one value is inserted, or we're pulling a value from a table, we hit this.  This question has attracted some potential duplicates, including:       I think it is different from these questions because I'm literally not doing anything with this column. I'm not using it in a filter, or a sort, or a grouping, or a join, or in a function - any of these things makes the scenario more complicated. All I'm doing is inserting a bigint into a nvarchar, which should never impact a meaningful cardinality estimate that I can think of.  What I'm specifically looking for out of an answer is:   An explanation of why I get this warning despite nothing of interest going on - is it just that SQL Server will be conservative and report even when it won't affect plan choice? What cardinality estimate is actually at risk here, and what operation would change based off of inaccuracies in that cardinality estimate? Is there a scenario where this could affect plan choice? Obviously if I start joining or filtering on the converted column it could, but as-is? Is there anything that can be done to prevent it from warning, besides changing data types (assume this is a requirement of how the data models interact)   I recreated it with the below simple example ()  DROP TABLE IF EXISTS #MyFunStringTable; DROP TABLE IF EXISTS #MyFunIntTable;  CREATE TABLE #MyFunStringTable (   SuperCoolColumn nvarchar(50) COLLATE DATABASE_DEFAULT NULL );  CREATE TABLE #MyFunIntTable (   EvenCoolerColumn bigint NULL );  INSERT INTO #MyFunIntTable ( EvenCoolerColumn ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT EvenCoolerColumn     FROM #MyFunIntTable;  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) VALUES ( 1 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) VALUES ( 1 ), ( 2 );  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT 1;  INSERT INTO #MyFunStringTable ( SuperCoolColumn ) SELECT 1 UNION ALL SELECT 2;  INSERT INTO #MyFunStringTable ( SuperCoolColumn )   SELECT 1     FROM #MyFunIntTable;
9841
I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias:  sum(reviews.rev_rating)/count(reviews.rev_id) as avg_rating
25040
I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).  CREATE TABLE  `clients` (   `clientId` int(10) unsigned NOT NULL auto_increment,   `clientName` varchar(100) NOT NULL default '',   `courseNames` varchar(255) NOT NULL default '' )   The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"  CREATE TABLE  `clientenrols` (   `clientEnrolId` int(10) unsigned NOT NULL auto_increment,   `studentId` int(10) unsigned NOT NULL default '0',   `courseId` tinyint(3) unsigned NOT NULL default '0' )   The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.  Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")  Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:  SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`] FROM ...
104158
I wanted to get some UI design advice. I am not sure this is the proper forum to ask this question so here goes.  My situation is I have a table that has 51 bit values to hold the values of statutes.     In production, this table would have over 10,000 records for the different LocationID's.  The UI for this in its pure database for would have a page with the dozens of text boxes.  Is there a better, cleaner way to do this?  Any recommendation or links to how others have handled this situation.  Please feel free to post any additional questions if further clarification is needed.
69678
I installed this third party thingy, sp_Blitz.  sqlcmd -S localhost -U sa -i ./sp_Blitz.sql   But when I run   1&gt; EXEC sp_blitz; 2&gt; go   I get,  Warning: Null value is eliminated by an aggregate or other SET operation Msg 1934, Level 16, State 1, Server x230, Procedure sp_blitz, Line 4338      INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.     Attempts at the server  When I try to configure QUOTED_IDENTIFIER I get,  1&gt; sp_configure 'QUOTED_IDENTIFIER'; 2&gt; GO Msg 15123, Level 16, State 1, Server x230, Procedure sp_configure, Line 62 The configuration option 'QUOTED_IDENTIFIER' does not exist, or it may be an advanced option.   That's having previously run sp_configure 'show advanced options', 1; RECONFIGURE;  Attempts at the client  sqlcmd.exe  Ok.. So      -I Sets the SET QUOTED_IDENTIFIER connection option to ON. By default, it is set to OFF. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).   However, running with -I will show SESSIONPROPERTY('QUOTED_IDENTIFIER') as 1 but it doesn't seem to work either  sqlcmd -S localhost -U Sa -I Password:  1&gt; EXEC sp_blitz; 2&gt; GO Warning: Null value is eliminated by an aggregate or other SET operation. Msg 1934, Level 16, State 1, Server x230, Procedure sp_blitz, Line 4338 INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.   mssql-cli  This starts up with SESSIONPROPERTY('QUOTED_IDENTIFIER') set to 1 by default, but it generates the same error as above.
25718
I'm looking for duplicate records in dbf files based upon the attribute called 'ID'. I have various dbf files from 500,000 records to 1.5 million and I know there are a host of duplicates.   I would like to add a field 'Duplicate' that says Yes or No (or 1 or 0 is fine) when the ID attribute is present elsewhere. Using the following python script in Field Calculator returns 1 for a duplicate entry and 0 for unique entry;  uniqueList = [] def isDuplicate(inValue):   if inValue in uniqueList:     return 1   else:     uniqueList.append(inValue)     return 0 isDuplicate(!FIELD_NAME!)   However, the 1st record of, for example, 5 duplicate IDs will also be returned as a 0 (the subsequent 4 are considered the duplicates). I would need all 5 to be marked as duplicate as the ID exists elsewhere.  Using the following code will give you an incremental count of how many times that ID occurs with 1 meaning the 1st occasion and so forth;  UniqueDict = {} def isDuplicateIndex(inValue):     UniqueDict.setdefault(inValue,0)     UniqueDict[inValue] += 1     return UniqueDict[inValue]  isDuplicateIndex( !YOUR_FIELD! )   I just want a 1 (or Yes) if the ID of that record exists elsewhere! (ArcGIS version 10.1)  I have seen other answers such as  but it doesn't quite work.
116505
From 2012 onward the SQL Server docs show they support  which I'm trying to use instead of a LIMIT.  The following works fine in PostgreSQL to sample a result set,  SELECT * FROM ( VALUES (1),(2),(3) ) AS t(x) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;   However, with SQL Server, I get  Msg 153, Level 15, State 2, Line 4 Invalid usage of the option FIRST in the FETCH statement.   What's going on here? Does SQL Server support the standardized OFFSET .. FETCH?
40040
I'm trying to create the following table. This is the code which I wrote.   \begin{table}[!htb] \centering \caption{"table1"} \begin{tabular}{|c|c|c|c|c|c|c|c|} System &amp; \multicolumn{2}{c}{Interaction} &amp; \multicolumn{2}{c}{Commit} &amp; Ver &amp; LOC &amp; terms \\\hline \multirow{3}{c}{System1} &amp; \multicolumn{2}{c}{3272 traces} &amp; \multicolumn{2}{c}{5093 revisions} &amp; \multirow{3}{c}{3.4} &amp; \multirow{3}{c}{1700} &amp; \multirow{3}{c}{2400} \\\hline &amp; File &amp; Method &amp; File &amp; Method &amp; &amp; &amp; \\\hline &amp; 12 &amp; 13 &amp; 14 &amp; 15 &amp; &amp; &amp; \\\hline  \end{tabular} \end{table}   and this the error that Latex shows:! Missing number, treated as zero.!
24654
Is there any performance difference between these two queries?  select name from Account where id = :myId  select name from Account where id = :myId limit 1   Or, I guess more general: does salesforce stop searching after first hit when there's a unique value as a condition?
31999
We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).    Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in).  This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.  Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.  Would really appreciate any thoughts/feedback people might have.  Thanks Dave
21318
I tried searching around, but I couldn't find anything that would help me out.  I'm trying to do this in SQL:  declare @locationType varchar(50); declare @locationID int;  SELECT column1, column2 FROM viewWhatever WHERE CASE @locationType     WHEN 'location' THEN account_location = @locationID     WHEN 'area' THEN xxx_location_area = @locationID     WHEN 'division' THEN xxx_location_division = @locationID   I know that I shouldn't have to put '= @locationID' at the end of each one, but I can't get the syntax even close to being correct. SQL keeps complaining about my '=' on the first WHEN line...  How can I do this?
2711
I have this code:  DECLARE @MyTable AS TABLE (    [Month] INT,    Salary INT );  INSERT INTO @MyTable VALUES (1,2000), (1,3100);  SELECT [Month], Salary FROM @MyTable;   Output:    I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100'  How would I do this efficiently?
110175
I am receiving an error stated above on line 26**(listAccount1)**, can anyone guide me on how to solve it?  public class FRB_UpdateExceptionsforBatch {    public static void FRB_Exceptions(){    string FRBRecordTypeID = utility.getFRBProfileNameLabel();       List&lt;Account&gt; lstAccount = new List&lt;Account&gt;();         List&lt;Account&gt; lstAccount1 = new List&lt;Account&gt;();         List&lt;Account&gt; lstAccount2 = new List&lt;Account&gt;();         List&lt;Account&gt; lstAccount3 = new List&lt;Account&gt;();    lstAccount = [Select id,FRB_Firm_Check__c,Number_of_Exception__c,Number_of_exceptions_under_4_6_bucket__c,Number_of_exceptions_under_7_10_bucket__c,Number_of_exceptions_under_11_19_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '0-3 Days (Green - New Exception)' ) FROM Account WHERE FRB_Firm_Check__c = TRUE];     if(lstAccount.size()&gt;0){           for( Account acc : lstAccount )           {               List&lt;Process_Adherence_Monitoring__c&gt; lstCont = acc.Process_Adherence_Monitoring__r;                acc.Number_of_Exception__c = lstCont.size();               acc.FRB_Firm_Check__c = true;           }            update lstAccount;     }         //To update 4-6 bucket      lstAccount1 = [select Id, Name,FRB_Firm_Check__c, Number_of_exceptions_under_4_6_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '4-6 Days (Yellow - Outside Correction Timeframe)' ) from Account where FRB_Firm_Check__c = TRUE];        if(lstAccount1.size()&gt;0){           for( Account acc : lstAccount1 )           {               List&lt;Process_Adherence_Monitoring__c&gt; lstCont1 = acc.Process_Adherence_Monitoring__r;                              acc.Number_of_exceptions_under_4_6_bucket__c= lstCont1.size();               acc.FRB_Firm_Check__c = true;           }            update lstAccount1 ;       }         //To update 6-11 bucket         lstAccount2 =  [select Id, Name,FRB_Firm_Check__c, Number_of_exceptions_under_7_10_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '7-10 Days (Orange - Approaching Critical Timeframe)' ) from Account where FRB_Firm_Check__c = TRUE];        if(lstAccount2.size()&gt;0){           for( Account acc : lstAccount2 )           {               List&lt;Process_Adherence_Monitoring__c&gt; lstCont2 = acc.Process_Adherence_Monitoring__r;                acc.Number_of_exceptions_under_7_10_bucket__c= lstCont2.size();               acc.FRB_Firm_Check__c = true;           }           update lstAccount2 ;       }        //To update 11 bucket            lstAccount3 =  [select Id, Name,FRB_Firm_Check__c, Number_of_exceptions_under_11_19_bucket__c,(select id,Account_Standing__c from Process_Adherence_Monitoring__r  where Status__C != 'Closed' AND  recordtypeId =: FRBRecordTypeID AND Account_Standing__c = '11-19 Days (Red - Immediate Correction Required)' ) from Account where FRB_Firm_Check__c = TRUE];        if(lstAccount3.size()&gt;0){           for( Account acc : lstAccount3 )           {               List&lt;Process_Adherence_Monitoring__c&gt; lstCont3 = acc.Process_Adherence_Monitoring__r;                acc.Number_of_exceptions_under_11_19_bucket__c= lstCont3.size();               acc.FRB_Firm_Check__c = true;           }                              update lstAccount3 ;       }      }   }
55284
When I run the following query in query editor I get this error.  Error     Unknown error parsing query   Query  SELECT Id, OwnerId,Account.OwnerId FROM Contact where OwnerId = Account.OwnerId limit 100   Does anybody know what the problem is?
79591
I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this.   I'm working on trying to use alias names to pull two different sets of tables and display the fields in each table, all while ordering them by a specific field.  Here is the code I'm currently trying to use when to do that:    SELECT ars.facility_id, ars.service_code, ars.coding_system,    ars.result_label_seq, ars.display_seq, ats.facility_id,    ats.service_code, ats.coding_system, ats.display_name, ats.test_name    FROM anc_test_codes AS ats,   anc_results_seqs AS ars    WHERE ats.service_code = ars.service_code   AND ars.coding_system = ats.coding_system   AND ars.facility_id = ats.facility_id    AND ats.facility_id = 'C' AND ats.service_code = 'CBC'   ORDER BY ats.display_name   I'm sure my methods and style is totally hideous, so excuse this.  The error I'm getting is a generic Oracle SQL error: SQL Command Not Properly Ended - ORA-00933  Can anyone tell me what I'm doing wrong? Any help on this would be greatly appreciated.    Thanks!
28038
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure?   For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it.   SQL Server 2005 is my only applicable limitation I think.  create procedure getDepartments   @DepartmentIds varchar(max) as   declare @Sql varchar(max)        select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'   exec(@Sql)
98617
I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.  How things start:  SELECT COUNT(fullpath) FROM data;  12395   I use the column fullpath a lot so I create an index:  CREATE INDEX "index" ON data (fullpath);   I then add the column:  ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);   Insert values into the Sub column based on the fullpath:  insert into data(Sub) select case     when fullpath like '%subA%' then 'subA'     when fullpath like '%subB%' then 'subB'     else 'neither' end from data;   Check the number of rows again:  SELECT COUNT(fullpath) FROM data;  24790   I don't understand the syntax and the operations. Do I need to join or something to respect the index?