Hi,
I need to iterate over millions rows in a table and I need call an extended
stored procedure (written in C++ and not possible be written in TSQL) using
the columns of each row as parameters and write the return values to an new
table. The current script open a cursor.
What's the best way to implement it? BCP to a text file and external program
parse and write the text file and BCP back? sp_cmdshell an executible for
each row? (so needn't worry about C++ memory leak issu). XML?....
Thanks,"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,
I'd say the "best" solution would be to rethink your architecture and
implement it differently if you plan to do this on a regular basis. This
doesn't sound like a very scalable or desirable way to use a client-server
database. Have you considered using SQL Server 2005, where you can implement
.NET code in the database? Or implementing your code in ADO rather than wit
h
an XP?
If it's just a one-off requirement then you just need to test which approach
works best for you. It isn't really a SQL question since, for the purposes
of this exercise, you are just using SQL Server as a file dump rather than
what it was designed for. Why not just loop in TSQL and call the proc for
each row?
David Portas
SQL Server MVP
--|||If you have a situation that calls for looping through a cursor, then it's
better to implement the cursor on the client side than on the server. Open a
read-only, forward only ADO recordset and Command.Execute the stored
procedure for each row.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
> Hi,
> I need to iterate over millions rows in a table and I need call an
> extended
> stored procedure (written in C++ and not possible be written in TSQL)
> using
> the columns of each row as parameters and write the return values to an
> new
> table. The current script open a cursor.
> What's the best way to implement it? BCP to a text file and external
> program
> parse and write the text file and BCP back? sp_cmdshell an executible for
> each row? (so needn't worry about C++ memory leak issu). XML?....
> Thanks,sql
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Tuesday, March 27, 2012
Best solution
Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.
ThanksNope, it's not making sense :-) Can you provide a small example with data to illustrate what you are trying to do?
Terri|||If I understand correctly, can you do several UNIONs and get them in turn?
Table1 join table 2 on col2 UNION
Table1 join table 2 on col3 UNION
Table1 join table 2 on col4 UNION
etc.
What it sounds like is that you should have a third table that contains a record for each possible combination of keys between table1 and table2. It sounds correcting the database structure is the best bet if you are able to do that.|||okay let me try :)
Let say I have a row that consist of the following:
TABLE 1:
key|ele1|ele2|ele3|ele4|ele5
1 6 2 5 null null
key column contains the rowID
ele1 - ele5 columns contain row IDs from the same table. ele1 is not nullable but the rest is nullable. I think if I use JOINS I will get an "ambigious error."
Table 2 ( ele ):
key|name |value
1 | "first" | 1
2 | "second" | 2
3 | "third" | 3 and so on.|||You should be able to accomplish what you need using JOINs with aliases.
SELECT
table1.key,
table2key.name,
table2key.value,
table1.ele1,
table2ele1.name,
table2ele1.value,
table1.ele2,
table2ele2.name,
table2ele2.value,
table1.ele3,
table2ele3.name,
table2ele3.value,
table1.ele4,
table2ele4.name,
table2ele4.value,
table1.ele5,
table2ele5.name,
table2ele5.value
FROM
table1
LEFT OUTER JOIN
table2 AS table2key ON table1.key = table2key.key
LEFT OUTER JOIN
table2 AS table2ele1 ON table1.ele1 = table2ele1.key
LEFT OUTER JOIN
table2 AS table2ele2 ON table1.ele2 = table2ele2.key
LEFT OUTER JOIN
table2 AS table2ele3 ON table1.ele3 = table2ele3.key
LEFT OUTER JOIN
table2 AS table2ele4 ON table1.ele4 = table2ele4.key
LEFT OUTER JOIN
table2 AS table2ele5 ON table1.ele5 = table2ele5.key
Terri|||Thanks so much for all your help Terri!
Monday, March 19, 2012
Best practice for storing long text fields
As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
TzankoTzanko wrote:
I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
Do these text fields hold the same text that spans fields, or are
they different texts?
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
>
>
Do these text fields hold the same text that spans fields, or are
they different texts?
>
>
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
>
>
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
>
If the columns are different texts, I see little point to use the
text data type.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||@.sh (spam@.spam.com) writes:
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thnaks for your replies.
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
TzankoTzanko wrote:
Quote:
Originally Posted by
As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:
Quote:
Originally Posted by
As we all know, there is a 8060 bytes size limit on SQL Server rows.
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
Quote:
Originally Posted by
I have a table which requires a number of text fields (5 or 6).
Do these text fields hold the same text that spans fields, or are
they different texts?
Quote:
Originally Posted by
I am given to review a design, which esentially suggests moving the text
columns to a separate TextFields table. The TextFields table will have
two columns - a unique reference and a VARCHAR (4000) column, thus
allowing us to crossreference with the original record.
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
Quote:
Originally Posted by
My first impresion is that I'd rather use the SQL Server 'text' DB type
instead, which would allow me the same functionality with much less
effort and possibly better performance.
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
If the columns are different texts, I see little point to use the
text data type.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Are you saying that in SQL 2000 you can Span VarChar's into multiple columns
automatically? If so how?
Cheers, @.sh
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983CEED4849AEYazorman@.127.0.0.1...
Quote:
Originally Posted by
Tzanko (tzanko.tzanev@.strategicthought.com) writes:
Quote:
Originally Posted by
>As we all know, there is a 8060 bytes size limit on SQL Server rows.
>
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
>
Quote:
Originally Posted by
>I have a table which requires a number of text fields (5 or 6).
>
Do these text fields hold the same text that spans fields, or are
they different texts?
>
Quote:
Originally Posted by
>I am given to review a design, which esentially suggests moving the text
>columns to a separate TextFields table. The TextFields table will have
>two columns - a unique reference and a VARCHAR (4000) column, thus
>allowing us to crossreference with the original record.
>
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
>
Quote:
Originally Posted by
>My first impresion is that I'd rather use the SQL Server 'text' DB type
>instead, which would allow me the same functionality with much less
>effort and possibly better performance.
>
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
>
If the columns are different texts, I see little point to use the
text data type.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||@.sh (spam@.spam.com) writes:
Quote:
Originally Posted by
Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool!
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...
Quote:
Originally Posted by
@.sh (spam@.spam.com) writes:
Quote:
Originally Posted by
>Are you saying that in SQL 2000 you can Span VarChar's into multiple
>columns automatically? If so how?
>
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Many thnaks for your replies.
Just to clarify the issue:
The requirement is to create a table that has say 6 columns which store
strings (such as Description, Notes, etc.) Each of these 6 columns
should store a char string of max length of 4000 characters. The
problem is that SQL Server 2000 will not work if I simply defined the
columns as varchar(4000) as at some point the row size reaches the page
size of 8060 and this generates an error. There is a 8060 bytes limit
on SQL Server 2000 rows. Note that I am not trying to store the same
string into 6 different columns spanning from column to column. I have
a separate string to store in each column.
The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.
Second question is what is the situation with SQL Server 2005. I
understand I can simply define the columns as varchar(max) and do not
have to do anything special. Has someone used this successfully and can
you confirm it ste case?
Thanks for your help.
Tzanko
@.sh wrote:
Quote:
Originally Posted by
Cool!
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...
Quote:
Originally Posted by
@.sh (spam@.spam.com) writes:
Quote:
Originally Posted by
Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Quote:
Originally Posted by
The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.
The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.
If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.
You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Thursday, March 8, 2012
Best Practice - Lookup or SQL from Variable?
Hi,
I am pulling data from FoxPro tables into SQL 2005, and want to only pull new or changed rows. Accordingly each table in Fox has a column LastChangedDateTime, indicating the last time the row was updated, and I have a table in SQL which has one row per Fox table, listing the table name and the most recent data pulled into SQL.
In 2000 DTS I would have pulled the SQL datetime value into a package variable, then used a parameterized SQL statement with ".. WHERE LastChangedDateTime > ? " to select the rows I require.
In SSIS this approach does not seem to be possible, and the options are that I either use a variable for the entire SQL statement or, as the first SSIS tutorial suggests, use a lookup against the SQL table.
Gut feel is that the lookup will perform slower than creating the variable SQL and executing that (given that the source table is 13 million rows and rising, and I only want the last 100,000 or so from today).
What is considered best practice under these circumstances?
Also is it possible to write SSIS scripts in C# rather than VB.NET, as the syntax differences are driving me mad? ;-)
Thanks in advance,
Richard R
I would go with the DTS style method, it should work just fine. An Exec SQL Task can get the date value and store it in a variable. The variable can then be used in a parameterised query, in the same way as you did with DTS, but obviously using a Data Flow task, and the correct source. Saying that I have not tried it with FoxPro, but you will be using the same OLE-DB driver I assume so it should work fine. Parameter support is available in the OLE-DB Source, and the driver should support it if it did in DTS.
Using a lookup would not make sense as you will be doing far more work.
Using a variable for the command (with EvaluateAsExpression = True) is also perfectly valid, and sometimes the better choice, but for a simple query like this and since you have parameter support, I'd go with the former method, but there is nothing in it really.
The Script Task and Script Component both use Visual Studio for Applications (VSA), which means you get the power of .Net rather than a interpreted script language. Unfortunately VSA has only been implemented for VB.Net, there is no C# support. No idea if or when there will be either, but you are certainly not the first to raise the issue.
Thanks Darren,
Sometimes it's good to check out a gut feeling - just in case the whole underlying system architecture has changed.
I'm not sure the FoxPro v9 driver OLEDB actually has parameter support, it didn't seem to work when I tried it, hence the original post. This is the first time I've had to interface to FoxPro, and there are definitely a few oddities about the process...
Regards,
Richard
Saturday, February 25, 2012
Best GUID Storage
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
Best GUID Storage
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?
Best filtering solution for performance?
Hello,
i have a report with approximately 20 reportparameters from queries. This is really slowing down the system, although the dataload (rows sent back) is not that huge. I read on the internet that there are basicly three approaches.
1, Query parameter with alot of diffrent datasources
2, Table filtering
3, Stored procedures
I wonder which approach is the best and why it takes such a long time to get the report with the report parameters (before generating the report itself).
Thank you for your help! 
I would think stored procedures would be the best approach. Have you tried using SQL profile to trace where the slowdown is?
cheers,
Andrew
|||Hello,
thank you for you fast reponse! I now changed to Stored Procedures, i thin in long term it is better to use SP. But now i Have problems with multivalued parameters, which will be sent as an array to SQL Server. I found some articles about this matter an will try to solve it. The performance problem seems to be solved although I don't know why. Will look into that later. Thanks fpr the profiler tip! Best regards!
Friday, February 24, 2012
Best Command to Use for getting first instance of a value
In a select statement, I want to evaluate a value, and when I get that value, I want the value for all other rows to be set to that value so I end up with just one row.
For example:
Meeting# Vote
12345 Yes
12345 Maybe
12345 No
12345 See Comment
12345 Yes
Whenever I get a vote that says, See Comment, I want the end result to look like:
Meeting# Vote
12345 See Comment
If my result does not have See Comment, Yes will be next in line:
Meeting# Vote
12345 Yes
12345 Maybe
12345 No
12345 Yes
Whenever I get a vote that says, Yes, I want the end result to look like:
Meeting# Vote
12345 Yes
And so on.... I hope this makes sense.
Thanks, Iris
Try:
select
*
from
dbo.t1 as a
where
Vote = (
select top 1 Vote
from dbo.t1 as b
where b.Meeting# = a.Meeting#
order by
case
when Vote = 'See Comment' then 1
when Vote = 'Yes' then 2
when Vote = 'Maybe' then 3
when Vote = 'No' then 4
else 5
end
)
-- 2005
;with cte
(
select
Meeting#,
Vote,
row_number() over(partition by Meeting# order by
case
when Vote = 'See Comment' then 1
when Vote = 'Yes' then 2
when Vote = 'Maybe' then 3
when Vote = 'No' then 4
else 5
end
) as rn
from
dbo.t1
)
select
Meeting#, Vote
from
cte
where
rn = 1
go
AMB
|||The first row for all of my meeting#'s was 'No'. So when select top 1 Vote is used, it got 'No' everytime, and set everything to no.
For the first meeting#, it looked like:
Meeting# Vote
12345 No
12345 Yes
12345 See Comment
12345 Yes
It took 'No' first, and went on to the next Meeting#. What I want it to do is be selective. If 'See Comment' exists, it takes precedent over 'Yes' and 'No'. If 'See Comment' does not exist, it will then look for 'Yes'. Then if 'Yes' or 'See Comment' does not exist, it will then set to 'No'.
Thanks, Iris
|||It works for as expected for the sample data. If it is not what you expect, then post sample data and expected result.
use northwind
go
Code Snippet
createtable #t (
Meeting# intnotnull,
Vote varchar(25)notnull
)
insertinto #t values(12345,'No')
insertinto #t values(12345,'Yes')
insertinto #t values(12345,'See Comment')
insertinto #t values(12345,'Yes')
insertinto #t values(12346,'No')
insertinto #t values(12346,'Yes')
insertinto #t values(12346,'Yes')
insertinto #t values(12347,'No')
insertinto #t values(12347,'Maybe')
insertinto #t values(12348,'No')
selectdistinct
*
from
#t as a
where
Vote =(
selecttop 1 Vote
from #t as b
where b.Meeting# = a.Meeting#
orderby
case
when Vote ='See Comment'then 1
when Vote ='Yes'then 2
when Vote ='Maybe'then 3
when Vote ='No'then 4
else 5
end
)
orderby
Meeting#
;with cte
as
(
select
Meeting#,
Vote,
row_number()over(partitionby Meeting# orderby
case
when Vote ='See Comment'then 1
when Vote ='Yes'then 2
when Vote ='Maybe'then 3
when Vote ='No'then 4
else 5
end
)as rn
from
#t
)
select
Meeting#, Vote
from
cte
where
rn = 1
orderby
Meeting#
droptable #t
go
AMB
|||Thanks, that worked.
Iris
Friday, February 10, 2012
BCP's /F switch
We have a text file in which the first row has four fields and it needs
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets
over that first short row and starts grabbing
at the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets over that first short row and starts grabbing
>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets over that first short row and starts grabbing
> at the second longer row (really row 3).
BCP? BCP never gets , but users of BCP often gets
over it!
I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets
over that first short row and starts grabbingat the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets
over that first short row and starts grabbing>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets
over that first short row and starts grabbing> at the second longer row (really row 3).
BCP
? BCP never gets , but users of BCP often gets over it!I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)