73,607 questions
Score of 2
1 answer
136 views
Unexpected behavior with using a CTE to update a temp table
DDL:
IF OBJECT_ID('tempdb..#Tab2And3Pop') is not null
DROP TABLE #Tab2And3Pop;
SELECT CAST(115704 AS INT) AS ConflictNoticeID
, CAST(16003573 AS INT) AS MerchantID
, CAST(13 AS ...
- reputation score 633
Advice
0
votes
8
replies
92
views
T-SQL hint to block key lookup, without forcing a particular index
Is there an index hint in T-SQL that says "do not use any nonclustered index that would require a key lookup"? Apart from that one restriction, I want to let the query planner have a free ...
- reputation score 1632
Advice
0
votes
7
replies
170
views
Avoiding a LIKE '%value%' in a JOIN
I have a reference table that contains a computed, delimited hierarchy column for items in a parent-child-grandchild relationship. For example if item #11 is under item #1, its ancestry is "~1~11~...
- reputation score 36625
Score of 0
0 answers
82 views
How do I programmatically drop a generated DEFAULT constraint? [duplicate]
I created a table in a script where one of the columns was established as such:
CREATE TABLE [schema].[table](
[ColumnName] [int] DEFAULT 45);
This will randomly generate a constraint like so: ...
- reputation score 1579
Advice
1
vote
15
replies
234
views
Get latest partnership when start date is in one table & end date in another of partnerships
I have two tables. One has the 2 partners & their events start date. The other has identical layout but has the last event date of the partnership. I need to get the last/current partnership with ...
- reputation score 53
Advice
0
votes
5
replies
122
views
SSRS Report using Transact SQL
I have an SQL query that displays all products, but I want to somehow apply a filter to display a subset of products from within using the field rWardStockList.TitleText that looks for the following ...
- reputation score 1
Score of 1
1 answer
148 views
Create a foreign key with multiple columns but only one reference
I have two tables, and I'm looking to create a foreign key constraint between them. The tables are essentially structured as follows:
Table 1:
Name
Description
idx
Table 2:
Tbl1_idx1
Tbl1_idx2
idx
...
- reputation score 21
Score of 0
1 answer
146 views
WHERE clause with nvarchar can't distinguish between special characters
I am working with a database that contains some special characters in nvarchar columns. Specifically, it has some text written in special characters, like those font generators create for Twitter or ...
- reputation score 67
Advice
1
vote
3
replies
134
views
Search SQL Server Database And Return Results Based On A Subset of The Conditions Being Met
I would like to query SQL database and set conditions in the Where clause that return results if a specified number of the conditions are met rather than if all or any of the conditions are met.
For ...
- reputation score 91
Score of -5
1 answer
109 views
Matching on dates when a MM/DD combination needs to be excluded [closed]
I've been tasked to write a query where a month and date combination from one column needs to not equal another date column.
i.e. MED_EFF_DATE = 06/01/XXXX and MED_CANC_DT NOT EQUAL 08/29/XXXX
I've ...
- reputation score 8495
Advice
2
votes
2
replies
124
views
Is there an exercise repository for AdventureWorks database tables?
I am currently practicing SQL using the AdventureWorks sample database.
Before posting, I tried searching but couldn’t find a structured collection of exercises specifically designed for practicing ...
- reputation score 1
Best practices
0
votes
3
replies
68
views
Replicating an app on my company's website using T-SQL
I have a report application that i'm replicating. This report shows us 4 columns based on a date filter. Assembly Line, Product#, Enters and Exits. The Enters and Exits are time Stamps of when this ...
- reputation score 1
Best practices
0
votes
5
replies
126
views
Alter a SQL Server table column size in production
What is the best way to alter a production SQL Server 18 table's column size, varchar(n +1).
My understanding would be the following:
Shut the website down to stop the transaction from coming in.
...
- reputation score 31
Score of 9
1 answer
443 views
Selecting from a temporary #table whose column is sp_renamed has stopped working for SQL Server version 2019 and later
In a stored procedure (version 2019), renaming a column of a #table to a @value stops working for subsequent executions. The #table retains/gets the @value name of the very first execution.
Is this an ...
- reputation score 99
Score of 3
1 answer
141 views
Get table name of the columns, used only in select statement of the view
Suppose the following is the select statement of a view
SELECT Emp.ID, Emp.FullName, Emp.JoiningDate, Dept.DepartmentName, Reg.RegionName
FROM Employees AS Emp
INNER JOIN Departments AS Dept
...
- reputation score 370
