Need help constructing Full Names

Hi,
I have three fields that make up employees' names in the database i.e.
FirstName, MiddleInitial and LastName fields. FirstName and LastName fields
are varchar(30) and varchar(50) respectively. MiddleInitial field is char(1)
.
When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
EmployeeName construct, I get no data because most records have a null value
for MiddleInitial field.
If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
desired.
How can I take data from all three fields and return EmployeeName as single
field in a stored procedure? Even if I make it work, I'll have two spaces
between FirstName and LastName if the employee has no MiddleInitial due to
null value in that field. I'm sure there's a civilized way to do this. I'd
appreciate some pointers here.
Thanks,
SamYou need to use COALESCE, ISNULL or CASE in your SELECT query:
CREATE TABLE Names
(
FirstName nvarchar(20) NOT NULL
,MiddleInitial nchar(1) NULL
,LastName nvarchar(30) NOT NULL
)
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('John', NULL , 'Doe')
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('Alfred', 'E', 'Neuman')
SELECT
FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
AS EmployeeName
FROM Names
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
> Hi,
> I have three fields that make up employees' names in the database i.e.
> FirstName, MiddleInitial and LastName fields. FirstName and LastName field
s
> are varchar(30) and varchar(50) respectively. MiddleInitial field is char(
1).
> When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
> EmployeeName construct, I get no data because most records have a null val
ue
> for MiddleInitial field.
> If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
> desired.
> How can I take data from all three fields and return EmployeeName as singl
e
> field in a stored procedure? Even if I make it work, I'll have two spaces
> between FirstName and LastName if the employee has no MiddleInitial due to
> null value in that field. I'm sure there's a civilized way to do this. I'd
> appreciate some pointers here.
> --
> Thanks,
> Sam|||Great... Thank you very much Tibor.
--
Thanks,
Sam
"Tibor Karaszi" wrote:

> You need to use COALESCE, ISNULL or CASE in your SELECT query:
> CREATE TABLE Names
> (
> FirstName nvarchar(20) NOT NULL
> ,MiddleInitial nchar(1) NULL
> ,LastName nvarchar(30) NOT NULL
> )
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('John', NULL , 'Doe')
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('Alfred', 'E', 'Neuman')
> SELECT
> FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
> AS EmployeeName
> FROM Names
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
>|||Also you might want to have a look at concat_null_yields_null parameter
in BOLsql

Need help comparing tables Query

I have two tables that I need to be compared. The first table <prams>
consists of many fields (DBName, TBName, IName, Other fields) The first
three (DBName, TBName, IName) work together to form the primary key.
The second table <temp> will also contain the same structure.
I need to take the first table <prams> and compare it to the second
table <temp>. If the Row is missing from the second table <temp>, I
need to copy the information form the first table <prams> into third
"Purgatory" table. If the row exists in the first table <prams> I
need to copy the other fields into the second table <prams>.
Once it has gone though and compared all the table entries the <temp>
table will replace the <prams> table.
Thanks
-Matt-Whoops forgot, I am running SQL2005, and would like to keep it SQL not
VB or C#.
Thanks again,|||INSERT INTO [Purgatory]
SELECT t1.[dbname],t1.[tbname],t1.[iname],t1.[other fields]
FROM [Prams] WHERE NOT EXISTS
(SELECT t2.[dbname] FROM [Temp] t2 WHERE t1.[dbname] = t2.[dbname]
AND t1.[tbname] = t2.tbname AND t1.[iname] = t2.[iname])
UPDATE [Temp] --is this what you meant? You repeated the same table name
SET [Other Field1] = t1.[Other Field1]
/* Repeat for other fields that need to be copied */
FROM [Prams] t1 INNER JOIN [Temp] t2 ON
t1.[dbname] = t2.[dbname]
AND t1.[tbname] = t2.tbname
AND t1.[iname] = t2.[iname]
If the temp table replaces the prams table after the update, shouldn't you
be keeping prams in sync with temp instead of the other way around?
"MKruer@.gmail.com" wrote:

> I have two tables that I need to be compared. The first table <prams>
> consists of many fields (DBName, TBName, IName, Other fields) The first
> three (DBName, TBName, IName) work together to form the primary key.
> The second table <temp> will also contain the same structure.
> I need to take the first table <prams> and compare it to the second
> table <temp>. If the Row is missing from the second table <temp>, I
> need to copy the information form the first table <prams> into third
> "Purgatory" table. If the row exists in the first table <prams> I
> need to copy the other fields into the second table <prams>.
> Once it has gone though and compared all the table entries the <temp>
> table will replace the <prams> table.
> Thanks
> -Matt-
>|||Maybe my logic is totally fired but what I am trying to do it rebuild
all the DBs, Tables, and Indexes, and only store those that are on the
system, any DBs, Tables, or Indexes that are no longer there get sent
to a new file.
So I take what was good <prams> compare it to what is known good <temp>
then once everything is processed, <temp> should become the new <prams>
correct?|||Not necessarily, because I am only running this comparison once a day
and I do not know what entities may have changed, but I still want to
know that they changed. So it does not need to be real time.
PS
Thanks you so much for this.

Need help building/reading C# SQL Array

I'm stuck and uinder a bit of a time crunch.

I have 5 fields I want to get out of a sql database using a function that I'm writing. I figure it sounds like an array. basically I want to make an array, and fill it up with the results of a sql select, then read the array.

This is what I have so far....

String TempHRAcctCode, TempJobDescription, TempHourlyRate, TempEmplID;
Array TempArray;
TempJobDescription = DDDept.SelectedItem.Text; (to get KeY Value)

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCONN"].ToString());
connection.Open();
SqlCommand command = new SqlCommand("Select HRAcctCode, HourlyRate , EmplID, ... FROM TimeMyProfile WHERE JobDescription = " + TempJobDescription + " ", connection);
SqlDataReader TempDataReader = command.ExecuteReader;
while TempDataReader.Read
(
... OK I GIVE UP!

Thanks in advance

why you want read as an array ?? If you really want to read these data later then load into aDataSetrather than reading from Data Reader into an array and then later read again the array...

using (SqlDataAdapter da = new SqlDataAdapter(command))
{
DataSet ds = new DataSet();

// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);

connection.Close();

// Return the dataset
return ds;
}

And you can read the data set like

foreach (DataRow dr in ds.Tables[0] .Rows)

{

string myVar = dr["HourlyRate"].ToString()

}

|||

Funny thing is, as I was typing th equestion I was asking myself the same thing, WHY use an array!
Excellent suggestion, it works GREAAT!

Thanks

Need Assistance Creating a stored procedure

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@.sccnet.com

Thanks,
ShawnHi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.

Regards,

Tyler
"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> Hi,
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
> Please email me at shawnf@.sccnet.com
> Thanks,
> Shawn|||Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn

"Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@.news.datasync.com...
> Hi Shawn,
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
> Regards,
> Tyler
> "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > Hi,
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> > Please email me at shawnf@.sccnet.com
> > Thanks,
> > Shawn|||It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@.newsreader.goldenga te.net...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >|||Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@.newsreader.goldengate.ne t>...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >

Need advice on searching text fields

I have a table with 2 million + rows. I need to allow the user to do a quer
y
such as is shown below. I have full text search enabled on this database,
and have built a text search catalog for it. The problem is, that I keep
getting timeouts on the query. Granted, I can increase the timout value to
a
value that works, but I need this to come back fairly quick. I need advice
on how to set up this application. Think of eBay. You can search on any
term. They have to have millions of rows in their database also. How do
they return results so quick? They have to be doing very similar query logi
c?
Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
AND ([Field1] + [Field2] Like '%term2%')Does the LIKE operator utilize the full-text search engine? Perhaps you be
using the CONTAINS and FREETEXT functions instead.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>I have a table with 2 million + rows. I need to allow the user to do a
>query
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value
> to a
> value that works, but I need this to come back fairly quick. I need
> advice
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query
> logic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like
> '%term2%')|||Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
I'll try the other two verbs and see what happens.
"JT" wrote:

