Viewing 15 posts - 2,761 through 2,775 (of 7,619 total)
Forced-off-row types require only a 16-byte pointer. The 2-byte variable-length overhead per pointer, though, causes even fewer than 500 columns to be possible [I thought SQL would be able to...
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".
July 29, 2019 at 9:09 pm
SELECT
Yr, Pd, Loc,
SUM(Complete) AS Complete,
SUM(Pending) AS Pending,
SUM(Canceled) AS Canceled,
...
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".
July 29, 2019 at 6:42 pm
If using nvarchar(max), in particular, you can simply force the large values off the page into LOB/overflow area.
After creating the table, but before loading it, run the following command:
EXEC sys.sp_tableoption...
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".
July 29, 2019 at 6:39 pm
Like so:
SELECT EmpName,
MAX(CASE WHEN row_num = 1 THEN Item END) AS item1,
MAX(CASE WHEN row_num = 2 THEN Item 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".
July 26, 2019 at 3:20 pm
If I need to know the location, I usually set a variable with a location id that is unique.
DECLARE @code_location varchar(20)
SET @code_location = '1000-SELECT'
BEGIN TRY
...
END TRY
BEGIN CATCH
SELECT...
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".
July 25, 2019 at 5:28 pm
Just create a separate drive path for backups on that machine and, to keep it simple, make sure there are no spaces or other unexpected chars in the path name.
Btw,...
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".
July 24, 2019 at 5:17 pm
For an OUTER JOIN, you must put conditions on the possibly-missing table in the JOIN clause, not in the WHERE clause.
select * from #temp1 T
left join #temp2...
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".
July 23, 2019 at 2:54 pm
I don't know about the overall logic, but the JOINs above seem needlessly convoluted. Instead, maybe:
INNER JOIN #UserB AS B
ON ...
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".
July 22, 2019 at 6:53 pm
I think the Milestone table does not contain a column named "TOT_ABSENCES". Please review the column names in the Milestone table.
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".
July 15, 2019 at 5:14 pm
Create an index keyed on pbRecordId on that table.
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".
July 12, 2019 at 8:55 pm
The clustered index is the table itself. That is, all columns are stored in the clustered index. Thus, the width is the total width of the entire row.
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".
July 8, 2019 at 6:33 pm
The system itself already has all the metadata views you need. It's best to just use those views: sys.objects, sys.columns, sys.key_constraints, etc..
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".
July 8, 2019 at 4:51 pm
I too would not drop compression to do a load, especially a large one. Page compression can help data load much faster by reducing I/O (compression takes more CPU but...
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".
July 5, 2019 at 7:05 pm
If these are staging tables, just be sure to TRUNCATE the table rather than DELETE the rows. TRUNCATE will keep the table allocations clean without having to create a clustering...
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".
July 5, 2019 at 7:00 pm
I prefer to use CROSS APPLY(s) for such calcs.
Select emp_code, emp_name,emp_last_name, emp_max_pay, emp_yearly_salary
From dbo.table_name
Cross Apply (
Select case when emp_max_pay < 10000 then emp_max_pay...
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".
July 5, 2019 at 6:58 pm
Viewing 15 posts - 2,761 through 2,775 (of 7,619 total)