Veritabanı Modelleme Kaynakları

Ref: http://www.databaseanswers.org/modelling_tools.htm

PRODUCT	VENDOR	PRICE	COMMENTS
AquaData Studio ER Modeler	AquaFold	$400	A Free 14-day evaluation copy can be downloaded. The purchase price includes 12-month support. After the first year, support can purchased for 35% of the original license price. "Discounted licences are available for qualified Academic users and Free licenses are available for qualified Open Source Software (OSS) Developers."
Altova DB Design	Altova		One of a broad range of Tools.
Archimate	OMG	Free Trial available	Archmimate is described as an Enterprise Architecture Modelling Tool, which would be great ;-). Unfortunately, the download is for documentation and not for the product !!! It looks like Archimate is a specification for which a number of Vendors have developed Modelling Tools. Here's Wikipedia's entry for Archimate.
Argo UML	Tigris	Free - Open Source	For Modelling UML Diagrams - but not for the faint-hearted.
Astah (was Jude)	Change Vision	astah* comes with three editions: astah* com - Free UML modeling tool astah* UML - UML and Mind Mapping integrated tool for $50 astah* professional - Total System Design tool (UML, ERD,DFD, Flowchart, Mind Map, CRUD and Requirement diagrams) A Free Trial is available.	A Free Community version is available, and they publish a lot of very favourable User Comments in their Community Forum. Follow them on Twitter
Azzurri Clay	Azzuri (Japan)	Free	From Japan -Open Source Plug-In for Eclipe - Reverse Engineering and generates SQL. Pro version costs 35,000 Japanese Yen (about $150) !!
Borland Together	Borland	No prices on the Website	Described as "Visual Modeling for Software Architecture Design". The Website offers information on Model-Driven Development.
CA Gen	Computer Associates	No prices on the Website	Provides Model-Driven support for Agile Development. Has a UK Partner in Response Systems.
Case Studio 2 Acquired by Toad (see below under Toad Data Modeler)	CharonWare	FreeDownload Trial, then $175(Lite) or $369(Full)	Looks impressive - supports an unusally wide range of Databases, including Firebird, MaxDB,Postgres - if you try it, please me have your comments. Here's a "5-Star" Review from Dan Horn.
CASEWise	CASEWise		Aimed at Business Process Modelling, but has links to DataArchitect.
ConceptDraw	CS Odessa	$179 (50% off)	For ERD, UML, ORM, FlowCharts, etc. - Free Download Trial is available. I like the appearance of the diagrams this Product produces and this is an important consideration for me.
DataArchitect	Sybase	$2,000	If you are buying for the company, buy this one. DataArchitect is part of Power Designer, which is described below.
DataArchitect	TheKompany	$39.99	Highly recommended by Andy Brown and an unbeatable price. Comments and experiences please.
Database Design Tool (DDT)	Open Source		THANKS VERY MUCH to Josh Bosh, Milwaukee's most beloved Web specialist, for providing this link.
Database Design Studio	Chilli Source	$39 - $200	Comes in two versions - Lite at $79 ($39 Educational), which supports ERD Modeling and Forward Engineering and Professional at $299, which adds support for Reverse Engineering.
Database Designer	MicroOlap	$125	Specially for PostgreSQL and includes Reverse Engineering.They give it away Free if you mention it in your Blog.
DB Constructor	Robert Schambach	Free/$150	Here's a glowing reference at the FreeDownloadCenter Web Site.
DBDesigner 4	FabForce.net	Free (Donations welcome)	Open Source and highly recommended if you work with MySQL.
DBDesigner-Fork	SourceForce.net	Free	A Fork of DBDesigner 4 (see above)- generates SQL scripts for Oracle, SQL Server, MySQL and FireBird.
DB Diagram Designer	PrimeLogics	Offers a Free evaluation download or $130 to buy.	Looks very interesting - modeller plus data editor using ODBC connections. If you try it please let me know your opinion.
DB Main	Rever	Free for the Data Modeller, with add-ons available at typical prices of 1,000 Euros or US Dollars.	Produced in Belgium, with documentation in English and French. Offers a very impressive range of features, including :- "... meta-modeling components that allow its users to develop new functions and extend its repository"
DBSchema	Concrete Software, Romania	Free download and then only $45 to buy.	Reverse Engineer Oracle and other Databases using JDBC connections. Can also generate SQL to create Databases from a Data Model.
DBVisualiser	Minq	Free evaluation and then only $129 to buy,($103 for Education).	Helpful for DBA work, like performance monitoring. A Finalist for 'Best Data Access Tool 2005' in the Java Developers Journal Readers Choice Award and included in the BEA WebLogic Workshop. Free version available with "the basic level of features that most users require".
	Nizana	$149	"Forward and Reverse Engineer, Affordable, Easy to Use."
