1

We created an indexed view by such sql:

Select Table1_ID, Count_BIG(*) as Table2TotalCount from Table2 inner join
Table1 inner join... where...

Also after creating the view, we set clustered unique index on column Table1_ID.
So View consists of two columns:

Table1_ID
Table2TotalCount

We have two questions:

  1. If we use Count(1) or Count_BIG(1) instead of Count_BIG(*), SQL doesn't allow us to set index on Table1_ID
  2. How can we control the type of second column (Table2TotalCount) - SQL give a long nullable type to this column. But we want to set int not nullable type to this column. After creating View, we can't find any way to change column type. Also t-sql we use for creating view, always return int values to Table2TotalCount.
arena-ru
  • 990
  • 2
  • 12
  • 25

2 Answers2

1

You have to use COUNT_BIG(*). It's one of the restrictions on creating an indexed view:

The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:

  • COUNT (COUNT_BIG(*) is allowed.)

You could create a second view that isn't indexed, that references this first view, and casts the data type of the count column to your desired type (and possibly uses an ISNULL() to prevent the nullable property)

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

  1. That's probably because you are counting on an expression instead of a field or *. Using count(1) gives the same result as count(*), i.e. counting the number of records, so there is no reason to use count(1).

  2. Use count instead of count_big to get int instead of bigint (long). (The documentation specifically says that the only difference between them is the return type.) I don't know why the value is nullable as count never returns null, but that might be the same for all aggregates, and probably not possible to control.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • did you read why do we use count_big instead of count ? We can't set index later. Don't understand your answer on question 1 (about expression instead of a field) and how it correlates to our problem. If you write that difference in count and cout_big only in return type - so how can it affects creating index on other column? Also count(1) takes less resource then count(*) - and this is why we want to use count(1). – arena-ru Dec 09 '10 at 10:48
  • 2
    @mmcteam.com.ua - I think the last time count(1) used less resource than count(*) was, approximately, SQL Server 6.5. It might have been 4.2 or 7, but certainly had been resolved by 2000. – Damien_The_Unbeliever Dec 09 '10 at 12:52
  • 1
    @mmcteam.com.ua: There are two forms of the `count` aggregate, either `count(expression)` or `count(*)`, so `count(1)` is a count on the expression `1`. If `count_big(*)` works but not `count_big(1)`, just use `count_big(*)`. As an expression can be pretty much anything, it's quite reasonable if you can't create an index on a count on an exression. – Guffa Dec 09 '10 at 14:44