Datatype column type in SQL Server 2000
-
Hi all, Is there a datatype "column" in SQL SERver 2000? If not can we generate user-defined datatype as "column"? How to convert a variable to datatype column ? Please reply asap.. Senthil
-
What exactly do you mean by datatype column. Is this an object? If you are talking about saving objects in sql I would suggest you serialize the object into xml and save the xml in a text column. Ben
Hi, Thanks for your reply. I will give an example Declare @i int Set @i=1 while @i<=7 Begin Update set + @i = value End In the above example, column name will be dynamic , i.e., column1, column2. So, I want to use dynamic query for updation. As dynamic query will hit my performance,I need to avoid dynamic query. Is there any possible way for this? There is a datatype called table . Similar to that, whether there is any datatype called column? Senthil
-
Hi, Thanks for your reply. I will give an example Declare @i int Set @i=1 while @i<=7 Begin Update set + @i = value End In the above example, column name will be dynamic , i.e., column1, column2. So, I want to use dynamic query for updation. As dynamic query will hit my performance,I need to avoid dynamic query. Is there any possible way for this? There is a datatype called table . Similar to that, whether there is any datatype called column? Senthil
Certainly if your table doesn't have very much data I would look at doing this in memory. If you are using .net there is a DataTable which you can dynamically create. There are DataColumns which you can dynamically add to the dataTable. Then you can take the devaultview of the datatable and use the rowfilter to query the table's data. In sql 2000 and 2005 there are table variables, but they only stick around for the duration of the stored procedure so you probably want it to be around a little longer then that. If there aren't too many rows and columns I would think seriously of keeping the data in an internal table in your program. Ben
-
Certainly if your table doesn't have very much data I would look at doing this in memory. If you are using .net there is a DataTable which you can dynamically create. There are DataColumns which you can dynamically add to the dataTable. Then you can take the devaultview of the datatable and use the rowfilter to query the table's data. In sql 2000 and 2005 there are table variables, but they only stick around for the duration of the stored procedure so you probably want it to be around a little longer then that. If there aren't too many rows and columns I would think seriously of keeping the data in an internal table in your program. Ben
I am using such type of query in a stored procedure. My stored procedure is compiled each time when it is executed. I am in a position to remove those dynamic queries to get my stored procedure execute faster. I hope u understood. If not I can explain some more. Senthil
-
I am using such type of query in a stored procedure. My stored procedure is compiled each time when it is executed. I am in a position to remove those dynamic queries to get my stored procedure execute faster. I hope u understood. If not I can explain some more. Senthil
Perhaps it would be better to explain exactly what you are trying to do. Why do you need a dynamic query? Why do you need to create a table? If you dynamic query returns certain columns you can just return those columns in the result set and your program can read them. The only issue because how your program knows what to do with the columns. Ben