вівторок, 17 листопада 2009 р.
Ignore duplicates for unique fields
How to make the insert skip those values?
Create the index in this way:
CREATE INDEX NotAllowed
ON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
go
The size of NULL
What nullable types allocate space even when null?
Do columns occupy space even if they store NULL?
http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!387.entry
Space used for NULL
http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/e6b8abb8-851e-498d-93f0-2701f8e44b22
Null Values
четвер, 29 жовтня 2009 р.
MSSQL: users and roles
select name, RoleName FROM sys.sysmembers sm
JOIN
( select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
from sysusers where issqlrole = 1 or isapprole = 1 ) roles
ON sm.groupuid=roles.RoleID
JOIN
( select * from master.sys.sysusers where islogin=1 ) logins
ON logins.uid=sm.memberuid;
вівторок, 15 вересня 2009 р.
BCP and problems with Unicode
> bcp madhu1.dbo.testunicode in "D:\testunicode.txt" -S ABCSqlSrv -U sa -P sa -f "d:\fmt.txt"
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
contents of testunicode.txt are :
मधुकर|pune|33
राहुल|pune|36
==========================
contents of format file fmt.txt are
8.0
3
1 SQLNCHAR 0 0 "|" 1 name SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 0 "|" 2 address SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "\r\n" 3 age SQL_Latin1_General_CP1_CI_AS
================
However, this can be avoided by using -w option and removing -f option
понеділок, 14 вересня 2009 р.
Example of MSSQL quiet installation
Setup.exe /QUIET
/INDICATEPROGRESS
/Action=Install
/FEATURES=SQLENGINE
/INSTANCENAME="EVAL33"
/SQLSYSADMINACCOUNTS="BUILTIN\Administrators"
/SECURITYMODE=SQL /TCPENABLED="1"
/NPENABLED="1"
/ADDCURRENTUSERASSQLADMIN="True"
/SQLSVCSTARTUPTYPE="Automatic"
/SQLSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"
/SAPWD="xxxxx"
неділя, 30 серпня 2009 р.
Auto-increment field
a) MySQL:
CREATE TABLE `t1` ( `id` INT UNSIGNED AUTO_INCREMENT , ... ) AUTO_INCREMENT=... ;
b) PostgreSQL:
first create the sequence:
CREATE SEQUENCE "seq1" INCREMENT BY ... START WITH ... ;
and then - create the table:
CREATE TABLE "t1" ( "id" INT NOT NULL DEFAULT nextval('seq1') ...);
c) Microsoft SQL Server
CREATE TABLE [t1] ( [id] [int] NOT NULL identity(1, 2) ...);
(this means start with 1 and increment by 2 each time )
Paint & SQL Server. How to export diagram to image.
But this tool can greatly help you with database design.
Say, you have the database diagram built in MSSQL Management Studio but you have no idea how to export it into the image.
Just PAINT !!!
Select all of the tables in the diagram, press Ctrl+C (or copy in your usual way) and paste it into Paint.
Do you like it?
субота, 25 липня 2009 р.
ER tools for PostgreSQL
But what about developers? Which ER tools can we use for database design? I'm not talking about the databases with 5 tables, I mean more complex designs.
- MySQL Workbench: a tool developed by Sun. It exports design into the MySQL-specific code, thus you have to modify it by hand in the text editor. Pros: you can show the graphic database design to the customer. Contras: MySQL-specific SQL and ... BUGGY !! (at least - the latest version. Just click the "Not NULL" checkbox in the fields list ... and the program dies).
- Database Designer for PostgreSQL from MicroOLAP. Never used but look at the prices: starting from 124.95 USD for personal use. I think the tool is good, but the price...
Any more ideas guys?