首先我们先创建两个表：
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 阅读(1986)
评论(1) 编辑 收藏