cube design issue

Hi.

I have two modules that have several dimensions in common but at the same time each has its own dimensions. is it best to design 2 cubes or one combining both?

thanks in advance

Christina

It really depends ;)

In many cases one will find useful to create a single cube with 2 measure groups.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

CTE with multiple update statements?

HI Gurus

I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)

---

Isnt it possible that I can use 1 CTE with mutiple update statements?

waiting for your reply....

A CTE can be used multiple times in a single statement but not in multiple statements. You would need a table variable or temporary table if you will have multiple references.

More CTE documentation in BOL here: http://msdn2.microsoft.com/en-us/library/ms175972.aspx

|||thanx for your reply

CTE vs. Table Variable (Paging)

Hello Experts.
I'm trying to find the pros / cons of using CTEs (Common Table Expressions)
vs. Table Variables for 'Paging' through data.
Here is the scanario:
A. Table Variable Example:
Select Query returns 100k posible matching rows, I store the matching key
column along with an identity column in a table varable (i.e. DECLARE @.tmp
TABLE (rowid int identity, userid int) ), then I left join users to userid i
n
@.tmp where @.tmp.rowid between N and N.
B. CTE Example:
WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT * from
CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
What I noticed so far is:
CTE compares ROW_NUMBER() expression to the values of the BETWEEN expression
and returns only matching rows,
While Table Variables are first saving all userids in memory, then going
back for a table scan for a match for the BETWEEN expression.
I would like to know if anyone here has a definitive answer on why and in
what scanario can CTEs be faster then Table Variables and vice versa.
Thank you in advance for all your help.
- Eyal Zinder.>> I would like to know if anyone here has a definitive answer on why and in
In general, unless you have an environment where every factor that affects
performance is controlled, comparisons are moot. Also, there could be
several other ways one could achieve similar results. There could be even
different approaches for paging rows using a CTE or table variable as well.
So simply asking which construct/structure is "faster" doesn't mean much.
Your posted examples are not very clear either. Please post a sample table
structure, a few sample data as insert statements and legible code snippets
that demostrates your paging attempts.
Anith|||There are far better options than table variables.
http://www.aspfaq.com/2120
I have not played with CTEs in this case.
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||".. unless you have an environment where every factor that affects
performance is controlled, comparisons are moot.. "
The database environment I work with currently exceeds 500,000 hits per
second.
I am very much concerned with the smallest difference in performance.
As for my examples, here is a detailed view:
/* TABLE VARIABLE EXAMPLE: */
SET NOCOUNT ON
DECLARE @.tmp TABLE (rowid int identity, userid int)
INSERT INTO @.tmp (userid)
SELECT userid
FROM users (nolock)
WHERE userStatus = @.N1
ORDER BY userLastLoginDate
SELECT u.*
FROM @.tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
/* CTE EXAMPLE */
SET NOCOUNT ON
WITH tmp AS
(
SELECT userid, ROW_NUMBER() OVER (ORDER BY u.userLastLoginDate) AS rowid
FROM users (nolock)
WHERE userStatus = @.N1
)
SELECT u.*
FROM tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
Again, I am NOT looking for new / better ways to page through data or any
Cursor based paging. I am looking for what pros / cons does CTE offer over
the above solution (variable table example). I am interested to know how CT
E
works and how it differs from the above example.
Thank you again for all your help and prompt reply.
Eyal Zinder.
"Anith Sen" wrote:

