So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect. What I end up doing, is getting a standard set of items (rows) from another table that contains the defaults for each country, and then store it in this sessiontable with a guid in the sessionid field. The user can then modify the items for their session only. What I want to do instead is to search for an existing session match that has the same items and respective values, to avoid session repetition as the processing later in the application is expensive (reports etc). A match would return the sessionid, otherwise I will stash the new session and items for repeated use. I'm struggling to search for a solution on stackExchange (im working off [this q/a](https://dba.stackexchange.com/questions/72641/checking-whether-two-tables-have-identical-content-in-postgresql) , I've realised EXIST and UNION won't work because I dont know the sessionid and my users session guid wont match (hence the question to begin with). I'm thinking something to do with group by sessionid, and check with the worlds longest sql query (or LINQ madness) with a check for each item and item-field, and addtionally a check that no other (unmatched) items exist in matching sessions, and in theory there will only ever be one match or none! Here's my pseudo-code: useritems = getCountryDefaultItems(); var existingid =( select grpresult from (select item from db.sessionints group by item.sessionid where item in useritems (ignore sessionid) ) g where g.count == useritems.count ) .first().sessionid; I've had a go, and I believe it should be FULL OUTER JOIN so I hear, but sqlfiddle says no so I played with left + right + inner joins :/ sqlfiddle SELECT COUNT(1),a.SessionID FROM sessionitems a RIGHT JOIN useritems b -- should be FULL OUTER JOIN ? USING (SessionID,InterventionCode, Included, IntLvl, Uptake Baseline Smokers, RelativeEffect, Cost, IntType, Baseline, Current, Alternative, UpPopulation, EffPopulation, Name) WHERE (a.InterventionCode IS NULL OR b.InterventionCode IS NULL) AND (a.Included IS NULL OR b.Included IS NULL) AND (a.IntLvl IS NULL OR b.IntLvl IS NULL) AND (a.Uptake Baseline Smokers IS NULL OR b.Uptake Baseline Smokers IS NULL) AND (a.RelativeEffect IS NULL OR b.RelativeEffect IS NULL) AND (a.Cost IS NULL OR b.Cost IS NULL) AND (a.IntType IS NULL OR b.IntType IS NULL) AND (a.Baseline IS NULL OR b.Baseline IS NULL) AND (a.Current IS NULL OR b.Current IS NULL) AND (a.Alternative IS NULL OR b.Alternative IS NULL) AND (a.UpPopulation IS NULL OR b.UpPopulation IS NULL) AND (a.EffPopulation IS NULL OR b.EffPopulation IS NULL) AND (a.Name IS NULL OR b.Name IS NULL) GROUP BY a.SessionID; , but sqlfiddle is having errors doing MSSQL schema today, so I've used mysql for now which doesnt support FULL OUTER JOIN on sqlfiddle: I can get a count of exactly matching rows when there's an exact match, but the id comes back null, and if I remove the COUNT(1) and return * it returns only one row (is this a restriction on results in nested select queries) which might be okay as the session is a match, but I'm worried I have overlooked something and don't appreciate where the query will fall down...

