Can someone give me the right syntax for using an index hint in a delete statement as below?
DELETE table1 WITH (INDEX('indexname_IX'))
WHERE datecolumn < @datevariable
I get this error message:
Incorrect syntax near the keyword 'index'.
I'm using Query Analyzer in SQL 2000 Enterprise.
Thanks,
Gerald
According to what I see in BOL, the following should work:
delete
from dbo.tbl1 WITH (INDEX(idx_un01))
where tbl1_no = 1
Yet when I syntax check this I get:
Msg 1069, Level 15, State 1, Line 2
Index hints are only allowed in a FROM clause.
(Please ignore the fact that index hints are unnecessary / a bad idea / etc.)
Hello!
I would like when I compare query plans to be able to compare
2 queries where the 1. is the "normal" version and the 2. the version where I forced compiler
not to use a specific index (i don't want to force at table scan, so hint index(0) can't be used).
The only way I see how I could achieve something similar is to to drop the index and compare response time
before and after. But building index could be time consuming...
Is it possible?
select col42 from tbl;
select col42 from tbl with...
pressing CTRL+L
Greetings
Bjørn
what's wrong with this code?
i'm trying to create a clustered index
I am executing
DROP INDEX [IX_Users] ON [Users]
and i get a syntax error
what is the exact syntax to DROP an INDEX for MS SQL 2000
thank you
What is the equivalent for INCLUDE clause (in Create index syntax) in SQL Server 2000.
SQL Server 2005 will support Include clause in Create index syntax . How to attain it in SQL Server 2000
Example : I have below query executable in SQL Server 2005
CREATE INDEX Index_Name ON mytable(col1 ASC) INCLUDE (name,id);
What is the equivalent for the above query in SQL server 2000
Thanks ,
Sushma
Query hints exceed maximum command buffer size of 1023 bytes(1029 bytes input).
i tried a long query in BCP because i have to insert a character to one of its fields.
hope somebody had encountered and solved this (sql server 2000). thanks in advance..
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]....
Background:I am currently working on a mission critical web based applicationthat is accessed 24 hours a day by users from just about every timezone. We use MS SQL Server as our database and we have lots ofproblems with time-outs. We used to have lots of problems with locksuntil my management decided that we would use the WITH (NOLOCK) hinton EVERY select statement and WITH (ROWLOCK) on EVERY updatestatement. I have argued since the beginning that the NOLOCK hintshould be the exception and not the rule. Meanwhile we continue tohave problems related to time-outs.Problem:I'm the one that they call when there are time-out errors.I am a programmer first and a DBA when I have to be. I'd really liketo hear from some of you who are the opposite. I realize that thereare many factors that contribute to slow response from a databaseserver (indexes, RAM, disk speed, etc.), but what I really need tohear from an expert is whether or not using NOLOCK on **EVERY** queryin a 30GB database that has 344 tables is a bad idea.Thanks in advance,Stephen McMahonJoin Bytes!
View 2 Replies View RelatedHi all
If i have a view:
CREATE VIEW vw_Users
AS
SELECT * FROM Users WITH(NOLOCK)
Is it suggested to use nolock in views?
And if i needed to use this view in stored procs is it then suggested to apply the nolock hint?
CREATE PROC [dbo] .[usp_GetCompanyUsers]
AS
SELECT * FROM Companies WITH(NOLOCK) JOIN
vw_Users WITH(NOLOCK) --<< ---is this suggested?
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select
' + char (39) + @country + char (39) + ' as PAIS,
A.ID,
A.IDUSUARIO MSISDN,
NULL AS MSISDN_COD,
convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL,
NULL AS FECHA_MO_LOCAL_D,
[code]...
Hi All,I have a question about lock hint for you :If the first user currently run a select commandwith share lock and hold it. What kind oflock (lock hint) should be used by the second userin the select command (from the same table) so thatthis command will wait until the first user releasesthe lock ?I have tried using tablock, tablockx, xlock andupdlock hint in the select command for the seconduser, but it is not successful. Below is myunsuccessful test :The first user :Begin Transactionselect fprefix from ut1 where fprefix = '000'The second user :select * from ut1 with (tablockx)Please help meThanks in advanceAnita Hery*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedCan I use the NO Lock hint when running a SELECT against a view that points to another server? Will the no lock "travel" to the other server through the view?
amk
Hi there,
I need to get back the actual characters (without trailing blanks) contained in a char(43) field.
So i tried:
- substring(fieldname,1,len(fieldname)) which yields a 43 char column
- using a variable that contains the actual length in
substring(fieldname,1,@nchar)) the same.
- tricking by concatenating the resulting string with a dummy like "" didn't
work either.
So apparently I am at a dead end.
Anybody a clue, hint or solution????
Thanks to all contributors
We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?
Craig
Hi,
Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).
Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.
Any suggestions on whether this is possible would be welcome.
Thanks,
Karl
Hi,
I want to implement the following pattern (pseudo-code follows):
[for index in x different databases, where x is rather big]
create database MyDatabase_#{index}_Temp
launch a dedicated ssis package on this database
if I detect no error, proudly rename MyDatabase_#{index}_Temp to MyDatabase_#{index}_Last_Known_Good (drop the last_known_good first if it exist)
Later on, a consolidation process collect the data in Last_Known_Good versions of the databases.
My question: what is the simpliest way to achieve the rename operation (step 3) ?
I have tried an "alter database #{old_database_name} modify name = #{new_database_name}", but it works only once: once renamed, the database keeps the old mdf and ldf filenames, and the next create database will choke on this.
I have also tried to rename the logical filenames with ALTER DATABASE XXX MODIFY FILE (NAME = YYY, NEWNAME = ZZZ) but it doesn't work either.
I'd like to avoid specifying absolute mdf and ldf filenames myself, is it possible ?
best,
Thibaut
An interesting discussion yesterday. One of the programmers asked about the use of the NOLOCK optimizer hint with an iterator table aka table of numbers. His comment was that this optimizer hint was not efficient. Rather than give a knee-jerk response I thought it would be better to ask. The main circumstance is that the iterator table is completely static with a fill factor of 100%. My purpose is to eliminate lock contention if I can.
Are there reasons to not use the NOLOCK hint in this case to potentially improve performance?
Dave
My God! What happens if I miss a step, or put it in a different order??? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...
View 14 Replies View RelatedHi All,I want to lock 2 tables on 2 servers using TABLOCKXhint. These tables function as semaphores in myapplication. It means when the tables are lockedthen other users will not be able to access themand automatically they can not continue their works.I have tried using the following code, but itdoes not work. I always got the error :"Cannot specify an index or locking hint fora remote data source" on the select command.begin transelect * from server1.accounting.dbo.semtabwith (tablockx) where fprefix = '000'select * from server2.accounting.dbo.semtabwith (tablockx) where fprefix = '000'--commit tranThe error will disappear if I remove the servername, like this :select * from accounting.dbo.semtabwith (tablockx) where fprefix = '000'But, it does not meet my requirement.It only locks one table on one server(default).Please help meThanks in advanceJohn S.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 9 Replies View RelatedI've got a dilemma which I hope someone has a solution to.
Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".
There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.
My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.
I was hoping to find some sort of parent-child hint in the column properties but found none.
What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.
Geof
Hello
we have two SqlServer 2005 in cluster. The machines act very slow (although the CPU load is low) as soon as we achieve one of these operations:
- drop / create database
- restore database
We achieve those operations through the SqlServer Browser or via sqlcmd.
Everything else is running smoothly.
Is there any known reason on why it can be so slow ?
best regards
Thibaut
(hope this is not too OT, but could not find any cluster-specific forum).
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
thank you in advance
I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime.
2. I cannot use backup and restore option either.
Hi,
I am stuck with that error when running my ASP.NET application on my production server for many weeks now and still don't have a complete solution to that problem.
I formerly posted that thread and read that other thread without success, so as I have made a few progress in delimiting the problem I am posting this new thread with the hope somebody will finally be able to help me.
To summarize:
1) I have developed an ASP.NET application,
- on a Windows XP Pro SP2 computer
- with VS studio 2005 Dev and included SQL 2005 engine
- this application works fine on the dev computer
2) I have deployed the application on a production server which is:
- a Windows 2000 SP 4 computer
- with SQL Express SP1 with Advance services installed
When I launch the prod application I have this error:
Login failed for user xxxxASPNET
I have found that I can bypass this error by doing the following:
1) manually attach my .mdf database with SQL Server Management Studio Express and specifying the field Attached as with myDatabaseName (in place of the default fullPhysicalPath/myDatabaseName.MDF) then detach it
2) change the auto generated connection string in web.config file from
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
To
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabaseName.mdf;User ID=sa;Password=mySAPassword;database=myDatabaseName" providerName="System.Data.SqlClient"/>
That would have been an acceptable substitution solution but unfortunately my
application also uses the new Login and Webparts features of VS 2005 which implies the automatic creation of an ASPNETDB.MDF database by VS.
As I have no mean (or at least don't know how) of modifying the associated Connection String for that database (it does not appear in web.config) I cannot bypass the problem for it and thus the error still appears when the application try to use this database.
Please I really need help on this matter, I can provide any other useful information as needed (logs, sources ...)
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
Hi,
*** Skip all and go to last 2nd para for main question ****
We have our custom ETL tool that loads data in SS 2005 Enterprise Vesion using BCP. We use '-h "TABLOCK"' table hint and '-b 10000' batch size options.
Prior to 2005 Enterprise version (2K and 2K5 Standard Versions), we use to maintain around 10 DBs with same schema/sps and views in each of the dbs.
Now, in Enterprise version we are maintaining one DB where all tables are partitioned by one column.
Multiple instances might run from different workstations. Here, I want to clarify you that no instance will load data pertaining to another partition.
But at the same time there will be other components running that will query the tables in a SELECT sql and all tables have NOLOCK table hint.
There can be multiple instances of this component running from different workstations. Again, none of the instance will not access data pertaining to another instance (including the component that is loading data)
I have no clue why we some times get deadlock issues when we run multiple instances of custom ETL tool for loading data and other component that just has select only sql.
More over I get deadlocks many times only in BCP, this is a puzzle to me because i use NOLOCK table hint. This is the only place where table is populated by data and data in table neither updates or deleted.
Can some one tell which of these below concurrent transactions will get conflicts and leads to dead lock?
bcping data into table of partition A
bcping data into table of partition B
deleting few rows of data from table of partition E
updating few rows of data from table of partition F
querying table of partition C
querying table of partition D
Thanks
- D
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP