一个表USER,就一个列ID,里面有1,2,3三行,ID是主键就是按升序排的DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i这样输出的竟然是2DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users ORDER BY id asc
PRINT @i这样输出就是1然后不写SET ROWCOUNTDECLARE @i INT
SELECT @i=id FROM dbo.Users ORDER BY id
PRINT @i这样输出是3,就是把最后一个值赋予@i了然后不写orderDECLARE @i INT
SELECT @i=id FROM dbo.Users
PRINT @i
输出变成1了。谁能解释上述4种情况的原理,我找不出原因,非常感谢
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i这样输出的竟然是2DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users ORDER BY id asc
PRINT @i这样输出就是1然后不写SET ROWCOUNTDECLARE @i INT
SELECT @i=id FROM dbo.Users ORDER BY id
PRINT @i这样输出是3,就是把最后一个值赋予@i了然后不写orderDECLARE @i INT
SELECT @i=id FROM dbo.Users
PRINT @i
输出变成1了。谁能解释上述4种情况的原理,我找不出原因,非常感谢
SET IDENTITY_INSERT [USER] ON
INSERT INTO [user](id)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
SET IDENTITY_INSERT [USER] OFF
DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM [User]
PRINT @i
/*
1
*/
2.你ROWCOUNT 1 指定 ID ASC 后当然返回是1
3。你指定排序并赋值给变量@I 那么最后一次的值显示是3 当然输出是3
4.跟你1同理由.
注意 set ROWCOUNT 是会话级别的,不断开会话设置回一直存在
USE [ZyDb]
GO
/****** 对象: Table [dbo].[Users] 脚本日期: 10/16/2012 21:26:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[qqOpenID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[userName] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Users_userName] DEFAULT ('猪猪会员'),
[wbName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[qqImg] [varchar](600) COLLATE Chinese_PRC_CI_AS NOT NULL,
[experience] [int] NOT NULL CONSTRAINT [DF_Users_experience] DEFAULT ((0)),
[wealth] [int] NOT NULL CONSTRAINT [DF_Users_wealth] DEFAULT ((0)),
[balance] [int] NOT NULL CONSTRAINT [DF_Users_balance] DEFAULT ((0)),
[rankType] [tinyint] NOT NULL CONSTRAINT [DF_Users_rankType] DEFAULT ((0)),
[sex] [bit] NOT NULL CONSTRAINT [DF_Users_sex] DEFAULT ((1)),
[introduction] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[isAllow] [bit] NOT NULL CONSTRAINT [DF_Users_isAllow] DEFAULT ((1)),
[guidKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_guidKey] DEFAULT (newid()),
[typeid] [smallint] NOT NULL,
[fire] [int] NOT NULL CONSTRAINT [DF_Users_fire] DEFAULT ((0)),
[up] [int] NOT NULL CONSTRAINT [DF_Users_up] DEFAULT ((0)),
[inTime] [datetime] NOT NULL CONSTRAINT [DF_Users_inTime] DEFAULT (getdate()),
CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID GUID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'ID'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1主网站2是360PC平台' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'typeid'GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users'GO
USE [ZyDb]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [CK_Users] CHECK ((charindex('&',[userName])=(0)))
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [CK_Users_1] CHECK ((charindex('&',[introduction])=(0)))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL--,
--[qqOpenID] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
--[userName] [nvarchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_Users_userName] DEFAULT ('猪猪会员'),
--[wbName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
--[qqImg] [varchar](600) COLLATE Chinese_PRC_CI_AS NOT NULL,
--[experience] [int] NOT NULL CONSTRAINT [DF_Users_experience] DEFAULT ((0)),
--[wealth] [int] NOT NULL CONSTRAINT [DF_Users_wealth] DEFAULT ((0)),
--[balance] [int] NOT NULL CONSTRAINT [DF_Users_balance] DEFAULT ((0)),
--[rankType] [tinyint] NOT NULL CONSTRAINT [DF_Users_rankType] DEFAULT ((0)),
--[sex] [bit] NOT NULL CONSTRAINT [DF_Users_sex] DEFAULT ((1)),
--[introduction] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
--[isAllow] [bit] NOT NULL CONSTRAINT [DF_Users_isAllow] DEFAULT ((1)),
--[guidKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_guidKey] DEFAULT (newid()),
--[typeid] [smallint] NOT NULL,
--[fire] [int] NOT NULL CONSTRAINT [DF_Users_fire] DEFAULT ((0)),
--[up] [int] NOT NULL CONSTRAINT [DF_Users_up] DEFAULT ((0)),
--[inTime] [datetime] NOT NULL CONSTRAINT [DF_Users_inTime] DEFAULT (getdate()),
CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--GO
--SET ANSI_PADDING OFF
--GO
--EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID GUID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'ID'
--GO
--EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1主网站2是360PC平台' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users', @level2type=N'COLUMN', @level2name=N'typeid'
--GO
--EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Users'
--GO
--USE [ZyDb]
--GO
--ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [CK_Users] CHECK ((charindex('&',[userName])=(0)))
--GO
--ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [CK_Users_1] CHECK ((charindex('&',[introduction])=(0)))
SELECT * FROM [users]
DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i
go
DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users ORDER BY id asc
PRINT @i
go
DECLARE @i INT
SELECT @i=id FROM dbo.Users ORDER BY id
PRINT @i
go
DECLARE @i INT
SELECT @i=id FROM dbo.Users
PRINT @i
(
@qqOpenID VARCHAR(50),
@userName NVARCHAR(30),
@qqImg VARCHAR(600),
@rankType TINYINT,
@sex TINYINT,
@guidKey CHAR(36) OUT,
@wbName VARCHAR(50),
@id INT OUT,
@introduction NVARCHAR(500),
@typeid SMALLINT
)
AS
DECLARE @oldWb VARCHAR(50)
DECLARE @oldqqImg VARCHAR(600)
SELECT @id=ID,@guidKey=guidKey,@oldWb=wbName,@oldqqImg=qqImg FROM Users WHERE qqOpenID=@qqOpenID
IF(@@ROWCOUNT>0)
BEGIN
IF(@oldWb IS NULL AND @wbName<>'')
UPDATE Users SET wbName=@wbName WHERE ID=@id
IF(@oldqqImg<>@qqImg)
BEGIN
set xact_abort ON
BEGIN TRAN
UPDATE Users SET qqImg=@qqImg WHERE ID=@id
DECLARE @qqImg100 VARCHAR(200)
SET @qqImg100=dbo.img(@qqImg,100)
UPDATE room SET qqImg=@qqImg100 WHERE id=@id
COMMIT TRAN
END
RETURN 65;
END
ELSE
BEGIN
INSERT Users(qqOpenID,userName,qqImg,rankType,sex,wbName,typeid,introduction) VALUES(@qqOpenID,@userName,@qqImg,@rankType,@sex,@wbName,@typeid,@introduction)
SELECT @id=ID,@guidKey=guidKey FROM Users WHERE qqOpenID=@qqOpenID
RETURN 66;
END
2FD75BB32F31655810C2AA0C5C1E8F30结果就是2了,简直无语了,超自然现象
GO
/****** 对象: Table [dbo].[Users] 脚本日期: 10/16/2012 22:31:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[id] [int] IDENTITY(1,1) NOT NULL,
[openid] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF先建立这个表然后运行
INSERT users(OpenID) VALUES('DA58CA3A8BF9015CBAC9A8A05FB92DF5')
INSERT users(OpenID) VALUES('2FD75BB32F31655810C2AA0C5C1E8F30')DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i输出为2然后TRUNCATE TABLE dbo.UsersINSERT users(OpenID) VALUES('1')
INSERT users(OpenID) VALUES('2')输出为1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[id] [int] IDENTITY(1,1) NOT NULL,
[openid] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT users(OpenID) VALUES('DA58CA3A8BF9015CBAC9A8A05FB92DF5')
INSERT users(OpenID) VALUES('2FD75BB32F31655810C2AA0C5C1E8F30')
DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i
--输出为2
--然后
TRUNCATE TABLE dbo.Users
INSERT users(OpenID) VALUES('1')
INSERT users(OpenID) VALUES('2')
DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i
--输出为1
INSERT users(OpenID) VALUES('DA58CA3A8BF9015CBAC9A8A05FB92DF5')
INSERT users(OpenID) VALUES('2FD75BB32F31655810C2AA0C5C1E8F30')DECLARE @i INT
SET ROWCOUNT 1
SELECT @i=id FROM dbo.Users
PRINT @i显示就是2了
2条数据啊INSERT users(OpenID) VALUES('DA58CA3A8BF9015CBAC9A8A05FB92DF5')
INSERT users(OpenID) VALUES('2FD75BB32F31655810C2AA0C5C1E8F30')
INSERT users(OpenID) VALUES('2FD75BB32F31655810C2AA0C5C1E8F30')
select * from users
你这样执行,怎么痒都是第二条,而且只有第二条,我在看你是不是哪里做了设置,的确很奇怪
就要加order by ,想偷这个懒,会付出惨痛的代价的,而且也不需要用 set rowcount吧,你想取第一个,用top就好了
这个是 SET ROWCOUNT 0 的关系,之前设了SET ROWCOUNT 1 ,他缓存了,您执行下SET ROWCOUNT 0就好了,您是不是2呢?
你那个前面SET ROWCOUNT 1 执行到了,缓存了,你执行下SET ROWCOUNT 0,再select * from users 就2条了
string类型,是腾讯QQ登录的OPEDNID,这个没问题的吧,大侠我想问个东西,那个非聚集索引的ASC升序降序是什么意思,不是都按照主键升序降序排的吗。
2、CREATE CLUSTERED INDEX Clustered_user ON users (id,openid ASC )
3、把id设回主键然后你插入数据,就得到你想要的了
/*name collation_name
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
master Chinese_PRC_CI_AS
tempdb Chinese_PRC_CI_AS
model Chinese_PRC_CI_AS
msdb Chinese_PRC_CI_AS
ReportServer Latin1_General_CI_AS_KS_WS
ReportServerTempDB Latin1_General_CI_AS_KS_WS
SQLTool Chinese_PRC_CI_AS
TestDB Chinese_PRC_CI_AS*/
打开执行几乎看看知不是进行了索引扫描,根本没有访问聚集索引
如果是这样的话就是按照索引的排序顺序来的
因为你没有ORDER BY ,数据库并不帮你维护这个顺序,所以这也是有可能的
您刚才这样CREATE CLUSTERED INDEX Clustered_user ON users (id,openid ASC )
就不是2了吗,就是1了吗,那怎么解释呢,这样就用到聚集索引了吗,您这样不会是多列主键吧,根本不是非聚集索引
因为你希望数据按你想的方式排序,所以就要加
嗯,我就是这样建的,而且我测试了大数据比如select * from user where openid='asxasxasx'这样,是走到那个非聚集索引的,就是起作用了是吧
否则,只能取决于数据库自己的不同实现而不同了,它是不保证的