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!

Best Replication For Me?

To add a column, you can use sp_repladdcolumn and to
remove, sp_repldropcolumn, and have a look at this
article for datatype changes:
http://www.replicationanswers.com/AddColumn.asp.
However, if your changes to the schema are often and
involve datatype changes, you might be better off
reinitializing, in which case this shouldn't affect the
type of replication you'll implement.
BTW, in SQL Server 2005 the Alter Table statement is
allowed on replicated tables within the context of
replication, so things become much easier.
As for changes to the developer side of things, this are
some simple comments off the top of my head: merge
replication and replication with updating subscribers
will generally add a guid column. In the case of merge it
might not if there is one already there with the rowguid
attribute. Apart from that, the trigger firing order can
be important in certain replication types, as again merge
and updating subscribers will add triggers to the
replicated table. Transactional replication will not
itselt change the publisher's tables, but there is a
schema requirement - the published table must have a PK,
so this might change your code. Finally, if your code
expects to work on the subscriber in exactly the same way
as the publisher, this might not work as in some cases
the schema is subtly altered, eg PKs become unique
indexes.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046a01c53437$7c6b7570$a601280a@.phx.gbl...
> To add a column, you can use sp_repladdcolumn and to
> remove, sp_repldropcolumn, and have a look at this
> article for datatype changes:
> http://www.replicationanswers.com/AddColumn.asp.
> However, if your changes to the schema are often and
> involve datatype changes, you might be better off
> reinitializing, in which case this shouldn't affect the
> type of replication you'll implement.
>
Can you explain this in more detail? I don't follow how reinitializing
allows for the schema and datatype changes.
WB
|||It doesn't Basically what I'm thinking is that the
process to change a datatype is longwinded, in terms of
the processing requirements, and there comes a point when
it would be less work to simply reinitialize. Perhaps
this point is 2 datatype changes - need to check?
Rgds,
Paul Ibison

Best practise of storing a column which is of string type

Hi,

Hi I am currently using SQL Server 2005.

Whats the difference between nvarchar & varchar datatypes in SQL server and which is opted best to store strings.

Thanks,

Uma Ramiya

nvarchar is used commonly used to store national characters / unicode characters. so if your front end application accepts unicode characters to be stored to your db then you should use nvarchar. if not use varchar instead
|||

I use ASp.Net 2.0 as front end. In that case what should I use

Thanks,

Uma Ramiya

|||its not dependent on asp.net. it depends on the localization of your application e.g. does your asp.net application can be viewed, transalated and/or accepts inputs with chinese , korean characters, etc. then you should use nvarchar. if you application only stores english characters then use varchar
|||

If you want to store english (latin based) string then you can use varchar.

If you want to store non-english string (ex. Japanese, Hindi, Tamil etc) then you have to use NVarchar.

Nvarchar uses Unicode (which supports all the alphabets/numbers)

Varchar Uses ASCII CODE (which supports only english alphabets/numbers)

Nvarchar each char occupies 2 Byte

Varchar each char occupies 1 Byte

Nvarchar max length in SQL Server 2000 = 4000

Varchar max length in SQL Server 2005 = 8000

If your app supports (or planned to support on future) Globilization/Localization then use the NVarchar

Otherwise Varchar is more enough..

Best practices for Unicode column supporting mixed languages and searching

(I posted this to sqlserver.server newsgroup but did not get a helpful
response)
Globalization gurus,
The problem is storing in a single column character data from mixed
languages and then providing a search capability to find the best match
given a search string in some arbitrary language. The column, of course, is
Unicode data type with some collation. What is recommendation for table
design for efficiency?
As an example, consider an international directory of business names and a
function to search for a name "like" <some string> where <some string> is
user input in any language. Since collation determines comparison rules it
seems the appropriate collation is one that best matches the language of the
search string. Further, to facilitate matching it seems appropriate to relax
restrictions such as case and accent sensitivity. That is a strict binary
comparison is not "user friendly." However there are performance
implications when the collation of the search string does not match the
collation of the database column.
Has anyone solved this problem or know of a good discussion?
Thank you,
SethSeth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>|||If anyone has ideas on non-FTS solutions I would appreciate that as well.
John,
Good idea!
I have read about FTS enhancements in SQL Server 2005 but I admit to having
an aversion to FTS and dismissed it as an option prematurely. It is
definitely worth consideration.
Seth
--
"John Kane" wrote in message news:OVefrPe2FHA.1140@.tk2msftngp13.phx.gbl...
Seth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>

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

Best Data type to hole phonenumber

Whats the best type to hold a phone number in this format with a column in my sql table...should i use varchar? or something else....any help would be great!!

Is this phone number US-only or is it international?

Thanks

Waseem

|||its only US|||

Take a look at this example

http://www.rampant-books.com/t_super_sql_128_user-defined_data_types.htm

Hope this helps

Best and quickest approach to importing thousands of 2 meg XML files into XMl column?

Hi all,
I intend to use SQL Server 2005 (which I haven't used before), to store hundreds of thousands of XML files this autumn and I'm trying to figure out which approach is the best to do this. These files are very complex (multiple nested elements) and use multiple namespaces (imports).The files will be on the same server as the Database. Below is a list of some ideas I have.

1) Use Some DTS process to import the XML files if possible in SQL Server 2005?
2) Create a stored procedure that interates through a local directory, opens each XML file and inserts it's content into the database column using the bulkload method (any examples would be appreciated). Does this require the use of some scripting code such as VB script?
3) Run a server-side script such as PHP that loops through a local directory and stores the content of the XML file into a variable which is passed to a Stored procedure which stores the variable in to the database column? I have tried this and it seems very unstable and slow, roughly 2 minutes per file (the application server and database servers are on different boxes). I'm worried when I need to loop through thousands of files it will crash the servers!

