Trying to understand what "covering a query means" with a specific example If I have a table with say 3 columns:  Col1  Col2  Col3  And I put an index on Col1 and Col2  is "covering a query" determine by the columns selected in the SELECT or the columns in the the WHERE Clause? Thus :  1) select Col1, Col2 from MyTable where Col3=XXX  2) Select Col3 from MyTable where Col1=xxx and Col2=yyy 3) Select Col1, Col2 from MyTable where Col1=xxx and Col2=yyy   Which of these three are truly "Covered"?
123497
Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft's SQL Server?
98818
I am using SQL server, and I have been looking closely at the concept of key lookups,    So if you have a key lookup you can create an index with the 'include' columns to cover the non index columns you have in the select statement.  For instance,  SELECT ID, FirstName FROM OneIndex WHERE City = 'Las Vegas' GO   This index will include a key lookup,  CREATE NONCLUSTERED INDEX [IX_OneIndex_City] ON [dbo].[OneIndex] ( [City] ASC ) ON [PRIMARY] GO   But this one will remove the key lookup,  CREATE NONCLUSTERED INDEX [IX_OneIndex_Include] ON [dbo].[OneIndex] ( City ) INCLUDE (FirstName,ID) ON [PRIMARY] GO   I mean how much of an impact will this have on performance? The key lookup has an operator cost of 0.295969 (99%), but what does that really mean?  How do you know that you need the second index there, and at what point does it become the case that you are trying to add too many indexes and it is not worth it?  It seems to me that some queries can include index scans, key lookups, and still seem to perform very fast.
10615
I have a query to insert data from one table into another. One of the source columns is larger than the destination table's, although the actual data selected in the query isn't. So e.g. the source table is varchar(25), the destination table is varchar(10), and the query selects records that do not exceed 10 characters for that specific column.  My initial query failed with a truncation error, but when I rewrote it selecting the same records but changing the where clause i.e. to use WHERE IN and filtering on a cte it ran successfully.  i.e instead of doing  select * from tbl where col1 = 1   I did:  with cte as (select 1) select * from tbl where col1 in (select 1 from cte)   I understand that this is a bug in SQL Server, still I am wondering at which point does SQL Server validate the data for insertion. At which point is the error thrown?
61005
If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update?  For example would there be any difference in execution speed between UPDATE 1 and UPDATE 2 in the following:  CREATE TABLE MyTable (ID int PRIMARY KEY, Value int); INSERT INTO MyTable (ID, Value) VALUES     (1, 1),     (2, 2),     (3, 3);  -- UPDATE 1 UPDATE MyTable SET     Value = 2 WHERE     ID = 2     AND Value &lt;&gt; 2; SELECT @@ROWCOUNT;  -- UPDATE 2 UPDATE MyTable SET     Value = 2 WHERE     ID = 2; SELECT @@ROWCOUNT;  DROP TABLE MyTable;   The reason I ask is that I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. As such I used the UPDATE 2 form. If there is a performance benefit to using the UPDATE 1 form, is it possible to get the row count that I need somehow?
118445
I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since that is putting the expression in the field selection, not the where clause.  Here is what I have tried:  $category = 'mobile wifi'; $query = db_select('taxonomy_term_data', 'ttd')     -&gt;fields('ttd', array('tid')); $query-&gt;innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid'); $query-&gt;addExpression("LOWER(ttd.name) = $category"); $result = $query-&gt;condition('machine_name', 'images_cat', '=')     -&gt;execute()     -&gt;fetchAssoc();   And this:  $category = 'mobile wifi'; $query = db_select('taxonomy_term_data', 'ttd')     -&gt;fields('ttd', array('tid')); $query-&gt;innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid'); $result = $query-&gt;condition('machine_name', 'images_cat', '=')     -&gt;condition('LOWER(ttd.name)', $category, '=')     -&gt;execute()     -&gt;fetchAssoc();   The second one becomes invalid and the first one causes the problem I mentioned above. Any thoughts or suggestions?  Thanks, Patrick
59876
Out of the following queries, which method would you consider the better one? What are your reasons (code efficiency, better maintainability, less WTFery)...  SELECT MIN(`field`) FROM `tbl`;  SELECT `field` FROM `tbl` ORDER BY `field` LIMIT 1;
82262
I'm just getting started with Postgres. Reading  I came across this query:  SELECT title, ts_rank_cd(textsearch, query) AS rank FROM apod, to_tsquery('neutrino|(dark &amp; matter)') query WHERE query @@ textsearch ORDER BY rank DESC LIMIT 10;   I can understand everything in this query, except for this: FROM apod, ....  What does this , mean? I'm used to joins but not to multiple FROM statements separated by a comma.  I searched the net for no avail. After looking at it and thinking, it seems to me that it's declaring a variable called query so it can use it multiple times. But if this is true, what does it have to do with FROM?
84603
Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed?  Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key.  Edit: an additional question - will it do any harm to additionally index the primary key?
16003
We're working on a Log Viewer. The use will have the option to filter by user, severity, etc. In the Sql days I'd add to the query string, but I want to do it with Linq. How can I conditionally add where-clauses?
50025
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.  I need something like  SELECT * FROM table AS t WHERE ANY(t.* IS NULL)   I don't want to have to do  SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL   This would be a HUGE query.
110579
I've been working with databases for the last few years and I'd like to think that I've gotten fairly competent with using them. However I was reading recently about Joel's  and I realised that even though I can write a query to get pretty much anything I want out of a database, I have no idea how the database actually interprets the query. Does anyone know of any good articles or books that explain how databases work internally?  Some specific things I'm interested in are:   What does a database actually do to find out what matches a select statement? How does a database interpret a join differently to a query with several "where key1 = key2" statements? How does the database store all its memory? How are indexes stored?
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.
85186
SELECT name, gdp/population as capita  FROM world w1  WHERE continent = 'Europe'       AND capita &gt;  (     SELECT gdp/population      FROM world      WHERE name = 'United Kingdom' );   How come I can't refer to capita in the WHERE clause? It returns this error:  Unknown column 'capita' in 'where clause'   I can however, run the query if I replace capita with gdp/population.  I don't want to do gdp/population &gt; .... How can I do capita &gt; ...? Or is this not possible?
110989
I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?    In the example shown, the query should return count as 4 rows.
53567
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):  SELECT A.identifier      , A.name      , TO_NUMBER(DECODE( A.month_no              , 1, 200803               , 2, 200804               , 3, 200805               , 4, 200806               , 5, 200807               , 6, 200808               , 7, 200809               , 8, 200810               , 9, 200811               , 10, 200812               , 11, 200701               , 12, 200702              , NULL)) as MONTH_NO      , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE   FROM table_a A      , table_b B  WHERE A.identifier = B.identifier    AND MONTH_NO &gt; UPD_DATE   The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
42749
If i run below query it returns all records of that object:  Select ID, name from Contact where Name like '%%%'   This should return only those records which have % in the name. For example if i run below query it will return only those records which have test in the name.  Select ID, name from Contact where Name like '%test%'   Any idea, What is going on here?
110155
If I have a query like:  Select EmployeeId  From Employee  Where EmployeeTypeId IN (1,2,3)   and I have an index on the EmployeeTypeId field, does SQL server still use that index?