ASP.NET Utility to create SQL Server Database for Membership and Roles Management

Ok, ok. I always forget so to save me some time, here it is...

Open up command line from Visual Studio, then run... cmd>aspnet_regsql.exe

Then execute the following...

-- Register Application
EXEC dbo.aspnet_Applications_CreateApplication 'AppName', NULL

-- Check if Application was registered
SELECT * FROM dbo.aspnet_Applications

-- Create a Role
EXEC dbo.aspnet_Roles_CreateRole 'AppName', 'Administrator'

-- Check if Role was created
SELECT * from dbo.aspnet_Roles

-- Add myself as Admin
DECLARE @Date AS DATETIME
SET @Date = (SELECT GETDATE())
EXEC aspnet_UsersInRoles_AddUsersToRoles 'AppName', 'DOMAIN\username', 'Administrator', @Date

-- Check if I got added as Admin
EXEC dbo.aspnet_UsersInRoles_GetUsersInRoles 'AppName', 'Administrator'

Update table with data from another table in SQL Server

Ok, pretty much self explanatory

UPDATE table1 
    SET table1.col1 = table2.col1, 
        table1.col2 = table2.col2
FROM table1, table2
WHERE table2.col = table1.col

Or using alias:

UPDATE A1 
    SET A1.col1 = A2.col1, 
        A1.col2 = A2.col2
FROM table1 A1, table2 A2
WHERE A2.col = A1.col

Best Practices for Query Optimization

I'd like to take note of few tips from Pinal Dave. Certainly good reminders when creating queries.

How to make a quick copy of SQL Server database table

Here's how to create a quick copy of a database table including the data in SQL Server.

select * into [dbo].[TableName_Bak] from [dbo].[TableName]

PIVOT Is Elegant Solution In Summarizing Data

Project I'm working on has to do with counting how many machines are being used in our environment. In our database, I'm able to list data like the following:

LocationCode	MachineType	MachineStatus
SJO	Desktop	Production
SJO	Notebook	Production
FSK	Desktop	Factory
TMP	Desktop	Production
AUS	Notebook	Production
TMP	Desktop	Factory
FSK	Notebook	Production
SJO	Desktop	Production
FSK	Desktop	Production

Now I need to provide a summary like as follows:

Category	San Jose	Fishkill	Tempe	Austin
Qualified Desktop	2	1	1	0
Qualified Notebook	1	1	0	1
Factory Desktop	0	1	1	0

Here's the solution using PIVOT:

SELECT
    Category AS [Category],
    [AUS] AS [Austin],
    [FSK] AS [Fishkill],
    [SJO] AS [San Jose],
    [TMP] AS [Tempe],
    [AUS]+[FSK]+[SJO]+[TMP] AS [Total]
FROM
    ( SELECT
    LocationCode,
    (CASE
        WHEN MachineStatus = 'Production' AND MachineType = 'Desktop' THEN 'Qualified Desktop'
        WHEN MachineStatus = 'Production' AND MachineType = 'Notebook' THEN 'Qualified Notebook'
        WHEN MachineStatus = 'Factory' THEN 'Factory Desktop'
    END) AS [Category]
    FROM V_PcCount_Hardware) AS SourceTable
PIVOT
(
    COUNT(LocationCode)
    FOR LocationCode IN([AUS], [FSK], [SJO], [TMP])
) AS PivotTable