Query Problem

Dear All,
I have a problem about creating a view to concat all the row field
together.
For example I have a table with 2 columns
ColA, ColB
1 , A
1 , B
2 , A
2 , B
2 , C
I want to make a view using group by and the excepted view should return
ColA, ColB
1 , A-B
2 , A-B-C
Thanks for your help.
^_^You have to use Aggregate Function with Group By and since no existance sql
function to do so.. Then you have to write one by yourself, or do it in your
application
for me C# code is easier than TSQL
<Windy> wrote in message news:%233DyE7vMGHA.3708@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a problem about creating a view to concat all the row field
> together.
> For example I have a table with 2 columns
> ColA, ColB
> 1 , A
> 1 , B
> 2 , A
> 2 , B
> 2 , C
> I want to make a view using group by and the excepted view should return
> ColA, ColB
> 1 , A-B
> 2 , A-B-C
>
> Thanks for your help.
> ^_^
>|||On Thu, 16 Feb 2006 21:47:45 +0800, <Windy> wrote:

>Dear All,
> I have a problem about creating a view to concat all the row field
>together.
>For example I have a table with 2 columns
>ColA, ColB
>1 , A
>1 , B
>2 , A
>2 , B
>2 , C
>I want to make a view using group by and the excepted view should return
>ColA, ColB
>1 , A-B
>2 , A-B-C
Hi Windy,
See http://www.aspfaq.com/show.asp?id=2529.
Hugo Kornelis, SQL Server MVP

Query performance on paritioned views with check constraints

Hi,

I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.

I have two partitioned tables and a view on top of both tables as below:

create table [dbo].[Table_1]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create table [dbo].[Table_2]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create view TableView

as

select * from Table_1

union all

select * from Table_2

GO

Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.

I then ran the query execution plan for the following query on both SQL server 2000 and 2005.

select * from TableView where TableID = 10

On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.

Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:

create table [dbo].[Table_1]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK ([TableID] BETWEEN 1 AND 999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create table [dbo].[Table_2]

(

[TableID] INTEGER PRIMARY KEY NONCLUSTERED

CHECK ([TableID] BETWEEN 1000 AND 1999),

[AnyOtherColumn] int NOT NULL ,

) ON [Primary]

GO

create view TableView

as

select * from Table_1

union all

select * from Table_2

GO

Now run the query execution plan for the same query again.

select * from TableView where TableID = 10

This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.

I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?

Is it a bug in SQL server?

Or am I missing any thing?

Any help appreciated.

Thanks

NOT FOR REPLICATION means that your check constraint is not enforced when a replication agent performs insert, update, or delete operations. This means that the system cannot assume that the data in the column actually meets that constraint and so it has to scan both tables.

Removing the option means that it knows that data in the column meets the constraint and only has to scan the table whose constraint contains that value.

|||

Thanks,

I understand why we use NOT FOR REPLICATION clauses. But replication was not is the scope of this query. I meant to know the performance impact for queries on the partitioned view, with and without the NOT FOR REPLICATION clause on check constraint in the underlying tables.

for more detail, I have mentioned the problem here

http://vikramkamath.wordpress.com/2007/03/21/partitioned-views-check-constraints-with-not-for-replication-clause/

Cheers

Query Notifications in SQL Express?

I am finding conflicting information about enabling query notifications for SQL Express. One book says it can be done and shows an example but most places on MSDN claim it requires the Service Broker. I am using Management Studio for SQL Express which does not show the Service Broker while an installation with SQL Server 2005 Dev Edition does show the Service Broker.

This is all very confusing. I want to be able to develop locally with SQL Express with Query Notifications and later deploy to a server which is more capable. Previously I have read that only real difference between SQL Server 2005 and SQL Express is a 2gb memory limit.

How would I go about setting up Query Notifications for SQL Express? Where would I find such documentation on MSDN?

I am partially past this problem. Upgrading SQL Express to SP1 has fixed this command from handing in Management Studio.

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Now I can see the Service Broker is enabled, but I guess I cannot really manage it, which is fine as long as I can still use the default queue for notifications. This query confirms it is on.

SELECT name,is_broker_enabled FROM sys.databases

By default the Service Broker is on for new databases but when I load the AdventureWorks database it is not enabled, so I had to get this working. Now I am stuck on these commands...

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO brennan
GRANT SEND ON SERVICE::SqlQueryNotificationService TO brennan

The first one works but the second one fails. It says it does not recognize that service. I would like to know if there is a query I can run to see the available services.

Any help is appreciated.

Query Notification

Hi,
I am using .net 2.0 and sql server 2005.
Now i want to notify user Application (C# Application) about the change in certain table.
For Example: if tbl1 be any table and when insert operation is carried out in that table.
Then after 100 or multiple of 100 record insertion in tbl1 i want the notification to be given to user Application (C# Application).
Is this possible through Query Notification.?
I even dont know if this is the right forum for this question..

Please Help

It is a common mistake to confuse Query Notifications with Notification Services.

Query needs to loop?

Is this possible?
A char variable length field contains this data: (for example)
ABC,XYZ,GEF,CAB
I need to query another table on the contents of each of these names that
are separated by comma.
I need to read ABC and then query another table for ABC in field1 and pick
up some fields.
Then, I need to do the same for XYZ, etc, reading across.
How can I parse the field that's comma delim, and in one continuous select
statment pick all the data from another table? Put a looping statement into
the Select statement to read through the comma delim field until done?
Thanks,
Don
On Thu, 25 Aug 2005 14:55:34 -0700, DonSQL2222 wrote:

>Is this possible?
>A char variable length field contains this data: (for example)
>ABC,XYZ,GEF,CAB
>I need to query another table on the contents of each of these names that
>are separated by comma.
>I need to read ABC and then query another table for ABC in field1 and pick
>up some fields.
>Then, I need to do the same for XYZ, etc, reading across.
>How can I parse the field that's comma delim, and in one continuous select
>statment pick all the data from another table? Put a looping statement into
>the Select statement to read through the comma delim field until done?
>Thanks,
>Don
Hi Don,
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

query multi tables, count/sum 1 of the tables

I'm quering about 5 tables using inner join. On one of the tables i just
want to count or sum up the results. Here is the example I'm trying to get
working. When I add the SUM(Private.PrivateIDs) I get an error. Is
something like this possible?
SELECT Units.UnitServiceLine, Units.UnitName,
Staffing_Types.StaffingType, Unit_Request.UnitOnDutyStaff,
SUM(Private.PrivateIDs)
FROM Unit_Info
INNER JOIN Unit_Request ON Unit_Info.UIID = Unit_Request.UIID INNER JOIN
Units ON Unit_Info.UnitID = Units.UID
INNER JOIN Staffing_Types ON Unit_Request.STID=Staffing_Types.STID
INNER JOIN Private ON Unit_Info.UIID = Private.UIID
On Wed, 18 May 2005 09:51:13 -0700, LU wrote:

>I'm quering about 5 tables using inner join. On one of the tables i just
>want to count or sum up the results. Here is the example I'm trying to get
>working. When I add the SUM(Private.PrivateIDs) I get an error. Is
>something like this possible?
>SELECT Units.UnitServiceLine, Units.UnitName,
>Staffing_Types.StaffingType, Unit_Request.UnitOnDutyStaff,
>SUM(Private.PrivateIDs)
>FROM Unit_Info
>INNER JOIN Unit_Request ON Unit_Info.UIID = Unit_Request.UIID INNER JOIN
>Units ON Unit_Info.UnitID = Units.UID
>INNER JOIN Staffing_Types ON Unit_Request.STID=Staffing_Types.STID
>INNER JOIN Private ON Unit_Info.UIID = Private.UIID
Hi LU,
Without knowing your table structure, sample data and expected output,
the best I can offer is a wild guess:
SELECT Units.UnitServiceLine, Units.UnitName,
Staffing_Types.StaffingType, Unit_Request.UnitOnDutyStaff,
PS.SumOfPrivateIDs
FROM Unit_Info
INNER JOIN Unit_Request
ON Unit_Info.UIID = Unit_Request.UIID
INNER JOIN Units
ON Unit_Info.UnitID = Units.UID
INNER JOIN Staffing_Types
ON Unit_Request.STID=Staffing_Types.STID
INNER JOIN (SELECT UIID, SUM(PrivateIDs) AS SumOfProvateiDs
FROM Private
GROUP BY UIID) AS PS
ON Unit_Info.UIID = PS.UIID
(untested)
If this doesn't help, then check www.aspfaq.com/5006, then post again,
following the instructions therein.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)