as stated in the title i start a performance tuning of a table query with pagination generated by a legacy program that use Linq To SQL as ORM. I have found this resource in which is highly recommended to sort table before pagination : https://rimdev.io/optimizing-linq-sql-skip-take/ So i have follow the suggestion provided and experimented an huge difference. It's clear to me that is somewhat related on how row_nummber is calculated but is not clear to me exactly what happened and why there is so much difference between the two queries. Original slow query (dataset of ~7K elements, take ~3s) : SELECT [t7].[ID], [t7].[ID_BRAND], [t7].[CODE], [t7].[CODFOR], [t7].[COD_ALT01], [t7].[COD_ALT02], [t7].[COD_ALT03], [t7].[ID_UOM], [t7].[IS_ACTIVE], [t7].[_ATTRIBUTES] AS [_ATTRIBUTES], [t7].[_DOCUMENTS] AS [_DOCUMENTS], [t7].[_SEO] AS [_SEO], [t7].[_TRANSLATIONS] AS [_TRANSLATIONS], [t7].[_TAGS] AS [_TAGS], [t7].[_NOTES] AS [_NOTES], [t7].[_METADATA] AS [_METADATA], [t7].[IS_B2B], [t7].[IS_B2C], [t7].[IS_PROMO], [t7].[IS_NEWS], [t7].[CAN_BE_RETURNED], [t7].[IS_SHIPPABLE], [t7].[HAS_SHIPPING_COSTS], [t7].[IS_PURCHEASABLE], [t7].[test], [t7].[ID2], [t7].[CODE2], [t7].[BUSINESS_NAME], [t7].[NAME], [t7].[PHONE_01], [t7].[PHONE_02], [t7].[PHONE_03], [t7].[FAX_01], [t7].[FAX_02], [t7].[COUNTRY_01], [t7].[CITY_01], [t7].[ADDRESS_01], [t7].[COUNTRY_02], [t7].[CITY_02], [t7].[ADDRESS_02], [t7].[EMAIL_01], [t7].[EMAIL_02], [t7].[PEC], [t7].[SITE_01], [t7].[SITE_02], [t7].[SITE_03], [t7].[SITE_04], [t7].[VAT_NUMBER], [t7].[SORT], [t7].[GROUPID_01], [t7].[IS_GROUPLEADER_01], [t7].[GROUPID_02], [t7].[IS_GROUPLEADER_02],[t7].[IS_ACTIVE2], [t7].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t7].[[_SEO]]2] AS [_SEO2], [t7].[[_METADATA]]2] AS [_METADATA2], [t7].[test2], [t7].[ID3], [t7].[CODE3], [t7].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t7].[[_METADATA]]3] AS [_METADATA3], [t7].[test3], [t7].[ID4], [t7].[ID_LINE], [t7].[ID_GROUP], [t7].[ID_CLASS], [t7].[ID_FAM], [t7].[ID_ARTICLE] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID], [t2].[CODE], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE], [t2].[_DOCUMENTS], [t2].[_SEO], [t2].[_METADATA], [t4].[test], [t4].[ID], [t4].[CODE], [t4].[_TRANSLATIONS], [t4].[_METADATA], [t6].[test], [t6].[ID], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[CODE] AS [CODE2], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE] AS [IS_ACTIVE2], [t2].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t2].[_SEO] AS [[_SEO]]2], [t2].[_METADATA] AS [[_METADATA]]2], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE3], [t4].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t4].[_METADATA] AS [[_METADATA]]3], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE] FROM [dbo].[tbl_ana_Articles] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[ID], [t1].[CODE], [t1].[BUSINESS_NAME], [t1].[NAME], [t1].[PHONE_01], [t1].[PHONE_02], [t1].[PHONE_03], [t1].[FAX_01], [t1].[FAX_02], [t1].[COUNTRY_01], [t1].[CITY_01], [t1].[ADDRESS_01], [t1].[COUNTRY_02], [t1].[CITY_02], [t1].[ADDRESS_02], [t1].[EMAIL_01], [t1].[EMAIL_02], [t1].[PEC], [t1].[SITE_01], [t1].[SITE_02], [t1].[SITE_03], [t1].[SITE_04], [t1].[VAT_NUMBER], [t1].[SORT], [t1].[GROUPID_01], [t1].[IS_GROUPLEADER_01], [t1].[GROUPID_02], [t1].[IS_GROUPLEADER_02], [t1].[IS_ACTIVE], [t1].[_DOCUMENTS], [t1].[_SEO], [t1].[_METADATA] FROM [dbo].[tbl_ana_Brands] AS [t1] ) AS [t2] ON [t2].[ID] = [t0].[ID_BRAND] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[_TRANSLATIONS], [t3].[_METADATA] FROM [dbo].[tbl_ana_UoMs] AS [t3] ) AS [t4] ON [t4].[ID] = [t0].[ID_UOM] LEFT OUTER JOIN ( SELECT 1 AS [test], [t5].[ID], [t5].[ID_LINE], [t5].[ID_GROUP], [t5].[ID_CLASS], [t5].[ID_FAM], [t5].[ID_ARTICLE] FROM [dbo].[tbl_src_ArticlesCategories] AS [t5] ) AS [t6] ON [t6].[ID_ARTICLE] = [t0].[ID] WHERE ( (CASE WHEN 1 = 1 THEN CONVERT(Int,[t0].[IS_ACTIVE]) ELSE 0 END)) = 1 ) AS [t7] WHERE [t7].[ROW_NUMBER] BETWEEN 7272 + 1 AND 7284 ORDER BY [t7].[ROW_NUMBER] Here slow query execution plan : https://www.brentozar.com/pastetheplan/?id=Sk-rLnY3F Revised fast query (dataset of ~7K elements, take ~0s) : SELECT [t7].[ID], [t7].[ID_BRAND], [t7].[CODE], [t7].[CODFOR], [t7].[COD_ALT01], [t7].[COD_ALT02], [t7].[COD_ALT03], [t7].[ID_UOM], [t7].[IS_ACTIVE], [t7].[_ATTRIBUTES] AS [_ATTRIBUTES], [t7].[_DOCUMENTS] AS [_DOCUMENTS], [t7].[_SEO] AS [_SEO], [t7].[_TRANSLATIONS] AS [_TRANSLATIONS], [t7].[_TAGS] AS [_TAGS], [t7].[_NOTES] AS [_NOTES], [t7].[_METADATA] AS [_METADATA], [t7].[IS_B2B], [t7].[IS_B2C], [t7].[IS_PROMO], [t7].[IS_NEWS], [t7].[CAN_BE_RETURNED], [t7].[IS_SHIPPABLE], [t7].[HAS_SHIPPING_COSTS], [t7].[IS_PURCHEASABLE], [t7].[test], [t7].[ID2], [t7].[CODE2], [t7].[BUSINESS_NAME], [t7].[NAME], [t7].[PHONE_01], [t7].[PHONE_02], [t7].[PHONE_03], [t7].[FAX_01], [t7].[FAX_02], [t7].[COUNTRY_01], [t7].[CITY_01], [t7].[ADDRESS_01], [t7].[COUNTRY_02], [t7].[CITY_02], [t7].[ADDRESS_02], [t7].[EMAIL_01], [t7].[EMAIL_02], [t7].[PEC], [t7].[SITE_01], [t7].[SITE_02], [t7].[SITE_03], [t7].[SITE_04], [t7].[VAT_NUMBER], [t7].[SORT], [t7].[GROUPID_01], [t7].[IS_GROUPLEADER_01], [t7].[GROUPID_02], [t7].[IS_GROUPLEADER_02],[t7].[IS_ACTIVE2], [t7].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t7].[[_SEO]]2] AS [_SEO2], [t7].[[_METADATA]]2] AS [_METADATA2], [t7].[test2], [t7].[ID3], [t7].[CODE3], [t7].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t7].[[_METADATA]]3] AS [_METADATA3], [t7].[test3], [t7].[ID4], [t7].[ID_LINE], [t7].[ID_GROUP], [t7].[ID_CLASS], [t7].[ID_FAM], [t7].[ID_ARTICLE] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[CODE] AS [CODE2], [t2].[BUSINESS_NAME], [t2].[NAME], [t2].[PHONE_01], [t2].[PHONE_02], [t2].[PHONE_03], [t2].[FAX_01], [t2].[FAX_02], [t2].[COUNTRY_01], [t2].[CITY_01], [t2].[ADDRESS_01], [t2].[COUNTRY_02], [t2].[CITY_02], [t2].[ADDRESS_02], [t2].[EMAIL_01], [t2].[EMAIL_02], [t2].[PEC], [t2].[SITE_01], [t2].[SITE_02], [t2].[SITE_03], [t2].[SITE_04], [t2].[VAT_NUMBER], [t2].[SORT], [t2].[GROUPID_01], [t2].[IS_GROUPLEADER_01], [t2].[GROUPID_02], [t2].[IS_GROUPLEADER_02], [t2].[IS_ACTIVE] AS [IS_ACTIVE2], [t2].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t2].[_SEO] AS [[_SEO]]2], [t2].[_METADATA] AS [[_METADATA]]2], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE3], [t4].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t4].[_METADATA] AS [[_METADATA]]3], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[ID_LINE], [t6].[ID_GROUP], [t6].[ID_CLASS], [t6].[ID_FAM], [t6].[ID_ARTICLE] FROM [dbo].[tbl_ana_Articles] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[ID], [t1].[CODE], [t1].[BUSINESS_NAME], [t1].[NAME], [t1].[PHONE_01], [t1].[PHONE_02], [t1].[PHONE_03], [t1].[FAX_01], [t1].[FAX_02], [t1].[COUNTRY_01], [t1].[CITY_01], [t1].[ADDRESS_01], [t1].[COUNTRY_02], [t1].[CITY_02], [t1].[ADDRESS_02], [t1].[EMAIL_01], [t1].[EMAIL_02], [t1].[PEC], [t1].[SITE_01], [t1].[SITE_02], [t1].[SITE_03], [t1].[SITE_04], [t1].[VAT_NUMBER], [t1].[SORT], [t1].[GROUPID_01], [t1].[IS_GROUPLEADER_01], [t1].[GROUPID_02], [t1].[IS_GROUPLEADER_02], [t1].[IS_ACTIVE], [t1].[_DOCUMENTS], [t1].[_SEO], [t1].[_METADATA] FROM [dbo].[tbl_ana_Brands] AS [t1] ) AS [t2] ON [t2].[ID] = [t0].[ID_BRAND] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[_TRANSLATIONS], [t3].[_METADATA] FROM [dbo].[tbl_ana_UoMs] AS [t3] ) AS [t4] ON [t4].[ID] = [t0].[ID_UOM] LEFT OUTER JOIN ( SELECT 1 AS [test], [t5].[ID], [t5].[ID_LINE], [t5].[ID_GROUP], [t5].[ID_CLASS], [t5].[ID_FAM], [t5].[ID_ARTICLE] FROM [dbo].[tbl_src_ArticlesCategories] AS [t5] ) AS [t6] ON [t6].[ID_ARTICLE] = [t0].[ID] WHERE ( (CASE WHEN 1 = 1 THEN CONVERT(Int,[t0].[IS_ACTIVE]) ELSE 0 END)) = 1 ) AS [t7] WHERE [t7].[ROW_NUMBER] BETWEEN 7272 + 1 AND 7284 ORDER BY [t7].[ROW_NUMBER] Here the fast query exectution plan : https://www.brentozar.com/pastetheplan/?id=B10l82K2Y Note : all the query code is autogenerated by ORM The two queries look very similar, and is not clear to me what improve so drammatically the performance. I really appreciate an hint in what help SQL Server so much, so that i can better understand how to tune ORM in future.

