SQL Server Date Formats

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM
Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘) AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘) AS [YYYY-MM-DD]
1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] 08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 2006 1
Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD 1 SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] 12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] 92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY] Sep-02 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY] 25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function.

T SQL Query Ultilities

— Find all Column not in Table

select name from sys.tables
where name not in
(
SELECT t.name 
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE 'IsDeleted'
)

— Deleting all duplicate rows but keeping one [duplicate]

WITH cte AS (
  SELECT FirstName, 
     row_number() OVER(PARTITION BY FirstName ORDER BY FirstName) AS [rn]
  FROM Client
)
DELETE cte WHERE [rn] > 1

— Adding a column to all user tables in T-SQL

exec sp_msforeachtable 'alter table ? add IsDeleted bit not null default 0';

--- Adding a column to all user table if not exists
EXEC sp_msforeachtable '
if not exists (select name from sys.tables
where name not in(SELECT t.name FROM sys.columns c
				JOIN sys.tables t ON c.object_id = t.object_id
				WHERE c.name LIKE ''IsDeleted'')
) 
begin
    ALTER TABLE ? ADD IsDeleted bit NOT NULL DEFAULT 0;
end';

 

SQL Server CONVERT() Function

Definition and Usage

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)

 

Value Description
data_type(length) Specifies the target data type (with an optional length)
expression Specifies the value to be converted
style Specifies the output format for the date/time (see table below)

The style value can be one of the following values:

Without century With century Input/Output Standard
0 or 100 mon dd yyyy hh:miAM (or PM) Default
1 101 1 = mm/dd/yy
101 = mm/dd/yyyy
USA
2 102 2 = yy.mm.dd
102 = yyyy.mm.dd
ANSI
3 103 3 = dd/mm/yy
103 = dd/mm/yyyy
British/French
4 104 4 = dd.mm.yy
104 = dd.mm.yyyy
German
5 105 5 = dd-mm-yy
105 = dd-mm-yyyy
Italian
6 106 6 = dd mon yy
106 = dd mon yyyy
7 107 7 = Mon dd, yy
107 = Mon dd, yyyy
8 108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default + millisec
10 110 10 = mm-dd-yy
110 = mm-dd-yyyy
USA
11 111 11 = yy/mm/dd
111 = yyyy/mm/dd
Japan
12 112 12 = yymmdd
112 = yyyymmdd
ISO
13 or 113 dd mon yyyy hh:mi:ss:mmm (24h) Europe default + millisec
14 114 hh:mi:ss:mmm (24h)
20 or 120 yyyy-mm-dd hh:mi:ss (24h) ODBC canonical
21 or 121 yyyy-mm-dd hh:mi:ss.mmm (24h) ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
126 yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601
127 yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) ISO8601 with time zone Z
130 dd mon yyyy hh:mi:ss:mmmAM Hijiri
131 dd/mm/yy hh:mi:ss:mmmAM Hijiri

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2014 11:45 PM
11-04-14
11-04-2014
04 Nov 14
04 Nov 2014
04 Nov 2014 11:45:34:243

How To Repair A Suspect Database In MSSQL

How To Repair A Suspect Database In MS SQL

Issue
You have a database in MS SQL that is tagged as (suspect) and you are unable to connect to the database.

Possible Causes

  • The database could have become corrupted.
  • There is not enough space available for the SQL Server to recover the database during startup.
  • The database cannot be opened due to inaccessible files or insufficient memory or disk space.
  • The database files are being held by operating system, third party backup software etc.
  • There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

Resolution
These steps require you to have Microsoft SQL Server Management Studio installed on your computer.  If you do not have this installed please follow the steps outlined in the following article: How To Connect To Your MS SQL Database

Always back up the website before making any changes to the database . Shared hosting customers can do this through the Control Panel. Refer to Back Up Your Website Using Plesk. Dedicated server customers can back up the site either through the Control Panel, or through the Control Suite. Refer to How to Back Up a Domain Using Control Suite.

  1. Open Microsoft SQL Server Management Studio and connect to your database
  2. Click the New Query button
  3. Paste the following SQL script into your New Query page replacing [YourDatabase] with the name of your database.
    EXEC sp_resetstatus [YourDatabase];
    ALTER DATABASE [YourDatabase] SET EMERGENCY
    DBCC checkdb([YourDatabase])
    ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
    ALTER DATABASE [YourDatabase] SET MULTI_USER
  4. Click Execute

