DisAdvantages Of Cursors.
Apr 25, 2008can any one help me out to know the disadvantages of the cursor
View 2 Replies
ADVERTISEMENT
Autonumber Disadvantages
Oct 4, 2004hi,
i am thinking of using autonumber in my table which is more likely to grow over time. but i am also concerned that what if the records get too much? is there is any other fields to use instead of autonumber? is uniqueidentifier a better option? i have a bit of discussion with my colleague about this matter.
any help is appreciated
Disadvantages Using Functions
Jun 21, 2001any disadvanges using user defined functions calling from stored procedures??
View 4 Replies View RelatedAdvatnages And DisAdvantages Of
Apr 7, 2004Running SQL Server as a DOMAIN user account.
View 3 Replies View RelatedDisadvantages Of Using Uniqueidentifier (Tell Your Tale)
Nov 19, 2003Dear SQL...
I'm about to use uniqueidentifier and NEWID() for my new DB,
The DB may grow wild and I want to super safely create connections between tables,
so it would not just rely on numbers (that can B changed) as used with IDENTITY...
If U had some BAD experience with this or if U have any tips I can use please let me know
+
Should I take any special steps when using index on it ?
Advantages And Disadvantages Of The Replication Ty
Feb 13, 2008hi,
Can any body help me in knowing what are the advantages and disadvantages of all the three replication types?
Advantages/Disadvantages To Using More Than One Database
Dec 18, 2006I have a content site where everything is currently in one SQL ServerDB. As I add features to the site, for example message boards andblogging, does it make sense to put those features in a separatedatabase? What would I lose and gain in doing so? Thanks so much.Erik
View 3 Replies View RelatedAdvantages/Disadvantages B/w Being Consultant Vs Employee
Jul 12, 2000Hi, I would appreicate your opinion/ feed back about Being consultant vs Being employee.
What are the advantages/disadvantages.
Does consultant makes more money than an employee and why so
Thanks for your response
Ali
Advantages&&Disadvantages Logical Drives
Aug 5, 2005Are they're any real disadvantages or advantages in having 1 massive disk partioned to create 2 logical drives (not including the C drive) and separating the SQL Database File & Transaction Log so that it doesnt reside on the same logical drive?
After all, it is still a single disk.
thanks.
Disadvantages Of Running 80 Compatibility On 2005?
May 1, 2007I have a hosted solution where the web host provider tells me I'm running on a 2005 platform, but when I check the DB options, it shows compatibility level 80. The provider tells me I'm not losing any performance by running in the lower compatibility mode. Is this truly the case? What else am I losing out on in terms of features or capabilities?
View 11 Replies View RelatedI Want To Know About Advantages And Disadvantages Of SQL Server 2005.
Nov 23, 2007Please reply me as soon as possible.
View 4 Replies View RelatedDisadvantages Of The Implementation Of Explicit Subtypes On MS SQL 7 And Oracle8i/9i
Jul 20, 2005Could some database expert post some information or link about that?thanksJanusz
View 1 Replies View RelatedOlap Mining - Advantages, Disadvantages And Problems
Jan 19, 2007Dear Sirs and Madams,
it's quite hard to find informations about OLAP mining I think. So I have some questions to you:
What advantages do I have by using Olap Mining instead of "normal" mining (relational databases)?
Is it just faster or are there other advantages?
What are the disadvantages? The data can be overaggregated (no detailled results), can not? Are there other disadvantages?
What problem do I have to face?
Whats going on with empty cells?
Thank you very much in advance and have a nice weekend.
Thorsten
Transact SQL :: Disadvantages Of Column Store Index
Nov 3, 2015What are the disadvantages of columnstore index in Sql Server 2012
View 4 Replies View RelatedUsing Cursors
Jul 16, 2006Hello,
Can anyone direct me a good article that is about why we should try avoiding using cursors and what are the alternatives?
Using Cursors From Asp.net
Feb 16, 2008can we call cursors from asp.net as we can call stored procedures from asp.net.
Cursors
Jan 7, 2005Hi,
I'm writting a stored procedure to insert new rows into a table, but I need to loop through an exsiting table and create a new record for every row in the old table. So I think what I'm needing to use is a cursor, but I've never worked with cursors before, and I just want to check that I have the right idea on what they are used for and if what I have so far looks ok.
This what I have so far, the StoreFees table only has 5 rows:
DECLARE @StoreFee as smallmoney
DECLARE @StoreLineID as int
DECLARE MyStoreFees CURSOR
FORWARD_ONLY
FOR
SELECT LineId, StoreFee FROM StoreFees ORDER BY StoreFee
OPEN MyStoreFees
FETCH NEXT FROM MyStoreFees
INTO @StoreLineId, @StoreFee
--Do my Inserts into other tables
INSERT INTO OtherTable (...,...,.., @StoreFee)
...
...
-- Done working with that row
CLOSE MyStoreFees
Cursors
Feb 28, 2005I am trying to pull the data via fetching rows into a variable.
Begin
Fetch Next Into @temp...
Select @MainVariable = @MainVariable + @temp < @temp doesn't refresh with next fetch)
Print @temp < this works fine and returns the value for each fetch
End
I can print @temp and the data returns fine, but when I try to cancantenate with the above select, it doesn't refresh @temp wit the column in the table.
Any ideas?
DotNetNow
Cursors
Jun 19, 2001I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.
The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.
The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message:
Server: Msg 3910, Level 16, State 1, Line 1
Transaction context in use by another session.
[OLE/DB provider returned message: Unspecified error]
There seems to be a connection issue. Can someone help me work around this?
Thank you,
Neal
FIRST PROCEDURE (this one works perfectly):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>INSERT INTO ##dbtemp
>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
exec spal_db_files_update
SECOND PROCEDURE (see >>>> to note problem area):
truncate table dbidname
CREATE TABLE ##dbtemp ( dbname VarChar(50),
dbsize varchar (25),
dbownervarchar(50),
dbid smallint,
createdate datetime,
statusvarchar(75) )
declare@servernamevarchar(50)
declare dbupdate cursor
global
for
select servername from sqlservers where status = 'a'
open dbupdate
fetch next from dbupdate into @servername
while @@fetch_status = 0
begin
print @servername
EXEC sp_addlinkedserver @servername, 'SQL Server'
>>>>INSERT INTO ##dbtemp
>>>>exec (@servername + '.master..sp_helpdb')
alter table dbidname alter column sqlservers_id int null
insert into dbidname (dbsystemid, dbname)
select dbid, dbname from ##dbtemp
update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and
dbidname.sqlservers_id is null
update dbidname set whenupdate = getdate()
alter table dbidname alter column sqlservers_id int not null
exec sp_dropserver @servername
delete ##dbtemp
fetch next from dbupdate into @servername
end
close dbupdate
deallocate dbupdate
drop table ##dbtemp
Cursors
Nov 28, 2000I am receiving this error when running my cursor:
Error Messages...
Server: Msg 16933, Level 16, State 1, Line 0
The cursor does not include the table being modified.
The statement has been terminated.
Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)...
The cursor does not include the table being modified.
The statement has been terminated.
query:
declare cursor_test CURSOR for
select emp_ssn, effective_date1 from temp_employee_benefit_load
open cursor_test
declare @ssn char(9), @process_date char(8)
fetch next from cursor_test into @ssn, @process_date
while (@@fetch_status=0)
update test_cursor
set ssn = @ssn, process_date = @process_date
where current of cursor_test
fetch next from cursor_test into @ssn, @process_date
close cursor_test
deallocate cursor_test
Any help is appreciated:
Thanks,
Cursors?????????
Sep 7, 2000I have stored procedure in that I am using a cursor to fetch the row by row.
cursor is fetching 75000 records so that the procedure is taking long time.
Is there any way to replace the cursor to fetch the records row by row.
He needed.
Ranch
Cursors
Apr 14, 2000Please help resolve a problem (and debate!).
We have an app using VB with Access as the front end to SQL. SQL statments were built and sent
directly to the server obtaining set-oriented results. Response was fair.
We have a new app developed by an "expert" using VB & Interdev that was to be ported for use on
the internet. The app was designed using the same logic to build the SQL statements, but it is also
using cursors to retrieve all data. RESPONSE IS TERRIBLE!
The designer says that to access SQL over the Web, cursor use is a MUST!
True? Not True?
If true, and I am stuck with an app using cursors, any basic suggestions on where to look to improve
response time?
Thanks for any input.
FJ
How To Use Cursors
Apr 23, 1999Hi,
I am using SQL Server 6.5 and I have a VB routine that updates a field in a tble so it can be used in a primary key. This is run after input of data using bcp. I have noticed on several posts re: using cursors to move through a recordset and was wondering if I could use this functionality to replace my VB routine. I checked the archives and found several messages but I am not sure where to start. My VB routine is:
I first would query the data to return all rows where the value of R08SegmentValue= Null. Then I move through the resultset changing the value of the field R08Segments.
***********************Start of VB Code****************
'Set initial value for sTReportHeaderIdx
With rs1
sTReportHeaderIdx = !ReportIdx & !HeaderIdx
sPReportHeaderIdx = sTReportHeaderIdx
End With
bCount = 1
'loop through rs and when streportheaderidx changes reset bCount to 1
'otherwise increment bcount by 1 and write to field R08SegmentValue
Do While rs1.EOF = False And blnContinue = True
DoEvents
Do While sPReportHeaderIdx = sTReportHeaderIdx
With rs1
.Edit
!R08SegmentValue = bCount
.Update
bCount = bCount + 1
sPReportHeaderIdx = sTReportHeaderIdx
.MoveNext
If Not rs1.EOF Then sTReportHeaderIdx = !ReportIdx & !HeaderIdx
End With
Loop
sPReportHeaderIdx = sTReportHeaderIdx
bCount = 1
Loop
In the end my data should look like
ReportIdx HeaderIdx R08SegmentValue
1 1 1
1 1 2
2 1 1
2 1 2
2 1 3
3 1 1
3 1 2
3 1 3
1 2 1
1 2 2
1 2 3
etc..
Can this be done via an sp? How would I go about writing something like this?
Thanks in advance...any assistance you could provide would be appreciated!
Kevin
Using Cursors
Aug 6, 2002I hope someone can help me with this, as BOL didn't get me the answer I am looking for.
I want to write an update step that will transform the following:
1 abc
1 def
2 lmn
3 rst
4 tuv
4 xyz
4 jkl
5 pqr
into this:
1 abc,def
2 lmn
3 rst
4 tuv,xyz,jkl
5 pqr
In other words, I want to concatenate multiple instances into 1 record per row, but I am not familiar with working with cursors or loops.
Thanks in advance!
bv
Cursors - Use Them Or Not???
Nov 16, 1998According go textbooks and T-SQL developers experience - using cursors is not recommended, more over they say: avoid cursors where it's possible.
Could someone please recommend any other way to go through the recordset(resultset) forward and backward to perform some search or calculations, if there are a specific requirement for not using front-end tools such as VB or MS Access(please don't ask why), other words - all the work must be performed in T-SQL stored procedure.
Thanks
Cursors
Feb 24, 2008Hi
I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )
how do I view the contents of the cursor ?
T-SQL Cursors?
Aug 25, 2004I have a stored proc that merges records from an undeduped table to a deduped table. It is running really slowly. Merging 70 million records against a deduped 70 million is taking 115 hours and counting on decent hardware. Very slow.
I suspect there is significant room for optimization here. Some of my ideas:
- Write/update back to the cursor rather than executing separate UPDATE statements.
- Try a dynamic cursor instead of a READ ONLY cursor.
- Dump new elements to a separate table or a file and then do a single large INSERT.
Anyone else think any of these ideas will work? Can anyone think of something better?
BTW, I've tried to replaced the procedural cursor code with set based UPDATES/INSERTS but the short version of the story is that that route just didn't pan out. I know that is very common optimization advice.
I've made minor simplifications to the code:
- Took out code to handle last line
- Took out progress logging code
- Removed some DECLARE statements. These are needed to run but it should be obvious what they were supposed to be.
Cursors
Aug 27, 2004Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.
DECLARE [TM #] CURSOR
FOR SELECT * FROM [2004 TERMS];
Cursors
Jan 14, 2005nevermind forget I asked thanks anyways
View 11 Replies View RelatedCursors Are Bad
Feb 10, 2005These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
Be nice. I have to work with these guys.
Cursors
Jan 24, 2006In what situations would you use a cursor over a t-SQL update/select statement
View 3 Replies View RelatedCursors....How To Get Away From Using?
Jun 26, 2007Hey guys
I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.
I am quite novice in my abilities and I am completely stumped on how to get around using them.
As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?
I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.
Any help or pointers or a link to a good tutorial would be woderful.
Thanks
tibor
Cursors
Jan 23, 2004declare BadRecords cursor dynamic for select lngZipCodeID
from ZipCodes where lngZipCode=@ZipCode and lngZipCodeID<>@NewZipID
/* this is the syntax in Sybase
open BadRecords;
BadRecordsLoop: loop
fetch next BadRecords into BadID;
if sqlstate<>0 then
leave BadRecordsLoop
end if
*/
I have to convert it to sql , I m just checking whether my syntax is correct or not
open BadRecords
while @@fetch_status = 0
begin
set @BadId = fetch next BadRecords
if @@Error = 0
end
I have doubt in my syntax. Can you help me out in this?
Thx