Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with query
Search options answers only
not deleted
user 1186
Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).
18
votes
Accepted
Combine column from multiple rows into single row
This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless yo …
- 182k
16
votes
Accepted
Are these two queries logically equivalent?
In this case, I much prefer query 2, especially if LogDateTime is indexed (or might ever be).
I don't like the shorthand date math and I recommend against it. …
- 182k
8
votes
Extract rows of a table, that include less than x NULLs
test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
(2,NULL,NULL,1,2),
(2,3,NULL,NULL,2),
(NULL,3,NULL,1,NULL),
(2,3,NULL,1,2);
To see why ISNULL isn't effective here, run this query …
- 182k
7
votes
Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?
If you don’t believe the commands you’re issuing clear the plan cache / buffer pool, you can check those things via DMVs right before issuing your query. …
- 182k
6
votes
Accepted
Invalid column name even after checking if it exists so it should skip the query part
<query that references column_name> ...;';
END
Also here is why I avoid shorthand metadata helpers like OBJECT_NAME(), SCHEMA_NAME(), etc., and instead recommend creating your own helper views. …
- 182k
6
votes
Accepted
SQL To Fetch Partition Boundaries and Filegroups
This relationship isn't altogether intuitive but I think this query will get you what you need:
SELECT
PScheme = ps.name,
PFunction = pf.name,
Boundary = dds.destination_id,
BoundaryValue …
- 182k
5
votes
when selecting from multiple tables I get a multiplication of the records
You should consider using the more modern INNER JOIN syntax. Using table1, table2, table3 often leads to cartesian product. It's still possible with table1 INNER JOIN table2 ON ... INNER JOIN table3 O …
- 182k
5
votes
Should Query Tuning be Proactive or Reactive?
individual and overall performance to known baselines and/or against the requirements (e.g. some reports run in the background or are otherwise automated, so performance - or rather speed - of every single query …
- 182k
4
votes
Accepted
Identifying rows which don't match a master row
1, 1, '') + ' FROM dbo.table1_master
)
SELECT m.LocationID
FROM s INNER JOIN dbo.table1 AS m ON 1 = 1
' + @c2;
SELECT @sql;
--EXEC sp_executesql @sql;
You can take the output of this query … as is and store the query somewhere, or you can comment out the SELECT and uncomment the EXEC and leave it as permanent dynamic SQL - in this case it will automatically adapt to column changes in the …
- 182k
4
votes
Accepted
Flat query to Column structure
SQL Server doesn't have dynamic or variable-based pivot, so one approach could be dynamic SQL.
DECLARE @StartDate DATE = '20120101';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT TOP (7)
@sql += N' …
- 182k
4
votes
remove leading and trailing quotes
Given this data:
CREATE TABLE #temp(col varchar(255));
INSERT #temp(col)
SELECT 'foo' UNION ALL SELECT '"bar"'
UNION ALL SELECT 'blat"' UNION ALL SELECT '"mort';
This query yields the same results …
- 182k
3
votes
Accepted
Sql Server string concat with order by and subquery
String concatenation in that form combined with ORDER BY is undefined (and unstable and unreliable). I have seen the same symptom where you only get one result. If you want all the rows, drop the ORDE …
- 182k
3
votes
Accepted
How can i make month appear with two digits SQL Server
DECLARE @t TABLE(InsertionDate datetime);
INSERT @t(InsertionDate) VALUES(GETDATE()), ('20190125');
SELECT CONVERT(char(6), InsertionDate, 112) FROM @t;
Result:
202004
201901
- 182k
2
votes
Fastest Aggregate function when you have series of duplicate values
Given the current design, I don't know if there will be one approach that is noticeably more efficient than any other. You may be able to minimize the pain by creating an index on just OrderID and Pri …
- 182k
2
votes
Accepted
Find a doubled value between two columns in the same row
Your WHERE clause can include basic math like this, e.g.
WHERE GRIR.ReceivedQty = 2.0 * GRIR.OrderedQty;
...or...
WHERE GRIR.OrderedQty = GRIR.ReceivedQty / 2.0;
(And this db<>fiddle demonstrates wh …
- 182k