MSSQL vesus MySQL

MSSQL 을 쓰다가 MySql을 셋팅하다 보니, 
다른 점과 불편한 점들이 있어서 몇 자 적어 놓는다.

가장 큰 차이점은

1. MySql은 limit 와 offset 개념이 있어서 paging 처리가 쉽다는 것.

2. MySql은 column data에서 boolean type 이 없어서, tinyint(1) 을 쓴다는 점. (이거 제일 처음에는 bit가 왜 없을까 고민하게 만들었었다…-_-;;)

3. MySql은  테이블마다 Database Engine 이라는 걸 셋팅할 수 있으며,
MySql Default 설치값이 MYISAM 이라는 Database Engine을 쓴다. 
근데 이 MYISAM이라는 녀석은 Foreign Key  Constraint 가 안 먹어서,
MSSQL은 그런걸 고민한 필요가 없었는데…-_-;;

아무튼 결론적으로다가  MySql에서는 InnoDB 를 Database Engine으로 셋팅해야
FK Constraint가 잘 먹어준다. 또한 Transaction도 실행시킬 수가 있다. 

4. Column 명, data value 에 해당하는 것들의 구분을 MSSQL에서는 ‘ 로 구분하지만,
MySQL 에서는 Column명에서는 `(키보드의 탭키 위쪽 물결 무늬랑 같은 키)로 구분한다. 

MySQL

insert into `table1` (`CustomerID`) values ('customer1');

참고 사이트 : http://www.people.vcu.edu/~agnew/Misc/MySQL-MS-SQL.HTML

http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html

5. MySQL은 Custom Data type을 지원하지 않는다. (이거 무지 불편하다.) -_-;;

6. MySQL은 varchar(xx) 와 같은 항목에서 min char을 설정하는 Constraint를 사용할 수 없다. (Trigger로 제어 불가능). 즉 DB단에서는 불가능하고, 프로그램에서 제어를 해야 한다.

7. MySQL은 Datetime Column에서 Default 값으로 now() 가 들어가지 않는다. 쓰고 싶다면 트리거를 설정해 줘야 한다. (근데 이 트리거가 또 권한이 보통 기존 권한으로 안되는 경우가 많다. -_-;;) 

CREATE  TRIGGER SomeTriggerName BEFORE INSERT
 ON SomeTableName
 FOR EACH ROW
SET New.SomeDatetimeColumnName = NOW()
;

8. sql hierarchical query를 처리하는 방식이 다르다. 이 부분은 내용을 정리해야 하는데, 시간이 없는 관계로다가, 간단한 내용 및 링크로 정리해 놓고 추후 정리할 시간이 있으면 정리해 보겠다.

9. bit 칼럼이 틀리다. MySQL 에서는 MSSQL에서 쓰는 Boolean 형태의 Bit 칼럼이 없다. 정확하게는 전혀 없는 건 아니고 동일한 기능은 Tinyint(1) 으로 대응되고 있다. 그런데, MySQL에서는 Bit 칼럼이 있다. 이건 MSSQL의 Bit와는 전혀 틀리다. Bitmask 칼럼이다. 아래 예제를 보면 명확해 질 듯.

ALTER TABLE DatabaseName.TableName
 Modify `ColumnName` BIT(4) DEFAULT b'0000' COMMENT '1:관심분야가 스포츠 2:관심분야가 예술 4:관심분야가 등산 8:관심분야가 공부'

 10. 아놔…미치겠다. mysql 은 서버 셋팅 기본값이 로그를 쌓아놓지 않는다. 이런 젠장할… 

11. 미치겠는 항목 하나 더 추가다.  InnoDB에서 auto_increment 가 mysql daemon이 restart 되면, 끊어진 번호에서부터 시작한다. 
예를 들어서, 8,9,10 이렇게 record가 insert 되고, 10번 record를 delete 하고, mysql 이 살아 있을 때는 insert 하게 되면 11번이 부여되지만, mysql을 restart 하게 되면 10번이 부여된다. 

http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html

http://serverfault.com/questions/228690/mysql-auto-increment-fields-resets-by-itself

링크를 참조하면 된다. 결국엔 연번을 채번하기 위해서는 오라클 방식으로 할 수 밖에 없을 것 같다. 

Differences between SET and SELECT in SQL Server

Original Post : http://vyaskn.tripod.com/differences_between_set_and_select.htm

What is the difference between SET and SELECT when assigning values to variables, in T-SQL? 





Note: Information & code samples from this article are tested on SQL Server 2005 RTM (Yukon) and found to be working. Will update the article in case of any compatibility issues.

Traditionally, SQL Server database developers are accustomed to using SELECT for assigning values to variables. This was fine and a perfectly valid practice right until SQL Server 6.5. Microsoft released SQL Server 7.0 in 1999. SQL Server 7.0 introduced the new SET statement for initializing and assigning values to variables. SQL Server 7.0 Books Online also stated: “It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.”

This caused some confusion in the database developer community, as Microsoft never mentioned, why SET is recommended over SELECT for assigning values to variables. In this article, I will highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT.

If you are completely new to T-SQL, then the following examples give you an idea of what I am talking about:

/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int

/* Setting @Variable1 to a value of 1 using SELECT */
SELECT @Variable1 = 1 

/* Setting @Variable2 to a value of 2 using SET */
SET @Variable2 = 2 

Now coming to the differences between SET and SELECT! Are standards important to you? If your answer is ‘yes’, then you should be using SET. This is because, SET is the ANSI standard way of assigning values to variables, and SELECT is not.

Another fundamental difference between SET and SELECT is that, you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. Here’s how:

/* Declaring variables */
DECLARE @Variable1 AS int, @Variable2 AS int

/* Initializing two variables at once */
SELECT @Variable1 = 1, @Variable2 = 2

/* The same can be done using SET, but two SET statements are needed */
SET @Variable1 = 1
SET @Variable2 = 2

===========================================================================

and, original article have lots more text. above is brief one.
If you want to read further reading, read original Post.

ms sql paging, ms sql pagination, ms sql limit offset

Original Post : http://blog.pengoworks.com/index.cfm/2008/6/10/Pagination-your-data-in-MSSQL-2005
http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived-table/
http://msdn.microsoft.com/en-us/library/ms979197

OK. What is limit and offset ?

If you don’t know exactly. Please, read this article.
http://www.petefreitag.com/item/451.cfm

From MS SQL 2005, CTE (Common Table Expression) was announced.





Pagination in MSSQL 2005


Categories: Source Code, SQL


Paginating data is one of those commonly required tasks. In previous version of MS SQL Server it’s been tricky to handle, but made easy via the use of stored procedures that handle the pagination logic for you. However, if you’re using SQL Server 2005 there’s a much easier way to handle pagination and that’s to use a Common Expression Table (CTE.)


Microsoft created the CTE syntax to make solving complicated tasks easier (such as returning a query of hierarchical tree data.) When you create a CTE, you’re essentially creating a virtual table you can query against. CTEs generally perform very well and can often replace the need for creating temp tables.


Let’s look at an example of query out records 11-20 from a Employee database:


create the Common Table Expression, which is a table called “pagination”
with pagination as
(
your normal query goes here, but you put your ORDER BY clause in the rowNo declaration
select
row_number()
over (order by department, employee) as rowNo,
a list of the column you want to retrieve
employeeId, employee, department
from
Employee
where
disabled
= 0
)
we now query the CTE table
select
add an additional column which contains the total number of records in the query
*, (select count(*) from pagination) as totalResults
from
pagination
where
RowNo
between 11 and 20
order by
rowNo

The first part of the query creates the table expression we’ll actually query against when we grab the actual results from the database. In the CTE you’re going to write the SQL you’d normally write to grab all the records with one exception—you use the row_number() function to generate a virtual column that orders your result rows for you. The actual “order by” clause you want to use will actually go in the row_number() declaration—this makes SQL Server assign the correct row number for each record.


It’s important to remember that a CTE can be as complex as you need it to be. You can do joins, pivots, etc—whatever you need.


The next step is to actually query against the CTE you created. This query will virtually be the same for all pagination queries you write. You are simply grabbing all of the columns and then limiting the returned data to just the rows you want to display (in this case rows 11 – 20.)


In this example, I added an additional column that contains the total results that were returned from the CTE. This is not needed, but it’s handy if you need to do something like “Results 11 – 20 of 4,567 records.” There are many ways you could generate the total records, this is just one method.


If you’ve upgraded to SQL Server 2005 and haven’t started using CTEs yet, you really need to start getting familiar with them as they really simplify many tasks in SQL Server that previously were very difficult to solve.



 
OK. Make it MS SQL Function. and, then use that.