How can I convert a date and an amount in my select statement

I need to convert a date like 08/1/2009 to 0809

I also need to show currency as 100.00 and not 100.0000

How can I do these in a select statement?

SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment

I appreciate any help!

hi Jackxxx,

can you try this

SELECT convert(varchar,datepart(dd,getdate()))+convert(varchar,datepart(yy,getdate())) AS ExpirationDate, convert(decimal(10,2), 2323.2422)

thanks,

Satish.

|||

I tried the expiration date and the date was 7/1/2009 and your statement returned 172007.

Also I goofed on the other the field name is AmountPaid that I need to show 100.00 for.

|||

hi Jackxxx,

what i gave was an example you need to modify your actuall query accordingly like i've put getdate() so you need to put your datetime field in there similary amount field also.

thanks,

satish,

|||

It's almost perfect, the date still shows all for digits of the year. Is there a way to only show the last two digits? Like 09 for 2009

I very much appreciate your help!

|||

hi jackxxx,

i tried alot but its giving 4 digits atlast i had to cheat, use

select right(datepart(yy,getdate()),2)

hope it works now.

regards,

satish.

How can I connect to a remote sql server using windows authentication?

It is simple

1- I open the Sql Server 2005 Management Studio

2- I select Windows Authentication from the drop down.

3- I cannot write the user name and password, it chooses the default once, the one I am logged in with!

But I am in a virtual machine outside the domain controller, I can access shares on machines that are on the domain controller, thanks to the file sharing of windows, but I cannot login to sql server, thanks to a meaningless restriction on that dialog :-)

Now, how can I still use the Windows Authentication and login, how can I avoid the sql server authentication?

If you are outside domain, then you should use SQL Authentication.|||

Ok, then why the text boxes for the user name and password are still there if I choose the Windows authentication, and the text boxes have the user name already filled in, and all are disabled.

What is the point of having those there? In my case, I was trying to find a way to enable them from the settings; I guess just a false hope.

And why cannot I use the windows authentication? NTFS does allow me to do it and access the file system from outside the domain using windows authentication against the domain, what does make sql server more special?

|||

The reason for the textbox is just to let you know which Windows account is being used to connect to SQL Server using Windows authentication. To access SQL Server using SQL authentication, click the Authentication drop-down to see the SQL Server Authentication option. You'll see the User name and Password textboxes enabled.

If you want to use Windows authentication, the easiest way is to join your SQL Server to your domain.

|||

Thank you for the help, but I know how to use the SQL Server authentication, and the SQL Server is the development server and it is on the domain.

My virtual machine is the development machine, it is a virtual machine and it cannot join the domain, it must stay as it is, the real machine is on the domain, but the virtual machine that I am trying to use is not.

From the virtual machine I can do lots of things, including accessing the file system and the intranet sites on the domain, using the domain authentication box, or cached credentials, but I cannot do that with the SQL Server.

|||

if you want an nt authentication

then you must promote your virtual machine to a domain controller

How can I combine three fields together of dif datatypes

I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them?

(Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit)

Select Item_Description&' '& Item_Cost&' '& Is_ActiveAsDescriptionFROM tblItemList

I tried Casting this but same incompatable message.

Select Item_Description&' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) AsDescriptionFROM tblItemList

What I'm hoping to end up with is:

Brake Pedal, $36.00, True

Did you try using the + operator after casting?

Select
Item_Description + ' ' + CAST(Item_Cost AS NVARCHAR) +' '+ CAST(Is_Active AS NVARCHAR) As Description
FROM
tblItemList

What are the datatypes of the above fields?

|||

Ok, Scott, I tried the + again and it did work, however, can you point me in the direction of how to format the money to $36.00 and the bit to True or False. I would like to end up with:

Brake Pedal, $36.00, True

|||

SELECT
Item_Description+', $'+Cast(Item_Costasnvarchar)+', '+ dbo.ufnGetBoolean(Is_Active)
FROM
tblItemList

The ufnGetBoolean function is:

CREATEFUNCTION [dbo].[ufnGetBoolean](@.BitValue [bit])
RETURNSvarchar(5)
AS
-- Returns a string value based on the bit value passed
BEGIN
DECLARE @.retvarchar(5);
SET @.ret='false'

IF(@.BitValue= 1)
SET @.ret='true'

RETURN @.ret