Dia	Hosted by Sourceforge	Free - Contributions suggested	"Dia is software for drawing diagrams similar to Visio", such as UML. It's Open Source with many authors. Install it from here
Druid	From SourceForge		Generates SQL Scripts and Java classes. Thanks again to Josh Bosh.
Enterprise Architect	Sparx Systems	$99	UML Analysis & Design Tool and very affordable.
Entity Framework Add-on	IdeaBlade	$995+/developer	"Makes Microsoft's Entity Framework easier to use"
ER Creator	Model Creator	About $100	Includes a Tutorial and a Trial version.
ER Diagrammer	KeepTool	$240	Free download is available and a User Forum is supported.
ERD Concepts 5	ERD Concepts	$179 Euros (single-user)	A Free download is available.
ER/Studio	Embarcadero	$1,500 up to $7,500 (!!!)	In response to a helpful comment from Daryl Purcell I have added a link to US prices and UK prices.
ERWin	Computer Associates	No price on Web Site !!!	One of the Market Leaders - an enterprise solution at an enterprise price
Experience	Electronic Exerience	Free Trial then 90 Euros (about $50)
Google WWW SQL Designer	Ondrej Zara (Czechoslovakia)	Free Open Source	Good-looking sample Data Model.
Happy Fish	Polderij NV. (in Dutch)	$89	Renamed ERD Concepts.
IBM InfoSphere Data Architect	IBM	$5,570 !!!	Provides reverse and forward engineering facilities for a range of databases, also ERD and UML Modelling.
ImportER MySQL	Datanamic	Excellent value at $55	Reverse Engineering for MySQL
KeepTool	KeepTool	$300 / 300 Euros	Can Reverse Engineer Oracle Databases to create Data Models - Comments and experiences welcome.
MagicDraw	No Magic		Award-winning UML Modeller and very popular with Java enthusiasts.
Mega Database Builder	Mega		Looks like a great product with a wide range of features BUT there is no price information on their Site and they offer an evaluation version but you can't download it - instead they will contact you to discuss your requirements !!!
ModelRight	ModelRight	Free Trial, then $495 for DB2, Oracle and SQL Server, $295 for MySQL and PostgreSQL, and $195 for Access.	An enthusiastic user is David McKelvey from Richardson,Texas, who says “As a pure Database Physical Modeling Tool, nothing else comes close."
MySQL Workbench	MySQL	Free with MySQL (also Free)	Looks good and offers Reverse Engineering.
Objecteering	Objecteering.com	Free download trial	Looks good with a comprehensive range of features, including UML support.
OmniGraffle	Omni Group	Free download trial	For Mac only
Open ModelSphere	Model Sphere	Free	Open Source and UML
Oracle Designer	Oracle	$200	Available from the Oracle Technology Network (Free to join). Oracle says "Oracle Designer is our model-driven development tool based on the principles of Information Engineering."
Oracle J-Developer	Oracle	Free Download	UML diagramming is part of J-Developer. Also available from the Oracle Technology Network. Oracle says "Oracle JDeveloper is an integrated development environment with end-to-end support for modeling, developing, debugging, optimizing, and deploying Java applications and Web services."
Oracle SQL Developer Data Modeler	Oracle	Free	Many thanks to Tuomas Kassila in Finland for telling us about this. Oracle says :- SQL Developer Data Modeler is a Free data modeling and design tool, for ERD and Multi-dimensional modeling, with forward and reverse engineering. It imports from and exports to a variety of sources and targets.
ORM Designer	Inventic	Offers a Free trial then costs $140 or 99 Euros.	Thanks to Ludek Vodicka in Czechoslovakia.
PG Designer	From John.		Open Source - for PostgreSQL. Thanks again to Josh Bosh.
Platinum Repository	Computer Associates		This is a Repository, rather than a Modelling Tool, with a 'Where-used' facility, and it lets you add entity types,such as Business Function, Business Role and Script.
Poseidon	GentleWare		Comes highly recommended, good for UML, and has an open-source Free version.
Power*Architect	SQL Power	Free (Open Source)
Power Designer	Sybase	$2,000	If you are buying for the company, buy this one. The Data Modelling Tool in this composite product is called "DataArchitect". Unfortunately, the design of the web site has changed recently and information about Data Architect is difficult to find. But the product is excellent, and I have found its powerful Reverse Engineering facilities to be very useful.
ProVision	MetaStorm	Free Trial, but prices not avalable on the Website.	Includes Frameworks, such as Zachman, and also Reference Models, such as Customers, Finance and Telecomms, and an interesting 3-D Architecture
RevJ	SourceForge	Free Open Source	This Object Source product generates ERD Data Models from SQL. It is a way of understanding complex SQL that you might have 'inherited' from somebody else. Please Email me if you have any comments on RevJ.
Rhalotel DB Diagram Mgr	Rhalotel	98 Euros/$100
RM Bench	Byte Refinery	Free Trial then $120	"Graphical Modelling of Relational Databases in Eclipse".
Saphir	Computer Associates (acquired from Silwood)	Used to be about $14,000 but check with CA	Saphir is more than a Modelling Tool. It sits between an ERP like SAP and a Modelling Tool like ERWin. It provides a very useful and powerful Metadata manipulation package which allows you to extract metadata from ERPs such as SAP, J.D. Edwards and PeopleSoft, which can then be 'reverse engineered' into a Modelling Tool like ERwin, to see the Data Model.
SilverRun ModelSphere DM Data Modeller	Grandite	Offered on a unique Buy or Rent Scheme ($1,500/year). But the Web Site says 'Please contact us for a customized quote' - which often means 'If you have to ask, you can't afford it'.	Offers Evaluation Download
SmartDraw	SmartDraw	$50 !!!	Recommended - Promoted as a UML Drawing Tool with impressive support.
SQL Developer Diagram Editor		Free	Not to be confused with Oracle's SQL Developer. Offers a range of attractive features and has some glowing references.
SQL Maestro	SQL Maestro Group	Freeware	Please let me know if you have any experience with this product.
SQL Manager	EMS	Free Trial available
Squirrel	Squirrel	Free	Open Source
StarUML	SourceForge	Free	"The Open Source UML/MDA Platform" (from Korea).
System Architect 2001	Popkin	$3,000-5,000 per seat.	In an evaluation, I found this product very difficult to use, the diagrams were very ugly and it crashed too often for me to recommend it. Downloads available for Evaluation.
Toad Data Modeler	Quest	Free Download, then $479	Price is 30% off to CASE Studio 2 customers From the makers of Toad, and based on CASE Studio 2 – Includes 24x7 support, runs on over 40 DBMSs, and you can create ER & Data Flow diagrams, reverse engineer and generate SQL Scripts.
TogetherSoft Solo	Borland	Downloadable	Supports UML
Toolkit for Conceptual Modeling (TCM)	University of Twente, Holland	Free and Downloadable	Very interesting combination of Tools, including UML. Thanks to Tony Adams for suggesting a better link here.
Umbrello	SourceForge	Free Open Source	UML Modeller
Visio	Microsoft	Trial CD available	Many Versions- Standard($199),Technical($399),Professional($399) and Enterprise($999)
Visual Paradigm	Visual Paradigm	Free Community Edition, also Free trial.	Produced by a Hong Kong-based company - supports ERDs and UML, imports Visio
Visual Thought	CERN (Godfathers of the Internet)		Give it a try for UML. THANKS AGAIN to Josh Bosh, Milwaukee's most beloved Web specialist, for providing this link.
Win A&D	Excel Software	No price information on the Web Site	Unusually offers products for MAC as well as Windows - UML Modelling as well as ERD.
xCase	Resolution Software	$800 (Download Trial available)	"xCase is a complete database design and maintenance tool including archiving and model compare and merge across different DBMSs".

