Can I create a schema bound view from this?

• SQLOzzie

  Default port

  Points: 1442

  More actions

  March 28, 2012 at 12:34 pm

  #255690

  I am trying to create a schema bound view as follows.

  -- GroupID - BIGINT NOT NULL

  -- LanguageID - INT NOT NULL

  -- PrimaryCluster - BIT NOT NULL

  CREATE VIEW [dbo].[NoPrimaries] WITH SCHEMABINDING

  AS

  SELECT [GroupID], [LanguageID], SUM( CAST( PrimaryCluster AS INT ) ) AS PC

  FROM [dbo].[BabelGrouping]

  GROUP BY GroupID, LanguageID

  CREATE UNIQUE CLUSTERED INDEX [PK_NoPrimaries_GroupID_LanguageID] ON [dbo].[NoPrimaries]

  (

  GroupIDASC,

  LanguageIDASC

  )

  I can create the view - but when I create the UNIQUE CLUSTERED INDEX I receive the following:

  Msg 10138, Level 16, State 1, Line 1

  Cannot create index on view 'Babel.dbo.NoPrimaries' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

  Any way to make this happen? This seems like an incorrect error - I am not COUNTING Babelid. I'm not entirely sure I know what this error is saying.

  Thanks,

  Doug

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 28, 2012 at 12:51 pm

  #1465922

  Directly from Books Online:

  If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.

  You need to add COUNT_BIG(*) to your select list.

• SQLOzzie

  Default port

  Points: 1442

  More actions

  March 28, 2012 at 2:53 pm

  #1466021

  Lynn,

  That rocked! - How obscure!

  That knocked 10 seconds off of a crucial SQL which was seeing DEADLOCKs.

  Thanks so much,

  Doug