Thủ tục tạo chuỗi ký tự ngẫu nhiên trong Mssql

Bạn muốn tạo một chuỗi ký từ ngẫu nhiên như việc tạo Password, Hay tạo mã sản phẩm? Trên hmweb đã có bài tạo chuỗi ngẫu nhiên bằng C#. Bài này tôi giới thiệu thủ tục trong SQL server

Thủ tục được tạo như sau:

CREATE PROCEDURE [dbo].[sp_GeneratePassword]
    @Length int
AS
BEGIN
    DECLARE @RandomID varchar(32)
    DECLARE @counter smallint
    DECLARE @RandomNumber float
    DECLARE @RandomNumberInt tinyint
    DECLARE @CurrentCharacter varchar(1)
    DECLARE @ValidCharacters varchar(255)
    SET @ValidCharacters='ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'
    DECLARE @ValidCharactersLength int
    SET @ValidCharactersLength = len(@ValidCharacters)
    SET @CurrentCharacter = ''
    SET @RandomNumber = 0
    SET @RandomNumberInt = 0
    SET @RandomID = ''
    SET NOCOUNT ON
    SET @counter = 1
    WHILE @counter < (@Length + 1)
    BEGIN
        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, 
            ((@ValidCharactersLength - 1) * @RandomNumber + 1))
        SELECT @CurrentCharacter = 
            SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
        SET @counter = @counter + 1
        SET @RandomID = @RandomID + @CurrentCharacter
    End
    Select @RandomID As Pass
END

Thủ tục này sẽ lấy chuỗi ngâu nhiên bao gồm @Length ký tự từ chuỗi cho trước ABC...YZ1234567890.
Ví dụ bạn muốn lấy chuỗi ngẫu nhiên có độ dài là 8 ký tự bạn thực hiện: Exec sp_GeneratePassword 8 Sẽ sinh ra cho bạn chuỗi '9WAVZ3'

Thủ tục trên chỉ lấy ngẫu nhiên trong dãy ký tự đã có trước. Giờ bạn cải tiến đi một chút để dãy ký tự này cũng được chuyền vào thành tham số như sau:

CREATE  PROCEDURE sGeneratePassword_InputString 
    @Length int,
    @InputString nvarchar(255)
AS
BEGIN
    DECLARE @RandomID varchar(32)
    DECLARE @counter smallint
    DECLARE @RandomNumber float
    DECLARE @RandomNumberInt tinyint
    DECLARE @CurrentCharacter varchar(1)
    DECLARE @InputStringLength int
    SET @InputStringLength = len(@InputString)
    SET @CurrentCharacter = ''
    SET @RandomNumber = 0
    SET @RandomNumberInt = 0
    SET @RandomID = ''
    SET NOCOUNT ON
    SET @counter = 1
    WHILE @counter < (@Length + 1)
    BEGIN
            SET @RandomNumber = Rand()
            SET @RandomNumberInt = Convert(tinyint, ((@InputStringLength - 1) * @RandomNumber + 1))
            SELECT @CurrentCharacter = SUBSTRING(@InputString, @RandomNumberInt, 1)
            SET @counter = @counter + 1
            SET @RandomID = @RandomID + @CurrentCharacter
    End
    Select @RandomID As Pass
END

Giờ bạn chạy thử với lệnh:

 

Exec sGeneratePassword_InputString 8,'abcqwertyuiolkjAHSNKA09876jh&654321'


Biến thứ 2 là tùy ý bạn truyền vào một dãy ký tự tùy thích kết quả minh họa là '60rje52u', Với mỗi lần thực thi thì kết quả sẽ một khác

Hy vọng nó có ích với bạn. Chúc bạn thành công

 

 

Sao lưu tất cả database của SQL Server

Đôi khi nhiều thứ dường như có vẻ phức tạp thì lại đơn giản hơn những gì bạn nghĩ và đây là sức mạnh của việc sử dụng câu lệnh T-SQL để thực hiện những công việc lặp đi lặp lại. Một trong những công việc này có lẽ là việc backup tất cả các cơ sở dữ liệu...

Đôi khi nhiều thứ dường như có vẻ phức tạp thì lại đơn giản hơn những gì bạn nghĩ và đây là sức mạnh của việc sử dụng câu lệnh T-SQL để thực hiện những công việc lặp đi lặp lại. Một trong những công việc này có lẽ là việc backup tất cả các cơ sở dữ liệu trong server của bạn. Điều này không có gì lớn nếu như bạn chỉ có một vài cơ sở dữ liệu, nhưng có những server chứa tới hàng trăm cơ sở dữ liệu trên một instance của SQL Server thì vấn đề bắt đầu nảy sinh. Bạn có thể s�� dụng tiện ích Enterprise Manager để backup các cơ sở dữ liệu, hoặc thậm chí dùng Maintenance Plans để lên kế hoạch bảo trì, songT-SQL vẫn cho phép bạn thực hiện dễ dàng và nhanh chóng hơn nhiều.