> Does the LIKE operator utilize the full-text search engine? Perhaps you be
> using the CONTAINS and FREETEXT functions instead.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>
>|||Perhaps you're not taking advantage of the full text search with the
LIKE and the concatenated columns.
Try using FREETEXT instead of LIKE
... where ( FREETEXT (Field1, 'term1') or FREETEXT(Field2, 'term1') )
and ( FREETEXT (Field1, 'term2') or FREETEXT(Field2, 'term2') )
Brian Kitt wrote:
> I have a table with 2 million + rows. I need to allow the user to do a qu
ery
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value t
o a
> value that works, but I need this to come back fairly quick. I need advic
e
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query lo
gic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like '%term2%')[/colo
r]|||Even with the full-text search functioning, you may not get the crisp
response time you are looking for. After all, eBay has more processing power
at their disposal than 95% of us do.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
I can assure that T-SQL LIKE does NOT use the same technology as the
Full-Text Search (FTS) predicates of CONTAINS or FREETEXT !!
-- John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
While you may not have the processing power of eBay, you can get good
performance out of FTS with a bit of tuning on both the FT Indexing (Change
Tracking with Update Index in Background) and with using CONTAINSTABLE or
FREETEXTTABLE with the Top_N_Rank parameter and limiting your results to the
top 2000 by RANK, for example:
SELECT TOP 200 T.* FROM TableWithFTColumn as T,
CONTAINSTABLE(TableWIthFTColumn,*,'John'
,300) as CT
WHERE T.key=CT.key AND T.a > 5
ORDER BY CT.rank
Where 300 is the Top_N_by_RANK value. See KB artilce 240833 "FIX: Full-Text
Search Performance Improved via Support for TOP" at
http://support.microsoft.com//defau...kb;EN-US;240833 for more
details. Also, review SQL Server 200 Books Online (BOL) title "Full-text
Search Recommendations" for more tips!
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"JT" <someone@.microsoft.com> wrote in message
news:O2tY0S8sFHA.1940@.TK2MSFTNGP14.phx.gbl...
> Even with the full-text search functioning, you may not get the crisp
> response time you are looking for. After all, eBay has more processing
> power at their disposal than 95% of us do.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
>

Need advice on Designing a VLDB OLTP Database

Hi,
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>

Need a quick hand using ORDER BY with two fields in the same table.

I have a problem with ordering and I am hoping that someone is able to help.

In my table I have two fields, "requestdate" and "sentdate", and when I display the records I would like to sort by BOTH fields. I want to do this so that the full query is in order by date.

I tried:

ORDER BY requestdate, sentdate DESC

But obviously all that does is order by requestdate (which is NULL or a date) and than it will order by sentdate.

Can somebody tell me how to order by both as if they were the same field?

Thanks!What exactly do you mean? Do you want to sort by both dates, both DESC? Then

ORDER BY requestdate DESC,sentdate DESC|||Oops thanks so much Douglas, I must be half asleep this morning.

I missed the first DESC keyword which is why it was not working properly. Thanks again so much!

NEAR operator

What is the specific function of NEAR?
I'm indexing across 3 text fields and expect hits for 'white NEAR black' and
only getting a hit when the two words are in the same field, but not if they
are spread across two of the fields.
How NEAR does the other word have to be?
Nearness is reflected in rank. The closer together two words are the higher
the rank everything else being equal.
Nearness is calculated on a per column basis, it cannot look across columns.
FreeText factors nearness into rank (everything else being equal) and can
look across columns. FreeText ignores the near operator however, as it is
calcuated in the rank.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bill D" <delaneWC-nospam@.mail.slh.wisc.edu> wrote in message
news:e2kX0QboEHA.3488@.TK2MSFTNGP12.phx.gbl...
> What is the specific function of NEAR?
> I'm indexing across 3 text fields and expect hits for 'white NEAR black'
and
> only getting a hit when the two words are in the same field, but not if
they
> are spread across two of the fields.
> How NEAR does the other word have to be?
>
|||Very helpful. I had found that using Freetext instead of Contains was
getting me more results. Thanks Hilary.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Op2bxxboEHA.1576@.TK2MSFTNGP12.phx.gbl...
> Nearness is reflected in rank. The closer together two words are the
higher
> the rank everything else being equal.
> Nearness is calculated on a per column basis, it cannot look across
columns.
> FreeText factors nearness into rank (everything else being equal) and can
> look across columns. FreeText ignores the near operator however, as it is
> calcuated in the rank.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Bill D" <delaneWC-nospam@.mail.slh.wisc.edu> wrote in message
> news:e2kX0QboEHA.3488@.TK2MSFTNGP12.phx.gbl...
> and
> they
>