Disclaimer: I am a beginner in the field of database. I am trying to design a database in PostgreSQL which will record the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM and Repository approach. The simplified proforma is like this: Items: Details of items we deal with. Purchases: Records the details of items we purchased. Sales: Records the details of items we sold. Stock: Quantity of each Item in hand. Now, I also have a stock table where current stock of each item is maintained. The dilemma I am facing is this: When any Invoice is generated, it indicates that a sale has occurred, and the stock of each item in the Invoice is updated accordingly. Similarly, when a purchase from our side occurs, we record it in the purchases table, and the respective items in Stock table has their quantity updated. I would have to also consider the scenario of correcting the stock table if an existing invoice is modified or deleted. These are the only three scenarios when the stock table would change. Now, I can achieve this update of stock table using Triggers. I can also use stored procedures in order to encapsulate the entry in Purchases or Sales table along with updating the stock table. I can also do this in application logic using LINQ. Which approach would be the most pragmatic one? **THE DETAILED STORY** Please allow me to discuss the entities that matter in this current context. We are a reseller. We buy materials in bulk, we process the materials and repackage them and then sell them. In this table, the items we sell are marked as the entity ITEM. There is are two attributes in the ITEM table worth noting. One is MaterialType and the other NumberOfPieces. The entity MATERIAL is the raw material we buy. Now suppose, we buy 100 pieces of pens, and we resell them in units of 1 or 5. So, in this case, the records in the item table would be 1. Pens x1 (nickname) --- Pen (MaterialType) ----- 1 (NumberOfPieces) 2. Pens x2 (nickname) --- Pen (MaterialType) ------5 (NumberOfPieces) So, in PURCHASES table, we record the Material we have bought and the Quantity of it. With each purchase, we need to make three modifications: 1. Insert the purchase details in PURCHASE 2. Modify CurrentStock in MATERIAL(increase it by the amount we have purchased) 3. Insert the amount of increase in Materials in STOCKHISTORY table. Please note that StockHistory does not record the cumulative stock, but just the amount of increase/decrease associated with each transaction. Similarly, for each sale, we need to make three modifications. 1. Insert the sales details in INVOICE. 2. Modify CurrentStock in MATERIAL(decrease it by the amount we have purchased) 3. Insert the amount of decrease in Materials in STOCKHISTORY table. Please note that INVOICE table here represents the invoices generated by us, i.e. the sales made from our end. Here is the schema: Now, if we need to delete or modify any record of PURCHASES or INVOICE, we also need to modify the CurrentStock in MATERIAL as well as the corresponding records in STOCKHISTORY table. Here is a rundown of STOCKHISTORY table: 1. Material records the material whose stock has changed. 2. Amount records the amount by which the stock has changed. 3. Invoice records the invoice number of the transaction. Now here is where it gets murky. And I need help with the schema here. The FlowDirection records whether it was a purchase or a sale. The PurchaseId records the invoice number of the purchase IF it was a purchase. The SalesId records the invoice number of the sale IF it was a sale. There is a constraint on the table where the nullity of PurchaseId and SalesId attributes are XOR'ed, i.e. One and only one of them will be null for a given record. I am thinking of getting rid of the FlowDirection as it is obviously redundant. Secondly, I also will get rid of the Invoice attribute, as it is already recorded in PurchaseId/SalesId. Or, I can point Invoice column to a union of Num attribute of the INVOICE table and the Invoice attribute of the Purchase table (I have got to rename all these tables and attributes too). Which one should be a better approach?

