如何在 Visual Studio 中开发 t-sql? [英] How to develop t-sql in Visual Studio?
问题描述
我们使用带有 SSDT 的 Visual Studio 2013 主要用于版本控制 t-sql 代码，因此正在开发服务器上开发 sql，然后我们使用模式比较将脚本传输到 Visual Studio(并检查到 Git).在部署之前(我们目前也使用模式比较)，我们必须替换数据库和服务器引用(使用 [$(database)] 等).如果我们在开发服务器中更改代码并再次比较，这样的 SQLCMD 变量将再次丢失.(我希望模式比较足够智能以保留 SQLCMD 变量，但我发现没有办法做到这一点).
We are using Visual Studio 2013 with SSDT mainly for versioning t-sql code, so the sql is being developed on the dev server and then we use schema compare to transfer the scripts into visual studio (and check into Git). Before deployment (which we currently do with schema compare, too) we have to replace database and server references (with [$(database)] etc.). If we change the code in the dev server and compare again, such SQLCMD variables are lost again. (I would expect schema compare to be smart enough to retain the SQLCMD variables but I found no way to accomplish this).
逻辑步骤是从一开始就在visual studio中开发sql.但到目前为止，很难说服团队中的任何人这样做.一个可以写sql在VS中执行，没问题.也可以切换到SQLCMD模式执行，好吧.但是当你创建例如VS中的一个视图，你必须写一个create语句，当然这可以执行一次，但是当改变视图并再次执行create语句时会产生错误.
The logical step is to develop sql in visual studio from the start. But so far, it has been hard to convince anybody in the team to do that. One can write sql and execute it in VS, no problem. One can also switch to SQLCMD mode and execute, all right. But when you create e.g. a view in VS, you must write down a create statement and of course this can be executed once but will yield an error when altering the view and executing the create statement again.
所以我的问题是，是否有人对如何专门在 Visual Studio 中进行数据库开发有一些基本技巧.我们能够直接获得数据库引用和所有这些，但无法获得开发过程.
So my question is if anybody has some essential tips on how to do database development exclusively in Visual Studio. We were able to get the database references and all that straight, but not the development process.
推荐答案
几年来，我一直在使用 Visual Studio 数据库项目来简化本地数据库的开发和部署.这里有一些提示.
I've been streamlining local database development and deployment using Visual Studio database projects for a few years now. Here are some tips.
一般来说...
使用本地数据库实例:每个开发人员都应该在本地安装自己的数据库实例.所有脚本(表、视图、存储过程等)都应该在 Visual Studio 中开发.创建用于将项目部署到本地数据库实例的发布配置文件.
Use local db instances: Each developer should have their own database instance installed locally. All scripts (tables, views, stored procs, etc.) should be developed in Visual Studio. Create a publish profile for deploying the project to the local db instance.
使用发布功能: 令人困惑的是，Visual Studio 提供了部署和发布选项，它们最终会做同样的事情.我建议只使用 Publish，因为它在 UI 中更为突出，您可以创建配置文件来为各种数据库实例配置部署过程.
Use Publish feature: Confusingly Visual Studio provides both a Deploy and a Publish option which ultimately do the same thing. I recommend using just Publish because it's more prominent in the UI and you can create profiles to configure the deployment process for various database instances.
使本地数据库保持最新:当开发人员对数据库项目进行更改并将其检入源代码管理时，其他开发人员应检出这些更改并将项目重新发布到他们的本地数据库.
Keep local db up to date: When a developer makes changes in the database project and checks them in to source control then the other developers should check out these changes and republish the project to their local databases.
创建与更改语句
您的所有语句都应该是 Create 语句.不需要 Alter 语句或存在检查.一切都应该像第一次创建数据库对象一样编写脚本.部署或发布时，VS 会知道是否对现有对象发出 Alter 语句.
All of your statements should be Create statements. There is no need for Alter statements or existence checks. Everything should be scripted as if you are creating the database objects for the first time. When you deploy or publish, VS will know whether to issue Alter statements for existing objects.
数据
一些想法:
将您的数据编写为一系列 Insert 语句.将它们包含在数据库项目的部署后脚本中.但这可能很乏味且容易出错.
Script your data as a series of Insert statements. Include them in a post-deployment script in the database project. But this can be tedious and error-prone.
保留包含所有测试数据的数据库备份.首次设置开发环境时，从备份创建数据库.在对数据进行重大更改后，创建一个新备份并让您的开发人员从备份中重新创建他们的数据库.在大多数情况下，如果备份与项目中定义的架构不同步也没关系——只需重新发布项目(确保关闭重新创建数据库"设置，以便仅发布差异，从而发布数据没有丢失).
Keep a database backup that includes all of your test data. When setting up a development environment for the first time, create the database from the backup. After you make significant changes to the data, create a new backup and have your devs recreate their databases from the backup. In most cases it's ok if the backup is out of sync with the schema defined in the project -- simply republish the project (make sure to turn off the "Re-create database" setting so that only the differences are published and thus the data is not lost).
可能有 3rd 方工具可以做到这一点，在这种情况下，它们值得研究.
There may be 3rd party tools to do this in which case they are worth looking in to.
创建您自己的数据部署解决方案.我的涉及以下内容并且工作得非常好(但需要大量的时间和精力！):
Create your own solution for deploying data. Mine involved the following and worked really nicely (but required a lot of time and effort!):
- 存储在 XML 文件中的所有数据 - 每个表 1 个文件 - 其结构类似于表
- 一个可执行文件，用于读取 XML 文件并为每行数据生成 SQL 合并(或插入/更新)语句并将它们保存到 SQL 脚本中
- 数据库项目中的预构建事件，用于运行可执行文件并将生成的 SQL 脚本复制到项目中的部署后脚本
- 发布项目，数据将在部署后推送
测试/生产部署
发布功能:您可以为测试和生产环境创建发布配置文件.但是，它将包括您的部署前和部署后脚本，您将无法获得其他选项提供的多功能性.
Publish feature: You can create publish profiles for your test and production environments. However it will include your pre- and post-deployment scripts, and you won't get the versatility that the other options provide.
dacpacs: Ed Elliott 在他的回答中涵盖了它们.优点:无需Visual Studio 部署，可以通过SQL Management Studio 或命令行使用sqlpackage.exe 进行部署，比T-SQL 部署脚本更容易使用.
dacpacs: Ed Elliott covered them in his answer. Advantages: no need for Visual Studio to deploy, they can be deployed via SQL Management Studio or the command line with sqlpackage.exe, they can be easier to work with than a T-SQL deployment script.
架构比较:如果您可以使用 Visual Studio 进行部署，并且您喜欢仔细检查正在部署的所有更改，则架构比较可能会很好.您还可以有选择地忽略更改，这在您不够幸运没有完全反映生产环境的开发环境时很有用.
Schema Compare: Schema compare may be good if you can use Visual Studio for your deployments and you like to double check all of the changes being deployed. You can also selectively ignore changes which is useful when you aren't lucky enough to have a development environment that completely mirrors production.
这篇关于如何在 Visual Studio 中开发 t-sql?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
