Viewing 15 posts - 2,866 through 2,880 (of 7,619 total)
I doubt you need a temp table. You can just UNION ALL all the different query results. If a given query returns no rows, that's OK, it will...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 6:27 pm
Do you want to list inactive data in the report?
If not, you can just join to the other tables including "AccountStatus IN (1)" in the JOIN ON clause, something like...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 6:10 pm
Personally I'd use CROSS APPLY:
SELECT sl.Name, sl.Country, types.type
FROM Sales_Ledger sl
CROSS APPLY ( VALUES([Type 1]), ([Type 2]), ([Type 3]), ([Type 4]) ) AS types(type)
WHERE types.type > ''
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2019 at 3:17 pm
Hopefully SQL's split algorithm isn't that poor for bit columns.
But, if it turns out to be, create an index on:
( MyTable_ID, is_MyTable_Text_NULL ).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2019 at 8:22 pm
Add a bit column to indicate null status of [MyTable_Text], then index on that column.
ALTER TABLE [dbo].[MyTable] ADD is_MyTable_Text_NULL AS CAST(CASE WHEN MyTable_Text IS NULL THEN 1 ELSE 0 END...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2019 at 4:50 pm
You don't want index_columns and indexes in the query as it exists, because it will just generate duplicate rows for no reason.
SELECT TOP (1)
st.name [Table Name],
c.name [Column Name],
t.name [Data Type]
FROM...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2019 at 4:43 pm
You need more clear examples.
For example, if line 1 was the starting line, which of these lines would be included:
0 200 0 0 501 --line 5
200 0 0 0 0...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 5, 2019 at 8:57 pm
That's a very interesting idea. It sounds very complex to implement, but quite nice once you get it working.
The trigger is actually rather easy to write and can be...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 5, 2019 at 5:12 pm
Bit data type is numeric in SQL: naturally 1 means Yes, 0 means No.
So:
CREATE TABLE dbo.table_name ( bit_column bit DEFAULT 1 );
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 5, 2019 at 1:25 pm
But you also made the blanket statement that:
Indexes are for reducing the rows read, they have no value at all when you don’t have a where clause filtering the rows.
And...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2019 at 6:19 pm
Not necessarily true. Indexes can also allow SQL to avoid a sort (and sorts are expensive operations).
For example:
Add a clustered index on id to the table.
Then this query should...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2019 at 3:22 pm
You'd have to use an INSERT trigger. It could be an INSTEAD OF INSERT or an AFTER INSERT trigger.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 3, 2019 at 6:39 pm
Yep. Save the system variables (@@) into local variables (@) after the relevant statement(s), then test the local variables instead of the system vars. Note that you need...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 3, 2019 at 4:35 pm
I didn't, and wouldn't, look at the links. But thanks for the heads-up on this poster.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 2, 2019 at 3:12 pm
You can have the db name in an ALTER, just not the server name (note the message says two prefixes, i.e., a total of 3 levels of object name). ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2019 at 9:21 pm
Viewing 15 posts - 2,866 through 2,880 (of 7,619 total)