Is it true what i've just heard ? that if I have SP , and I declare (inside) declare @i int ( not as parameter , but inside the Sp's body) , and then I have a query which uses : ....where myindexColumn=@i , then it won't use the index ?
(There's a DBA in our company right now who came to speed up our queries) so he told me that
DECLARE @CCLink INT,@SQL NVARCHAR(MAX),@IndentDocument NVARCHAR(10)
set @CCLink=50042
SET @SQL='SELECT ProductID,SUM(Quantity) ReqQty, C.DCCCNID29 PartID FROM INV_DocDetails T WITH(NOLOCK)
INNER JOIN COM_DocCCData C WITH(NOLOCK) ON T.InvDocDetailsID=C.InvDocDetailsID
WHERE CostCenterID='+@IndentDocument+'
AND dcCCNID'+CONVERT(NVARCHAR,@CCLink)+'='+CONVERT(NVARCHAR,36129)+' GROUP BY ProductID, C.DCCCNID29'
@AndriyM true. My vast experience stems from the 7.0 times - 2000 was already out, but we did not have it. This way I had to invent creative ways to rewrite things that used UDFs (because the dev had a 2000 installation)
@dezso I've never seen 7.0. So, you are saying it didn't have functions. Might not have been entirely bad, because people were unable to abuse the option either. But yeah, rewriting something that's already implemented as a function... Some fun too, probably not much worse than a 5000-line SP :)
@ypercube Yes that's why I wrote "This new tag is unnecessary. It should be a synonym of update or deleted." in the rejection reason. I proposed the necessary synonym.
But it would be nice if sql-update just disappeared.
Assume you have binary data files of size > 1GB.
These files contains events each marked by 585 in hex.
I convert the binary to hex by xxd -ps data.raw | sed ':a;N;$!ba;s/\n//g'.
I can get wanted hex position to binary back by |xxd -r.
I characterize characteristics of data in hex about which...
@JonEricson why was it never tried to simply create a review queue for them? I was thinking this morning about how tag synonyms always require mod intervention on P.SE (and on lower volume sites than SO altogether this is probably always the case), and my thought was make it a simple privilege just like the others: 7.5k or 10k or whatever required, 4-5 votes required to pass it, and a review queue to ensure it gathers those votes, the last part was obviously key when I thought about this as tag suggestions would quickly be overlooked. — Jimmy HoffaSep 16 '13 at 14:45
Executive Summary
Jon Ericson asked, Does the tag synonym suggestion system work?
The answer, unfortunately, is no.
Tag Synonyms were introduced in August 2010:
It’s been clear for a very long time now that we needed some kind of tag synonym system, one that the community itself could main...
Your statements are either indicating a serious bug (did you file a bug report?), a serious bug in the way you dump your database or some misunderstanding. Lacking any facts one cannot decide among these. — dezso8 secs ago
@TomV Not all cursor types support a parallel plan. What is the reason for a non-parallel plan shown in the query plan (assuming you have 2012 or later)?
If the fast-forward cursor typically does not pick dynamic at run time, you could change the type of cursor to something parallel-compatible like static or keyset.
Though since it is an API cursor, you may not have that option.
I'm running into a performance problem with a query that I can't seem to get my head around.
I pulled the query out of a cursor definition.
This query takes seconds to execute
SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EX...
@swasheck Locally, it snows once maybe every 30 years or so, and you might not even call that snow. More generally, more than one or two days per winter where we don't get into double digits centigrade is a shocker.
FAST_FORWARD cursors do not support parallelism.
When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you.
It is hard to tell from the plan graphics provided, but if the optimizer typically chooses a static-like plan for the FAST_FORWARD cursor, you could change t...
@MarkSinkinson we use posh/wmi to pull volume space available and correlate that to data files (which we have in a central DBA management database) ... we pull data file sizes for all systems twice per day
@MarkSinkinson No, not yet. It's still a fairly manual process on my end. Need time to get it automated and hook in alerts. It's very much a "1000 foot view"
Talking about what it is. Where it fits into the order of operations. How satistics fit in and how the overall estimate ultimately affects the exectuion plan.
@JamesLupolt duplicate column sets in different stats objects that exist on the same object ... unfortunately, filtered stats show up as dupes still ... but just thought of a way to partition them out
It means that the parameter should be defined to match the data type of the underlying column - currently the parameter is nvarchar(4000) but it should be nvarchar(255).
Whether this actually makes your query slow or leads to a bad plan is tougher to determine. But making those two definitions m...
I don't see a warning with different size nvarchar
Why there is no full-scan (On SQL 2008 R2 and 2012)?
Test data:
DROP TABLE dbo.TestTable
GO
CREATE TABLE dbo.TestTable
(
TestTableID INT IDENTITY PRIMARY KEY,
VeryRandomText VarChar(50),
VeryRandomText2 VarChar(50)
)
Go
Set NoCount ON
Declare @i int
Set @i = 0
While @i < 10000
Begin
...
create table T
(
C nvarchar(255) collate SQL_Latin1_General_CP1_CI_AS,
OtherColumn char(100) default '',
index IX_T (C)
)
go
insert into T(C)
select name
from sys.all_columns
go
declare @C nvarchar(4000) = N'bitpos'
select *
from T
where C = @C
Change data type of C to varchar(255) and you have a warning.
I might be missing something. Tested on SQL Server 2014.
How do you grant a user the permission to access and search the database (basic crud stuff inside it), but only to that database in Postgres? Can you restrict it like that or is it impossible?
(I'm also having troubles connecting to the database unless my user is a superuser :( )
@ypercube Thanks. This helped me a lot :) For some reason it's like the manual I'm reading and the parts you're linking me to are different manuals. :/
It's toolset is ugly and feels like it hasn't been updated since 97. It's expensive as hell. It's pretty quick but in order to achieve that speed it has to sit on a mountain of hardware.
trump is saying the most offensive things. however, nobody really takes his candidacy seriously. jeb is alienating the only actual demographic that would give him a chance
Assume you have binary data files of size > 1GB.
These files contains events each marked by 585 in hex.
I convert the binary to hex by xxd -ps data.raw | sed ':a;N;$!ba;s/\n//g'.
I can get wanted hex position to binary back by |xxd -r.
I characterize characteristics of data in hex about which...
Executive Summary
Jon Ericson asked, Does the tag synonym suggestion system work?
The answer, unfortunately, is no.
Tag Synonyms were introduced in August 2010:
It’s been clear for a very long time now that we needed some kind of tag synonym system, one that the community itself could main...
I'm running into a performance problem with a query that I can't seem to get my head around.
I pulled the query out of a cursor definition.
This query takes seconds to execute
SELECT A.JOBTYPE
FROM PRODROUTEJOB A
WHERE ((A.DATAAREAID=N'IW')
AND ((A.CALCTIMEHOURS<>0)
AND (A.JOBTYPE<>3)))
AND EX...
FAST_FORWARD cursors do not support parallelism.
When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you.
It is hard to tell from the plan graphics provided, but if the optimizer typically chooses a static-like plan for the FAST_FORWARD cursor, you could change t...
Why there is no full-scan (On SQL 2008 R2 and 2012)?
Test data:
DROP TABLE dbo.TestTable
GO
CREATE TABLE dbo.TestTable
(
TestTableID INT IDENTITY PRIMARY KEY,
VeryRandomText VarChar(50),
VeryRandomText2 VarChar(50)
)
Go
Set NoCount ON
Declare @i int
Set @i = 0
While @i < 10000
Begin
...
