In the code below, please replace "UserName" with the name of the user, "LoginName" with the name of the login to which the user is mapped, "DBName" with the name...

Try this as well.

WITH Partitioned AS (
SELECT
memb_MembershipID
,memb_MemberID
,memb_type
,MIN(memb_type) OVER (PARTITION BY memb_MemberID) AS MinMemb
,MAX(memb_type) OVER (PARTITION BY memb_MemberID) AS MaxMemb
FROM dbo.Members
)
SELECT 
memb_MembershipID
,memb_MemberID
,memb_type
FROM Partitioned
WHERE MinMemb <> 'Tier 1'
AND MaxMemb...

Sam

1.  Yes, it will.  Locking depends on whether you rebuild with the ONLINE=ON option (only available in Enterprise Edition).  The table will be available during index rebuilds only with ONLINE=ON.

...

A self-join would work, but may not perform well on large data sets.

SELECT DISTINCT
t1.rowID
,t1.myDateTime
,t1.myCount
,CASE
WHEN t2.rowID IS NULL THEN 0
ELSE 1
END AS invalid
FROM aaaMyTable t1
LEFT JOIN aaaMyTable t2
ON...

Will the new file be in the same filegroup as the existing file?  What is the driver for doing this work - ease of administratio, performance, something else?

John

Try this.

select C.EmpLocationName, B.EmpCategoryName, count(A.EmpCategoryID) as CategoryCount
FROM EmployeeMaster A
LEFT JOIN EmployeeCategory B ON A.EmpCategoryID = B.EmpCategoryID
INNER JOIN EmployeeLocation C ON A.EmpLocationID = C.EmpLocationID
where ISNULL(A.IsDeleted,0) = 0
Group...

What is the definition of the assets table?  My guess is that it contains off-row data.  Have a read of this.  I quote:

For an index, one row is returned...

Put EmployeeCategory first in your query, and LEFT JOIN it to EmployeeMaster.

John

Sounds like an Availability Group synchronisation problem.  Expand the Always On High Availability container in Object Explorer and/or query the dm_hadr DMVs to investigate.

John

That's not how it works, I'm afraid.  Feel free to send me a private message through this site if there's something you don't want everybody to see.

John

I don't know anything about Oracle, but it sounds as it's expecting a numerical value and you have supplied some non-numerical characters.  Beyond that, it's difficult to help given that...

"WHERE Date LIKE @Date" doesn't make any sense.  LIKE is a string comparison operator, and if you attempt to use it on dates, you can get unexpected results, as you've...

This is all somewhat vague.  What is the date and how are you specifying it?

John

Or this, given that I think you want either/or rather than both.  But Phil's right - table DDL in the form of CREATE TABLE statements, sample data in the form...

(1) Not necessarily.  DBCC CHECKDB creates an internal snapshot of the database.  Database snapshots are sparse files, so they start off at (near) zero size and only grow when data...