mJ471Fdn

1Web-Service: How to authenticate users with the SQL Server database table using VB
2-- Sample Table Creation and Index Creation script for MCIS-4423
3-- This script is designed to be "re-runnable", but you need to be careful, 
4-- Since this will DROP the table, which would be bad if it was an existing table with data
5
6-- Make sure you are in the correct database
7USE [AdventureWorks]
8GO
9
10-- Drop Check Constraint
11IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Team_TeamID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Team]'))
12ALTER TABLE [dbo].[Team] DROP CONSTRAINT [CK_Team_TeamID]
13GO
14
15-- Drop Table
16IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND type in (N'U'))
17DROP TABLE [dbo].[Team]
18GO
19
20-- Create Table
21CREATE TABLE [dbo].[Team](
22    [TeamID] [char](3) NOT NULL,
23    [TeamName] [varchar](20) NOT NULL,
24    [City] [varchar](50) NOT NULL,
25    [StateCode] [char](2) NULL,
26    [PostalCode] [char](5) NULL,
27CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED  
28(
29    [TeamID] ASC
30)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
31) ON [PRIMARY]
32GO
33
34-- Add Check Constraint
35ALTER TABLE [dbo].[Team]  WITH CHECK ADD  CONSTRAINT [CK_Team_TeamID] CHECK  (([TeamID] like '[A-Z][A-Z][A-Z]'))
36GO
37ALTER TABLE [dbo].[Team] CHECK CONSTRAINT [CK_Team_TeamID]
38GO
39
40-- Drop index if it exists
41IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND name = N'IX_Team_TeamName')
42DROP INDEX [IX_Team_TeamName] ON [dbo].[Team] WITH ( ONLINE = OFF )
43GO
44
45-- Add non-clustered index on StateCode column
46-- Use ONLINE = ON if you have Developer or Enterprise Edition
47-- Use MAXDOP = 2 (set to roughly 25% of the number of CPU cores to keep index creation from affecting performance)
48CREATE NONCLUSTERED INDEX [IX_Team_TeamName] ON [dbo].[Team] 
49(
50    [StateCode] ASC
51)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, MAXDOP = 2, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
52GO
53	
54SELECT username FROM users
55WHERE username=@username and password=@password