Your database should no longer be tagged as (suspect) and you should be able to access it.

How to Install SQL Server Management Studio

SQL Server Management Studio (SSMS) is the official and preferred client user interface which can be used to manage, configure, deploy, upgrade and administer a SQL Server instance. The tool interface is quite user friendly and comprehensive. It is shipped with every SQL Server version and is regularly updated and enhanced. If you are planning on learning how to use SQL Server from scratch, consider using SQL Server Management studio as a first step on your journey to learning SQL Server.

Why is this installation so important? Like any other technology, in SQL Server too we need to focus on theoretical knowledge as well as hands-on practice and in order to achieve hands-on practice, SQL Server Management Studio (SSMS) is essential.

In this SQL Server Management Studio tutorial, I will demonstrate how to install SQL Server Management Studio (SSMS) in 12 easy steps. This tutorial will help you to perform the installation process of the SQL Server Management Studio (SSMS).

Step 1:

First of all you need to download SQL Server Management Studio (SSMS) installation files (SQLManagementStudio_x64_ENU.exe / SQLManagementStudio_x86_ENU.exe) from the SQL Server download page depending on your server type (x64,x86), and keep it in a separate folder.

Figure illustrating the SQL Server Management Studio download page

Step 2:

Once you downloaded the respective file as per your server type, you need to execute it. It will then take you to the first screen namely SQL Server Installation Center as shown below. This is the primary installation screen of SQL Server. Other SQL server tools installations can be launched from here as well. Once you are on this screen, you need to select “New SQL Server stand-alone installation or add features to an existing installation” to proceed with the installation.

Selecting New SQL Server stand-alone installation or add features to an existing installation from the SQL Server Installation Center

Step 3:

Once you select the “New SQL Server stand-alone installation or add features to an existing installation” option, it will check the setup rules (pre-requisites) on the server and take you to the license terms screen. The license terms must be read and accepted like with any other application’s license terms. Please note that your server must pass the setup rules in order to proceed with the installation.

SQL Server 2012 Setup - accepting license terms

Step 4:

Once you accept the license terms, it is time to scan all the available product updates. The required updates, size and details will be displayed. However, if you need further details, you can select More Information which will take you to the support page where you will find all the details related to the updates. You can ignore these updates by deselecting the ‘Include SQL Server product updates’ option at this stage.

I highly recommend that you download these updates along with the installation. This step might take some time depending on the size of the update files.

SQL Server product updates dialog

Step 5:

The next step is the Install Setup Files step, where SQL Server Management Studio (SSMS) setup will download, extract and install all necessary setup files to your server.

Figure illustrating the Install Setup Files step

Step 6:

Once SQL Server Management Studio (SSMS) setup is done with the install setup files, it verifies the setup support rules in order to proceed. It then takes you to the feature selection screen. This is a very comprehensive screen which has detailed information about each feature. When we run the SQL Server Management Studio (SSMS) installation, it selects Management Tools by default, so you do not need to select anything here. In addition you can select Management tools – Basic. To get more information on this feature you can view the detailed feature description on right hand side.

This screen also gives you the ability to select and deselect all features in one go. This saves lots of time when you need to install multiple features. It also provides detailed information about the prerequisites, the selected features, which prerequisites have already been installed and which will be installed through this installation. You just need to click on the Next button to continue.

Figure illustrating Feature Selection step in SQL Server 2012 setup

Step 7:

In this step, SQL Server Management Studio (SSMS) setup will check the installation rules (pre-requisite for the SSMS). Just click on the Next button to continue.

Figure illustrating the Installation Rules step in SQL Server 2012 Management Studio Setup

Step 8:

In this step SQL Server Management Studio (SSMS) setup will verify the disk space. Please make sure that you have sufficient disk space available. Not having sufficient disk space may result in a faulty installation. Just click on the Next button to continue.

Figure illustrating the Disk Space Requirements step in SQL Server Management Studio 2012 Setup

Step 9:

In this step, you have the opportunity to decide whether or not to send the error notifications to Microsoft. This is highly recommended in order to help Microsoft improve future releases and to fix any bugs in the existing release.

Figure illustrating SQL Server 2012 Setup step where you decide whether or not to send the error notifications to Microsoft

Step 10:

Once you click on the Next button, setup checks the installation configuration rules and if it passed, setup continues. This step will take some time to install SQL Server Management Studio. You can sit back and relax :)

Figure illustrating the Installation Progress in SQL Server Management Studio 2012 Setup

Step 11:

This step displays the installation status of SQL Server Management Studio (SSMS) along with each feature, like a summary. This screen really helps to view what has been installed and what has not. In case one of the features could not be installed, you can re-run the same installation procedure and install that particular feature.

Figure illustrating what has been installed and what has not in SQL Server Management Studio setup

Step 12:

In order to check whether SQL Server Management Studio (SSMS) has been installed successfully, you need to select start menu of your server and then further select the SQL Server 2012 menu. You will find the link to SQL Server Management Studio there.

Link to SQL Server Management Studio there

Alternate Option:

Please note that above installation procedure is for a standalone installation of SQL Server Management Studio (SSMS). However you can install SQL Server Management Studio (SSMS) along with the SQL Server installation as well, just ensure that you have selected Management Tools in the feature screen as shown below.

Alternate option - choosing toinstall SQL Server Management Studio (SSMS) along with the SQL Server installation

Conclusion:

As you can see, the SQL Server Management Studio (SSMS) installation is not complicated at all, it is quite user friendly. You just need to read the instructions carefully and follow them accurately, for a smooth installation.

Useful resources:
SQL Server Management Studio (SSMS) Basics
Step-by-Step: Installing SQL Server Management Studio 2008 Express after Visual Studio 2010
SQL Server download page

– See more at: http://www.sqlshack.com/sql-server-management-studio-step-step-installation-guide/#sthash.ErnTqH07.dpuf

Sql Scripts – Delete all Tables, Procedures, Views and Functions

Trong môi trường chia sẻ MSSQL thường bạn không có quyền xóa một CSDL để tạo lại khi thấy nó không phù hợp. Lúc đó chúng ta thường ngay lập tức nghĩ đến giải pháp xóa hết các bảng (table), thủ tục (stored procedure)… để tạo lại dữ liệu mới. Trong trường hợp table, stored procedure của bạn quá nhiều (hàng trăm table chẳng hạn)  thì điều đó thật mất thời gian & nhàm chán.

Thật may mắn, MS cung cấp cho chúng ta những dòng lệnh trong Query để thực hiện xóa một lần toàn bộ table hay stored procedure một cách nhanh chóng & sạch sẽ.

Delete All Tables

--Delete All Keys

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

Delete All Stored Procedures

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'p'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName <> 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

Delete All Views

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'v'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop view ' + @procName)
                  fetch next from cur into @procName
      end
close cur
deallocate cur

 

Delete All Functions

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'fn'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop function ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur
 Chúc các bạn thành công!

Sử dụng CURSOR trong MS-SQL Server

1.  Khi nào dùng?

– Khi cần duyệt qua từng dòng dữ liệu của một bảng để thao tác với chúng.

-Chỉ dùng khi không còn cách nào khác để cho ra kết quả tương tự, hoặc trong khả năng của bạn, chưa thể tìm cách giải quyết, chỉ chiếm dưới 5% thôi, tùy trường hợp cụ thể,  còn hầu hết các trường hợp còn lại, đều có thể sử dụng các câu lệnh T-SQL khác để xử lý và nên sử dụng cách khác vì vấn đề tốc độ thực thi của cursor trong hầu hết trường hợp đều chậm hơn vì chúng thường sử dụng nhiều tài nguyên SQL Server.

-Hãy cân nhắc khi câu lệnh của bạn tác động đến bảng có số lượng lớn records, tốt nhất là khi bạn chắc chắn là bạn tác động đến không nhiều dữ liệu hiện tại cũng như sự phát triển số lượng dữ liệu sau này và không ảnh hưởng đáng kể đối với tốc độ của ứng dụng khi thực hiện tác vụ nào đó.

2. Dùng như thế nào?

Nhìn vào ví dụ dưới, các phần in đậm là những đoạn code cần thiết khi ta làm việc với CURSOR:

ALTER PROC up_BangKeToKhaiNhapKhauDuaVaoThanhKhoan(
	@LanThanhLy bigint,      
	@NamThanhLy int,      
	@MaDoanhNghiep varchar(50)) 
AS   
BEGIN 
	--
	DECLARE @BKToKhaiNhapDVTK  TABLE  
	(   
	 SoToKhai NVARCHAR(255),  
	 TenNPL NVARCHAR(1000), 
	 DVT VARCHAR(50), 
	 LuongNK numeric(18,8)      
	)  
	--
	DECLARE @soTK bigint --(1)
	DECLARE cs_DSTK CURSOR FOR (SELECT * FROM ufn_LayDSToKhaiDuaVaoThanhKhoan(@LanThanhLy))--(2)
	OPEN cs_DSTK --(3)
	FETCH NEXT FROM  cs_DSTK INTO @soTK --(4)
	WHILE @@FETCH_STATUS = 0 --(5)
	BEGIN
		INSERT INTO @BKToKhaiNhapDVTK			
		SELECT dbo.ufn_LaySoToKhai(dbo.ufn_LaySoToKhaiVnaccTuV4(@soTK)),				
				dbo.ufn_LayTenHangChoBaoCaoDanhSachToKhaiNhapDuaVaoThanhKhoan(@soTK, MaNPL, TenNPL,DonGiaTT, Luong),
				TenDVT_NPL, 
				Luong
		FROM ufn_LayDanhSachHangCuaToKhaiNhapDuaVaoThanhKhoan(@LanThanhLy, @NamThanhLy, @MaDoanhNghiep, @soTK)
		FETCH NEXT FROM cs_DSTK INTO @soTK --(6)
	END
	CLOSE cs_DSTK --(7)
	DEALLOCATE cs_DSTK--(8)

	SELECT * FROM @BKToKhaiNhapDVTK
END
GO

Bạn chú ý các đánh dấu số ở đoạn code trên.  (hình đoạn code trên để bạn dễ theo dõi khi đọc bài biết)

(1) Khai báo một hoặc nhiều  ‘scalar variable’, bạn dùng các biến này để thao tác với từng dòng dữ liệu được duyệt qua, các thông tin của dòng dữ liệu đang duyệt sẽ được gán vào các biến này.

Nếu cần khai báo nhiều biến, bạn có thể khai báo từng dòng, hoặc gộp luôn như  ví dụ sau:

DECLARE @soTK BIGINT, @tenTK NVARCHAR(255)

-Trường hợp bạn thao tác luôn với cả dòng dữ liệu, ví dụ INSERT tất cả dữ liệu vào một bảng, thì không cần phải khai báo.

(2)Khai báo CURSOR

DECLARE cs_DSTK CURSOR FOR (SELECT * FROM ufn_LayDSToKhaiDuaVaoThanhKhoan(@LanThanhLy))

Chi tiết phần này thì khá dài, bạn có thể tham khảo ở microsoft library, hoặc nếu tiếng Anh không tốt thì vào slideshare từ slide 9 đến 16, nội dung từ các trang này khá đầy đủ.

Một số lưu ý:

-Sau từ khóa DECLARE là tên biến cursor, thường bắt đầu bằng ‘cs_’ hoặc ‘cursor’ để phân biệt loại biến.

-Sau tờ khóa FOR là câu lệnh SELECT, bạn chỉ lấy các trường cần thao tác trong bảng dữ liệu được chọn, hoặc SELECT * nếu tất cả các trường đều cần thiết cho việc xử lý.

(3) Mở cursor bạn vừa khai báo.

OPEN [<tencursor>]

(4)Đọc qua dữ liệu từng dòng

FETCH NEXT FROM cs_DSTK INTO @soTK

Lưu ý sau câu lệnh INTO là danh sách các biến mà khi đọc một dòng, dữ liệu các trường sẽ được gán tuần tự vào.

Ví dụ khi bạn khai báo CURSOR như sau:

DECLARE cs_DSTK CURSOR FOR (SELECT SoToKhai, TenToKhai FROM ufn_LayDSToKhaiDuaVaoThanhKhoan(@LanThanhLy))

thì sau khi mở OPEN cursor, có thể lấy dữ liệu từng dòng như sau:

FETCH NEXT FROM cs_DSTK INTO @soTK,@tenTK

(5) Thao tác với dòng dữ liệu đang  được chọn

WHILE @@FETCH_STATUS = 0 
BEGIN
  <code xử lý>
  FETCH NEXT FROM cs_DSTK INTO @soTK  
END

(Bạn có biết) sau @ là khai báo biến tự định nghĩa, còn sau @@ là tên biến hệ thống, nó sẽ hiển thị màu hồng.

Trong phần <code xử lý> bạn có thể thao tác xử lý theo mục đích của bạn, thêm, sửa, xóa lên các bảng có dự liệu liên quan, hoặc xử lý với bảng tạm nào đó,v..v.

Ví dụ như trên, mình đang tạo một báo cáo với dữ liệu được lấy từ nhiều function khác nhau với tham số hàm có @soTK được đọc lần lượt từ các dòng dữ liệu trong cursor. Các dữ liệu này được INSERT vào một biến bảng (table variable) tên @BKToKhaiNhapDVTK, đó là dữ liệu ta cần lấy.

(6) Đọc dòng tiếp theo, tương tự (4)

FETCH NEXT FROM cs_DSTK INTO @soTK

(7) Giải phóng dữ liệu tham chiếu bên trong CURSOR

CLOSE cs_DSTK

(8) Giải phóng CURSOR ra khỏi bộ nhớ.

DEALLOCATE cs_DSTK

Đến đây mới thực sự hủy cursor, bất kỳ tham chiếu đến tên cursor đã hũy đều gây ra lỗi.

3. Tổng kết.

-Bạn chỉ nên sử dụng CURSOR khi không thấy phương pháp nào khả dĩ hơn, cân nhắc kĩ về tốc độ thực thi của ứng dụng khi sử dụng nó.

-Cách sử dụng của CURSOR khá đơn giản

+Khai báo cursor với một SELECT <các trường cần lấy> trong một bảng nào đó

+Đọc qua từng dòng dự liệu , gán tuần tự cho các biến đã khai báo nếu thấy cần thiết

+Thao tác với dữ liệu đọc được từng dòng

+Đọc dòng tiếp theo

+Khi đọc hết thì giải phóng các dòng dữ liệu tham chiếu và giải phóng cursor.

4. Các vấn đề mở rộng.

Bài tiếp theo, mình sẽ bàn về các phương pháp giúp thay thế cursor bằng các T-SQL query .
–================================ HAPPY READING & HAPPY LEARNING ===============================– 
–==========================================****tieunai****===========================================– 

Insert hình ảnh vào Sql Server trong C#

Insert hình ảnh vào Sql Server trong C#
Insert hình ảnh vào Sql Server trong C#

Khi làm việc với các ứng dụng quản lý trên C# như ứng dụng quản lý sinh viên, quản lý nhân viên, … chúng ta có nhiều tình huống cần lưu trữ hình ảnh, ví dụ như là hình ảnh của sinh viên, nhân viên… Làm sao chúng ta giải quyết vấn đề đó?
Các giải pháp nào để lưu trữ hình ảnh. Nếu muốn lưu hình ảnh đó vào Cơ sở dữ liệu Sql Server thì làm thế nào? Làm sao để hiển thị chúng ra ngoài ứng dụng?

Video Insert hình ảnh vào Sql Server trong C#

Tóm tắt Video

  • Các loại lưu trữ hình ảnh. Ưu và nhược điểm?
  • Tạo Cơ sở dữ liệu bảng Student (lưu thông tin sinh viên)
  • Tạo dự án WindowForm mới
  • Tạo form thêm sinh viên
  • Tạo DataGridview load danh sách sinh viên
  • Code xử lý chuyển ảnh thành byte.
  • Code xử lý chuyển byte thành ảnh
  • Code xử lý dữ liệu, (select, insert….)
Nếu bạn đã hoàn thành theo hướng dẫn mà vẫn gặp khó khăn, hãy tải mã nguồn ứng dụng tại đây:  Download

Cảm ơn bạn đọc và mình luôn đánh giá cao phản hồi của bạn.

Categories and Subcategories

The adjacency model

The fundamental structure of the adjacency model is a one-to-many relationship between a parent entry and its child entries. As with any one-to-many relationship, the child entries carry a foreign key to their parent. What makes the adjacency model different is that the parent and child entries are both stored in the same table.

create table categories
( id       integer     not null  primary key 
, name     varchar(37) not null
, parentid integer     null
, foreign key parentid_fk (parentid) 
      references categories (id)
);

Here’s some sample data that might populate this table, and we should be able to get an idea of the parent-child relationships (if not grasp the entire hierarchy) just by looking at the data:

id name parentid
1 animal NULL
2 vegetable NULL
3 mineral NULL
4 doggie 1
5 kittie 1
6 horsie 1
7 gerbil 1
8 birdie 1
9 carrot 2
10 tomato 2
11 potato 2
12 celery 2
13 rutabaga 2
14 quartz 3
15 feldspar 3
16 silica 3
17 gypsum 3
18 hunting 4
19 companion 4
20 herding 4
21 setter 18
22 pointer 18
23 terrier 18
24 poodle 19
25 chihuahua 19
26 shepherd 20
27 collie 20

Terms commonly used with the adjacency model include tree, root, node, subtree, leaf, path, depth and level. There can be one or more trees in the table, and the parent foreign key is NULL for each tree’s root node. A root node is therefore at the “top” of its tree. A node is any entry, while a leaf is any node that has no children, i.e. for which there exists no other node having that node as its parent. A subtree is the portion of the tree “under” any node. The depth of a subtree is the maximum number of levels of subtree beneath that node. These may not be official terminology definitions, but they work for me.

Why is it called a tree when it grows down from the “root” which is at the top? Mere convention.

Now let’s see how a tree or hierarchy can be used to implement a category/subcategory structure.

Working with categories and subcategories

Using the adjacency model to implement categories and subcategories can be reduced to two simple steps:

  1. manage the hierarchical data
  2. display the hierarchical data

Managing the hierarchy is nothing special. Just look again at the table layout. There’s a primary key column (id) and a foreign key referencing it (parentid). Other than that, it’s a dead simple table. Use INSERT, UPDATE, and DELETE as with any other table. Whether we actually declare the foreign key on parentid, which is necessary for referential integrity, is secondary to the basic design. (Referential integrity means that the parent row should exist before the child row referencing it is inserted, and so on. See the article Relational Integrity in the Resources below.)

Displaying the hierarchy is challenging, but not difficult. Categories and subcategories can be handled in HTML in many ways. Current best practice is to use nested unordered lists. For further information, see Listamatic: one list, many options in the Resources below.

Displaying all categories and subcategories: site maps and navigation bars

To display the hierarchy, we must first retrieve it. The following method involves using as many LEFT OUTER JOINs as necessary to cover the depth of the deepest tree. For our sample data, the deepest tree has four levels, so the query requires four self-joins. Each join goes “down” a level from the node above it. The query begins at the root nodes.

select root.name  as root_name
     , down1.name as down1_name
     , down2.name as down2_name
     , down3.name as down3_name
  from categories as root
left outer
  join categories as down1
    on down1.parentid = root.id
left outer
  join categories as down2
    on down2.parentid = down1.id
left outer
  join categories as down3
    on down3.parentid = down2.id
 where root.parentid is null
order 
    by root_name 
     , down1_name 
     , down2_name 
     , down3_name

Notice how the WHERE clause ensures that only paths from the root nodes are followed. This query produces the following result set:

root_name down1_name down2_name down3_name
animal birdie NULL NULL
animal doggie companion chihuahua
animal doggie companion poodle
animal doggie herding collie
animal doggie herding shepherd
animal doggie hunting pointer
animal doggie hunting setter
animal doggie hunting terrier
animal gerbil NULL NULL
animal horsie NULL NULL
animal kittie NULL NULL
mineral feldspar NULL NULL
mineral gypsum NULL NULL
mineral quartz NULL NULL
mineral silica NULL NULL
vegetable carrot NULL NULL
vegetable celery NULL NULL
vegetable potato NULL NULL
vegetable rutabaga NULL NULL
vegetable tomato NULL NULL

Each row in the result set represents a distinct path from a root node to a leaf node. Notice how the LEFT OUTER JOIN, when extended “below” the leaf node in any given path, returns NULL (representing the fact that there was no node below that node, i.e. satisfying that join condition).

