Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CURSOR and TABLE Expand / Collapse
Author
Message
Posted Tuesday, October 1, 2013 6:10 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:24 AM
Points: 718, Visits: 545
Hi all,

I consider CURSOR and TABLE as data types but some one has argued that these are database objects.

Can you please share your views on the same !!

Thanks.
Post #1500360
Posted Tuesday, October 1, 2013 6:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 13,553, Visits: 10,429
A cursor is a construct in TSQL to iterate over data:
DECLARE CURSOR (Transact-SQL)

A table is obviously used to store data and it can be a "physical object", but you can also have table variables, which are temporary constructs to store data. So you can declare a variable of data type table.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1500364
Posted Friday, October 18, 2013 2:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 404, Visits: 2,384
T.Ashish (10/1/2013)
Hi all,

I consider CURSOR and TABLE as data types but some one has argued that these are database objects.

Can you please share your views on the same !!

Thanks.


I agree with your terminology.

For example,

DECLARE some_name CURSOR FOR select_statement

in my mind would create a variable called 'some_name' as type cursor, same with tables, with individual named cursors and tables as instances or "database objects" having those types.

Not sure how excited I would be about having to convince someone about that who thinks otherwise, but its a bit satisfying for a language nerd like myself.
Post #1506330
Posted Friday, October 18, 2013 2:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 13,272, Visits: 12,103
patrickmcginnis59 10839 (10/18/2013)
T.Ashish (10/1/2013)
Hi all,

I consider CURSOR and TABLE as data types but some one has argued that these are database objects.

Can you please share your views on the same !!

Thanks.


I agree with your terminology.

For example,

DECLARE some_name CURSOR FOR select_statement

in my mind would create a variable called 'some_name' as type cursor, same with tables, with individual named cursors and tables as instances or "database objects" having those types.

Not sure how excited I would be about having to convince someone about that who thinks otherwise, but its a bit satisfying for a language nerd like myself.


**cough**CURSOR**cough** is listed as a datatype (other). This to me is a bit strange. Generally datatypes can define either a column in a table or a variable. You can't have a column with a type of cursor. That makes no sense, yet it is a datatype of sorts.

http://technet.microsoft.com/en-us/library/ms187752.aspx

With the exception of cursor and table. You can have a column with any of the other datatypes.

For a table, it depends. If it is a permanent or temp table then I would argue in those circumstances that is a database and NOT a datatype. However, we can have table variables. I would say that the type of that variable is a table. In other words a table can be either in some situations.

An interesting question that can have a number of interpretations and the explanation can be as challenging as the question itself.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1506332
Posted Friday, October 18, 2013 2:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 8,684, Visits: 9,212
I won't comment on cursors, as I see no point in thinking about them.

Table however is clearly not a datatype; associated with any datatype is the somain for that datatype, and an object that has that datatype can take any value from that domain. So if I have two declarations
declare @a table(x int primary key, y varchar(32) not null, z decimal(15,3) check (z>=0));
declare @b table(x nvarchar(4) not null, y nvarchar(4) not null, z int check (z<0), primary key(y,x), unique(z,y));
the variable @a can take one set of values and the variable @b can take a different set of values; most values of @a could never by values of @b, and vice versa. That makes it absolutely clear that @a and @b have two different types. It's not Table which is a tpe, but an expression like table(x int primary key, y varchar(32) not null, z decimal(15,3) check (z>=0)).


Tom
Post #1506333
Posted Friday, October 18, 2013 3:17 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 404, Visits: 2,384
Sean Lange (10/18/2013)
patrickmcginnis59 10839 (10/18/2013)
T.Ashish (10/1/2013)
Hi all,

I consider CURSOR and TABLE as data types but some one has argued that these are database objects.

Can you please share your views on the same !!

Thanks.


I agree with your terminology.

For example,

DECLARE some_name CURSOR FOR select_statement

in my mind would create a variable called 'some_name' as type cursor, same with tables, with individual named cursors and tables as instances or "database objects" having those types.