Any suggestions would be appreciated

Muhi

Muhi,

SQL Server provides a command line tool called 'BCP' which is one of the good ways to bulk load XML data into an XML column. For instance you can store your XML instances in a file with a delimiter inbetween each instance (default delimiter is ,) and you can use the following command to bulk load your data:

bcp YourDB..YourTable in "XMLData.csv" -T -b 300 -N -h "TABLOCK"

This will insert the instances from 'XMLData.csv' into 'YourTable' in database 'YourDB'. BOL has a lot more information about the command line options in BCP. There are also more hints you can provide to BCP to improve performance. For instance if you have a clustered index on the table and if you know that your input data in the file is ordered on the index column (say id) then you can provide another hint to BCP -h "ORDER(id)" to avoid some additional sorts.

Thanks

Babu

|||

Hi Muhi,

try SQL Server 2005 Books Online

Examples of Bulk Importing and Exporting XML Documents

http://msdn2.microsoft.com/en-us/library/ms191184.aspx

Best regards

Jiri

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
it
inside
>
>.
>

Behavior question about updateable resultsets...

I have a question regarding a certain behavior of updateable
resultsets. If I update a column using any of the updateXXX methods and
then try to use the getXXX methods from the same column to see if it
updated the results locally and not on the server, I get the same old
value. I have to call updateRow() but that updates the underlying
database and still gives me the old value until I execute the same
query again and get a new resultset. Maybe the code below will clarify
my question more..
Connection con = null;
Statement stmt;
ResultSet rst;
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
System.out.println("Getting connection.");
con = DriverManager.getConnection(url);
System.out.println("Connection successful.");
String st = "select age,sname,snum FROM student;";
stmt =
con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
rst = stmt.executeQuery(st);
rst.last();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
rst.updateInt(1,23);
rst.updateRow();
System.out.print(rst.getInt(1)+" ");
System.out.print(rst.getString(2)+" ");
System.out.print(rst.getLong(3)+"\n");
The output is:
Getting connection.
Connection successful.
25 Edward Baker 578875478
25 Edward Baker 578875478
If I were the run the same code again, I get:
Getting connection.
Connection successful.
23 Edward Baker 578875478
23 Edward Baker 578875478
Any/all help is appreciated
Thanks
Devansh Dhutia
University of Iowa
This is a bug and it does not have a trivial fix. I would like to encourage
you to file this using the product feedback website (below).
The problem here is that there are two mutually exclusive places where
column values transit through the driver. The first, used only by getters,
is through the columns array (lives on the statement). The second, used
only by setters, is through the colParam array (also lives on the
statement). The columns array is read only the colParam array is write
only...
Note that the JDBC spec provides (in section 27.1.22, p. 718 - 719 JDBC API
Tutorial and Reference, Third edition, (Fisher, Ellis, Bruce)) that a result
set's own updates need not be visible to it. Obviously we would not like
this to be the default behavior but it is going to take a lot of work and it
would help to have customer feedback that clarified why this behavior should
be changed.
Entering a bug:
Go to http://lab.msdn.microsoft.com/produc...k/default.aspx
Product/Technology:
SQL Server 2005
Category:
JDBC Driver
Make sure to add JDBC SqlServer 2005 to the but title.
Angel Saenz-Badillos [MS] DataWorks
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://weblogs.asp.net/angelsb/
<devansh.dhutia@.gmail.com> wrote in message
news:1128188361.813553.300740@.g47g2000cwa.googlegr oups.com...
>I have a question regarding a certain behavior of updateable
> resultsets. If I update a column using any of the updateXXX methods and
> then try to use the getXXX methods from the same column to see if it
> updated the results locally and not on the server, I get the same old
> value. I have to call updateRow() but that updates the underlying
> database and still gives me the old value until I execute the same
> query again and get a new resultset. Maybe the code below will clarify
> my question more..
> Connection con = null;
> Statement stmt;
> ResultSet rst;
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
> System.out.println("Getting connection.");
> con = DriverManager.getConnection(url);
> System.out.println("Connection successful.");
> String st = "select age,sname,snum FROM student;";
> stmt =
> con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E,ResultSet.CONCUR_UPDATABLE);
> rst = stmt.executeQuery(st);
> rst.last();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> rst.updateInt(1,23);
> rst.updateRow();
> System.out.print(rst.getInt(1)+" ");
> System.out.print(rst.getString(2)+" ");
> System.out.print(rst.getLong(3)+"\n");
> The output is:
> Getting connection.
> Connection successful.
> 25 Edward Baker 578875478
> 25 Edward Baker 578875478
> If I were the run the same code again, I get:
> Getting connection.
> Connection successful.
> 23 Edward Baker 578875478
> 23 Edward Baker 578875478
> Any/all help is appreciated
> Thanks
> Devansh Dhutia
> University of Iowa
>

Beginner Question: How to return list of right-most entries in a cross tab tabl

I currently have a report that contains a crosstab. Each row is a different property and each column is a different time. Several of the entries in the table are blank i.e. not all properties are recorded at all the times.

I would like to be able to turn this report into a list of the most recent values for each property along with the time it was recorded. I would be very grateful if someone could provide an example of a formula that would approximate this. Please let me know if I have not provided enough information.

Many thanks

JonI think you must ckick the time field in crosstab expert then select ascending.