Otomatik Primary Key üretimini TSQL ile durdurmak ve başlatmak

-- Toplu INSERT işlemi
INSERT INTO hedefTablo(PKalanAdi, alanAdi1, alanAdi2...)
SELECT (PKalanAdi, alanAdi1, alanAdi1....) FROM kaynakTablo

-- Yukarıdaki durum Primary Key, auto increament olarak ayarlanmışsa
-- çalışmayacak çünkü, haberlerin id lerinide geçiyoruz.
-- Otomatik üretmemesi için aşağıdaki SQL çalıştırılır
SET IDENTITY_INSERT tabloAdi OFF

-- Tekrar aktif etmek için :
SET IDENTITY_INSERT tabloAdi ON

MSSQL Backup Yöntemleri

ref: http://www.databasejournal.com/features/mssql/article.php/3716561/BACKUP-compression-in-SQL-Server-2008.htm

BACKUP compression in SQL Server 2008
By Muthusamy Anantha Kumar aka The MAK

After a long wait, SQL Server 2008 is going to be released with inbuilt compression for backups. Until the release of SQL Server 2005, compression was only available via third party backup software such as SQL LiteSpeed, SQLZip, etc.

This article demonstrates how to take Full, Differential and Transactional log backups with compression, without compression and how to enable compression as a default.

Note: This article is written based on the SQL Server 2008 – Nov CTP.

Let us create a database “MyDB” as shown below.

USE [master]
GO

/****** Object: Database [MyDB]
Script Date: 12/10/2007 01:08:14 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
DROP DATABASE [MyDB]
GO

USE [master]
GO
/****** Object: Database [MyDB]
Script Date: 12/10/2007 01:05:09 ******/
CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB_Data',
FILENAME =
N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
MSSQL\DATA\MyDB_Data.mdf' ,
SIZE = 2176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDB_log',
FILENAME =
N'F:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\
MSSQL\DATA\MyDB_log.LDF' ,
SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [MyDB] SET RECOVERY FULL
GO

Now let us create a table “MyTable” in the database “MyDB” as shown below

USE [MyDB]
GO

/****** Object: Table [dbo].[MyTable]
Script Date: 12/10/2007 01:12:00 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDB]
GO

/****** Object: Table [dbo].[MyTable]
Script Date: 12/10/2007 01:12:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MyTable](
[id] [int] NULL,
[name] [char](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Let’s add 10000 rows of data to the table “MyTable” as shown below.

USE [MyDB]
GO

declare @myid int
set @myid=1
while @myid<=10000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Select the data using the following T-SQL command. [Refer Fig 1.0]

use MyDB
go
Select * from MyTable
go


Fig 1.0

Create a folder, D:\Backup, as shown below. [Refer Fig 1.1]


Fig 1.1

Now let us take a full backup as shown below. [Refer Fig 1.2]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full.bak' with init


Fig 1.2

Let’s add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB]
GO

declare @myid int
set @myid=1
while @myid<=1000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Now let us a take a transaction log backup, as shown below. [Refer Fig 1.3]

Backup log MyDB to disk ='d:\Backup\MyDB_TLog_1.bak' with init


Fig 1.3

By default, SQL Server does not compress the backups. We can compress the backups in two different ways.

a. Change the default behavior of SQL Server to compress all of the backups.

b. Add an optional keyword “With COMPRESSION” in the backup clause.

The database MyDB and the Full backup, Transactional log backup that we took were without compression. That is the default SQL Server behavior.

Now let’s take a full backup of the database with compression as shown below. [Refer Fig 1.4]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full2.bak' with COMPRESSION



Fig 1.4

From figure 1.4, you can see that the size of MyDB_Full2.bak is much smaller when compared to MyDB_Full.Bak and MyDB_Tlog_1.bak.

Add some more data [1000 rows] to the table “MyTable” in the database “MyDB” as shown below.

USE [MyDB]
GO

declare @myid int
set @myid=1
while @myid<=1000
begin
insert into MyTable select @myid, 'A'+convert(varchar(10),@myid)
set @myid =@myid +1
end

Now let’s take a transaction log backup as shown below. [Refer Fig 1.5]

Backup log MyDB to disk ='d:\Backup\MyDB_TLog_new.bak' with COMPRESSION


Fig 1.5

In figure 1.5, you can see that the size of MyDB_Tlog_new.bak is much smaller when compared to MyDB_Tlog_1.bak.

Let’s take a differential backup without compression and compare it with the differential backup with compression.

Execute the following commands as shown below. [Refer Fig 1.6]

backup database MyDB to disk ='d:\Backup\MyDB_Diff.bak' with differential

backup database MyDB to disk ='d:\Backup\MyDB_Diff2.bak' with differential, COMPRESSION


Fig 1.6

Fig 1.6 shows the compression ratio between MyDB_Diff.bak and MyDB_Diff2.bak.

Let’s change the default behavior of SQL Server from uncompressed backup to compressed. This can be done using the SP_CONGIFURE command.

Execute the command as shown below.

USE master
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

Now let’s take a full backup of the MyDB database, without the optional keyword “WITH COMPRESSION”. [Refer Fig 1.7]

Backup Database MyDB to disk ='d:\Backup\MyDB_Full3.bak'


Fig 1.7

From figure 1.7, we can clearly see that the backup by default is compressed.
Conclusion

This article demonstrated how to take a Full backup, Differential backup and transactional log backup with or without compression and how to enable compression as a default.

» See All Articles by Columnist MAK

MS SQL de hatalar

Sistemde kayıtlı mesajları görmek için:

SELECT *
FROM sys.messages


Hata mesajı eklemek:

sp_addmessage @msgnum=50001, @severity=11, @msgtext='Üye zaten sisteme kayıtlı', @with_log='true'

with_log = true: event log'a eklesin.


BEGIN TRY
RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR

// Complete normal process if no errors encountered above
PRINT 'IMPORT SUCCEEDED'
END TRY
BEGIN CATCH
PRINT 'IMPORT ABORTED. ERRORS ENCOUNTERED'
END CATCH



USE AdventureWorks;
GO

-- Declare and set variable
-- to track number of retries
-- to try before exiting.
DECLARE @retry INT;
SET @retry = 5;

--Keep trying to update
-- table if this task is
-- selected as the deadlock
-- victim.
WHILE (@retry > 0)
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

UPDATE my_sales
SET sales = sales + 1
WHERE itemid = 2;

WAITFOR DELAY '00:00:07';

UPDATE my_sales
SET sales = sales + 1
WHERE itemid = 1;

SET @retry = 0;

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Check error number.
-- If deadlock victim error,
-- then reduce retry count
-- for next update retry.
-- If some other error
-- occurred, then exit
-- retry WHILE loop.
IF (ERROR_NUMBER() = 1205)
SET @retry = @retry - 1;
ELSE
SET @retry = -1;

-- Print error information.
EXECUTE usp_MyErrorLog;

IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
END CATCH;
END; -- End WHILE loop.
GO


CURSOR

DECLARE @sehir varchar(11), @sehir_id varchar(20)

DECLARE cr CURSOR FOR
SELECT il_id,adi FROM Iller

OPEN cr

FETCH NEXT FROM cr INTO @sehir_id , @sehir

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE adresler set il=@sehir_id where il=@sehir

FETCH NEXT FROM cr INTO @sehir_id , @sehir

END


CLOSE cr
DEALLOCATE cr

GO

T-SQL: Get only Date from DateTime

Sadece tarih bilgisidir.... Saat, dakika, saniye hep 0

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
2009-01-16 00:00:00.000







SQL SERVER - Tek insert into ile birden fazla kayıt girebilmek - UNION ALL kullanımı

Ref:http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

UNION
UNION ilişkili tabloların aynı tipte verileri olması kaydıyla DISTINCT ile getirmesidir. JOIN lemesi gibi.

UNION ALL
UNION ALL, UNION komutuna eşittir bir farkla, o da tüm değerleri seçmesi.

Union all çiftlenmiş kayıtlarıda getirir. Tüm kayıtlarını getirir ve tablolarını birleştirir.

A UNION komutu SELECT DISTINCT sonucunu getirir. Eğer tüm satırların eşsiz olarak ayrıştığını biliyorsanız UNION ALL kullanın, çünkü daha hızlıdır.

Örneğin:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth

Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)


USE YourDB
GO
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('First',1);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Second',2);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Third',3);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fourth',4);
INSERT INTO MyTable  (FirstCol, SecondCol)
        VALUES ('Fifth',5);
GO


yerine

USE YourDB
GO
INSERT INTO MyTable  (FirstCol, SecondCol)
    SELECT  'First' ,1
    UNION ALL
SELECT  'Second' ,2
    UNION ALL
SELECT  'Third' ,3
    UNION ALL
SELECT  'Fourth' ,4
    UNION ALL
SELECT  'Fifth' ,5
GO


sonuç:
INSERT : 5 saniye
INSERT SELECT UNION ALL : 40 milisaniye