END;

Let me know if that works.

How can I combine 2 SELECT statements into 1 ?

Please consider the following SELECT statements; is it possible to combine
them into a sole SELECT statement returning 4 columns ? Thank you.
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
--
Gilbert.HI
Use UNION or UNION ALL
Cheers
vinu
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Sorry but, as far as I know, with UNION I will get more lines with always 3
columns. This is not why I need. I need the same number of lines
(approximatively) with 4 columns.
--
Gilbert.
"vinu" wrote:

> HI
> Use UNION or UNION ALL
> Cheers
> vinu
>
> "Gilbert" <gilbert@.nospam.nospam> wrote in message
> news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
>
>|||Please post DDL, sample data and expected results.
You might also consider joining the two queries as derived tables on common
columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fool
you into thinking I have any idea what you really want. Stop us guessing and
explain your goals.
ML
http://milambda.blogspot.com/|||I thought that giving the SELECT statements was enough details; sorry. I
intend to do monthly statistics on a table storing user requests. The first
query gives the number of requests per month. The second query gives the
average delay of the answer per month, for all requests that are closed (thi
s
is why there is a WHERE clause). If I execute 2 independent SELECT statement
s
I receive 2 tables with 3 columns, each line displaying the value for a
specific month. What I would like to do is to execute only 1 SELECT, giving
me 1 table of 4 columns (year, month, number of requests, and average delay)
,
each line displaying the values for a specific month. Thank you for your hel
p.
--
Gilbert.
"ML" wrote:

> Please post DDL, sample data and expected results.
> You might also consider joining the two queries as derived tables on commo
n
> columns (e.g. ItvNumAnee and ItvNumMois), but don't let this suggestion fo
ol
> you into thinking I have any idea what you really want. Stop us guessing a
nd
> explain your goals.
>
> ML
> --
> http://milambda.blogspot.com/|||Ok, I see. But we still need to understand how the data in the two queries
can be joined to form one valid result-set.
That's why we need to see the DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||OK. Here is the DDL (simplified by hand to remove unuseful columns and
constraints) :
CREATE TABLE [InterventionDI] (
[NumIntervention] [int] IDENTITY (1, 1) NOT NULL ,
[DhEnregistrementIntervention] [datetime] NOT NULL CONSTRAINT
[DF_InterventionDI_DhEnregistrementInter
vention] DEFAULT (getdate()),
[DhClotureIntervention] [datetime] NULL ,
[CodEtatIntervention] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL,
CONSTRAINT [PK_InterventionDI] PRIMARY KEY NONCLUSTERED
(
[NumIntervention]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
Here is a set on 10 records (INSERT created by hand) :
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (18, '13/09/2002 09:52:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (19, '13/09/2002 09:53:18', '21/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (20, '14/09/2002 09:54:18', '20/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (21, '14/09/2002 09:55:18', '15/09/2002 16:53:57', 'O')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (22, '14/09/2002 09:56:18', '15/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (23, '14/09/2002 09:57:18', '18/09/2002 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (24, '13/10/2002 09:58:18', '20/09/2003 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (25, '13/10/2002 09:59:18', '20/09/2004 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (26, '13/01/2003 09:12:18', '20/09/2005 16:53:57', 'F')
INSERT INTO dbo.InterventionDI (NumIntervention,
DhEnregistrementIntervention, DhClotureIntervention, CodEtatIntervention)
VALUES (27, '13/01/2003 09:22:18', '20/09/2006 16:53:57', 'F')
Thank you.
--
Gilbert.
"ML" wrote:

> Ok, I see. But we still need to understand how the data in the two queries
> can be joined to form one valid result-set.
> That's why we need to see the DDL and sample data:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
> --
> http://milambda.blogspot.com/|||Based on what the two queries have in common this might give the correct
result:
select Demande.Anee
,Demande.Mois
,Demande.ItvQteDemande
,Duree.ItvDuree
from (
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Demande (Anee, Mois, ItvQteDemande)
inner join (
SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
, MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
, ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0) AS ItvDuree
FROM dbo.InterventionDI
WHERE (CodEtatIntervention = 'F')
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
) Duree (Anee, Mois, ItvDuree)
on (Duree.Anee = Demande.Anee)
and (Duree.Mois = Demande.Mois)
order by Demande.Anee
,Demande.Mois
Please, validate this against a sufficient amount of cases before using it
in production.
ML
http://milambda.blogspot.com/|||UNTESTED
SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
, MONTH(DhEnregistrementIntervention) AS ItvNumMois
, COUNT(NumIntervention) AS ItvQteDemande
, (
case when CodEtatIntervention = 'F' then
ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
DhClotureIntervention)), 0)
ELSE NULL END
)AS ItvDuree
FROM dbo.InterventionDI
GROUP BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention)
ORDER BY YEAR(DhEnregistrementIntervention),
MONTH(DhEnregistrementIntervention);
"Gilbert" <gilbert@.nospam.nospam> wrote in message
news:3B7C228D-3913-49EF-85E3-776520901701@.microsoft.com...
> Please consider the following SELECT statements; is it possible to combine
> them into a sole SELECT statement returning 4 columns ? Thank you.
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ORDER BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention);
> --
> Gilbert.|||Wonderful ! Thank you for having teached me something new.
--
Gilbert.
"ML" wrote:

> Based on what the two queries have in common this might give the correct
> result:
> select Demande.Anee
> ,Demande.Mois
> ,Demande.ItvQteDemande
> ,Duree.ItvDuree
> from (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvNumAnnee
> , MONTH(DhEnregistrementIntervention) AS ItvNumMois
> , COUNT(NumIntervention) AS ItvQteDemande
> FROM dbo.InterventionDI
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Demande (Anee, Mois, ItvQteDemande)
> inner join (
> SELECT YEAR(DhEnregistrementIntervention) AS ItvAnneeEnregistrement
> , MONTH(DhEnregistrementIntervention) AS ItvMoisEnregistrement
> , ROUND(AVG(DATEDIFF(DAY, DhEnregistrementIntervention,
> DhClotureIntervention)), 0) AS ItvDuree
> FROM dbo.InterventionDI
> WHERE (CodEtatIntervention = 'F')
> GROUP BY YEAR(DhEnregistrementIntervention),
> MONTH(DhEnregistrementIntervention)
> ) Duree (Anee, Mois, ItvDuree)
> on (Duree.Anee = Demande.Anee)
> and (Duree.Mois = Demande.Mois)
> order by Demande.Anee
> ,Demande.Mois
> Please, validate this against a sufficient amount of cases before using it
> in production.
>
> ML
> --
> http://milambda.blogspot.com/sql

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many

records from or into this table in less than one second.

i can't buy a very expensive hardware(Server) for this SQL Server 2005

but i can buy many medium price hardwares(Servers) for this SQL Server

2005.

how can i distribute or cluster this table between many hardwares(Servers)?

note: i have few users (maximum 5 users) for my database but i have a

very large table and Sql server 2005 server need to respond to this

users in less than 1 second.

i want to distribute this huge table in seperated hardwares. becuase i

can't buy a very expensive hardware from my server but i can buy many

medium price hardware for my server.

note: i need this: when a user run a select query on this huge table

his/her request distribute between many hardwares not one hardware.moving to availablility folder...|||

One option for you is to use peer-to-peer replication as a way of partitioning the data between various servers and having the updates on each server reflected on all others. You'll need some mid-tier logic to direct queries to the appropriate server though.

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

Thanks

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many

records from or into this table in less than one second.

i can't buy a very expensive hardware(Server) for this SQL Server 2005

but i can buy many medium price hardwares(Servers) for this SQL Server

2005.

how can i distribute or cluster this table between many hardwares(Servers)?

note: i have few users (maximum 5 users) for my database but i have a

very large table and Sql server 2005 server need to respond to this

users in less than 1 second.

i want to distribute this huge table in seperated hardwares. becuase i

can't buy a very expensive hardware from my server but i can buy many

medium price hardware for my server.

note: i need this: when a user run a select query on this huge table

his/her request distribute between many hardwares not one hardware.moving to availablility folder...|||

One option for you is to use peer-to-peer replication as a way of partitioning the data between various servers and having the updates on each server reflected on all others. You'll need some mid-tier logic to direct queries to the appropriate server though.

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

Thanks

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
Clusters are for high availability (HA) - not load balancing. You should
look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
your queries always include the partitioning column in the WHERE clause, you
should be able to realize a performance benefit without necessarily going to
more servers. In the event that partitioned tables, or local partitioned
views, don't do it, then distributed partitioned views -0 where you do use
multiple servers - can be the solution.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
|||Table partitioning allows me to spread the load across disks. However, all
data will still be managed by a single server. my performance bottleneck is
not only Disks resources but also CPU or memory, this method can resolve my
Disk Performance bottleneck but it can't resolve my CPU or memory Performance
bottleneck.
please help me to find a solution for all resource include (CPU,Ram,Disk,...).
"Tom Moreau" wrote:

> Clusters are for high availability (HA) - not load balancing. You should
> look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
> your queries always include the partitioning column in the WHERE clause, you
> should be able to realize a performance benefit without necessarily going to
> more servers. In the event that partitioned tables, or local partitioned
> views, don't do it, then distributed partitioned views -0 where you do use
> multiple servers - can be the solution.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
> i have a table with 10,000,000,000 records and i need Select and Insert many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
>
|||Well, without seeing your entire system, that would be difficult.
If you have a disk I/O bottleneck, it could be that you're memory starved.
It could also be that your RAID array has very few spindles.
What numbers have you been getting from Perf Mon?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
Table partitioning allows me to spread the load across disks. However, all
data will still be managed by a single server. my performance bottleneck is
not only Disks resources but also CPU or memory, this method can resolve my
Disk Performance bottleneck but it can't resolve my CPU or memory
Performance
bottleneck.
please help me to find a solution for all resource include
(CPU,Ram,Disk,...).
"Tom Moreau" wrote:

> Clusters are for high availability (HA) - not load balancing. You should
> look at partitioned tables (SQL 2005) or partitioned views (SQL 2000). If
> your queries always include the partitioning column in the WHERE clause,
> you
> should be able to realize a performance benefit without necessarily going
> to
> more servers. In the event that partitioned tables, or local partitioned
> views, don't do it, then distributed partitioned views -0 where you do use
> multiple servers - can be the solution.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:0366D5B7-B160-417E-8037-5269258365FF@.microsoft.com...
> i have a table with 10,000,000,000 records and i need Select and Insert
> many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many
> hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
>
|||i think it is not difficult. i want to distribute my big table between many
servers(include CPU,Ram,Disks) not one server.
i want to use a method that is is distributable. for example: when my table
being larger i could add some new servers to my system to increase my
performance without changing current servers.
note: i only want increase my performance only by add a complete new servers
(include CPU,Ram,...) to currenct system
"Tom Moreau" wrote:

> Well, without seeing your entire system, that would be difficult.
> If you have a disk I/O bottleneck, it could be that you're memory starved.
> It could also be that your RAID array has very few spindles.
> What numbers have you been getting from Perf Mon?
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
> Table partitioning allows me to spread the load across disks. However, all
> data will still be managed by a single server. my performance bottleneck is
> not only Disks resources but also CPU or memory, this method can resolve my
> Disk Performance bottleneck but it can't resolve my CPU or memory
> Performance
> bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "Tom Moreau" wrote:
>
>
|||As Tom mentioned, look in Books Online for "Distributed Partitioned Views".
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...[vbcol=seagreen]
>i think it is not difficult. i want to distribute my big table between many
> servers(include CPU,Ram,Disks) not one server.
> i want to use a method that is is distributable. for example: when my
> table
> being larger i could add some new servers to my system to increase my
> performance without changing current servers.
> note: i only want increase my performance only by add a complete new
> servers
> (include CPU,Ram,...) to currenct system
>
> "Tom Moreau" wrote:
|||That's the definition of distributed partitioned views. If you have a copy
of "Advanced Transact-SQL for SQL Server 2000", Chapter 13 gives you details
on how to do it. Using DPV's, Microsoft was able to set a new tpmC
benchmark soon after going RTM with SQL Server 2000.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...
i think it is not difficult. i want to distribute my big table between many
servers(include CPU,Ram,Disks) not one server.
i want to use a method that is is distributable. for example: when my table
being larger i could add some new servers to my system to increase my
performance without changing current servers.
note: i only want increase my performance only by add a complete new servers
(include CPU,Ram,...) to currenct system
"Tom Moreau" wrote:

> Well, without seeing your entire system, that would be difficult.
> If you have a disk I/O bottleneck, it could be that you're memory starved.
> It could also be that your RAID array has very few spindles.
> What numbers have you been getting from Perf Mon?
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
> news:D066863B-BAB8-48CE-8D1B-C3BBC451E505@.microsoft.com...
> Table partitioning allows me to spread the load across disks. However, all
> data will still be managed by a single server. my performance bottleneck
> is
> not only Disks resources but also CPU or memory, this method can resolve
> my
> Disk Performance bottleneck but it can't resolve my CPU or memory
> Performance
> bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "Tom Moreau" wrote:
>
>
|||I think you are not considering the full ramifications of partitioning that
Tom has recommended, even on a single hardware installation.
It is not just a disk resource issue that partitioning attempts to resolve.
Unless you expect each of the 5 users to query the entire 10 Billion rows on
each pass, partitioning will limit the user to the specified data needed at
for any individual pass if the partitioning function is chosen correctly.
I suspect this table is large because the data is time sensitive, that is,
historical?
If so, if you had a dedicated table (or partition) for each month, week,
day, whatever the granularity is, and then one only needed data for that
particular date range, they would then only access a single table
(partition) instead of the entire data set.
If this data represents years of monthly, or daily, data, then each query
would only have to deal with a handful of tables, each only a few hundred
thousand or so records. Each query then becomes 10, 100, maybe even 1,000
or more times quicker.
Sincerely,
Anthony Thomas

"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:A1D3E5D7-2077-443D-9F6E-D6056A4BCA83@.microsoft.com...
> i think it is not difficult. i want to distribute my big table between
many
> servers(include CPU,Ram,Disks) not one server.
> i want to use a method that is is distributable. for example: when my
table
> being larger i could add some new servers to my system to increase my
> performance without changing current servers.
> note: i only want increase my performance only by add a complete new
servers[vbcol=seagreen]
> (include CPU,Ram,...) to currenct system
>
> "Tom Moreau" wrote:
starved.[vbcol=seagreen]
all[vbcol=seagreen]
is[vbcol=seagreen]
my[vbcol=seagreen]
should[vbcol=seagreen]
If[vbcol=seagreen]
clause,[vbcol=seagreen]
going[vbcol=seagreen]
partitioned[vbcol=seagreen]
use[vbcol=seagreen]
Insert[vbcol=seagreen]

How can i cluster sql 2005 for load balancing?

i have a table with 10,000,000,000 records and i need Select and Insert many
records from or into this table in less than one second.
i can't buy a very expensive hardware(Server) for this SQL Server 2005
but i can buy many medium price hardwares(Servers) for this SQL Server
2005.
how can i distribute or cluster this table between many hardwares(Servers)?
note: i have few users (maximum 5 users) for my database but i have a
very large table and Sql server 2005 server need to respond to this
users in less than 1 second.
i want to distribute this huge table in seperated hardwares. becuase i
can't buy a very expensive hardware from my server but i can buy many
medium price hardware for my server.
note: i need this: when a user run a select query on this huge table
his/her request distribute between many hardwares not one hardware.
I would look at distributed partition views for something like this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:C90FB31F-57D4-4E3D-AA02-001133C904F3@.microsoft.com...
>i have a table with 10,000,000,000 records and i need Select and Insert
>many
> records from or into this table in less than one second.
> i can't buy a very expensive hardware(Server) for this SQL Server 2005
> but i can buy many medium price hardwares(Servers) for this SQL Server
> 2005.
> how can i distribute or cluster this table between many
> hardwares(Servers)?
> note: i have few users (maximum 5 users) for my database but i have a
> very large table and Sql server 2005 server need to respond to this
> users in less than 1 second.
> i want to distribute this huge table in seperated hardwares. becuase i
> can't buy a very expensive hardware from my server but i can buy many
> medium price hardware for my server.
> note: i need this: when a user run a select query on this huge table
> his/her request distribute between many hardwares not one hardware.
|||i need a method like partitioned tables in SQL 2005 but Table
partitioning only allows me to spread the load across disks. However,
all data will still be managed by a single server. my performance
bottleneck is not only Disks resources but also CPU or memory, this
method can resolve my Disk Performance bottleneck but it can't resolve
my CPU or memory Performance bottleneck.
please help me to find a solution for all resource include (CPU,Ram,Disk,...).
"abssoft2000" wrote:
|||As I mentioned previously it is called distributed partition views. Look it
up in BOL.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"abssoft2000" <abssoft2000@.discussions.microsoft.com> wrote in message
news:219F0D2E-B2AF-42EB-A6C2-F186956F6341@.microsoft.com...
>i need a method like partitioned tables in SQL 2005 but Table
> partitioning only allows me to spread the load across disks. However,
> all data will still be managed by a single server. my performance
> bottleneck is not only Disks resources but also CPU or memory, this
> method can resolve my Disk Performance bottleneck but it can't resolve
> my CPU or memory Performance bottleneck.
> please help me to find a solution for all resource include
> (CPU,Ram,Disk,...).
> "abssoft2000" wrote:

How can I check for Null or Empty in an Insert/Select Statement - example in Access

The following sample of code in access is what i need to be able to do in
MSSQL 2000.
Can i use iif statements like this in the select part of the insert if so i
cannot get this to work in MSSQL
iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
INSERT INTO dbo_ContactAddress (ContactID, AddressTypeCode, AddressLine1,
AddressLine2, AddressLine3, AddressLine4, AddressLine5,AddressLine6,
CountryCode, Town, PostalCode, State, DoNotMarket, DoNotSell )
SELECT NBCDON.ID+100100000, 1,iif(IsNull(NBCDON.Unit), "",NBCDON.Unit+"/") +
NBCDON.Number +iif(IsNull(NBCDON.suf), "",NBCDON.suf)+ " " + NBCDON.Street
," ", " ", " ", " ", " ",1, iif(IsNull(NBCDON.SubTown),
"?",NBCDON.SubTown) ,iif(IsNull(NBCDON.CODE),"9999",NBCDON.Code),
NBCDON.State, 0, 0
FROM NBCDON
WHERE not IsEmpty(NBCDON.Street)
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003I resolved this by Using the IsNul( field, value if null) Expression.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003sql

How can I check for Null or Empty in an Insert/Select Statement

Is there a way in the following Query to check if the _vocon.COMPANY is
null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
statement 2) as each record is processed. I would like to use something like
IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id +
10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
If the company name is null
INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
_vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
_vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
'01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003Hi
You don't give the version of SQLServer, it is also better to post DDL
(Create table statements etc...), example data (as Insert statements) to
avoid ambiguities.
The behaviour of concatenating with null can be set with the
CONCAT_NULL_YIELDS_NULL option see:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_set-set_2z8s.htm
To update your values you could use:
UPDATE _vocon
SET Company = Id + 10200000
WHERE Company IS NULL
OR LEN(RTRIM(Company)) = 0
John
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uqveIhNzDHA.2568@.TK2MSFTNGP09.phx.gbl...
> Is there a way in the following Query to check if the _vocon.COMPANY is
> null/empty and if so replace _vocon.Id + 10200000 with null (as shown in
> statement 2) as each record is processed. I would like to use something
like
> IIF( IsNull(_vocon.COMPANY), Null, _vocon.Id + 10200000)
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, _vocon.Id
+
> 10200000, 1, '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM
_vocon
> If the company name is null
> INSERT INTO Contact (ContactID, ContactCode, ContactFormat,Title,
> FirstName, MiddleName, LastName, notes, CompanyName, Position, CompanyID,
> ContactOwner, CreateDate, CreatedBy, LastChangeDate, LastChangedBy,
> LanguageCode, Sex, CurrentBalance, Flag19 ) SELECT _vocon.Id + 10100000,
> _vocon.Id + 10100000, 'I', _vocon.TITLE, _vocon.FIRSTNAME,
> _vocon.OTHERNAMES, _vocon.LASTNAME, NULL, _vocon.COMPANY, NULL, NULL, 1,
> '01/08/2003', NULL, '01/08/2003', 0, 1, 'U', 0, 1 FROM _vocon
> Regards
> Jeff
>
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.554 / Virus Database: 346 - Release Date: 20/12/2003
>

How can I bind to an SqlDataSource from Code

My code behind file builds a select statement and I would like to fill an SqlDataSource control with it. Can some show me an example of how I might do that? Maybe something like this?

Me.SqlDataSourceSearchResult.ConnectionString ="ConnectStr"Me.SqlDataSourceSearchResult.SelectCommand ="SelectStatement"

gvSearchResult.DataSource =

Me.SqlDataSourceSearchResult

gvSearchResult.DataBind()

Hello Jackxxx,

If you are going to be doing this in code-behind, I would not bother with the SqlDataSource control. I would bind the data directly to the destination control as follows: -

Dim conn As New SqlConnection("YourConnectionString")
Dim command As New SqlCommand("SELECT * FROM YourTable", conn)
Dim da As New SqlDataAdapter(command)
Dim tblData As New DataTable
da.Fill(tblData)
conn.close()

gvSearchResult.DataSource = tblData
gvSearchResult.DataBind()

Also add the following 2 lines at the top of the code behind if they are not there already: -

Imports System.Data.SqlClient
Imports System.Data

Kind regards

Scotty

|||

Scotty,

I was trying to use the sqldatasource so that I could some how use its built in sort functionality.

|||

Hello Jackxxx,

I see now. In that case, do the following after populating tblData: -

Dim dvData as new DataView(tblData)

dvData.Sort = "MyField DESC" ' you can also apply filtering on a DataView

mygrid.DataSource = dvData ' binds to the DataView, not the DataTable

mygrid.DataBind()

Kind regards

Scotty

|||

I'm really hoping to find a way to bind to the SqlDataSource so that I can take advantage of sorting and paging for multiple fields without all the extra coding.

There must be a way.

|||

Hi Jack,

Suppose your SqlDataSource returns a DataSet, you can

mygrid.DataSource = sqlDatasource.Select()
mygrid.DataBind()

However, if you need to take advantage of sorting and paging, I suggest you bind with the designer.

How can I Add 02.45 hour to my Date

StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
now.
Set @.StartDate = '1899-12-30 02:15:00.000'
Select GetDate() now, GetDate() + @.StartDate as added
-- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
Thanks
Murat BUDAKYou should specify to what you want to add it to, SQL's date is broken down
into segments, year, month day etc...
Have a look at the dateadd function
Untested :
select dateadd(hour,2.15,getdate())
You would say, dateadd(hour,@.targetdate,2.15) I don't know if the decimal
might bugger up your solution.
Personally I would convert the amount of time in hours to seconds and then
use dateadd seconds to add the seconds to my current date
"Murat BUDAK" wrote:

> StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
> now.
> Set @.StartDate = '1899-12-30 02:15:00.000'
> Select GetDate() now, GetDate() + @.StartDate as added
> -- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
> Thanks
> Murat BUDAK
>
>|||On Tue, 8 Feb 2005 14:15:10 +0200, Murat BUDAK wrote:

>StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
>now.
>Set @.StartDate = '1899-12-30 02:15:00.000'
>Select GetDate() now, GetDate() + @.StartDate as added
>-- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
>Thanks
>Murat BUDAK
>
Hi Murat,
I don't recommend it, but if you really insist on using the + operator
between two datetime variables to add an amount of time, then you need to
get at least your base date and time right.
Run this in QA
SELECT CAST ('02:15:00.000' AS datetime)
to see why getdate() + '1899-12-30 02:15:00.000' won't result in "now + 2
1/4 hour".
The recommended method to add time to a datetime value is to use DATEADD:
SELECT DATEADD (minute, (60*2) + 15, getdate())
or
SELECT DATEADD (hour, 2, DATEADD (minute, 15, getdate()))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Feb 2005 04:29:02 -0800, Mal .mullerjannie wrote:
(snip)
>Untested :
>select dateadd(hour,2.15,getdate())
Hi Mal,
DATEADD takes an integer as second parameter, so the fractional part
(0.15) will be discarded.
And even if DATEADD would take fractions, this would still be wrong, as
two hours and fifteen minutes equals 2.25 hours, not 2.15 hours.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How can I access dbase?

Hi,

I want to access dbase database, can anyone help?

I try :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\Account\;Extended Properties=DBASE III;',
TRANS.DBF)

and I got the following message :

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Thanks and I really appreciate any help
joe

make sure you have the proper MDAC installed:

http://msdn.microsoft.com/data/ref/mdac/downloads/

|||

Hi Dman1

How can I check if I have the correct MDAC installed, and what will installing this do?

Thanks

|||

MDAC is Microsoft Data Access Components and it is a package of software files that provides connectivity (communication to and from ) odbc compliant data sources. As far as checking the installation see the following:

MDAC Utility: Component Checker

http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9&displaylang=en

How can I access dbase?

Hi,

I want to access dbase database, can anyone help?

I try :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\Account\;Extended Properties=DBASE III;',
TRANS.DBF)

and I got the following message :

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Thanks and I really appreciate any help
joe

make sure you have the proper MDAC installed:

http://msdn.microsoft.com/data/ref/mdac/downloads/

|||

Hi Dman1

How can I check if I have the correct MDAC installed, and what will installing this do?

Thanks

|||

MDAC is Microsoft Data Access Components and it is a package of software files that provides connectivity (communication to and from ) odbc compliant data sources. As far as checking the installation see the following:

MDAC Utility: Component Checker

http://www.microsoft.com/downloads/details.aspx?FamilyId=8F0A8DF6-4A21-4B43-BF53-14332EF092C9&displaylang=en

