Viewing 15 posts - 5,551 through 5,565 (of 7,619 total)
zerbit (12/22/2014)
hjelmesethe (12/22/2014)
The original poster's code creates a human-readable date/time in a bigint format.
Ok, but the point stated was to use a bigint to improve performance not to get...
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".
December 22, 2014 at 8:53 am
If you're going to do this, do it much more efficiently without all the unnecessary variables:
alter function fn_generate_bigint(@datetime datetime)
returns bigint
as
begin
return (
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".
December 22, 2014 at 8:52 am
PiMané (12/19/2014)
the problem is that all the possible indexes generate lots of fragmentation and right now the main issue is to reduce page...
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".
December 19, 2014 at 9:18 am
PiMané (12/19/2014)
CELKO (12/19/2014)
This...
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".
December 19, 2014 at 9:04 am
Edit: Don't have data to test, but something like this should do it.
SELECT tn.ID,ca.Client,tn.TxDate,tn.GrossCost
FROM table_name tn
CROSS APPLY (
SELECT tn.Client AS Client
UNION ALL
...
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".
December 18, 2014 at 4:34 pm
1) Cluster the table on ( Date, TableName )
2) Edit: The variables for date are just in case you later want to keep more history and do other rowcount comparisons....
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".
December 18, 2014 at 4:20 pm
Not sure. I suggest running both queries and then looking at the plan cache:
SELECT ecp.usecounts, ecp.cacheobjtype, ecp.objtype, est.text
FROM sys.dm_exec_cached_plans ecp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) est
WHERE est.text LIKE '%field_enum_values%'
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".
December 18, 2014 at 3:18 pm
PiMané (12/18/2014)
You can have the pk on the uniqueid and have a fillfactor of 70 or...
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".
December 18, 2014 at 9:48 am
You can do the full db backup ahead of time. You can do a differential just before you do the index changes.
Script out all existing non-clustered indexes ahead of...
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".
December 17, 2014 at 3:47 pm
Here's a possible re-write of the first temp table INSERT:
insert into #temp_violation
select
tv.OrderID
,tv.StartDate
,tv.EndDate
,ca1.HourID
from EMSTBLSN.dbo.TimeViolation tv
cross apply (
select distinct fh.HourID
from G4SFEP1.dbo.Service fs
...
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".
December 17, 2014 at 10:00 am
SELECT name
FROM table_name
UNION ALL
SELECT DISTINCT LEFT(name, 1) AS letter
FROM table_name
ORDER BY 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".
December 17, 2014 at 9:32 am
First thing is to get the best clustering:
Cluster the violation table on ( OrderID, StartDate )
Cluster the sanction table on ( OrderID, IdentifiedDate )
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".
December 17, 2014 at 9:26 am
Might as well handle any db and any schema, but defaulting to your main one.
CREATE PROC CLEAR_MY_TABLE
@TableStat varchar(100)
AS
SET NOCOUNT ON;
DECLARE @TableStat varchar(100)
SET @TableStat = ISNULL(PARSENAME(@TableStat, 3),...
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".
December 16, 2014 at 4:41 pm
Truncating tables is a great idea when applicable. It's vastly less logging overhead than deleting all the rows instead. Dropping the table and recreating is also more overhead...
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".
December 16, 2014 at 4:31 pm
This query will find logins with a default db that is not online, and show any job(s) that login owns.
select sp.*, j.*
from sys.server_principals sp
left outer join msdb.dbo.sysjobs j 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".
December 16, 2014 at 4:27 pm
Viewing 15 posts - 5,551 through 5,565 (of 7,619 total)