> In general, unless you have an environment where every factor that affects
> performance is controlled, comparisons are moot. Also, there could be
> several other ways one could achieve similar results. There could be even
> different approaches for paging rows using a CTE or table variable as well
.
> So simply asking which construct/structure is "faster" doesn't mean much.
> Your posted examples are not very clear either. Please post a sample table
> structure, a few sample data as insert statements and legible code snippet
s
> that demostrates your paging attempts.
> --
> Anith
>
>|||Hi Eyal,
Make sure you keep it server side and only pass back the page the user wants
to the client, that will save significanly on resources especially the
network.
I, personally, don't tend to use CTE for paging because it does the whole
query first and the way I design schema I only need to join for those rows
on my page to get the 'meta' data - basically, I search on surrogate keys
where possible.
Personally I'd be inclided to use the ROWNUMBER() method and pump the output
into a table variable and join that table variable out to the base tables to
get my 'meta' as described above.
The reason is simple, it cuts down on IO.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||>> I am looking for what pros / cons does CTE offer over the above solution
There is no empirical evidence that suggests one approach is always better
than the other. In your specific situation, you should evaluate and compare
the query plans and execution times and decide which one performs better.
You can think of CTE as a temporary resultset/virtual table that lasts only
for the duration of the query. The primary benefits of a CTE include
generation of recursive queries, allowance of multiple references in the
same query and overall simplicity ( many complex queries can be simplified
with a well written CTE )
In your example, CTE offers nothing additional to the overall paging
functionality of the code. In other words, you can avoid the CTE altogether
and use a derived table to achieve similar results. In a small sample I
tested, the plans with a derived table and with a CTE were mostly similar
and provided similar performance.
In general, the "paging" methods are the SQL are an extension of a class of
queries called Quota queries in relational literature. Quota queries sort
the rows based on some explicit sequence of values in a column and then
identify the top/bottom subset (quota). You might want to research on that
if you'd like some background on such formulations.
There are several different approaches to this problem and Aaron's website
offers some of the best SQL 2000 methods that are frequently posted in this
newsgroup.
Anith|||Aaron,
Thank you. But the scale of this site does not allow for middle-tier paging
.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There are far better options than table variables.
> http://www.aspfaq.com/2120
> I have not played with CTEs in this case.
>
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||Tony,
Could you provide an example of using Meta Data for such a scanario?
"Tony Rogerson" wrote:

> Hi Eyal,
> Make sure you keep it server side and only pass back the page the user wan
ts
> to the client, that will save significanly on resources especially the
> network.
> I, personally, don't tend to use CTE for paging because it does the whole
> query first and the way I design schema I only need to join for those rows
> on my page to get the 'meta' data - basically, I search on surrogate keys
> where possible.
> Personally I'd be inclided to use the ROWNUMBER() method and pump the outp
ut
> into a table variable and join that table variable out to the base tables
to
> get my 'meta' as described above.
> The reason is simple, it cuts down on IO.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||The code below runs on my site http://sqlserverfaq.com and performs the
listing and searching of Articles.
You will see I use a temporary table with mostly id's in there and then at
the very end join only for those rows I'm throwing back to the client.
Tony.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ukug3_selGetKBArticles]
@.max_pages int output,
@.required_page int = 1,
@.rows_per_page int = 2,
@.FileType varchar(10) = '',
@.idEvents int = 0,
@.SearchKeywords varchar(200) = '',
@.OpType char(1) = 'F',
@.member_group_id int = NULL,
@.is_member_group_restrict char(1) = 'N'
as
begin
set nocount on
create table #results (
idrow int not null identity,
idKBArticle int not null,
Rank int not null,
Characterization varchar(500) not null default( '' )
)
declare @.from_row int
declare @.to_row int
set @.from_row = ( (@.required_page-1) * @.rows_per_page ) + 1
set @.to_row = @.from_row + ( @.rows_per_page - 1 )
declare @.sql nvarchar(4000)
if @.SearchKeywords > ''
begin
DECLARE @.user_search_text varchar(300)
SET @.user_search_text = @.SearchKeywords
SET @.OpType = 'C' -- Done for performance
SET @.SearchKeywords = dbo.fn_search_cleanse( @.SearchKeywords,
'AND' ) -- Gets rid of noise words and adds 'AND'
IF @.SearchKeywords = '' -- Bad search, give it another chance and
use freetext instead.
BEGIN
SET @.OpType = 'F'
SET @.SearchKeywords = @.user_search_text
END
-- If restricting to a member group then add the additional search
clause for that group
IF @.is_member_group_restrict = 'Y'
BEGIN
SELECT @.SearchKeywords = @.SearchKeywords + ' ' + search_clause
FROM member_group
WHERE id = @.member_group_id
END
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
-- IF @.member_group_id > 0
-- SET @.sql = @.sql + ' WHERE EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=kba.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
SET @.sql = @.sql + '
UNION ALL
SELECT
kba.idKBArticle,
[Rank] = 9999,
''''
FROM KBArticle kba
WHERE kba.ArticleFileName = ''' + @.SearchKeywords +
''' ) AS dt
ORDER BY Rank DESC'
end
else
begin
set @.sql = N'
select idKBArticle, 9999, ''''
from kbarticle k
where 1=1
and is_external_url_link_broken = ''N''
'
-- Search clause
if @.FileType <> ''
set @.sql = @.sql + N' and FileType=@.FileType'
else if @.idEvents = 0
set @.sql = @.sql + N' and FileType<>''wmv''' -- WMV is dealt
with in its own control now so without this we would duplicate content (ok
on the search though!)
if @.idEvents > 0
set @.sql = @.sql + N' and k.idEvents = @.idEvents'
else
set @.sql = @.sql + N' and FileType <> ''ZIP'''
IF @.member_group_id > 0
SET @.sql = @.sql + ' AND EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=k.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
set @.sql = @.sql + N'
order by EntryDate desc'
end
print @.sql
insert #results ( idKBArticle, Rank, Characterization )
exec sp_executesql @.sql,
N'@.FileType varchar(10), @.idEvents int',
@.FileType, @.idEvents
set @.max_pages = ( @.@.rowcount + ( @.rows_per_page - 1 ) ) /
@.rows_per_page
select id,
title,
author_name,
entry_date,
article_summary = dt.article_summary + case when len(
dt.article_summary ) = 100 then '...' else '' end,
FileType,
ArticleFileName,
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from (
select t.idRow,
id = t.idKBArticle,
title = k.KBArticleTitle,
author_name = case when k.external_url_link = '' or
k.external_url_link = 'HTTP://' then isnull( r.fullname, '' ) else '' end,
entry_date = CONVERT( varchar(20), k.ModifiedDate, 106 ),
article_summary= SUBSTRING( CASE WHEN t.Characterization = ''
THEN k.KBArticleAbstract ELSE t.Characterization END, 1, 100 ),
FileType = ISNULL( FileType, '' ),
ArticleFileName= ISNULL( ArticleFileName, '' ),
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from #results t
inner join kbarticle k on k.idKBArticle = t.idKBArticle
left outer join registrations r on r.idregistrations =
k.idregistrations
where idrow between @.from_row and @.to_row ) as dt
order by idrow
end
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>|||And the ASP.NET (VB.NET) to call the proc...
Dim dbConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))
dbConn.Open()
Dim cmdSQL As SqlClient.SqlCommand
Dim daSQL As New SqlDataAdapter
Dim dsSQL As New DataSet
' Get Articles
cmdSQL = New SqlCommand("ukug3_selGetKBArticles", dbConn)
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add(New SqlParameter("@.required_page",
Me.ResultsPageNumber))
cmdSQL.Parameters.Add(New SqlParameter("@.rows_per_page",
ConfigurationSettings.AppSettings("KBRowsPerPage")))
If Me.Search_FileType <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.FileType", Me.Search_FileType))
If Me.Search_EventId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.idEvents", Me.Search_EventId))
If Me.Search_Keywords <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.SearchKeywords", Me.Search_Keywords))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.member_group_id", Me.MemberGroupId))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.is_member_group_restrict", IIf(Me.isRestrictToMemberGroup,
"Y", "N")))
Dim sqlParm As SqlParameter
sqlParm = cmdSQL.Parameters.Add(New SqlParameter("@.max_pages",
CInt(0)))
sqlParm.Direction = ParameterDirection.Output
daSQL.SelectCommand = cmdSQL
Dim iPages As Integer
Try
daSQL.Fill(dsSQL)
datlArt.DataSource = dsSQL
datlArt.DataBind()
iPages = cmdSQL.Parameters("@.max_pages").Value
Catch ex As Exception
iPages = 0
End Try
trNoArticles.Visible = (datlArt.Items.Count = 0)
If iPages = 0 Then
tdPage.Visible = False
Else
lbtnPageNext.Visible = (Me.ResultsPageNumber < iPages)
lbtnPagePrev.Visible = Me.ResultsPageNumber > 1
lblPageCur.Text = Me.ResultsPageNumber.ToString
lblPageLast.Text = iPages.ToString
tdPage.Visible = True
End If
dbConn.Close()
dbConn.Dispose()
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>

CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expre

I am having this error when using execute query for CTE

Help will be appriciated

Would be interesting to have the code you tried to execute, because this i ibviously a syntax error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||? As Jens noted, you haven't showed us any code... But I'm betting you just need to use a semicolon before the "WITH": ;WITH myCTE AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <dba_sql@.discussions.microsoft.com> wrote in message news:35bbde4b-222b-4311-8087-09d80efaa94b@.discussions.microsoft.com... I am having this error when using execute query for CTE Help will be appriciated|||

I am getting this error when running the following code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int)

RETURNS @.ClassificationsInTree TABLE (ClassificationId int)

AS

BEGIN

WITH CLINTREE(ClassificationId) AS (

SELECT TopClassificationId FROM ClassificationTree

WHERE ClassificationTreeId = 81203717

UNION ALL

SELECT ClassificationId FROM Classification

INNER JOIN CLINTREE ON

CLINTREE.ClassificationId = Classification.ParentClassificationTree

WHERE Classification.ClassificationId <> CLINTREE.ClassificationId

)

--INSERT @.ClassificationsInTree

SELECT ClassificationId FROM CLINTREE

OPTION (MAXRECURSION 10);

RETURN

END

GO

The error messages:

Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18

Line 18: Incorrect syntax near 'MAXRECURSION'.

Any thoughts? This is the exact syntax found in the help files, no?

|||? I was able to run that batch on my end with no errors once I uncommented the insert line... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:0b38d9cc-f171-4ae6-8377-fc50393d8045@.discussions.microsoft.com... I am getting this error when running the following code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int) RETURNS @.ClassificationsInTree TABLE (ClassificationId int) AS BEGIN WITH CLINTREE(ClassificationId) AS ( SELECT TopClassificationId FROM ClassificationTree WHERE ClassificationTreeId = 81203717 UNION ALL SELECT ClassificationId FROM Classification INNER JOIN CLINTREE ON CLINTREE.ClassificationId = Classification.ParentClassificationTree WHERE Classification.ClassificationId <> CLINTREE.ClassificationId ) --INSERT @.ClassificationsInTree SELECT ClassificationId FROM CLINTREE OPTION (MAXRECURSION 10); RETURN END GO The error messages: Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7 Incorrect syntax near the keyword 'WITH'. Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18 Line 18: Incorrect syntax near 'MAXRECURSION'. Any thoughts? This is the exact syntax found in the help files, no?|||

Thanks for the reply.

I get those same two errors whether that line is commented or not...

Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.

|||? None that I know of. The only one I could think of was compatability level -- but I just tested with a database set to compatability level 80 (SQL Server 2000) and was still not able to replicate the error. Regardless, you should probably make sure yours is set correctly. Right-click on your database in SSMS, click Properties, then Options. Make sure Compatability Level is set to SQL Server 2005... Aside from that, though, I'm not sure what's going on. Can you use CTEs at all (outside of UDFs?) And did you try adding a semicolon before the WITH, as I suggested before? It appears to be unnecessary on my end, but it's always a good idea anyway... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:1cbe0923-6981-44eb-b4cd-8bdee48ad315@.discussions.microsoft.com... Thanks for the reply. I get those same two errors whether that line is commented or not... Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.|||

So your suggestion to check the compatability level led me to the answer. The database server I was trying to run the query against is a SQL2000 server. We have many instances of SQL Server running for development purposes, and I didn't realize I was working against a SQL2000 instance. That database has the data I need in it, so I will have to move it to another machine.

A silly mistake, but I wouldn't have realized it, Thanks for your help!

|||I got the same error in Crystal Reports XI. I used Toad to write the SQL, then copied it into the command editor in Crystal. It worked fine, until I opened the same saved report on CRXI from a Citrix client. Still works fine using Toad, same datasource, credentials, etc. After reading this thread, I tried putting the semicolon in front of WITH and it worked. Strange to me, but it works,