As we can see, this result set contains all our original categories and subcategories. If the categories and subcategories are being displayed on a web site, this query can therefore be used to generate the complete site map. An abbreviated query, that goes down only a certain number of levels from the roots, regardless of whether there may be nodes at deeper levels, can be used for the site’s navigation bar.

We can display this sample data using nested unordered lists like this:

  • animal
    • birdie
    • doggie
      • companion
        • chihuahua
        • poodle
      • herding
        • collie
        • shepherd
      • hunting
        • pointer
        • setter
        • terrier
    • gerbil
    • horsie
    • kittie
  • mineral
    • feldspar
    • gypsum
    • quartz
    • silica
  • vegetable
    • carrot
    • celery
    • potato
    • rutabaga
    • tomato

What’s the easiest way to transform the result set into the nested ULs? In ColdFusion, we use nested CFOUTPUT tags, with the GROUP= parameter on all but the innermost list. Very straightforward indeed. In other scripting languages, as the saying goes, your mileage may vary. Take comfort in the fact that once you’ve coded it, you will never have to change your site map page again.

What if the hierarchy is more than, say, three or four levels deep? What if it’s fifteen levels deep? My response to this question is threefold.

First, a query with fifteen self-joins may be a little more tedious to code but most assuredly will not present any difficulty to your database engine.

Second, in certain databases such as Oracle and DB2, recursion is built in, so you can go as many levels deep as you wish—although don’t fool yourself, the coding required to display an arbitrary number of levels is no picnic either. Do not make the mistake of simulating recursion by coding a script module that calls itself, because from the database perspective, this is a series of calls (a query in a loop) and the performance will reflect this.

Thirdly, if you have a tree that goes more than three or four levels deep, you may have difficulty conveying this structure satisfactorily in a visual way. You may want to go back and re-think how you expect your users to actually navigate through the hierarchy. Sometimes the best solution is simply to show no more than three levels, with some sort of visual clue that there are further levels below the nodes shown.

The path to the root: the breadcrumb trail

Retrieving the path from any given node, whether it is a leaf node or not, to the root at the top of its path, is very similar to the site map query. Again, we use LEFT OUTER JOINs, but this time we go “up” the tree from the node, rather than “down.”

select node.name as node_name 
     , up1.name as up1_name 
     , up2.name as up2_name 
     , up3.name as up3_name 
  from categories as node
left outer 
  join categories as up1 
    on up1.id = node.parentid  
left outer 
  join categories as up2
    on up2.id = up1.parentid  
left outer 
  join categories as up3
    on up3.id = up2.parentid
order
    by node_name

Here’s the result set from this query:

node_name up1_name up2_name up3_name
animal NULL NULL NULL
birdie animal NULL NULL
carrot vegetable NULL NULL
celery vegetable NULL NULL
chihuahua companion doggie animal
collie herding doggie animal
companion doggie animal NULL
doggie animal NULL NULL
feldspar mineral NULL NULL
gerbil animal NULL NULL
gypsum mineral NULL NULL
herding doggie animal NULL
horsie animal NULL NULL
hunting doggie animal NULL
kittie animal NULL NULL
mineral NULL NULL NULL
pointer hunting doggie animal
poodle companion doggie animal
potato vegetable NULL NULL
quartz mineral NULL NULL
rutabaga vegetable NULL NULL
setter hunting doggie animal
shepherd herding doggie animal
silica mineral NULL NULL
terrier hunting doggie animal
tomato vegetable NULL NULL
vegetable NULL NULL NULL

Here each row in the result set is a single path, one for every node in the table. On a web site, such a path is often called a breadcrumb trail. (This name is somewhat misleading, because it suggests that it might represent how the visitor arrived at the page, which is not always the case. The accepted meaning of breadcrumb is simply the path from the root.)

In practice, we’d have a WHERE clause that would specify a single node, so in effect, the results above are all of the breadcrumbs in the table.

To display a breadcrumb trail in the normal fashion, from root to node, just display the result set columns in reverse order, and ignore the nulls. For example, let’s say we run the above query for the category “companion” and get this:

node_name up1_name up2_name up3_name
companion doggie animal NULL

The breadcrumb would look like this:

Simple, eh?

Nguồn: http://sqllessons.com/categories.html