Given the following table CREATE TABLE Test( Id INT PRIMARY KEY IDENTITY(1, 1), Val INT ) CREATE UNIQUE INDEX UX_Test_Val ON Test(Val) WHERE Val IS NOT NULL DECLARE @i INT = 0 WHILE (@i x.Val == 123).FirstOrDefault(); this gets compiled to something similar to DECLARE @V INT SELECT TOP 1 Id FROM Test WHERE Val = @V OR (Val IS NULL AND @V IS NULL) Seems legit. But I am very disappointed when seeing the clustered index scan in the query plan. The plan looks way better when I remove the second clause. SELECT TOP 1 Id FROM Test WHERE Val = @V This time the plan is a non clustered Index Seek with 1 row read So my first guess is that the filtered index is the evil here and I created a second non-clustered not unique index on the same column CREATE INDEX IX_Test_Val ON Test(Val) This time both statements uses the newly created index and yields to the same index seek plan. Here comes three questions: 1. Why does filtered index has such poor performance in this case? 2. How can I make linq to omit the IS NULL part? 3. Is there a way to enforce unique constrain that allows NULL with just one non filtered index?

I want to use group by in C#, and my result must be like this code in SQL: SELECT MAX(EffectiveDate) AS EffectiveDate,details.PersonnelBaseID FROM dbo.tkp_PersonnelDetails AS details where details.EffectiveDate <= @Time GROUP BY PersonnelBaseID @Time is an input parameter; assume tkp_PersonnelDetails is a list of objects in C#.

After working on my database a bit, I am working on a VB.Net app that accesses it using LINQ. I have the following stored procedure in the database: CREATE PROCEDURE dbo.GetTableColumn( @ColName VARCHAR(MAX), @TblName VARCHAR(MAX), @Result BIT OUT ) AS BEGIN IF (EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TblName AND COLUMN_NAME = @ColName)) BEGIN DECLARE @SQL VARCHAR(MAX) SET @SQL = 'SELECT ' + @ColName + ' FROM ' + @TblName EXEC (@SQL) SET @Result = 1 END ELSE SET @RESULT = 0 RETURN @Result END I have added it to my DataLINQContext and call it in the following method in my VB.Net code: Public Function GetTableColumn(ByVal col As String, ByVal table As String) As AutoCompleteStringCollection GetTableColumn = New AutoCompleteStringCollection Using dbContext As New Customer_LINQDataContext Dim result As Boolean Dim query = dbContext.GetTableColumn(col, table, result) MessageBox.Show(query.ToString()) End Using End Function Now what I am trying to accomplish, is that the query object receives the data from the query performed by the procedure. This does not happen. Instead I only get a 1 or 0 result, based off whether or not the column exists within the table. So obviously something is wrong somewhere. I feel it's a mess up on the part of my procedure, but I am not sure how to code it to accomplish what I want it to do.

