首先我們先創建兩個表：
CREATE TABLE [dbo].[Roles](
[Name] [nvarchar](50) NOT NULL,
[CreatedAt] [datetime] NOT NULL,
[UpdatedAt] [datetime] NOT NULL,
[Deleted] [bit] NOT NULL,
[RoleStatus] [int] NOT NULL,
[ID] [uniqueidentifier] NOT NULL,
[SystemID] [uniqueidentifier] NOT NULL,
[RoleKind] [int] NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=默認管理員角色，2=普通角色' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Roles', @level2type=N'COLUMN',@level2name=N'RoleKind'
GO
ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_Deleted] DEFAULT ((0)) FOR [Deleted]
GO
ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_RoleStatus] DEFAULT ((1)) FOR [RoleStatus]
GO
ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_RoleKind] DEFAULT ((2)) FOR [RoleKind]
GO
CREATE TABLE [dbo].[RoleFunctions](
[MappingID] [int] IDENTITY(1,1) NOT NULL,
[Status] [int] NOT NULL,
[RoleID] [uniqueidentifier] NOT NULL,
[FunctionID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_RoleFunctions] PRIMARY KEY CLUSTERED
(
[MappingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleFunctions] ADD CONSTRAINT [DF_RoleFunctions_Status] DEFAULT ((0)) FOR [Status]
GO
以下是兩個條SQL查詢語句，但是不同的是一個是用inner jion，一個是用left jion，但是兩個查詢語句的執行計劃差距非常大
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [MappingID]
,[Status]
,[RoleID]
,[FunctionID]
FROM [RoleFunctions]
left join roles on roles.id = rolefunctions.functionid
SELECT [MappingID]
,[Status]
,[RoleID]
,[FunctionID]
FROM [RoleFunctions]
inner join roles on roles.id = rolefunctions.functionid
期望高手給予講解……
posted on 2012-05-19 14:55
mixer-a 閱讀(1991)
評論(1) 編輯 收藏