[SA 9.0.176 Pro] - SQL Ed. Exec Plan does not match SSMS

HRMMM

So I just profiled me opening a new tab in SSMS and this is one of the statements that came through:

Code:

SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON

The same thing with SQL Editor just does some of the DB Queries to figure out tables and indexes and such. No defaults set at all....

It seems like there should be some sane defaults...

	Mindflux wrote:
	It just seems with how much trouble and how much talk there is about the impact (positive or negative) about ARITHABORT it would seem like it would make sense to set it on or off (depending on circumstances).

Yes, but if you suspect it could be an issue you better play safe and set them explicitly rather than relying on settings that might not have the desired value. Imho.

gemisigo wrote:

	Mindflux wrote:
	It just seems with how much trouble and how much talk there is about the impact (positive or negative) about ARITHABORT it would seem like it would make sense to set it on or off (depending on circumstances).

Yes, but if you suspect it could be an issue you better play safe and set them explicitly rather than relying on settings that might not have the desired value. Imho.

Interestingly enough if your Compatibility level is set to 90 or more SET ANSI_WARNINGS ON already implies ARITHABORT ON.

Though that doesn't really fix our issue with the connections coming from SQL Assistance.

To the best of my knowledge, if you use ODBC for the connections, you can set most of these options in DSN profile settings as defaults (there is a bunch of check boxes for that). In SQL Assistant, you can connect to your database by DSN name. With ADO.NET connections, you can use the custom settings section in SQL Assistant Options to specify additional parameters in the connection string, but you are limited to the options supported by ADO.NET data provider (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396)

ODBC DSN's only allow you to set a few options, ANSI NULLS, QUOTED IDENTIFIERS and one or two others. It's not really feature parity with what SSMS sets for defaults. That sucks... that and having to rely on a DSN rather than a DSNLESS connection we are used to using with SQL Assistant.

Can you think of another way to set some connection defaults like SSMS does via ODBC or ADO?