What Sorts Of Statements Have To Be In Seperate Batches ?
Apr 7, 2008
Recently I was stumped on a problem where I was granting permissions to a user, from within a script that was creating a stored procedure. Then I would check the permissions on the procedure, but the permissions were empty. It turned out that I was missing a "go" statement to seperate the create procedure statement from the grant statement. So that got me to thinking about what other kids of statements must be seperated into seperate batches ? I would thik that anything that is being created must be seperated from any statements that grant or alter permissions, because the items would need to exist first.
I have a fact table with SalesAmount. I have a dimension with SalesType - either counter or direct.
I want to display a report that shows direct sales by region. No problem. I group on region and filter by direct sales.
However, I also want to show direct sales as a percentage of total sales. I've played around with multiple tables but I can't seem to find a way to display both direct sales and total sales in the same row of a report.
I wrote some views in sql2000 and all was well. I upgraded to sql 2005 and non of the order by clauses work when I run the views directly. If I open the view up in desing mode and run them, they work. Any Ideas? Thanks, Rick
On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
Hi, I want to ask a basic question, that is IN WHAT ORDER A CLUSTERED INDEX SORT THE DATA IN THE COLUMN????
Somewhere in the MSDN library I read the following line: "A clustered index physically sorts the table's contents in the order of the specified index columns"
But Sorting means it will be in ASCENDING ORDER (ASC) or It will be in DESCENDING ORDER (DESC) So my question is lets suppose a column on which the cluistered index is defined and it contains character data liek abcd so in wht order it will sort the data alphabetically ASC or DESC or If the same above case with integer type of values, if column having integer values then in wht order the data in the table will be sorted.
visual studio.net seems to default to single batch mode when runningsql scripts. does anyone know how to change this behavior? typicalbatches will include object existence, drop, and create batches priorto processing. i have attempted removing the graphical plan, usingbatch separator 'go', and the vba trick using ';' to no avail.tia
I am using SQL Server Express and Visual Studio 2005. I am new to batches and am trying to understand how they work. I am trying to write a query that creates an assembly and the functions that are contained in it. Here is my query:
USE ProductsDRM GO
IF NOT EXISTS (SELECT 'True' FROM sys.assemblies WHERE name = 'ComputedColumnFunctions') BEGIN CREATE ASSEMBLY ComputedColumnFunctions FROM 'C:WebsitesAssemblyTestStoredFunctionsStoredFunctionsinStoredFunctions.dll' GO
CREATE FUNCTION fImageFileName ( @ProductID int, @ImageSizeCode nvarchar(4000) ) RETURNS nvarchar(4000) AS EXTERNAL NAME [ComputedColumnFunctions].[StoredFunctions.UserDefinedFunctions].ImageFileName GO
CREATE FUNCTION fTestInt ( @ProductID int ) RETURNS int AS EXTERNAL NAME [ComputedColumnFunctions].[StoredFunctions.UserDefinedFunctions].TestInt GO
CREATE FUNCTION fTestInt2 ( @TestInt int ) RETURNS int AS EXTERNAL NAME [ComputedColumnFunctions].[StoredFunctions.UserDefinedFunctions].TestInt2 END ELSE BEGIN PRINT 'The assembly named "ComputedColumnFunctions" already exists. No new assembly was created.' END
GO
I read in a book about SQL Server 2005 about including a test for whether the object (such as assembly in this case) exists before trying to create it. If I only include the CREATE ASSEMBLY statement and the FROM line below it and delete the next GO down through the last CREATE FUNCTION (just before the END ELSE), it works fine. If I leave it as is, I get a runtime error on the GO line just after the CREATE ASSEMBLY statement. What am I doing wrong?
I have an application that processes a large number of input files in a CSV format and then posts the data to a table on SQL Server Express.
The data table can end up very large and I have no requirements to store all the data locally. I have included the table in my DataSet using visual studio express so I have access to the schema, but will not run Fill() on it.
Ideally I would like to process a CSV file at a time. I can add records to my local (empty) data table and when I am happy, I can call tableAdapter.Update() or dataSet.DataTable.AcceptChanges() to generate lots of SQL 'INSERT' commands to update the physical database at the server end.
I would then like to empty my local data table (so it doesn't get too big) and repeat the same process over again for each CSV file.
How can I empty my local table without causing it to generate a load of SQL 'DELETE' commands? I want to empty the table and fool ADO.NET into thinking that everything is synchronised, as if it has just done an update but actually hasn't.
Hello SQL Team! I'm stuck at this problem for days and need help. The problem is with the GO keyword. I know the GO causes the batch to get executed and all local variables are lost. But I can't seem to find a work around. I would like each stored procedure to get executed in different batches.
create table sql_cmd(cmd nvarchar(255)) insert into sql_cmd(cmd) values('exec user_sp param1,''param2'') insert into sql_cmd(cmd) values('exec user_sp2 param1,''param2'')
declare @sql nvarchar(255) declare c_sql cursor --small table peformance not a problem. Also open to other suggestions for select cmd from sqlcmd open c_sql fetch next from c_sql into @sql while @@fetch_status = 0 begin print @sql exec sp_executesql @sql GO fetch next from c_sql into @sql end
So i tweaked a stored procedure that did a 1 hour update for a specific countryId.
If that procedure was called at the same time with two different countryId than one update took place and afterwards the other.
Since all the rows are distinct i switched to an batch update only updating 10000 rows at a time ( and not all of the 2 million).
The general locking looked better afterwards but now i receive strange deadlocks.
My theory:
TX1 Updates a row on Page1 (P1) with rowlock. TX2 also does this on P1. Now TX1 deceides to escalate to PAGELOCK. TX1 waits for TX2 to be done with the row so it can lock the page. TX2 waits for TX1 to leave the page since TX2 may also want to pagelock.
Everybody waits for each other so we have a deadlock. Is that feasible ? OR is there another common problem when doing batch updates on the same table with distinct rows ( that can be on a same datapage ofc).
I have a query batch "update" script that upgrades my users database from,say version 0 to version 1, or from version 1 to version 2. I would like toknow how I can wrap the entire script in a transaction, so that either thewhole thing succeeds or none of it does.For example:BEGIN TRANSACTION.......... Alter some tables.....GO.......... Alter a stored procedure.....GO.......... Create a new stored procedure.....GOCOMMIT TRANSACTIONorROLLBACK TRANSACTIONGO(how do I get to the "ROLLBACK TRANSACTION" if an error occurs in the updatescript?)
Hi, I would like to delete a data from a 750million row table in chunks of 10000,without blocking the users.As ours is a 24/7 shop I donot want to block the users for a long time. Answer for this is highly appreciated. Thanks Samna
I want to update tableToUpdate in batches of 5000 per batch and set the lastenecryptionDT to null based on the the join to the tableValues using the column ENCRYPTIONID, and also output updated rows into another table. Incase I would need to do a rollback.
I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.
Say for example, following is the list of records I have in my table which I want to group into 5 batches
A123 A124 A124 A123 A127
After Ntile I will get the below,
Desired output is, Need output like Ntile but all same id should reside in single batch
Even if I n=5, maximum possibility of batches are 3 only.
In another forum post, a poster was deleting large numbers of rows from a table in batches of 50,000.
In the bad old days ('80s - '90s), I used to have to delete rows in batches of 500, then 1000, then 5000, due to the size of the transaction rollback segments (yes - Oracle).
I always found that increasing the number of deleted rows in a single statement/transaction improved overall process speed - up to some magic point, at which some overhead in the system began slowing the deletes down, so that deleting a single batch of 10,000 rows took more than twice as much time as deleting two batches of 5,000 rows each.
good rule-of-thumb numbers (or even better, some actual statistics and/or explanations) as to how many records should be deleted in a single transaction/statement for optimum speed? 50,000 - 100,000 - 1,000,000 or unlimited? Are there significant differences between 2008, 2012, 2014?
We are using the Transfer SQL Server Objects Task to transfer a large table. The trans log is filling up for this table. Is there a method to split the Data Transfer Task into smaller batches? (Smaller tables are transferring without issue.)
We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.
The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).
When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).
I read several posts regarding the batches and generations of changes, and decided to try increasing the €œDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?
I have got MSSQL 2000 set up on a machine in my rack at my local telehouse, and a web server set up at home on an ADSL line.
Both servers can see (ping) eachother fine , so you can rule out any kind of connectivity issues straight away, but when i try to get my forum to connect to the mssql database using the correct credentials it just fails saying that the credentials are incorrect ot the server does not exist.
I also installed an SQL database tool on my web server (Shusheng SQL Tool) and attempted to connect to my SQL server using that tool, and got the following message: '[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.'
The server is currently using mixed mode authentication (SQL/Windows) and has both TCP/IP and Named pipes enabled.
Is there some kind of 'Enable remote connections' option in SQL? I need to be able to allow connections to my SQL server from any system, anywhere...
I need to update a table on our Test Server which is GCSQLTEST, with another table thats on our live server GCSQL. How would I go about doing that in a stored procedure??
CREATE PROCEDURE [InsertRevised_MainTable] AS INSERT INTO dbo.RevisedMainTable ([IR Number], [Date], [I/RDocument], [Violation Type]) SELECT [Incident Report No], [Date], [I/RDocument], TypeOfIncident FROM dbo.RevisedMainTable WHERE NOT EXISTS (SELECT * FROM dbo.RevisedMainTable WHERE [IR Number] = [IR Number])
Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
I have a field that contains values such as 8ft , 7ft, 18ft I have a report in reporting services that shows this:
Before Restock After Restock
Date 1/12/2008 8ft 9ft
1/13/2008 10ft 7ft 1/14/2008 5ft 4ft
I want to create a subquery that grabs the before restock and figures out if it sheds the "ft" part of the value, and then put in a where before restock > (greater than) After Restock.
I have 4 tables with the respective amount of records 1) 6755 2) 2021 3) 2021 4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.
I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table.
Hey all, Here is what I would like to do.. if any can give some advice or direction with this i would appreciate it.
We have a "master database" (MS SQL) at our main office. Then a smaller office with only a dial-up connection. Can I have my db application write all the data input to a local DB. Then write something to connect with the master DB, through a Terminal server, etc., and update it with the data from the other smaller db?