We have a large number of tables for setup forms. In each form, there is a need to fill in the Code and the Description. The Code consists of a varchar(10) datatype. Now, the thing is, every time a user creates new data or updates the data, there is a need to check whether the Code conflicts with the others or not. In LINQ, this code checking is inevitable: string ExistingCode = "ITEM01"; int ExistingKey = 1; var recordWithConflictedCode = (from a in db.MyTable where a.Code == ExistingCode && a.Key != ExistingKey).ToList(); if(recordWithConflictedCode.Count >= 1) return BadRequest("Duplicated Code found"); Now, since this involves a string comparison on every process, I think it makes sense to index the column Code to make the query faster. Is this a common practice for you guys?

I have three tables Lab, Payments and Transaction I want to spilt transType into two columns, sum TransType Group By BillId and compare it with Lab.TotalBill to get Refundable Payments/Due payments I need SQL Query or LINQ Query please

I have posted a similar question before, but this one is a little modified. I have a table with 4 columns as shown below. > ITEMCHARGE CPT4 EFF_DATE CPT4MOD > 326.8 29075 20101204 GO > 326.8 29075 20110104 GO > 326.8 29075 20110204 GO > 326.8 29075 20110406 GO > 352.69 29075 20090611 GO > 352.69 29075 20090917 GO > 352.69 29075 20100614 GO > 352.69 29075 20100722 GO Now, if CPT4 and CPT4MOD match, I need to keep the rows with distinct ITEMCHARGE (with latest EFF_DATE). The output should be like below: ITEMCHARGE CPT4 EFF_DATE CPT4MOD 326.8 29075 20110406 GO 352.69 29075 20100722 GO There are 2 distinct ITEMCHARGE for the same CPT4 and CPT4MOD. So we took the latest EFF_DATE row for both of them. It would be very helpful If I can get a linq (with lamda) and SQL version for this.

I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date. **Deletion Condition:** When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records. I need a query which can achieve this in two scenarios. 1. When CPT4Mod is given by the user. 2. When CPT4Mod is not given by the user. Later I need to achieve the same using LINQ.

I just need to confirm that I understand something correctly: I recently viewed an SO question in which a user posted an answer in Linq like: from p in db.table where p.column.AddMinutes(1) > DateTime.Now select p To those unfamiliar with Linq, I would expect the output of that statement (not tested it in fairness) to be: SELECT * FROM table t WHERE DATEADD(min, 1, t.column) >= GETDATE() I posted an reply to this saying that the datetime manipulation should be on the variable (in this case GETDATE()) so in fact the statement should reflect something like: SELECT * FROM table t WHERE t.column >= DATEADD(min, -1, GETDATE()) In my reply, the bits i'm now unsure of, assume the following: 1. Indexes will not be used because of the manipulation of the column 2. The query plans will be different partly because of the above (not tested, assuming so) 3. Because of the above, the 1st query will actually perform worse than the 2nd. **My question:** Have I missed anything out in my reasoning? Am I correct? Lastly, does any body have any good articles on SARGability?

Please let me explain my problem and situation: I have a web application - MVC3, MSSQL Server 2005, LinqToSQL. It has been running great until one fine morning I pushed a lot of rows to a table that is heavily used and since then I was getting query timeouts. In order to fix the problem I run the Database Tuning Advisor and I added some Indexes and Statistics. I also created a maintenance plan to rebuild indexes daily. After those additions, the application has been behaving unstable; it would work fast for couple of hours then it would start timing out again. Next, life forced me to clean up the table in matter, and the amount of rows in it is even smaller now than it was before but the timeouts are still happening. So, I removed all indexes that I created and now the website is much more stable but from time to time I still see some timeouts. I've been trying to figure out how to fix those queries and when I profile it and paste the query directly into the SQL Management Studio it returns the results in 1 second, but when I run this query from my application, it's about 25 seconds. Then after it runs for the first time, next time it goes as fast as on the server! I started doing some research and it looks like when I played with all those indexes my query plans got messed up and now they are creating issues. My questions are : 1. Should I refresh my query plan cache (around 22000 queries - a lot of them has been used only once) and 2. If I do it, what would the impact be on the SQL while they are all rebuilding?

I'm not sure if this goes here or on Stack Overflow, but I figure someone here might know it. Why did the developers of LINQ decide that not only do you need ctx.InsertOnSubmit(item); but you also need ctx.Items.SubmitChanges(); Did they do this so you could execute several queries on one trip to the database or am I missing something?