Alexander Kuznetsovhttp://sqlblog.com/blogs/alexander_kuznetsov/atom.aspxCommunity Server2013-11-05T16:17:00ZCars, Databases, and Benchmarkshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/04/22/cars-databases-and-benchmarks.aspx2014-04-23T00:45:00Z2014-04-23T00:45:00ZOver the last few months we have migrated some functionality from Sql Server to PostgreSql, and developed a couple of new systems powered by PostgreSql. So far I do not see that either of these two products is better than another - they are different. Even though I guess this is the right time to write something like "N reasons why PostgreSql is better than Sql Server", or vise versa: "N reasons why Sql Server PostgreSql is better than PostgreSql", or both ( I guess I could write it both ways), I...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/04/22/cars-databases-and-benchmarks.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=53687" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxBook Review: Tribal SQL,Performance Tuning With SQL Trace and Extended Eventshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/18/book-review-tribal-sql-performance-tuning-with-sql-trace-and-extended-events.aspx2014-02-18T14:51:00Z2014-02-18T14:51:00ZTara Kizer wrote this chapter, and it is relevant for us developers. Tara briefly explains how to use the GUI, the Profiler, and states that it can heavily impact the performance on the server. Then she explains how to set up a server-side trace which should incur less overhead, and I noticed that the working example which she provided is quite similar to the one our team is using. There is one minor thing, however, that we do differently: our T-SQL is self-documenting. For example, instead of the...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/18/book-review-tribal-sql-performance-tuning-with-sql-trace-and-extended-events.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=52876" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxBook Review: Tribal SQL, Chapter 1.http://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/08/book-review-tribal-sql-chapter-1.aspx2014-02-08T22:49:00Z2014-02-08T22:49:00Z"Tribal SQL" is an interesting book, so I will be reviewing some, although not all, of its chapters. Quoting from the introduction, "This is a book for DBAs, for things you think they really ought to know". As an agile developer, I clearly do not belong in this book's target audience. Also I am not qualified to review some of the chapters, as I have zero real life experience with some of the technologies described in it. I will not be reviewing these chapters. As a developer frequently working with...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/02/08/book-review-tribal-sql-chapter-1.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=52760" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning postgreSql: serialization failures with SERIALIZABLEhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/01/06/learning-postgresql-serialization-failures-with-serializable.aspx2014-01-06T17:38:00Z2014-01-06T17:38:00ZWe shall reproduce a serialization failure and see SERIALIZABLE isolation level enforces data integrity. Prerequisites We shall need the following test data: CREATE TABLE Carpools ( Car_Name VARCHAR NOT NULL, Passenger VARCHAR NOT NULL ); INSERT INTO Carpools ( car_name , passenger ) VALUES ( 'Carol''s car' , 'Jim' ), ( 'Carol''s car' , 'Carmen' ), ( 'Carol''s car' , 'Ted' ); Reproducing a serialization failure Suppose that Carol can only take four passengers in her car, so we can add at most one...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2014/01/06/learning-postgresql-serialization-failures-with-serializable.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=52346" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: Fun with REPEATABLE READhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/19/learning-postgresql-fun-with-repeatable-read.aspx2013-12-19T15:11:00Z2013-12-19T15:11:00ZIn this post we shall run some examples under REPEATABLE READ, and see how they behave differently. Setting up test data On SQL Server, run the following: CREATE TABLE Tickets ( ID INT NOT NULL, Problem VARCHAR ( 100 ) NOT NULL, SpaceFiller CHAR ( 200 ) NOT NULL ); INSERT INTO Tickets ( ID , Problem , SpaceFiller ) SELECT Number * 10 , 'Problem ' + CAST ( [Number] AS VARCHAR ( 10 ))+ '0' , 'Space Filler' FROM data.Numbers ; ALTER TABLE Tickets ADD PRIMARY KEY ( ID ); UPDATE Tickets SET Problem =...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/19/learning-postgresql-fun-with-repeatable-read.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=52180" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: READ COMMITTED and Data Integrityhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/02/learning-postgresql-read-committed-and-data-integrity.aspx2013-12-02T17:34:00Z2013-12-02T17:34:00ZAs we have just discussed, READ COMMITTED isolation level behaves very much like Sql Server's READ_COMMITTED_SNAPSHOT. As such, we need to be very careful with data integrity - lots of code that just works on Sql Server under its default isolation level, READ COMMITTED, does not work on PostgreSql under its default isolation level, which is also READ COMMITTED, but behaves differently. Note: whenever we use READ_COMMITTED_SNAPSHOT on Sql Server, we need to be just as careful with data integrity -...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/12/02/learning-postgresql-read-committed-and-data-integrity.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51952" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: different behavior with READ UNCOMMITTED/COMMITTEDhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/27/learning-postgresql-different-behavior-with-read-uncommitted-committed.aspx2013-11-27T20:25:00Z2013-11-27T20:25:00ZBecause of multi-valued concurrency control aka MVCC, there are many differences in queries' behavior with different isolation levels. Before running examples, let us set up test data. Setting up test data The following script sets up the data we shall be playing with: DROP TABLE test ; CREATE TABLE test ( ID INT NOT NULL, CONSTRAINT PK_test PRIMARY KEY ( ID ), col2 INT NOT NULL, col3 FLOAT ); INSERT INTO test ( ID , col2 , col3 ) VALUES ( 1 , 0 , 0 ),( 8 , 1 , 1 ); There are no dirty reads. In one...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/27/learning-postgresql-different-behavior-with-read-uncommitted-committed.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51933" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: XACT_ABORT is Always ONhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/26/learning-postgresql-xact-abort-is-always-on.aspx2013-11-26T21:20:00Z2013-11-26T21:20:00ZPostgreSql has simple and consistent error handling, which can be roughly explained in Sql Server terms as follows: XACT_ABORT is Always ON. In other words, error handling in PostgreSql has substantially less features, but it does have all the features which we actually use in the project being migrated. Simplicity of error handling in PostgreSql is very good news - we need to learn much less before we are productive, and there are less chances to make a mistake. Let us consider some examples. On...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/26/learning-postgresql-xact-abort-is-always-on.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51926" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: No Nested Transactions, No Transactions in PL/pgSQLhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/25/learning-postgresql-no-nested-transactions-no-transactions-in-pl-pgsql.aspx2013-11-25T15:58:00Z2013-11-25T15:58:00ZTransactions and error handling in PostgreSql are substantially different. We shall discuss only the features that we needed to learn to complete our project. It will take us more than one post to describe them all. TL;DR; Transactions and error handling in PostgreSql are much simpler, but there are enough features necessary to implement robust error handling without too much learning. No Nested Transactions We do not use nested transactions in SQL Server, because we have never seen any practical...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/25/learning-postgresql-no-nested-transactions-no-transactions-in-pl-pgsql.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51888" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: UPDATE...FROM works differentlyhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/20/learning-postgresql-update-from-works-differently.aspx2013-11-20T17:33:00Z2013-11-20T17:33:00ZUPDATE...FROM command on PostgreSql may raise no errors, but produce completely different results. Later I shall provide a repro, but first let us briefly refresh how UPDATE...FROM works on Sql Server. UPDATE...FROM on SQL Server ignores ambiguity We shall need the following test data: CREATE TABLE #Problems ( ProblemID INT NOT NULL, Problem VARCHAR ( 30 ) NOT NULL, Solution VARCHAR ( 30 ) NULL); CREATE TABLE #SuggestedSolutions ( SuggestedSolutionID INT NOT NULL, ProblemID INT NOT NULL, IsAccepted...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/20/learning-postgresql-update-from-works-differently.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51845" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: replacing TOP and APPLY with LIMIT and LATERALhttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/19/learning-postgresql-replacing-top-and-apply-with-limit-and-lateral.aspx2013-11-19T17:06:00Z2013-11-19T17:06:00ZAll SQL Server queries using TOP and/or APPLY need to be changed - PostgreSql uses completely different syntax. Replacing TOP with LIMIT The following script shows how to do that: CREATE TEMP TABLE Runs ( State_Code VARCHAR , Run_Date DATE , Distance FLOAT , Description VARCHAR ); INSERT INTO Runs ( State_Code , Run_Date , Distance , Description ) VALUES ( 'IL' , '2013-11-16' , 16.6 , '2 loops in Wolf Road Woods' ), ( 'IL' , '2013-11-17' , 8.4 , 'Herrick Woods' ), ( 'IL' , '2013-11-19' , 7.2 , 'Chicago...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/19/learning-postgresql-replacing-top-and-apply-with-limit-and-lateral.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51812" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: differences in implementation of constraintshttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/15/learning-postgresql-differences-in-implementation-of-constraints.aspx2013-11-15T17:33:00Z2013-11-15T17:33:00ZConstraints in PostgreSql are implemented somewhat differently. To use them efficiently, there are quite a few details we need to be aware of. NULLs and uniqueness In PostgreSql, unique constraints allow multiple NULLs. This behavior is ANSI standard. SQL Server's implementation of unique constraints is not ANSI standard. The following example demonstrates how unique constraints allow multiple NULLs: The insert CREATE TABLE public .test ( ID INTEGER NOT NULL, CONSTRAINT PK_test PRIMARY KEY ( ID ),...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/15/learning-postgresql-differences-in-implementation-of-constraints.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51774" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: Embracing Change With Copying Types and VARCHAR(NO_SIZE_NEEDED)http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/12/learning-postgresql-embracing-change-with-copying-types-and-varchar-no-size-needed.aspx2013-11-12T16:56:00Z2013-11-12T16:56:00ZPostgreSql 9.3 allows us to declare parameter types to match column types, aka Copying Types. Also it allows us to omit the length of VARCHAR fields, without any performance penalty. These two features make PostgreSql a great back end for agile development, because they make PL/PgSql more resilient to changes. Both features are not in SQL Server 2008 R2. I am not sure about later releases of SQL Server. Let us discuss them in more detail and see why they are so useful. Using Copying Types Suppose...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/12/learning-postgresql-embracing-change-with-copying-types-and-varchar-no-size-needed.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51708" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: old versions of rows are stored right in the tablehttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/07/learning-postgresql-old-versions-of-rows-are-stored-right-in-the-table.aspx2013-11-07T20:26:00Z2013-11-07T20:26:00ZPostgreSql features multi-version concurrency control aka MVCC. To implement MVCC, old versions of rows are stored right in the same table, and this is very different from what SQL Server does, and it leads to some very interesting consequences. Let us play with this thing a little bit, but first we need to set up some test data. Setting up. First of all, let us create a numbers table. Any production database must have it anyway: CREATE TABLE Numbers ( i INTEGER ); INSERT INTO Numbers ( i ) VALUES...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/07/learning-postgresql-old-versions-of-rows-are-stored-right-in-the-table.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51644" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspxLearning PostgreSql: overloadinghttp://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/05/learning-postgresql-polymorphism.aspx2013-11-05T21:17:00Z2013-11-05T21:17:00ZFunctions in PL/PgSql can be overloaded, which is very different from T-SQL. Demonstrating overloading For example, the second CREATE FUNCTION in the following script does not replace the first function - it creates a second one: CREATE OR REPLACE FUNCTION public .GetQuoteOfTheDay ( someNumber INTEGER ) RETURNS VARCHAR AS $body$ BEGIN RETURN 'Say my name.' ; END ; $body$ LANGUAGE plpgsql ; CREATE OR REPLACE FUNCTION public .GetQuoteOfTheDay ( someNumber REAL ) RETURNS VARCHAR AS $body$ BEGIN RETURN...(<a href="http://sqlblog.com/blogs/alexander_kuznetsov/archive/2013/11/05/learning-postgresql-polymorphism.aspx">read more</a>)<img src="http://sqlblog.com/aggbug.aspx?PostID=51624" width="1" height="1">Alexander Kuznetsovhttp://sqlblog.com/members/Alexander+Kuznetsov.aspx