由于搜索引擎越来越强大,甚至于如果你的网页没有被搜索引擎搜索到,那么你的网页将深深的埋在80多亿的网页之下!
因此,越来越多的网页都执行了URL重写,以达到URL友好,搜索引擎友好!所以构建一个用户友好的主键ID利于url重写时进行更好的分析!
以下是我提供的一个sql存储过程,可以按年月日生成用户友好的主键ID。欢迎指点!
/*
生成一个按年月日连续的ID
(基于特定表的,只需要设置[Application.TableKey].TableID
及最后生成@NextID时把TableID的值嵌进去即可)
如果表名在表[Application.TableKey]不存在,则自动创建一条记录
生成的ID形如:
200605151(2006年5月15日的第一条记录)
2006051510(2006年5月15日的第十条记录)
200605161(2006年5月16日的第一条记录)
2006061610(2006年6月16日的第十条记录)
*/
![](/Images/OutliningIndicators/None.gif)
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Application.spGetNextID')
BEGIN
DROP Procedure [Application.spGetNextID]
END
![](/Images/OutliningIndicators/None.gif)
GO
![](/Images/OutliningIndicators/None.gif)
CREATE Procedure [Application.spGetNextID]
@TableName nvarchar(50),
@NextID bigint=null output
AS
set nocount on
declare @Year int
declare @Month int
declare @Day int
declare @SerialNumber int
select @Year=year(getdate()),@Month=month(getDate()),@Day=day(getdate()),@SerialNumber=0
if not exists( select 1 from [Application.TableKey] where TableName=@TableName )
begin
INSERT INTO [Application.TableKey]
(TableName,Year, Month,Day, SerialNumber)
VALUES (@TableName,@Year,@Month,@Day,@SerialNumber)
--update [Application.TableKey] set TableID=@@Identity where TableName=@TableName
end
else
begin
select @SerialNumber=isnull(SerialNumber,0) from [Application.TableKey]
where TableName=@TableName and year=@Year and month=@Month and Day=@Day
end
select @SerialNumber=@SerialNumber+1
update [Application.TableKey] set
year=@Year,month=@Month,day=@Day,SerialNumber=@SerialNumber
where TableName=@TableName
select @NextID = (@Year*POWER(10,4) + @Month*POWER(10,2) + @Day)*power(10,len(@SerialNumber)) + @SerialNumber
使用的表
/****** 对象: Table [dbo].[Application.TableKey] 脚本日期: 05/16/2006 22:57:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Application.TableKey](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[TableID] [int] NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Day] [int] NULL,
[SerialNumber] [int] NULL
) ON [PRIMARY]
因此,越来越多的网页都执行了URL重写,以达到URL友好,搜索引擎友好!所以构建一个用户友好的主键ID利于url重写时进行更好的分析!
以下是我提供的一个sql存储过程,可以按年月日生成用户友好的主键ID。欢迎指点!
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
使用的表
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)