How can change retrieved data in storedProcedure

Hello,

I have a small problem,

I have table:

data
---
id | er|
----
1 | 4|
2| 5|

I would like to retrevied this data, which is simply easy
select * from data,

but at the same time i would lket to change er values to strings : 4= OK, 5=BAD , that the query would return :

data
---
id | er|
----
1 | OK|
2| BAD|

but database would stay unchanged...

any ideas? is it possible at all?Question moved to SQL Server forum|||use CASE statment for the purpose.|||Try this:

select id,
case when er = 4 then 'OK'
when er = 5 then 'BAD'
else ''
End ER
from data

How can a variable used in SQL select command

select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.You can, but you are missing some basic insights here ...

You can do this like this:

string sql = "Select * from TABLE where user = '" + name + "'"

OR use a stringbuilder of so ...

If you are using SQL Server or any decent DBMS, try using stored procedures instead|||Thank you very much!

How are Execution Paths built?

Lets say you create a view.

then you select from that view for the first time. with no where clause.

Does it build a different execution path than it does if you select from that view with a where clause?

Hi,

Dynamic SQL statements are parameterized if possible (simple where ID = 5 might be cached as ID = ?) or the exact query text is used as a reference. So querying against your view with different criteria will generate different query plans.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Think of a view as basically a "macro" which inserts the view's select statement into your code at execution time. The execution plan is created when you use the view.

