Sql-server – better option than Union All for multiple selects from the same row

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed. The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit. If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table. UserID will be a nonclustered index key on Alerts table. This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table. UserId will probably be the clustering key in UserMap too, to make joining more efficient. UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert. And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation. Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of 340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only 4,294,967,296 INTs. Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt

Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.

With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you hard-code) if a match is not found or if you are using Windows groups for example (which prior to SQL Server 2012 do not support default schema):

CREATE PROCEDURE dbo.whatever_procedure
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX), @s SYSNAME;

  SELECT @s = d.default_schema_name
    FROM sys.database_principals AS d
    LEFT OUTER JOIN sys.server_principals AS s
    ON d.[sid] = s.[sid]
    WHERE SUSER_SNAME() IN (d.name, s.name);

  SELECT @s = COALESCE(@s, N'dbo');

  SET @sql = N'SELECT x FROM ' + QUOTENAME(@s) 
    + '.whatever_view_name;';

  EXEC sp_executesql @sql;
END
GO

You can test this:

USE tempdb;
GO
CREATE SCHEMA foo;
GO
CREATE LOGIN flubat WITH PASSWORD='flubat',CHECK_POLICY = OFF;
GO
CREATE USER flubat FROM LOGIN flubat WITH DEFAULT_SCHEMA = foo;
GO
GRANT SELECT ON SCHEMA::foo TO flubat;
GO
CREATE VIEW dbo.whatever_view_name AS SELECT x = 'dbo';
GO
CREATE VIEW foo.whatever_view_name AS SELECT x = 'foo';
GO
-- create the above procedure
GO
GRANT EXEC ON dbo.whatever_procedure TO flubat;
GO

And then execute it as yourself and also as flubat:

EXEC dbo.whatever_procedure;
GO
EXECUTE AS USER = N'flubat';
GO
EXEC dbo.whatever_procedure;
GO
REVERT;

Results:

x 
----
dbo

x
----
foo

But like I said, this is potentially fragile, and I would lean toward either creating a view per schema, or changing the design altogether.