Với việc sử dụng của T-SQL bạn có thể tạo ra các lệnh dự phòng của bạn và với việc sử dụng con trỏ, bạn có thể trỏ qua tất cả các cơ sở dữ liệu của bạn để backup chúng từng cái một. Đây là một quá trình thẳng tiến và bạn chỉ cần một số ít các lệnh để làm điều này.

Đây là script cho phép bạn sao lưu mỗi cơ sở dữ liệu trong instance SQL Server của bạn. Bạn cần thay đổi biến @path để dẫn tới thư mục dự phòng phù hợp và các tập tin sao lưu sẽ đưa vào tên có dạng "DBnameYYYDDMM.BAK".

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 
SET @path = 'C:\Backup\'  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  
       FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor
Trong script này chúng ta bỏ qua các cơ sở dữ liệu hệ thống, nhưng những cơ sở dữ liệu này cũng có thể đưa vào dễ dàng như vậy. Bạn cũng có thể thay đổi scropt này thành một thủ tục được lưu trữ và truyền vào tên cơ sở dữ liệu hoặc nếu để là NULL thì nó sẽ sao lưu tất cả các cơ sở dữ liệu.

Thêm Dữ Liệu Mới Vào Bảng Dùng LEFT JOIN

Giả sử bạn có hai bảng TableA và TableB có cùng cấu trúc gồm 3 cột như sau: ID int primary key, Ten nvarchar(100), Diachi nvarchar(200); Giờ bạn muốn INSERT dữ liệu vào bảng TableA từ bảng TableB cho các dòng vốn chưa có trong bảng TableA. Bạn có thể làm thế này:

INSERT dbo.TableA(ID, Ten, Diachi)
SELECT b.ID, b.Ten, b.Diachi
FROM TableB b
LEFT JOIN TableA a ON a.ID = b.ID
WHERE a.ID IS NULL
Giải thích: Câu lệnh SELECT dùng LEFT JOIN từ TableB tới TableA với điều kiện WHERE a.ID IS NULL sẽ lọc ra các bản ghi trong TableB mà không có trong TableA. Kết quả của lệnh SELECT này được dùng để INSERT vào TableA. Vì SQL Server thực hiện câu lệnh theo tập, nên lệnh SELECT chỉ được thực hiện 1 lần và các bản ghi tìm được sẽ được dùng cho INSERT (không phải là SELECT được 1 bản ghi rồi INSERT, rồi quay lại SELECT tiếp bản ghi thứ hai rồi lại INSERT…).

Kiểm Tra Bản Ghi Tồn Tại Với IF EXISTS trong MSSQL

Trong một thủ tục, bạn muốn kiểm tra xem có bản ghi nào thỏa mãn một số điều kiện nhất định trong bảng hay không, và rẽ nhánh chương trình tùy theo kết quả kiểm tra. Bạn hãy làm thế này:

IF EXISTS(SELECT 1 FROM TblName WHERE ...)
BEGIN
...
END
ELSE
BEGIN
...
END
Đây là cách làm tối ưu, vì đoạn lệnh “EXISTS(SELECT 1 FROM TblName WHERE …)” sẽ dừng lại ngay sau khi tìm được bản ghi đầu tiên; “SELECT 1″ cũng tránh cho hệ thống khỏi phải đọc dữ liệu từ bảng. Một cách khác có thể làm là:
SELECT TOP 1 1 FROM TblName WHERE...
IF @@ROWCOUNT > 0
BEGIN
...
END
ELSE
BEGIN
...
END
Tuy nhiên các cách sau đây thì tuyệt đối nên tránh:

Cách 1: KHÔNG NÊN DÙNG

IF (SELECT COUNT(*) FROM TblName WHERE...) > 0
BEGIN
...
END
ELSE
BEGIN
...
END

 Cách 2: tệ hơn nữa, KHÔNG NÊN DÙNG

SELECT * FROM TblName WHERE...
IF @@ROWCOUNT > 0
BEGIN
...
END
ELSE
BEGIN
...
END
Lý do: Cả hai cách đều thực hiện các thao tác vượt quá cần thiết, chỉ cần bản ghi đầu tiên tìm được là đủ để chương trình đi tiếp được rồi. Việc đếm số bản ghi (cách 1) hoặc trả lại tất cả các bản ghi (cách 2) làm kéo dài thời gian thực hiện đoạn lệnh. Trong nhiều trường hợp, để tìm ra bản ghi đầu tiên có thể thực hiện rất nhanh, nhưng để tìm ra toàn bộ các bản ghi lại mất một thời gian gấp nhiều lần.

Các Kiểu Backup Trong SQL Server

Khái niệm backup (sao lưu) và restore (khôi phục) chắc hẳn đã quen thuộc đối với đa số chúng ta: bạn thường xuyên backup, ví dụ copy toàn bộ thư mục sang một thiết bị lưu trữ khác, để phòng khi gặp sự cố gây mất mát dữ liệu thì có thể copy ngược trở lại. Với database thì việc backup diễn ra có khác, khi hệ thống đang vận hành thì bạn không thể đơn giản copy các data file và log file vì chúng bị khóa hoàn toàn. Bạn phải dựa vào cơ chế backup của hệ QTCSDL. SQL Server cung cấp ba loại backup như sau:

· Full backup: backup toàn bộ dữ liệu tại thời điểm đó. Đây có lẽ là loại được dùng thường xuyên nhất.
· Differential backup: backup các trang dữ liệu mới được cập nhật kể từ lần full backup trước đó.
· Transaction log backup: backup các log record hiện có trong log file, nghĩa là nó sao lưu các hành động (các thao tác xảy ra đối với database) chứ không sao lưu dữ liệu. Đồng thời nó cũng cắt bỏ (truncate) log file, loại bỏ các log record vừa được backup ra khỏi log file. Vì thế khi thấy log file tăng quá lớn, có nhiều khả năng là bạn chưa từng backup transaction log bao giờ.

Một nguyên tắc chung để giảm bớt lượng dữ liệu mất mát khi có sự cố là tăng tần suất backup. Tuy nhiên với một database có dung lượng lớn và được cập nhật liên tục, thì việc thực hiện full backup với tần suất cao là không khả thi, vì nó dùng rất nhiều CPU và I/O. Nhờ có differential backup và transaction log backup, bạn có thể tạo lập các phương án sao lưu thích hợp, đảm bảo dữ liệu được backup thường xuyên hơn mà không chiếm nhiều tài nguyên của hệ thống. Ví dụ, bạn có thể thực hiện:

· Full backup: một lần mỗi ngày vào 2h sáng.

· Differential backup: vào các thời điểm 6h, 10h, 14h, 18h, 22h (5 lần/ngày).

· Transaction log backup: 15 phút một lần vào các thời điểm 5′, 20′, 35′, và 50′ của mỗi giờ (4 lần/giờ). Chọn thời điểm như vậy để đảm bảo nó xảy ra sau differential backup.

Lưu ý là differential backup luôn sao lưu các trang đã thay đổi kể từ lần full backup trước (trong ví dụ trên là các trang đã thay đổi kể từ 2h), chứ không phải từ lần differential backup trước đó. Vì thế bản backup lúc 10h sẽ bao gồm các trang lưu trong bản lúc 6h, bản 14h gồm các trang đã có trong bản 10h… Transaction log backup thì ngược lại, chỉ sao lưu các log record kể từ lần transaction log backup trước đó.

Giả sử database bị hỏng vào thời điểm 10h55′, bạn cần khôi phục lại database theo trình tự sau:

Bước 1. Khôi phục từ bản full backup gần với thời điểm có sự cố nhất (bản full backup lúc 2h).

Bước 2. Khôi phục từ bản differential backup gần với thời điểm có sự cố nhất (bản lúc 10h).

Bước 3. Khôi phục tất cả các transaction log backup kể từ sau lần diferential backup gần đây nhất, lần lượt theo trình tự thời gian. Đó là các bản tại các thời điểm 10h5′, 1oh20′, 10h35′, và 10h50′.

Bước 1 và 2 đưa database trở lại trạng thái giống như lúc 10h. Ở bước 3, với mỗi lần khôi phục transaction log thì các thao tác chứa trong đó được đem ra thực hiện lại trên database (gọi là log forwarding) và do đó đưa nó về trạng thái gần hơn thời điểm xảy ra sự cố. Như vậy sau khi hoàn tất khôi phục bốn bản transaction log backup thì database sẽ ở vào trạng thái giống như lúc 10h50′. Tuy nhiên các thay đổi diễn ra trong 5 phút sau đó (từ 10h50′ đến 10h55′) đã vĩnh viễn bị mất.

Trong trường hợp may mắn hơn, khi sự cố xảy ra mà log file vẫn còn nguyên vẹn, bạn sẽ có cơ hội đưa database trở lại trạng thái ngay trước khi có sự cố, và do đó không có mất mát dữ liệu. Việc đầu tiên bạn cần làm là thực hiện ngay transaction log backup (nên nhớ, không được vội vàng khôi phục từ bản full backup). Sau đó các bước tiếp theo sẽ tương tự như trên:

Bước 0.
Sao lưu transaction log.

Bước 1. Khôi phục từ full backup file của sáng sớm hôm đó.

Bước 2. Khôi phục từ differential backup file lúc 10h.

Bước 3. Khôi phục các transaction log backup file kể từ sau 10h, lần lượt theo trình tự thời gian: các bản backup vào lúc 10h5′, 1oh20′, 10h35′, 10h50′, và cuối cùng bản lúc 10h55′ (vừa thực hiện ở bước 0).

Trong bài sau tôi sẽ giới thiệu script thực hiện từng bước qua một ví dụ cụ thể.