So, yes, the execution plan is different based on your where clause.

|||so would you say its better to have your first query against a view not have a where clause?
|||No. There is no reason to run a query on a view with no where clause. The execution plan is recreated every time the view is used.

|||I am not saying you are incorrect and i do appreciate your comments, but the first time i use a view it takes a little longer than all the other times i use the view. I understand this to be because it is building the Execution path and optimizing the views Execution path.|||

Yes, it would be slower the first time because of creating the execution plan. And it will use the same execution plan for similar where statements.

But, running a view with no where does nothing, unless you do that over and over. If you put in a "WHERE field=x" it creates an execution plan based on that. Then if you do "WHERE field2=y" it makes a new exeuction plan. It MIGHT have the plan cached if you have used a similar plan before, but you can't count on it.

|||

Thank you sir,

That answer helps me unserstand immensely. so it builds a different Execution path per each unquely patterened where clause..

How Analysis Services Server select the storage location for any object of within an analysis se

Hi, all experts here,

Thank you very much for your kind attention.

Could please any of you kindly advise me how can we find the storage locations of partitions of a cube from its code? (.xml file). Thanks a lot in advance and I am looking forward to hearing from you shortly for your advices and help.

With best regards,

Yours sincerely,

Hi, all experts here,

I am just a bit confused about the storage location of the objects of an analysis services database. It does not seem like it stores all the objects within the same folder the very first time when we created the analysis services database project from BI studio, since I created the project in a folder which is different from the folder where the dimensions files of a cube are stored? How it works? Please shed me some light on it and I am really looking forward to hearing from you for your kind advices.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||

The folder created by the BIDS project simply contains metadata files. When you deploy, process, or what not in BIDS, those metadata files are used to compile scripts that are submitted to Analysis Services. Analysis Services constructs its objects within its data folder. The root data folder can be found by openning SQL Server Management Studio, connecting to an instance of SSAS, right-clicking the SSAS instance object, and selecting Properties from the context menu. On the dialog to pops up, look for the DataDir entry.

Bryan

|||

Hi, Bryan,

Thanks for your kind advices.

With best regards,

Yours sincerely,

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC