sql prompt fails to format forceseek index hint (sql 2012)
gokhanvarol@gmail.com
Posts: 12
SQL prompt fails to format below index hint, MSDN documentation is below.
This is a major problem now, I cannot use sqlprompt period for formatting since most of my scripts uses the forceseek hint.
Thank you
WITH ( FORCESEEK(1(CntyCd, PclId, PclSeqNbr)))
SELECT CONCAT(@D, k.AddrAptNbr, @D, k.AddrCarrt, @D, k.AddrDirLeftCd, @D, k.AddrDirRightCd, @D, k.AddrHse1Nbr, @D,
k.AddrHse2Nbr, @D, k.AddrModeCd, @D, k.AddrPfx1Cd, @D, k.AddrSfx1Cd, @D, k.AddrSfx2Cd, @D, k.AddrStreetName, @D,
LTRIM(k.AddrTypCd), @D, k.CbsaCd, @D, k.CensId, @D, LTRIM(k.CSZTypInd), @D, k.DPIDCd, @D, k.DpvCd, @D,
k.GeoMatchCd, @D, k.LatDegr, @D, k.LongDegr, @D, k.MatchCd, @D, k.OrigAddr1, @D, k.OrigAddr2, @D, k.OrigAddr3, @D,
k.OrigCityName, @D, k.OrigStCd, @D, k.StdAddr1, @D, k.StdHse1Nbr, @D, k.StdHse2Nbr, @D, k.StreetNamePfx, @D,
k.StdCityName, @D, k.StdStCd, @D, k.OrigZipCd, @D, k.StdZipCd)
FROM tTrans.TransSitus AS k WITH ( FORCESEEK(1(CntyCd, BatchDt, BatchSeq)))
WHERE k.CntyCd = @CntyCd
AND k.BatchDt = t.BatchDt
AND k.BatchSeq = t.BatchSeq
AND k.SitusSeq = 1
http://msdn.microsoft.com/en-us/library/ms187373.aspx
WITH ( <table_hint> [ [, ]...n ] )
<table_hint> ::=
[ NOEXPAND ] {
INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ]
| FORCESCAN
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| SPATIAL_WINDOW_MAX_CELLS = integer
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
This is a major problem now, I cannot use sqlprompt period for formatting since most of my scripts uses the forceseek hint.
Thank you
WITH ( FORCESEEK(1(CntyCd, PclId, PclSeqNbr)))
SELECT CONCAT(@D, k.AddrAptNbr, @D, k.AddrCarrt, @D, k.AddrDirLeftCd, @D, k.AddrDirRightCd, @D, k.AddrHse1Nbr, @D,
k.AddrHse2Nbr, @D, k.AddrModeCd, @D, k.AddrPfx1Cd, @D, k.AddrSfx1Cd, @D, k.AddrSfx2Cd, @D, k.AddrStreetName, @D,
LTRIM(k.AddrTypCd), @D, k.CbsaCd, @D, k.CensId, @D, LTRIM(k.CSZTypInd), @D, k.DPIDCd, @D, k.DpvCd, @D,
k.GeoMatchCd, @D, k.LatDegr, @D, k.LongDegr, @D, k.MatchCd, @D, k.OrigAddr1, @D, k.OrigAddr2, @D, k.OrigAddr3, @D,
k.OrigCityName, @D, k.OrigStCd, @D, k.StdAddr1, @D, k.StdHse1Nbr, @D, k.StdHse2Nbr, @D, k.StreetNamePfx, @D,
k.StdCityName, @D, k.StdStCd, @D, k.OrigZipCd, @D, k.StdZipCd)
FROM tTrans.TransSitus AS k WITH ( FORCESEEK(1(CntyCd, BatchDt, BatchSeq)))
WHERE k.CntyCd = @CntyCd
AND k.BatchDt = t.BatchDt
AND k.BatchSeq = t.BatchSeq
AND k.SitusSeq = 1
http://msdn.microsoft.com/en-us/library/ms187373.aspx
WITH ( <table_hint> [ [, ]...n ] )
<table_hint> ::=
[ NOEXPAND ] {
INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ]
| FORCESCAN
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| SPATIAL_WINDOW_MAX_CELLS = integer
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
Comments
Please feel free to add your vote or if there is one already kindly vote for it here.
And then the project manager can directly decide what category this requirement comes under?
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com