Not sure how excited I would be about having to convince someone about that who thinks otherwise, but its a bit satisfying for a language nerd like myself.


**cough**CURSOR**cough** is listed as a datatype (other). This to me is a bit strange. Generally datatypes can define either a column in a table or a variable. You can't have a column with a type of cursor. That makes no sense, yet it is a datatype of sorts.

Its more of a language thing, I wouldn't necessarily be stuck on a datatype having to apply only to something that fits in a column, for instance, while we don't necessarily think of a procedure as being a datatype, in reality, a procedure name would actually point to a bunch of text (and all the compilation constructs), so even here I'd consider a procedure as a type also, with procedure definitions being instances of that type, but really, thats probably just me talking about programming languages and implementations, and is probably not something that has great practical value otherwise. Heck, for that matter, a real SQL server implementer would probably shoot my random musings full of holes.

http://technet.microsoft.com/en-us/library/ms187752.aspx

With the exception of cursor and table. You can have a column with any of the other datatypes.

For a table, it depends. If it is a permanent or temp table then I would argue in those circumstances that is a database and NOT a datatype. However, we can have table variables. I would say that the type of that variable is a table. In other words a table can be either in some situations.

It depends on how picky we are I guess, after all we're humans attaching names to bits and bytes. For example, I like to think that "table" is just one type, and according to the attributes of given instances of that type (whether a variable or non variable table), we can then determine what operations can be applied to instances of that type, for example, table variables are of type TABLE (in my view of things), but the INSTANCE of that type has particular attributes that limit us as to what operations we can do (like expect the data held by the instance to hang around, roll back from transactions, etc).

An interesting question that can have a number of interpretations and the explanation can be as challenging as the question itself.

I suspect the REAL answer is buried deep inside a Microsoft engineering document
Post #1506334
Posted Tuesday, October 29, 2013 12:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:19 PM
Points: 86, Visits: 676
In my opinion just similar to others:
- TABLES are database Objects and
- CURSORS are Programming Constructs (not to confuse with the cursor data-type),
no matter how you declare them.

An someone correctly pointed out that: data-types are applied at the table's column levels and at Cursor's parameters, so how can we call Tables/Cursors as a datatype.


~manoj
sqlwithmanoj.wordpress.com
Post #1509185
Posted Tuesday, October 29, 2013 11:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 404, Visits: 2,384
manub22 (10/29/2013)
In my opinion just similar to others:
- TABLES are database Objects and
- CURSORS are Programming Constructs (not to confuse with the cursor data-type),
no matter how you declare them.

I think this is fine if you are ok with it, after all names for concepts are again a human construct. Since I am sort of interested in programming languages and the sorts of things they describe, I then look at it like so: if a symbol is not a keyword, operator or delimiter, its probably a name for something, and since we 'name' our cursors, I like to then think what sort of thing are we referencing when we type in this name (or more importantly, when whatever reads T-SQL definitions does when encountering a previously defined name), and since we've previously declared it as a 'cursor', well thats what 'type' of thing it is, ie,. this 'name' references an 'object' that has type 'cursor', ie., I'm not going to have much success assigning it an integer value. In a parallel way, 'Programming Construct' pretty much does not have enough specificity for me, although I'd not deny that there are things that we can call 'Programming Constructs'.

But this is more for my interests, I don't think you need to adopt this thought process to be successful with using and administrating SQL Server. I'm just relating why I agree how cursor could be a type and that a named entity in T-SQL could reference an instance of something that has a type of 'cursor'.

But this isn't meant as a specific criticism for you, if you are successful with the mental models and terminology that you've grown accustomed to, I don't see a big reason to change.

An someone correctly pointed out that: data-types are applied at the table's column levels and at Cursor's parameters, so how can we call Tables/Cursors as a datatype.

Well, leaving tables and cursors aside, you should at least include variables (the strings with '@' in front of them ). (Of course then, we start talking about some variables having a type of 'table' and so on...)
Post #1509509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse