October 1, 2013 at 6:10 am
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.
October 1, 2013 at 6:15 am
A cursor is a construct in TSQL to iterate over data:
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 2:22 pm
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.
October 18, 2013 at 2:51 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 18, 2013 at 2:54 pm
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-2 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-2 can take a different set of values; most values of @a could never by values of @b-2, and vice versa. That makes it absolutely clear that @a and @b-2 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
October 18, 2013 at 3:17 pm
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 😀
October 29, 2013 at 12:17 am
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.
October 29, 2013 at 11:58 am
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...)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply