undefined | Better HN

0 pointszamalek10y ago0 comments

> optimize queries well

There are a few obvious cases which don't work (e.g. scalar UDFs aren't inlined - always use table UDFs), but the performance battle Oracle and Microsoft had circa 2000 really shows. The vast majority of the time you can code your intent and the query optimizer will simply do the right thing.

The main reason I would choose SQL over any of the competition is DataDude/VSSQL. We started using it where I work (~5100 schema objects) and it's revolutionary. T-SQL becomes a first-class language in Visual Studio:

- Full editor integration: intellisense and errors as you type.

- Build system: build (including static analysis) and deploy from Visual Studio. MSBuild-based integration for CI.

- Schema delta: you write/update your schema as though you are writing it for the first time and, as part of the build, the ALTER script will be generated for you. You rarely have to write migration scripts yourself (I've only had to when migrating data across columns/tables).

I have been thinking about solving this for PostgreSQL because I'm now completely unable to work without it - I just wish I had the time. Any other form of SQL development now feels like VBScript development in Notepad.

0 comments

wernercd10y ago

Links to more information about this? I've found some information about VSDBPro but it all is dated 2008. I like the sounds of this, but it seems dated...

Update: Seems like this has become SQL Server Data Tools (but not the BI tools of the same name?). I'll have to look into this more.

zamalekOP10y ago

As far as I remember it has only ever been optional in VS2010. It was introduced by default in 2013 (.dbproj) and then reworked in 2015 (.sqlproj). All you need to get this going is: Visual Studio > File > New > Project > Other Languages > SQL Server.

You can also get it to reverse-engineer an existing database into a project[2].

Getting it to work for CI isn't turn-key. When you build a SQL/DB project it results in both a CREATE/ALTER script (depending on whether you do a diff build or a CREATE build) as well as a schema file (.dbschema). You need to track previous versions of the dbschema yourself (we use branching to track this file across releases), so that it can it has the base for diffing during the build.

It's a good idea to add a database reference to [sys] and [master] in all your projects (which is not done by default).

Knowing the right keywords I Googled a bit for you:

[1]: https://visualstudiomagazine.com/articles/2015/01/01/visual-...

[2]: https://www.youtube.com/watch?v=kWKllVyozOg (talks about SQLAzure, but these tools do work on-premise)

[3]: http://www.codeproject.com/Tips/998465/Creating-a-SQL-Server...

[4]: https://blogs.msdn.microsoft.com/ssdt/2014/07/24/sql-server-... (if you use TFS CI, otherwise you can invoke MSBUILD directly from whatever CI you use)

j / k navigate · click thread line to collapse