Re: [SQL ] 一些關於SQL Server的問題
※ 引述《jameswiki》之銘言：
: <前面吃光光了..太長了XD>
: Well,蠻多人討論的..呵，
: 用guid,newid()來產生值做為PK的目的很多,
: 其實這種用Guid來做PK鍵的討論很多, 是否合用,見人見智,
: flack大大,您資料表上千萬上億筆的,或許不合用,
: 不過換成小弟,但在初始規劃時,我大概不會規劃這種一次存上億筆的,
: 不用比那個小小的字串跟整數當PK的效能,
: 光是left join一次上億筆就吃不消了
: 可能我經驗不足,不過若100萬筆資料來做連幾次left join,
: 前台的client大概都不要用了,上億筆?..我不敢想...
的確沒辦法用Left Join，一定要Inner Join
而且要參考我之前講的一個關鍵：Table裡面不能有半個字串欄位，只能有整數或Datetime
（Datetime只有八位元）
: (不要跟我說所有欄位都在同一table,你從來不用left join,那又要討論資料庫規劃
: -->離題了XD)
沒錯，面對這麼大的資料量作垂直或水平分割（Partition）是基本手段
不過還是有些場合時常要跨Partition查詢時，SQL 2000以前的Partition View
就常常不能將效能最佳化，而且它能Union的資料表還有255個的上限，所以這些
可能就得將資料塞回去同一個資料表
所幸SQL 2005的Partition Table終於比較強一點，而可支援的Partition數目
也比較多，但這也使資料表規劃技巧更形重要
: 我引述下面網址的文章
: http://blog.miniasp.com/post/2008/01/08/The-Gospel-of-the-GUID.aspx
: 其實我自己用久了,發現其實好處不止如此了..不一一列舉,就以這份文章討論吧
感謝您提供資料參考，這裡也提供一份SQL Magazine的資料
http://www.sqlmag.com/Articles/ArticleID/23449/23449.html?Ad=1
Surrogate Key vs. Natural Key
不過它是付費文章，只能請各位自己想辦法，節錄以下幾個重點，基本上就是看資料數
如果資料行數多到不可能同時塞進記憶體，那最好讓欄位寬度與Index瘦身，來盡可能
塞入更多Index到記憶體中
The kind of database platform you work from can help determine whether you
need to use a natural value or a surrogate identifier for your primary key.
The criterion that a primary key must be minimal means the fewer the columns,
the better. A single-column primary key simplifies data storage, retrieval,
and coding. SQL Server automatically creates a unique index (and, by default,
a clustered index) on any column designated as a primary key. In Figure 1's
Purchase table example, if the primary key were CustID plus PurchaseDate,
SQL Server would create a 12-byte index key. If you created a surrogate key
with an integer data type for the Purchase table's primary key, the index key
would be one-third the size. Eight bytes here and 8 bytes there don't sound
like much until you start calculating the additional I/O required for reading
and writing hundreds of thousands of records. If the records are short, you
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
can fit more records on a page that's being read into memory or written to
disk. Here's a general rule: The more records that can fit on a page, the
better the performance.
........
When people ask me which makes a better primary key—a natural value or a
surrogate identifier—I answer that the choice depends on the situation. In
all the modeling work that I've done, I've rarely been able to identify a
suitable natural key as a candidate for the job. I've been able to identify
natural search keys, both unique and nonunique. But when I apply the
prerequisite tests—is it unique? does it apply to all rows? is it minimal?
is it stable over time?—to these natural keys, most natural keys fail.
My personal preference is to use a surrogate key unless I can identify an
appropriate natural key that meets the four criteria for the primary key.
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 203.70.51.189
推
03/19 11:27, , 1F
03/19 11:27, 1F
討論串 (同標題文章)