Viewing 15 posts - 5,746 through 5,760 (of 7,619 total)
Suggested query below. If you're willing to add a nonclustered index, you can try the affect of adding the index below before running the query. I can't tell...
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".
October 6, 2014 at 4:02 pm
Add a clustered index to the "COLUMN_NEXT_ID" table on "Company" and "Form_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".
October 6, 2014 at 3:17 pm
Code below appears to confirm that. Although I did notice that if you added "TOP (100)", it became a KEYSET cursor, even w/o you explicitly indicating that.
DECLARE csr1 CURSOR...
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".
October 3, 2014 at 10:28 am
Yes, because the cursor is DYNAMIC, at least based on my reading of BOL:
"
DECLARE CURSOR
...
FORWARD_ONLY
...If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a...
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".
October 3, 2014 at 10:05 am
Agreed. I think sys.dm_db_index_operational_stats would be more useful to capture overall I/O, although it still doesn't link it to a specific query, unless you can do that yourself just...
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".
October 3, 2014 at 9:08 am
Did you try -1 as the length? Just a guess, but that's how SQL represents "MAX" internally in its system tables.
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".
October 2, 2014 at 2:48 pm
SQL will have to pull back all rows, then apply the WHERE conditions.
You need to add the relevant WHERE conditions:
WHERE oq.calldate > getdate() -1
AND oq.duration > ''00:00:20'''
to the remote query...
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".
October 2, 2014 at 2:34 pm
Oldest active transaction:
SPID (server process ID): 149
UID (user ID) : -1
Name : user_transaction
LSN : (3978:123016:460)
Start time : Sep 29 2014 11:37:23:380AM
To be sure of the...
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".
October 2, 2014 at 2:31 pm
If it's varchar, best would be to test it as varchar, thus not converting the column ... but that is only safe if all values are consistently left-padded with zero(s).
Either...
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".
October 2, 2014 at 1:37 pm
Format 'YYYYMMDD hh:mm' is always accurately translated in SQL Server.
Therefore, you need just two STUFFs:
SELECT
varchar_data AS original_string,
CAST(STUFF(STUFF(varchar_data, 9, 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".
October 2, 2014 at 1:16 pm
True, if they actually are using SQL 2012. Sometimes you can't be sure :-D.
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".
October 1, 2014 at 3:12 pm
The original version I wrote of this was in 2006 for Experts Exchange, and it was to strip nonnumeric chars (although of course the basic idea is the same):
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_21957163.html
Of course...
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".
October 1, 2014 at 3:01 pm
Can we assume the large tables have indexes keyed on the IDs that are being passed in?
If so, and the IDs are in a (fairly) tight range, you could also...
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".
October 1, 2014 at 2:44 pm
--Probably easiest would be to put the control values into a separate table, and if necessary use exclusive locks on that table when seeding that table.
CREATE TABLE dbo.control_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".
October 1, 2014 at 2:25 pm
No, changing the column size requires full logging for whatever logging it needs.
But note that increasing a varchar(nnnn) column should only change catalog data, not the data pages, so it...
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".
October 1, 2014 at 1:31 pm
Viewing 15 posts - 5,746 through 5,760 (of 7,619 total)