Evil Kraig F (3/5/2014)

---

GilaMonster (3/5/2014)

---

I would create, as an index for that query:

create index IX_A_Col1 on A ( Col1, IndexID )

Having it as a second key column is more useful...

Evil Kraig F (3/5/2014)

---

No, the clustered index is already included in all nonclustered indexes. The include would be superfluous unless you're trying to protect against someone changing the clustered...

I would create, as an index for that query:

create index IX_A_Col1 on A ( Col1, IndexID )

Having it as a second key column is more useful than having it as...

N_Muller (3/5/2014)

---

Would there be any benefit to include columns that are part of the primary key on a non-clustered index, even if I don't intend to add it to the...

SQLRNNR (3/5/2014)

---

GilaMonster (3/5/2014)

---

Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

SELECT *

FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa

...

iso is a CD/DVD image. Either burn it to a DVD and use that or get a program which can mount iso files as virtual drives.

No

No

sqlbuddy123 (3/5/2014)

---

Check this one ..

SELECT *, OBJ.name AS Object_Name

FROM sys.dm_os_buffer_descriptors AS bd

INNER JOIN

(

...

Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

SELECT *

FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa

WHERE allocated_page_file_id...

:hehe:

btw, these are valid

with cte as (

select Person

, row_number() over (order by Person) AS OrderingKey

...

You can't use a windowing function in a where clause. The examples you show work because they're not using a windowing function in a where clause, they're using it in...

Key columns, unless the nonclustered index is defined UNIQUE. Not...

Nothing. I have to assume that because I'm...

ChrisM@Work (3/5/2014)

---

GilaMonster (3/5/2014)

---

ChrisM@Work (3/5/2014)

---

If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still...

If you want to count the number of rows where the age is null, you probably want this:

select count(*) from dbo.prim

where age is null

Count(*) means count the number of rows....