I have a query which is written in format like this: How do I convert this query to a plain SQL Server query to see what its output is? Answer In Entity Framework there are a few ways to look at the SQL a query generates. Note: All these ways will use this query: Cast IQueryable to an ObjectQuery and
Tag: sql-server
Join many to many tables optionally
I have below tables User Intrest Hobby User_Intrest User_Hobby Now to find user ids who have both interests Eating and Sleeping I have written Output Same as above I also can find user ids with hobbies Smoking, Hiking, Browsing as below Output Now I want to mix these two optionally in a way that if only inter…
A problem with a simple join taking too long to finish
I´m having trouble identifying who to fix this simple join. My problem is, after adding the column B.CIDPRODUCT IS NULL this query takes hours to finish. table BO_PRICER001 rows 286537 table BO_PRODUCTCONFIG rows 7934844 on the table BO_PRICER001 exists 15329 rows with null with B.CIDPRODUCT IS NULL the live …
Use a common field in two tables with XML in SQL Server
How delete the redundance in the table #XMLItm? My code: In this code I want load information in two tables for a common field ID_LIST. How load the ID_LIST from #XMLLst in the temp table #XMLItm? I want delete the field ID_LIST from XML in the table #XMLItm and take a ID_LIST from temp table #XMLLst; Functio…
SQL – How to call a part of query conditionally
I need to write a SQL query like this: do step1 do step2 do step3 – this step does a lot of stuffs: define variables, use some variables defined in step 1, query a lot of data into temporary table… …
SQLGroup with Distinct count
I have query Outcome Not sure why not group by range and expect results. Thank you. Answer I would suggest cross apply to define the alias: SQL Server doesn’t allow aliases as GROUP BY keys. I think defining the alias in the FROM clause is the simplest method; you could also use a CTE or subquery or rep…
Find a word from a string in SQL Server
I have some values into a table like below :- Bank Of America testBank Of America State Bank Of India ICICI Bank Test @Bank* I want a query to get all the values which contains Bank only. Proper word should match. Result should be like :- Bank Of America State Bank Of India ICICI Bank Answer One method is to
How to select 7 rows or more from select query? even if table is returning less than 7 rows
I want a script that gives me an exact 7 rows. For example, if my table returns only 2 rows then we have to add another 5 rows with all columns contains a null value. But if the table returns more than 7 rows then select all returned rows. Please Help! Answer Create a dummy table with 2 Fields Now
SQL Server : assistance with INSERT INTO
I have a problem where I need to create new entries in the SpecialOfferProduct table. I am applying my created discount (SpecialOfferID = 20) to all products that have an inventory greater than 1800 but I can’t figure out what I am doing wrong. this is my code. It’s from the AdventureWorks2012 dat…
Optimal join from a column to the concatenation of those columns?
I have a table TableLHS with a column ObjInfo: which I would like to join to TableRHS, with columns: So the join here involves 1) dropping the leading zeroes from ObjNumber, and 2) concatenating the three columns in TableRHS together. My best shot at the join is: My current performance could use some improvem…