The surrogate ID on sys.database_principals is an integer. This suggests a maximum user count of a billion-ish. The Maximum Capacity Specifications for SQL Server mentions no limit on logins or users that I can see. It does say there is a maximum of 32,767 connections at any one time. From any practical point-of-view there's nothing to stop you creating one user per application.

From a performance perspective, I've never seen an issue that resolves to too many user being defined. This is different too many being active. Each active connection consumes a small but finite resource. From what I remember this resource is not shared if different connections have the same user, so nothing to gain there.

(There is such a thing as connection pooling, however. To improve performance all connection requests must be constructed identically, however, including user names. This could work per application (with application-specific users) or per instance (with a shared user). To be effective developers must be meticulous with their connections.)

User credentials and permissions have to be stored somewhere. So, yes, having a ludicrous number of users will consume measurable resource. Since SQL Server is built to handle terabytes of data I doubt this will ever be a limiting factor. Considering how often authorisation is checked I'd imagine the storage and processing code paths have had a lot of optimization attention through the years.

This is just a long way of saying many users equals more resource but you won't be able to measure the downside.

The overwhelming considerations for me are the benefits achieved in security and monitoring by having separate credentials.

A single user, used by all applications, must by definition have access to all the data. If there's a piece of data it doesn't need that data is not used by any application and shouldn't be in this database. That's a security risk. One breach by one bit of one application opens the whole data infrastructure to exploitation. In contrast having separate users which are granted only the SELECT or EXECUTE rights they explicitly require limits the damage any one application failure can expose.

Secondly - monitoring: something, somewhere will consume too much resource. Knowing it's running as user_generic is not helpful. Knowing it's running as user_nightly_product_load will tell you exactly which code to examine. The complement of this is Resource Governor which allows limiting a connection's access to CPU and memory, if that connection can be distinguished from others. The easiest way is to have separate user credentials.

Just for giggles I started a VM with SQL Server 2017 RTM, default installation. I set it to CREATE USER <user_name> WITHOUT LOGIN. The user names were created from a SEQUENCE.  It took a while, and the creation rate decreased over time, but it created over 270,000 users before I gave up. If there is a limit, it's greater than that.


Moving SQL Server database to a new disk while online

One method to move the entire database is with BACKUP and RESTORE.  The database will be unavailable during the final switch but downtime should be minimal with planning.  This procedure assumes the FULL or BULK_LOGGED recovery model:

1) Perform a FULL backup (or use your existing one).

2) Restore latest full backup to a different database name, specifying the WITH MOVE option to relocate files on SSD storage as desired and the NORECOVERY option so that subsequent differential and log backups can be restored.

3) Apply incremental changes to the new database until the time of the final cut-over with transaction log backups and RESTORE...WITH NORECOVERY.  This will minimize downtime for the final switch to the new database.

4) To switch to the new database, take the application offline, perform a final transaction log backup, and apply to the new database WITH RECOVERY.  Finally, rename the original database to a different name and rename the relocated database to the original name.  Drop the old database at your convenience.

In the SIMPLE recovery model, you could use a similar process but without the step 3 transaction log backup/restores.  Instead, use a differential database backup/restore in the final step.  That may require more downtime, depending on the amount of changes since the initial FULL backup.


Setting up Power BI to schedule publishing from PostgreSQL

You should be able to do this using the Power BI On-premises data gateway.  You'll need to install it on a Windows computer, along with the Postgres ODBC driver, but it shouldn't have any problem talking with your Postgres install on Linux.


What is the maximum latency / distance mysql replication can tolerate without having errors or wrong data?

You should consider what distance does to any DB connection.
I have some posts from myself and others on this subject

Jul 25, 2012 : Mysql database replication on different vlan/subnet/another site (From Me)
Aug 26, 2012 : Mysql database replication on different vlan/subnet/another site (Shlomi Noach)
Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication (From Me)

Another member of this StackExchange, Aaron Brown, wrote a nice blog about measuring latency even with semisynchronous replication. Although his blog is 8 years old and technology has improved since, you should write the same test Aaron did to test the latency yourself. After all, London to Tokyo is intercontinental where US West to US East is within the same country. Latency should be expected, but only your testing will reveal how tolerable it will be.
Please review them and see if you can setup semisynchronous replication.
CAVEAT : if you are using Amazon RDS or Google GCP, you are at their mercy when it comes to latency.


Database Design for Maintaining Supplier Invoice

Don't worry about having too many transactions in your table.

Relational Database Management Systems like MySQL are designed to handle amounts of data that would overwhelm other formats, like a spreadsheet for example.

You can easily store millions of records in your table and MySQL will find you the ones you want quickly, especially if you define indexes on the fields that will be part of your WHERE clauses in your queries.  For example, if you want to do monthly reporting, make sure you have an index on transdate.


Table with too many nullable columns - is there a better design?

You could have a table

CREATE TABLE user_info (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   user_id bigint NOT NULL REFERENCES users,
   property text NOT NULL,
   value TEXT NOT NULL
);

and store the additional properties there. That would be the relational way of doing it.

In recent PostgreSQL versions you could also add a jsonb column for all such information.

With a GIN index on it you can filter efficiently as long as you are looking for certain keys and their value. But you won't be able to do advanced things like substring or similarity matches that way.

It is a matter of taste which one you prefer.


The short answer is no, MySQL does not have that 'feature'.

The long answer (and opinion)...

Perhaps 1% of tables are partitioned.  Perhaps 1% of partitioned tables could use that feature.  Now imagine the effort to implement, test, and deploy such an obscure feature.  Oracle has had several decades to incorporate little-used features like that.  MySQL is relatively young.  It is still lean.  (But obscure features are creeping in.  That one has not shown up yet.)

The reason why only 1% of tables are partitioned is that there is no performance to be gained for the other 99%.  Usually an appropriate index will do just as good as partitioning.

Would you like to present your use case?  (Beware:  I may say that you gained nothing by using partitioning.)


SQL Server Extended Event - How to reduce the fields of the event "sql_statement_completed"?

I'm going to say you are getting all the extra fields because you picked a template when you built your event session (or used the wizard). If you only want certain fields then go uncheck what you don't want. However, there will be some event level info that will always be there, what is shown under the "Event Fields" for each event are their by default and cannot be removed.

Default event for "sql_statement_completed" only captures this, after I checked the global field for client application name:


Easiest way of shutting down a PostgreSQL server on Ubuntu

I decided to go the route of a fresh install. So instead of trying to figure out what postgresql-command will shut down the server I used:

   sudo apt-get purge postgresql-9.1

and:

   sudo apt-get purge postgresql-9.3

A fresh install might be an easier solution to some of my other problems I had since upgrading to Ubuntu 14.04.

I found this at:

https://askubuntu.com/questions/451569/postgresql-broken-after-14-04-despite-succesful-pg-upgrade-solved


SQL Server Fulltext search against big amount of search terms over some period of time

Execution plan XML would be useful here but as a shot in the dark:

Daft as it may sound, try pushing either the [id] or date predicate into the CONTAINSTABLE query. See SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned - Consider embedding filter conditions as keywords in the indexed text.
I don't remember where but I recall reading an article or blog post some time ago that flagged big OR full-text queries as problematic. IIRC the suggested hack/workaround was to issue a UNION query instead.

For your example, the UNION for 2) would be along the lines of:

INSERT
    @processed_rules
    (
    rule_id
    , story_id
    )
SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term one"')

UNION

SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term two"')

UNION

  SELECT
    @rule_id
  , s.[key]
FROM
   CONTAINSTABLE(Stories, (header, body), '"term three"')


Can't connect to the DB2 database on localhost, using DBeaver 3.1.1

First make sure db2 is started:

#> db2 connect to <db>

If that's not ok, use db2start. Next what port is db2 listening to?

#> cat /etc/services | grep db2

If it's not 50000 change your connections string in DBeaver. Next try to telnet to the port (assuming it is 50000):

#> telnet localhost 50000

If that's fine (saying something like Escape charater is ^]), check if DB2COMM is set:

#> db2set | grep DB2COMM

It should say DB2COMM=TCPIP

Is it a firewall issue? Check with:

#> sudo iptables -L


What is the data type 'name' in PostgreSQL

Name is a 63 byte (varchar) type used for storing system identifiers.

Using psql you can get type information using \dT or \dT+

So for the name type:

# \dT name

                           List of data types
   Schema   | Name |                 Description
------------+------+---------------------------------------------
 pg_catalog | name | 63-byte type for storing system identifiers
(1 row)

or

# \dT+ name
                                                    List of data types
   Schema   | Name | Internal name | Size | Elements |  Owner   | Access privileges |                 Description
------------+------+---------------+------+----------+----------+-------------------+---------------------------------------------
 pg_catalog | name | name          | 64   |          | postgres |                   | 63-byte type for storing system identifiers
(1 row)


Update all rows in the whole nested table:

update /*+ NESTED_TABLE_GET_REFS */ person_salaryTypes
set salary = salary * 1.1;
commit;

Update all rows in the nested table for 1 person:

update table(select p.salarytype from person p where p.id = 1)
set salary = salary * 1.1;
commit;

Update 1 row in the nested table for 1 person:

update table(select p.salarytype from person p where p.id = 1)
set salary = salary * 1.1
where startdate = ...;
commit;


Determine Which Parameters Passed Into Stored Procedure

You can run a Profiler Trace using the default template.  It will include the RPC:Completed Event.  When you review the trace log you will see the values of the parameters passed and you can determine which were left out, therefore using the default values.


Install galera cluster without remove existing mariadb

If you installed MariaDB 10.1, you don't need to remove anything because MariaDB Server and MariaDB Galera Server packages have been combined and Galera packages and their dependencies get installed automatically when installing MariaDB 10.1.

So you must only configure galera without remove anything.

Reference


How do I replace strings of a table from another table column

In Oracle SQL it goes like this :

UPDATE FIRST_TABLE FT
   SET FT.VALUE =
       (SELECT ST.REPLACE FROM SECOND_TABLE ST WHERE FT.VALUE = ST.SEARCH)

But for this this to work properly, keep in mind that the attribute Search in the SECOND_TABLE must be unique, otherswise you'll need to rearange your query.


PostgreSQL procedural languages - differences between PL/pgSQL and SQL

PL/PgSQL and plain SQL functions are both part of a larger tool set, and should be viewed in that context. I tend to think of it in terms of an ascending scale of power matched by ascending complexity and cost, where you should use the simplest tool that'll do the job well:

Use views where possible
Where a view is not suitable, use an SQL function
Where an SQL function isn't suitable, use PL/PgSQL.
Where PL/PgSQL is too limited or not expressive enough, use PL/Perl, PL/Python, PL/V8, PL/Java, or whatever your preference is
... and where no PL will do the job, use an external program and possibly LISTEN and NOTIFY to talk to it.

Very frequently a view is sufficient when you think a function is needed. Even if it's extremely expensive to SELECT the whole view, WHERE clauses in the query referencing the view are usually pushed down into the view and may result in very different query plans. I've often had big performance improvements from converting SQL functions into views.

The main time you find you can't use a view and should consider an SQL function is when:

Parameters that can't be expressed as simple WHERE clauses are needed, like a parameter within a WITH expression
You want a security barrier via a SECURITY DEFINER function, and the security_barrier views in PostgreSQL 9.2 and above aren't sufficient for your needs;
You need parameters that aren't pushed down into sub-clauses of a view by the optimizer and want to control it more directly; or
There are lots of params or there's lots of repetition of the params, so it's impractical to write the query as a view.

For most of those tasks a plain SQL function works fine, and is often easier to read than PL/PgSQL. SQL functions declared STABLE or IMMUTABLE (and not also declared STRICT or SECURITY DEFINER) can also be inlined into the calling statement. That gets rid of the function call overhead and can also sometimes result in huge performance benefits when a WHERE condition in the calling function gets pushed down into the SQL function by the optimizer. Use SQL functions whenever they're sufficient for the task.

The main time SQL functions won't do the job is when you need lots of logic. If/then/else operations that you can't express as CASE statements, lots of re-use of calculated results, building values up from chunks, error handling, etc. PL/PgSQL comes in handy then. Choose PL/PgSQL when you can't use SQL functions or they're a poor fit, like for:

Dynamic SQL and dynamic DDL via the EXECUTE statement
When you want to RAISE errors/warnings for the logs or client
When you need exception handling - you can trap and handle errors with EXCEPTION blocks instead of having the whole transaction terminate on error
Complex conditional logic that doesn't fit CASE ... WHEN very well
Lots of re-use of calculated values that you can't do fit into WITH and CTEs
Building dynamic records
You need to perform an action after producing the result set

With common table expressions (CTEs), especially writable CTEs and WITH RECURSIVE I find I use PL/PgSQL a lot less than I used to because SQL is so much more expressive and powerful. I use views and plain SQL functions a lot more now. It's worth remembering that plain SQL functions can contain more than one statement; the last statement is the function's result.


How do you create a relationship to a non-primary key in SQL Server?

Create a unique index or unique constraint on UserName then you can reference it in a FK constraint fine.

Your statement that

  Sql Server doesn't allow me to create a relationship on a non primary
  key column

is incorrect. SQL Server only cares that the column(s) participating in the FK relationship have a unique index defined.


Mysl hangs when calling function inside select

Consider adding the keyword DETERMINISTIC to the CREATE FUNCTION.


Use of smallserial gives error with PostgreSQL

Smallserial has been introduced in core with Postgres 9.2. In earlier version you could emulate it as it is only a short form. Documentation of 9.2 gives a hint for it


Joining ~ 40 normalized tables for 200 columns to display detailed user profile. Can this be optimized?

Option 4 - correct your schema design and stop using 'IDs' or whatever other surrogate key, the zodiac table is NOT a lookup table. it's a relation that holds all zodiac signs, and the way you tell the zodiac signs apart is their name, therefore, the key should be:

CREATE TABLE Zodiacs (Zodiac VARCHAR(11) PRIMARY KEY);

Use the Zodiac as the foreign key in your profile table, and now you don't need a join at all.
Do that to all the other one-to many as well, like relationship status, company, school etc. and you just saved yourself most of the joins.
For the many-to-many, like languages and countries, so the same, and you will now need only one join instead of two for each relation.
Give it a try and see how all your queries magically shrink by 90%, and perform orders of magnitude better. you will also explicitly maintain uniqueness and prevent logical duplicates, and you will also see how filters perform orders of magnitude better as the optimizer will be able to correctly evaluate distribution of the actual value, not some random ID from another table.

BTW - there are no fields in a SQL database, there are columns and rows only.
These are the counterparts of Tuples and attributes in the relational model

HTH


Querying from system.schema_keyspaces generates code=2200 [Invalid query]

The issue you are having here, is that you are using docs to explore Cassandra that are for version 2.0 and 2.1.  My guess is that you are probably using Cassandra 3.0.  In that case, you will need to query the system_schema keyspace for the "keyspaces" table:

cassandra@cqlsh:system_schema> SELECT * FROM system_schema.keyspaces;

 keyspace_name      | durable_writes | replication
--------------------+----------------+----------------------------------------------------------------------------------------
               test |           True | {'PowerWindows': '1', 'class': 'org.apache.cassandra.locator.NetworkTopologyStrategy'}
        system_auth |           True |    {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '1'}
      system_schema |           True |                                {'class': 'org.apache.cassandra.locator.LocalStrategy'}
 system_distributed |           True |    {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '3'}
             system |           True |                                {'class': 'org.apache.cassandra.locator.LocalStrategy'}
      system_traces |           True |    {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor': '2'}

(6 rows)

Cassandra underwent a major storage engine rewrite for 3.0+, so much of the schema interactions are different as of that version.  In the future, be sure to double-check valid documentation versions with your local Cassandra version.  From cqlsh, you can query that:

cassandra@cqlsh:system_schema> SELECT release_version from system.local;

 release_version
-----------------
             3.4

(1 rows)


How to start Oracle enterprise manager on Windows 64 bits

You need to start the service, e.g.

net start OracleDBConsoleorcl

The service name might be different on your system, but it usually starts with OracleDBConsole and then contains the service name of your database.

If you want it to always run, set the service start automatically through the control panel.


This may help you get closer to a solution:

You can & and | (that's 'and' and 'or') SET values.  Unfortunately you get an integer, the bits of which represent the result of the bit-wise operation.

The result of & will be non-zero if some fields match.

The result of & can be fed into BIT_COUNT() to see how many things match.

(a & b) != 0

should give you 1 for the item being in the set, zero if not.


You could either use Adam Mechanics sp_whoisactive or the below script and periodically insert the results into a table for later analysis.

Brent Ozar has a good post on how to do this is you decide you use sp_whoisactive

    SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    CASE [owt].[wait_type]
        WHEN N'CXPACKET' THEN
            RIGHT ([owt].[resource_description],
            CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],
    [es].[program_name],
    [est].text,
    [er].[database_id],
    [eqp].[query_plan],
    [er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON
    [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
    [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
    [es].[is_user_process] = 1
ORDER BY
    [owt].[session_id],
    [owt].[exec_context_id];
GO

For you info dm_os_waiting_tasks are what is currently happening on your sql server, dm_os_wait_stats are what has happened in the past/or since you restarted sql or it's services.


MySql takes too long with matching index

I think that in your case a hidden type conversion is the cause:

`id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci'

but in query:

WHERE id=5586929

It means INT

Change this to:

WHERE id='5586929'

Also from my point of view (not related to SELECT performance, but generally):

UNIQUE INDEX `id` (`id`, `minute`),
INDEX `id_minute_index` (`id`, `minute`)

is a duplicated construction. UNIQUE is already an index. There is no need for the second one.


Putting in a simple language :

A login gives you access to server.

A user gives you access to database.

A login must map a user so that you can access the database.

For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This script will help you or this SSMS addin from sqlskills.

For Windows Authentication, you should create a login and map it to your user database. Create/transfer the same login to other nodes.

A listener is a virtual interface and has nothing to do with logins or users.


If you make changes to the schema of the subscriber then it should keep running, but it really depends on the schema change that you make.  If you change an nvarchar(50) to varchar(10) then there's going to be data truncation problems that you have to deal with.

Also there's no way to simply switch replication from one direction to another.  Doing so would require deleting all the replication from the first server then setting up the replication from the second server back to the first server.

Depending on the changes that you need to make there should be basically zero downtime unless you are doing massive data changes or adding columns with default values or other large offline operations.


If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table.  Rather than trying to tune your current query, I would bet that the better answer would be to create a separate table that has just those 5 distinct values, create a foreign key relationship between your current table and the new lookup table, and then modify your query to hit the new lookup table instead.


macOS Catalina - postgreSQL - sysctl.conf still relevant?

You don't need the shared memory parameters any more, because PostgreSQL has been using POSIX shared memory for a while now.

Making sure that the PostgreSQL user can have enough open files is a good idea though.


What's a good way to add a PRIMARY KEY to a large InnoDB table?

this may be the wrong way to go about it, but..

couldn't you just alter the table structure,

add a primary key column to the table

then...

write a quick script to go through the entire table

adding an autoincrementing value to the new column?


The log seems to display an output of the DBCC MEMORYSTATUS, which is what happens when the engine runs out of memory. Did you configure the maximum amount of memory SQL Server can use ? To find out, you can run the following query :

SELECT  *
FROM    sys.configurations
WHERE   name LIKE '%mem%'

For the row for which the column named "name" is valued to "max server memory (MB)", you should see a value different greater than zero.

If it is set to zero, you can easily configure it with the following code, assuming that you server has a couple GB or RAM and is dedicated to SQL Server :

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'max server memory (MB)', [your_server_RAM_amount_in_MB_minus_4GB]
GO
RECONFIGURE
GO

EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

This takes effect immediately, without the need to restart the SQL Server instance.

If it is not this, did you update all the statistics of all databases on this instance ?


Cant Uninstall SP3 from SQL Server 2008r2

I try control panel add/remove option but its remove all database instance. Please suggest

That is not exactly the place from where you can uninstall SP, you have to go to add remove programs and then on top left you would see installed updates

Click on view installed updates and you would see all updates for your computer and programs installed on it. Locate SQL Server 2008 R2 Sp3, I have SQL Server 2014 SP2 and it shows like.

Right click on the Service pack and select uninstall.


SQL Multi Subnet Always on High Availability

We have now resolved this issue, was caused by the AWS DNS suffixes it appends to the domains. This caused issues with our domain names.
Have changed this setting to append primary and connect specific DNS suffixes and I was able to see both networks when building the cluster.


How do I find all uses of a data type in a MySQL database?

The following query will search for you usage of these datatypes across a database.

SELECT COLUMN_NAME, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = "database"
      AND DATA_TYPE in ('DATETIME', 'TIME', 'TIMESTAMP',
                        'YEAR(2)', 'YEAR(4)', 'YEAR');


Compare two servers on SQL query performance

One of the best ways to compare two servers is to repeat tasks that you (hopefully) normally do on both:

Run DBCC CHECKDB
Take Full backups
Run any index or statistics maintenance

And of course, one of the best ways to measure the impact they have on your server is to look at wait stats. If those pass, then move on to comparing queries. You generally don't want to test query performance in a vacuum, because a whole lot of other stuff may not run the way you expect it to

I wrote a whitepaper for Google earlier this year on how to do this. It's free, and I suggest you give it a read. It walks through how to do all the stuff you're interested in. Even though it's done using Google's Cloud, it's applicable to any server comparison.


MySQL Workbench is adding --enable-cleartext-plugin option on mysqldump command

"Enable Cleartext Authentication Plugin" is a connection setting, not a setting of "Data Export".

You can find it under "Database", "Manage Connections", here select your connection; inside the "Connection"-tab is the "Advanced"-tab with a checkbox to (un-)select that option.


A good starting point is the MySQL Slow Query Log instead of the general query log. You can set the

You'll want to log queries that aren't using indexes

Update
In your question, you state that the system is 'nice and responsive' over local network, but that you haven't done any performance tuning. The slow query log I pointed out will help you identify queries that are taking a long time to run (over 1 second, if configured that way). IMO, this is a great starting point.
The longer a query takes, it is much worse when the response has to be transmitted over a WAN.

One tool I've recently discovered is mk-tcp-model that analyzes output from tcpdump to help measure how long a request takes to respond. You can see how many request/responses are coming in and how long each takes. The best tuning over a WAN is to reduce the amount of requests you need to make.


Numeric vs Integer for a column - size and performance

Would this be a good idea?

No.

  would numeric(6,0) take less bytes?

No.

test=> SELECT pg_column_size(INT4 '999999'), pg_column_size(NUMERIC(6,0) '999999');
 pg_column_size | pg_column_size
----------------+----------------
              4 |             10
(1 row)

  how about the performance (this table is being queried a lot)?

Slower. It's stored as binary-coded decimal because it's an arbitrary precision value.


Backup policy for Oracle 11g database in non-archive mode

A partly online backup is certainly possible but switching to and back from archivelog mode needs downtime. As an alternative you could run in regular archivelog mode and trash the generated archived log files, until you run a backup.

For the backup to be recoverable it is important that it has ALL archived log files that were generated during the backup. End the backup with a log switch and also include those archives in the backup.

You must see this kind of backup as partially online. It does allow you to restore to the point in time where the backup completed, not to any other point in time. Very similar to regular offline backups, only, taken online. It saves downtime.

Why do you want this? This is a lot of effort just to safe maybe 10G of archive storage per day. It also introduces an extra error source and many dba's won't be able to work with this and expect to have all archives. My advice would be to keep it regular online backups, with regular archived log backups and keep things simple.


The CTE goes inside the view.

Take a query with a CTE

WITH cte AS (...) SELECT ...;

Just add CREATE VIEW AS .. GO

CREATE VIEW
AS
WITH cte AS (...) SELECT ...;
GO

MSDN does describe multiple CTEs (See example j)

CREATE VIEW
AS
WITH
   cte1 AS (...),
   cte2 AS (...),
   cte3 AS (...)
SELECT ...
GO


The SSIS intallation is working as a charm, the only problem was that we wanted to connect to SSIS 2014 from SSMS 2012.

At the end we agreed to upgrade the developers machines to sql server 2014 developer edition, and migrated the packages to sql 2014, and now everything is working fine.

I will leave the question here as a reference, for the community and for myself, because at the end I had so many problems, and they all have been solved.


Effects of monolothic databases on performance and security?

for security, you want your data duplicated on many servers as possible, so if one falls, the others can share the load.

"classic" oltp databases performance issues raise from concurrent transactions. Due mainly to locks, time/transaction grows exponentially. Hence, a simple way to mitigate this issue is to have multiple databases, as there will be less concurrent transactions by database. Thus, this is an attractive duct tape (cheap and easy, at least compared to alternatives).

One thing to consider is that historically the bottleneck has been storage (disks), so databases tend to minimize disk access by consuming lot of cpu and memory. As well, partitioning tables helps reduce the load on disks.

As well, multiple databases allow to cache most of it in memory, seriously improving performance too.

There are other options. Some databases can use multiple machines, or you can pool machines in a virtual one, but that only goes so far too.

The current trend is to split oltp and olap database. This make sense, oltp require fast row "access", olap require high throughput column reads.

Another trick is to have two databases, one in read/write, and one in read only mode. They are in a master/slave configuration. Most reads are done one the slave, and changes on the masters are pushed to the slave. This alleviate the burden on the master database, which deal with transactions.

If you observe your database carefully, you will notice that very few operations really need acid transactions. That when it might be interesting to leave the classical model behind, and build two or three systems. By example one for tickets (which needs transactions as you don't want to sell out of stock products), and another system for the rest. If you update a product price and the old price is used for 0.1 second, it does not mater much (except for an auction system of course). Point is, with a traditional system, you can't sell tickets while you update the price, as the row will be locked, and it will be unavailable for much longer (1-10 second), as your traditional db (which is now massive) is much slower.

Typically the transactions db will be "in memory" (based on kernel locks, or multi-paxos), and the read one in a "nosql" db for 99% of the rest. The in memory logs are then merged to the nosql database. Combined with other tricks it scales linearly, and hence can grow in ways traditional databases can difficultly achieve.

This is more complicated, require analysis, learning new paradigms, and massive time to implement. To my knowledge, it is used mainly by "giants" like google or twitter mainly. This is the basis of the "big data" movements.

If your company uses a traditional sql database, you better off using all the tricks available (sharding, partitionning, read/write dbs, vm) before changing your full echosystem.


Planning of Select on PostgreSQL 9.6 partitioned table very slow

The normal advice is not to go above 100 or so partitions, and 9300 is way beyond that.  But still, it shouldn't take 12 seconds.  Creating a simple simulation of 9300 partitions in 9.6, each with one constraint and one index, it takes about 1.2 seconds to plan a query (or about 2.0 seconds to do it the first time in a given session) which hits the parent and exactly one child.

I've repeated the simulation with two separate check constraints, instead of one constraint which ANDs together the conditions, and with an additional index on all the tables, and it didn't meaningfully change the results.  Planning it is not speedy, but still nowhere near 12 seconds.

Can you monitor the system (with top, for example) to see what is going on?  Or better yet, use perf top to identify the bottleneck?


If I understand your question correctly, your examples are just storing the vcount of visitors (visitors_stored) from today.

This means

You'll want a script to reset the visitors_today each day at midnight.
You'll want a trigger on visitors_stored to update the visitors_today table.

Why would you do it? If you regularly wanted to know how many visitors were from today, you would do a

SELECT vcount FROM visitors_today

instead of

SELECT v_count FROM visitors_stored WHERE vcount_date=NOW(); -- Probably wouldn't use NOW, but an actual static date!

It depends on how your server is tuned, but generally if you have an index on the vcount_date column in visitors_stored, it will suffice. If your visitors_stored table was overly large (>100GB say), the cache table might be of use.

If you start to see performance issues with computed columns, a cache column or table might be the way to go.


Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server

Create an explicit schema or schemas that are separate from any other schemas and objects that are replicated from your publishers.

GRANT ALTER ON SCHEMA::YourNewSchema TO User_Who_Can_Create_Stored_Procs;
GRANT CREATE PROC TO User_Who_Can_Create_Stored_Procs;
GRANT ALTER PROC TO User_Who_Can_Create_Stored_Procs;
GRANT DROP PROC TO User_Who_Can_Create_Stored_Procs;


"ENUM" is not vallid at his position, expecting ')'

MySQL (and SQL in general??) is very picky about the order of things1.
province CHAR(2) DEFAULT 'QC' ENUM('QC','ON','NB')

-->
province  ENUM('QC','ON','NB')  DEFAULT 'QC'  -- if you want ENUM

But also, ENUM and CHAR are two different datatypes.  So, maybe you wanted this:
province CHAR(2) DEFAULT 'QC'                 -- if you want CHAR

1 - You should try to become comfortable reading syntax diagrams, such as this for the create table statement.


SQL Query is sometimes slow, sometimes fast

There could be several reasons why the execution time is varying. The first step I would take is to test the query directly using the MySQL client from your MySQL server. This example is for Linux.

Create a script to test your query

vi query_1.sql

-- SQL_NO_CACHE means do not use the cache so you get the a more accurate execution time
SELECT SQL_NO_CACHE column_1, column_2 FROM sometable WHERE column_N = 'somevalue';
Save your file \wq
Execute the query from the console using the time command

time mysql -u root -p somedatabase < query_1.sql

real    0m2.009s

user    0m0.000s

sys     0m0.004s

You will get an output showing how long it truly took to execute your query. The times should remain constant.

You can also perform the same test from your rail app host, if you have the mysql client installed on that machine.

time mysql -h address.ip.database.host -u root -p somedatabase < query_1.sql

This might help to identify possible network issues if the query exectuion time is much longer from the Rails app host.

Update 12:25
If this is a network issue, you can follow network activity using a program like bwm-ng Bandwith Monitor to analyze network activity on every network card installed on your system and the rate at which traffic is traveling through those links.

If that is not the case, then we can move forward by looking at other possible factors such as IO, Memory and other applications


Handling Recovery of Disk Space in MySQL and MongoDB

InnoDB
I have mentioned how to shrink InnoDB tables and the System Tablespace (ibdata1) in the past

Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 15, 2013 : MySQL database drop insanely slow
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full

MyISAM
You could simply run for mydb.mytable
OPTIMIZE TABLE mydb.mytable;

which will shrunk a MyISAM table and recalculate the index statistics. To shrink all MyISAM tables, see my Jul 24, 2012 answer to MySQL: reducing ibdata file size for MyISAM tables
GIVE IT A TRY !!!


If you really want to return the number of rows, You need to create a text file A.SQL:

exec dbo.subprogram;
:EXIT(select @@rowcount)

Because EXIT must be at the beginning of a line, so you can't use -Q. Then you call:

C:\> SQLCMD -S servername -d database -i A.SQL -o A.OUT

Then the returned values by EXIT will be in ERRORLEVEL... You can query with:

C:\> ECHO %ERRORLEVEL%


It makes sense to use the deterministic keyword for procedures that are really deterministic, because:

  Declaring a deterministic routine as NONDETERMINISTIC might diminish
  performance by causing available optimizations not to be used.

But:

  Am I right in thinking that the only non-deterministic procedures are
  ones that call non-deterministic functions like RAND() or CURDATE()?

No, a procedure that reads data from the database and bases it's result on that data (in addition to it's inputs) is also non-deterministic (because that data might change between calls).

It is worth pointing out that whether a procedure is DETERMINISTIC or not needs to be considered in a replication context:

  When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

although:

  If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword.


How do I do an UPSERT in SQL Server, returning the pre-updated values?

You can use merge with the output clause.

Temp table:

create table #foo
(
  id int unique,
  chr char(1)
);

insert into #foo(id, chr) values
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D'),
(5, 'E');

Merge:

with t1 as
(
  select T.id,
         T.chr
  from (values (3, 'M'),
               (4, 'N'),
               (5, 'O'),
               (6, 'P'),
               (7, 'Q'),
               (8, 'R')) as T(id, chr)
)
merge #foo as T
using t1 as S
on T.id = S.id
when not matched then
  insert (id, chr) values(S.id, S.chr)
when matched then
  update set chr = S.chr
output inserted.id,
       inserted.chr,
       deleted.chr as oldchr;

Result:

id          chr  oldchr
----------- ---- ------
3           M    C
4           N    D
5           O    E
6           P    NULL
7           Q    NULL
8           R    NULL


Are GUIDs damaged when inserted into Varchar fields?

Under normal circumstances, a VARCHAR and a UNIQUEIDENTIFIER should implicitly convert in both directions.

However, in your production system, you're comparing two character strings. That's problem one

Problem two is that the formatting you're using (the braces) doesn't make the string into a GUID. If you're comparing a string formatted that way, it will match a GUID with that value (without the braces), but when you do a text comparison, 'X' <> '{X}'.

See the examples below to confirm:

DECLARE @my_guid uniqueidentifier = 'C75F516F-971A-4752-B25B-B9DB877FAE17';
DECLARE @my_varchar varchar(100) = @my_guid;

SELECT @my_guid as [GUID], @my_varchar as [Varchar];

SELECT CASE WHEN (@my_guid = 'C75F516F-971A-4752-B25B-B9DB877FAE17') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_Regular
      ,CASE WHEN (@my_guid = '{C75F516F-971A-4752-B25B-B9DB877FAE17}') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_Braces
      ,CASE WHEN (@my_guid = {guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'}) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_ODBC
      ,CASE WHEN (@my_varchar = 'C75F516F-971A-4752-B25B-B9DB877FAE17') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_Regular
      ,CASE WHEN (@my_varchar = '{C75F516F-971A-4752-B25B-B9DB877FAE17}') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_Braces
      ,CASE WHEN (@my_varchar = {guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'}) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_ODBC
      ,CASE WHEN (@my_varchar = CAST('C75F516F-971A-4752-B25B-B9DB877FAE17' as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_Regular
      ,CASE WHEN (@my_varchar = CAST('{C75F516F-971A-4752-B25B-B9DB877FAE17}' as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_Braces
      ,CASE WHEN (@my_varchar = CAST({guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'} as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_ODBC
;

The results show that comparing the string in braces to an actual GUID, or converting it into a GUID before comparing, works when it's compared to a string; comparing it to a string directly fails.

GUID                                 Varchar
------------------------------------ -------------------------------------
C75F516F-971A-4752-B25B-B9DB877FAE17 C75F516F-971A-4752-B25B-B9DB877FAE17

Test_GUID_Regular Test_GUID_Braces Test_GUID_ODBC
----------------- ---------------- --------------
Matches           Matches          Matches

Test_Char_Regular Test_Char_Braces Test_Char_ODBC
----------------- ---------------- --------------
Matches           Doesn't Match    Matches

Test_Cast_Regular Test_Cast_Braces Test_Cast_ODBC
----------------- ---------------- --------------
Matches           Matches          Matches


From this example, and from the code shown in \df+ pagc_normalize_address I was able to write this CAST.

CREATE OR REPLACE FUNCTION stdaddr2normaddy(addr stdaddr) RETURNS norm_addy AS $body$
  SELECT (
    (addr).house_num,  --address
    null,              --predirabbrev
    (addr).name,       --streetname
    (addr).suftype,    --streettypeabbrev
    null,              --postdirabbrev
    (addr).unit,       --internal
    (addr).city,       --location
    (addr).state,      --stateabbrev
    (addr).postcode,   --zip
    true               --parsed
  )::norm_addy;
$body$ LANGUAGE sql IMMUTABLE;

CREATE CAST (stdaddr AS norm_addy)
  WITH FUNCTION stdaddr2normaddy(stdaddr)
  AS IMPLICIT;

Now you can test it out...

SELECT x.* FROM pagc_normalize_address(
  '3311 My Street Dr., Kingwood Texas, 77345'
) AS x;

SELECT (g.x).*
FROM (
  SELECT x::norm_addy
  FROM standardize_address(
    'tiger.pagc_lex',
    'tiger.pagc_gaz',
    'tiger.pagc_rules',
    '3311 My Street Dr.',
    'Kingwood Texas, 77345'
  ) AS x
) AS g;


Use Natively-Compiled Stored Procedures in disk-based tables

Natively-compiled stored procedures can only reference:

in-memory tables
in-memory table variables / TVPs
in-memory natively compiled inline TVFs

Sounds like what you want is interop, and that can't be done inside a natively-compiled procedure. If it could, you would be discarding much of the performance benefits of the natively-compiled procedure.

This article is a couple of years old but not much about the core architecture has changed in 2016 / 2017:

The Promise – and the Pitfalls – of In-Memory OLTP


What effect will reducing the size of a varchar column have on the database file?

First things first: How much data is there in the table? Number of rows and size of the table?

Second: Can you back up and restore this table to a test server and run the alter statement to see the impact (assuming it is not unfeasible due to the table being too large to fit on a non-Production system)? I always find that testing in my environment is more accurate than advice from the interwebs since there are several factors that can influence the outcome that might not be provided in the question simply due to not knowing that those factors could affect the outcome.

Third: increasing the size of a variable-length field is (assuming you don't go over the 8060 byte limit) a simple meta-data operation since no actual data would be changing for such an operation. BUT, on the other hand, reducing the size of a variable-length field, even to something that will more than obviously work, is not a simple meta-data change because SQL Server doesn't know, prior to scanning all of the rows, that the newly requested size is valid.

Hence: Yes, this will lock the table for a period of time. How much time? Well, here is the test that I just did:

I had, from some other testing, a table with a single INT NOT NULL field and 1 million rows. I copied it to a new table for the purpose of doing this test via:

SELECT *, CONVERT(NVARCHAR(MAX), NEWID()) AS [StringField]
INTO dbo.ResizeTest
FROM dbo.ClusteredUnique;

This way I was starting with a similar scenario of having a MAX field (I just realized that you have VARCHAR and I am using NVARCHAR, but that shouldn't alter the behavior I am seeing) that I could then change to 500. And it has data in it that can easily fit within 500 characters. That took a few minutes.

I then ran:

ALTER TABLE dbo.ResizeTest ALTER COLUMN [StringField] NVARCHAR(500) NULL;

And that took just over 11 minutes.

I just re-ran the test again, this time dropping the [ResizeTest] table and changing both NVARCHARs to be just VARCHAR, just to be super-sure that I am comparing apples to something that at least looks like an apple ;-).

The initial table creation took 20 seconds while the ALTER TABLE took 2 minutes.

So, in terms of estimating downtime, that is really hard to do as it is based on disk I/O speeds, whether or not any auto-growth operations need to happen on the data file and/or the transaction log, etc. That is probably a large part of why my first test took 11 minutes to alter and the second, even with VARCHAR being half the size of the NVARCHAR data, took only 2 minutes (i.e. the files were pre-grown at that point). But still, you should keep in mind that my test is running on my laptop which is not the fastest disk, but it was also just 1 million rows of 2 small columns (22 or so bytes per row).

And since you asked what will it do to the data pages, here is your answer. I did an sp_spaceused after creating the table, after doing the ALTER COLUMN, and after doing ALTER TABLE dbo.ResizeTest REBUILD;. The results (the following numbers are based on the second test using VARCHAR, not the first test using NVARCHAR):

After initial table creation:        526,344 KB
After ALTER COLUMN VARCHAR(500):   1,031,688 KB  <--- !! Yikes!!
After ALTER REBUILD:                 526,472 KB

If you are concerned about needing to keep the operation to the shortest time possible, check out an article that I wrote about doing just that: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! (free registration required).


Optimizing slow SELECT query (postgresql 9.6.5)

Firstly, trying to reduce the number of calling the index. This filter answer_to_id = 8936203 returns 14740 rows which used to check on the other tables. However you need only top 25 of id.
What if you LIMIT 25 then JOIN the other tables.

WITH tmp_links_publicreply AS (
   SELECT ...
   FROM links_publicreply
   WHERE  "links_publicreply"."answer_to_id" = 8936203
   ORDER  BY "links_publicreply"."id" DESC
   LIMIT  25
)
SELECT
FROM tmp_links_publicreply t
JOIN ...
JOIN ...

The query above would work correctly if you have the constraint between links_publicreply and 2 tables links_link and auth_user. Why? Supposed that, you LIMIT 25, then found nothing when JOIN because there is no rows in links_link related to answer_to_id = 8936203.

Then, creating the new index on answer_to_id, id DESC.

Note: WITH (query above) called Common Table Expressions


Oracle 10gR2 to 11gR2 upgrade - performance hit

I think you have AMM - Automatic Memory Management - enabled. This quickly brings you in the situation where all memory goes to the shared_pool and leaves you a very small database cache. Looking at the number of IO's this could fit your case.

Start with a memory setup like you had while running 10g. Forget about AMM on a rdbms instance.


Do I need a database license for my application?

It does not sound like your database and application would be very big in current terms.

You could look into the freely available Microsoft tools for Visual Studio Express and SQL Server Express:

https://www.visualstudio.com/en-us/products/visual-studio-express-vs.aspx

http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx

Both of these are without charge and contain most features of the bigger products.  The limit is in database size, amount of memory, and number of processors that can be used.


MongoDB: What does '[DataFileSync] flushing mmaps took ... ms for ... files' mean?

MongoDB flushes the data changes in memory to disk using a background thread every 60 seconds (it's tunable, see syncdelay).  During normal operations, that is going to be inserts, updates, deletes and the like (including changes to indexes) and will roughly correlate to your activity in terms of those operations.  The mmaps piece relates to the fact that MongoDB uses memory mapped files

When you build an index, you are going to be creating a lot of new in-memory data and that is all going to be persisted to disk every 60 seconds.  How quick that flush is will be a function of:

How much data has to be flushed to disk (in your case how much index building has gone on)
How fast your disk can write that data out

Given that your example shows that it is taking 10 seconds to flush, that would imply that the disk is struggling a bit - single digit seconds is high, double digits is worrying.  Of course it does depend on the nature of your disk - if an SSD were taking 10 seconds (for example) that would need to be a huge amount of data.

That explains the messaging, but besides all that index builds are intensive (and blocking) operations - you can run them in the background on a primary to avoid some of the impact, but they will run in the foreground on secondaries until SERVER-2771 is fixed.  As a result the recommended way to build indexes with a replica set is outlined here:

http://docs.mongodb.org/manual/administration/indexes/#index-building-replica-sets

The impact will depend on how long the build takes, what you are doing at the time etc. but if you follow the instructions above you should be able to avoid any of those effects impacting your end users.


Oracle 12c, Will dropping partition which has 25% of total data, rebuild the indexes?

When you drop one or more partitions, local indexes are also dropped.
You need to rebuilt Global indexes unless the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause is specified in the ALTER TABLE DROP PARTITION statement.

For example, I have a table called TBL1 with range partition and global indexes.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i
where i.index_name=p.index_name
and table_name='TBL1';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX    GI3        USABLE
T_INDEX    GI2        USABLE
T_INDEX    GI1        USABLE

SQL> alter table tbl1 drop partition p0 UPDATE GLOBAL INDEXES;

Table altered.

SQL> select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i
where i.index_name=p.index_name
and table_name='TBL1';

INDEX_NAME PARTITION_ STATUS
---------- ---------- --------
T_INDEX    GI3        USABLE
T_INDEX    GI2        USABLE
T_INDEX    GI1        USABLE

As you can see, if I specify UPDATE GLOBAL INDEXES clause, Global indexes don't become unusable.

After dropping the partition, indexes entries belongs to those partitions remain. When a query uses these indexes, they simply ignore such entries(orphaned). You can clean up such orphaned entries in your regular maintenance window. Details are also given in the following documentations.

  The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.

  In any case, the dropping or truncation of a partition completes immediately, the index is always available for use, and the maintenance of the index is deferred

Documentation:

Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
On Oracle Database 12c


Recommendations on creating user-objects on system database

I can think of a few questions about this implementation that I would want to know before continuing:

What happens if, somehow, microsoft creates an object with the exact same name in a new version?
If databases are migrated to a new server, how will anyone know that there's something in master to migrate?
What happens if your objects corrupt the master database?
What adverse affects would having extra data in master have on the instance?
What would happen if an in-place upgrade was performed on this instance? (ie, upgrading 2008 to 2008 R2)
How would restoring the entire instance from backups be affected?

Most of these I don't know the answer to, but they're questions that come up when I contemplate putting objects in master.


How do I copy a schema for an instance of Oracle running in a Docker container?

expdp is a command line utility that interacts with the DBMS_DATAPUMP API. You run it on the host OS, not from within another client.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_datpmp.htm#BABHBEHH

  See Also:
  Oracle Database Utilities for more information on the concepts behind the DBMS_DATAPUMP API, how it works, and how it is implemented in the Data Pump Export and Import utilities

https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm#i1009203


mysql workbench - Couldn't find option to remove DROP TABLE IF EXIST

This is not a limitation of MySQL Workbench, but one of mysqldump (which is used by Workbench to create the data export). See also Create table if not exists from mysqldump.


You should retype the various Name columns from nvarchar(max) to nvarchar([right size]). It's unlikely that names will be up to 2GB in length, and making them max sized prevents them being used as a key in an index. A good general rule of thumb is to avoid large object data types wherever possible.

You'll probably need to make that change to the EF code, but in T-SQL:

-- Guessing at 100 characters maximum
ALTER TABLE dbo.[Status]
ALTER COLUMN Name nvarchar(100) NOT NULL;

ALTER TABLE dbo.Colors
ALTER COLUMN Name nvarchar(100) NOT NULL;

ALTER TABLE dbo.Sizes
ALTER COLUMN Name nvarchar(100) NOT NULL;

ALTER TABLE dbo.Widgets
ALTER COLUMN Name nvarchar(100) NOT NULL;

I have also changed the column definition to NOT NULL there.

Anyway, given indexes:

CREATE NONCLUSTERED INDEX IX_dbo_Sizes__DiameterInches
ON dbo.Sizes (DiameterInches);

CREATE NONCLUSTERED INDEX IX_dbo_Widgets__SizeId_ColorCode_StatusId__Name
ON dbo.Widgets (SizeId, ColorCode, StatusId)
INCLUDE (Name);

...you should end up with a plan that avoids lookups and sorts, something like:

With the small number of rows in Status and Colors, it is probably not worth indexing their Name columns right now, but that could change over time. In any case, if the name columns should be unique, you should constrain them to be so using a unique constraint or unique nonclustered index, for example:

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Colors__Name
ON dbo.Colors (Name);

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Status__Name
ON dbo.[Status] (Name);

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Sizes__Name
ON dbo.Sizes (Name);

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Widgets__Name
ON dbo.Widgets (Name);

Second approach

With the same indexes, if you are able to change the SQL, you could also separate out the Color and Status lookups:

DECLARE
    @StatusId integer,
    @ColorCode nvarchar(6);

SELECT @StatusId = ST.Id
FROM dbo.[Status] AS ST
WHERE ST.Name = N'Available';

SELECT @ColorCode = C.ColorCode
FROM dbo.Colors AS C
WHERE C.Name = N'Red';

SELECT TOP (1)
    W.*
FROM dbo.Widgets AS W
JOIN dbo.Sizes AS S
    ON W.SizeId = S.Id
WHERE
    W.StatusId = @StatusId
    AND W.ColorCode = @ColorCode
ORDER BY
    S.DiameterInches;

This makes for a simpler set of operations that also happen to make life easier for the optimizer:

Note also that Unicode string literals should be prefixed with N for correct data typing.

Oh, and you should also patch your instance. It is currently SQL Server 2008 Service Pack 1 - Service Pack 4 has been available for some time now.


How to set rank per group with a SQL UPDATE statement?

CTE:

You can embed you SELECT with RANK() into a CTE and then UPDATE the CTE.

WITH cte AS
(
    SELECT *, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
    FROM @data
)
UPDATE c
SET rank_in_department = r
FROM cte c;

Don't forget the ; terminator at the end of the line preceding the CTE statement.

Sub Query:

You can also self JOIN your table on a sub query with the expected RANK.

UPDATE d SET rank_in_department = r.r
FROM @data d
INNER JOIN (
    SELECT id
        , r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
    FROM @data
) r ON d.id = r.id

This query expects an Id or a group of column in both the sub query and the JOIN. It is used to uniquely identify each row and JOIN it to the table. From your data in your sample picture, this seems to be manager_code+, archive_year, archive_day_of_year

Sample Data used:

This gives your 2 correct syntaxes using this sample data. Queries must be adapted to your real table(s).

DECLARE @data TABLE(id int identity(0, 1), archive_year int, archive_day int, branch_code nvarchar(5), rank_in_department int, open_count int)
INSERT INTO @data(archive_day, archive_year, branch_code, open_count) VALUES
    (2016, 1, 'X', 5)
    , (2016, 1, 'X', 15)
    , (2016, 1, 'X', 52)
    , (2016, 1, 'X', 36)
    , (2016, 1, 'X', 55)
    , (2016, 1, 'Y', 65)
    , (2016, 1, 'Y', 85)
    , (2016, 1, 'Y', 42)
    , (2016, 1, 'Y', 96)
    , (2016, 1, 'Y', 15);

SELECT *
    , r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
FROM @data;


If you have a firewall, why is the database server exposed to the internet?

The firewall should block all access to BOTH servers apart from the ports required. Typically this would be 80 (http) and 443 (https) to the web server ONLY.
If (and only if) an external service requires access to the SQL Server, permit access to the specific IP addresses required at the firewall. This should occur via a VPN connection, not openly exposed 1433.
Create a new administrator account and disable the default 'sa'.
Preferably, switch from using "mixed mode" authentication to windows accounts.


How to create a non-sa SQL Server login / access without control server permission

You should not need to DENY CONTROL to a login.  Simply do not GRANT the right to that user.

Regarding the side effects of DENY CONTROL see the post here:
http://sqlity.net/en/2102/deny-control-side-effects/

Comment from that link: "The only option you have is to pay careful attention to not grant any permission to the principal that you did not indeed intend to include. You can consider using techniques like auditing to help you with this, but you cannot enforce it."

So REVOKE your DENY and just make sure that only the needed rights are granted.


To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC.

By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)

You can set this dynamically:

mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

Or permanently in your my.cnf:

[mysqld]
**other variables**
default_time_zone='+00:00'

Restart your server, and you will see the change:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)


Confusion normalizing data to 3NF and transitive dependencies

You have:

R (ABCDEFGH)

AB-->D
B-->C
B-->E
A-->H
H-->G

I'm not sure how you resulted in the decompositions but the first thing to do usually is to find the candidate keys if the relations. Here there is only one, the ABF.

Then you identify that the relation is not in 2NF, as there are dependencies on parts of candidate keys and not the whole of them (this part seems to have been done it quite well.) The partial dependencies (on parts of the candidate keys) are these and you only missed the last one, that can be derived from the others:

AB-->D
B-->C
B-->E
A-->H
A-->G             -- missed this one

So (leaving the first question for the end), the decompositions to:

R (ABCDEFGH)
                          -- splitting because of the AB -> D, fine
R*(ABCEFGH)  R1 (ABD)
                          -- splitting because of the B -> C, fine
R**(ABEFGH)  R2 (BC)
                          -- splitting because of the B -> E, fine
R***(ABFGH)  R3 (BE)

are rather fine, except some minor details like the D reappearing in the R relation.

The next part which is your second question regarding the A->H and H-G was problematic due to your missing of the implied A->G dependency. It should be:

                          -- splitting because of the A -> HG
R****(ABF)   R4 (AHG)

We have now resulted in 5 relations, all in 2NF:

R**** (ABF)
R1 (ABD)      -- AB-> D
R2 (BC)       -- B -> C
R3 (BE)       -- B -> E
R4 (AHG)      -- A -> HG ,  H -> G

In fact all are in 3NF except for R4. So, we have to split that into two relations:

R4* (AH)      -- A -> H
R5 (HG)       -- H -> G

We now have 6 relations (R****, R1, R2, R3, R4*, R5) which are all in 3NF and BCNF as well. In fact they are all in 4, 5 and 6NF.

Regarding your first question, you could have used one split into (BCE) instead of the two (BC) and (BE) and it would have been perfectly fine. Your final result would be five relations (instead of six), all in 3NF - and all the way up to BCNF, 4NF, 5NF, 6NF (except for that (BCE) which is in 5NF.)


SQL Server 2005: Full-text search catalog design

Just asked a Microsoft Certified Trainer during a developer course for SQL Server.  He suggested using a single catalog, and having the two indexes, "title" and "body" in the same catalog.

The only thing to watch is with too many indexes in the same catalog, there is a greater chance for fragmentation within the catalog.  With two columns though, this should not be a concern.


Indexing options for Postgres for a table with billion records when you want to index only recent records

Creating and dropping indexes does not harm the table in the least. Also, with a partial index, you will pay the price for index maintenance only if the row matches the condition.
You should consider range partitioning by updated_at. Then queries with that column in the WHERE condition will only scan the necessary partitions. As a side effect, deleting old data becomes easier.
The price you would be paying is twofold:

Most other queries would become at least slightly slower. Make sure that you don't have too many partitions.

Updating a row so that updated_at crosses a partition boundary will incur the overhead of moving the row to another partition.


From the official documentation, the parameter n is "The number of copies there is of every document. Replicas." so if you set it to 1 you will use less space for sure. Now if this is a production environment that may not be a good idea unless you have your database on a RAID with redundancy.


Run query against all databases on server containing specific string in name

You may try to generate a dynamic statement.

Basic example:

-- Declarations
DECLARE @stm nvarchar(max)
DECLARE @err int
SET @stm = N''

-- Dymanic statement
SELECT @stm = @stm + CONCAT(N'SELECT * FROM [', [name] , N'].sys.tables ')
FROM sys.databases
WHERE [name] LIKE '%the%'

-- Execution
EXEC @err = sp_executesql @stm

Example with your statement:

-- Declarations
DECLARE @stm nvarchar(max)
DECLARE @err int
SET @stm = N''

-- Dymanic statement
SELECT @stm =
    @stm +
    CONCAT(
    N'SELECT convert(varchar, DateTime, 100) AS "Date and Time", Text2 AS Area, Substring(PText7, 1,Charindex('':'', PText7)-1) AS Username FROM [',
    [name] ,
    N'].dbo.AlgViewENU AS data
    WHERE PText6 LIKE ''%BypIn%'' AND [DateTime] = (SELECT MAX(DateTime) FROM [',
    [name] ,
    N'].dbo.AlgViewENU WHERE Text1 = data.Text1 AND PText6 LIKE ''Set%''); '
    )
FROM sys.databases
WHERE [name] LIKE '%the%'

-- Execution
PRINT @stm
EXEC @err = sp_executesql @stm


Recommendation for free event monitoring software

Polymon from codeplex is ideal for my uses.  I'm monitoring not just server availability, but sql agent jobs success, a host of perfmon data, SQL locking/blocking, db and filesystem freespace, and a whole lot more.

It has a very nice Powershell interface, so any Powershell script you can imagine, it will run on the schedule you specify and then alert/notify/take action as you direct.

It requires a SQL2005 or greater instance to hold its db, but it can monitor any version of SQL.  (Using Powershell, I even have it monitoring CACHE on AIX.)

No cost besides having an available SQL 2005 server to host the db.


What does a deterministic symmetric key mean?

It's saying that it is possible to have different servers with the same symmetric key values generated. This may or may not be desirable per the business. For example, you can create your own symmetric keys that match.

The DMK just protects the symmetric key by encrypting it, however once decrypted the actual key portion could be the same. This can be desirable as symmetric keys have no backup/restore mechanism on their own.


Oracle DB union two select statements which return multiple rows

Code Review

Always define variables types with %TYPE where it makes sense.

This way, when the table changes, you don't have to go back and fix code.

Bulk Collect

When you are receiving multiple rows from a query, you need to BULK COLLECT INTO a collection.

Defining a Record, then a Table Type is one way to define a Collection Type.  If you were selecting all columns (eg SELECT *) then TYPE many_rows_t TABLE OF tablename%rowtype would be another way.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  TYPE one_row_t IS RECORD (
    referencelistid   tbrd_reference_lists.reference_list_id%TYPE -- number(20),
   ,referencename     tbrd_reference_lists.reference_name%TYPE -- varchar(20),
   ,startdate         tbrd_reference_lists.start_date%TYPE -- timestamp,
   ,enddate           tbrd_reference_lists.end_date%TYPE -- timestamp,
   ,creationuser      tbrd_reference_lists.creation_user%TYPE -- varchar(20),
   ,creationdate      tbrd_reference_lists.creation_date%TYPE -- timestamp,
   ,changeuser        tbrd_reference_lists.change_user%TYPE -- varchar(20),
   ,changedate        tbrd_reference_lists.change_date%TYPE -- timestamp,
   ,username          tbrd_reference_lists.username%TYPE -- varchar(20),
   ,changetime        tbrd_reference_lists.change_time%TYPE -- timestamp
  );
  TYPE many_rows_t IS
    TABLE OF one_row_t;

  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

--DBMS_OUTPUT.put_line('Start at: ' || timer_before);
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  BULK COLLECT INTO
    some_buffer
  FROM tbrd_reference_lists
  WHERE
    reference_list_id   = 0000
  UNION
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  FROM tbrd_reference_lists_h
  WHERE
    reference_list_id   = 0000;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
END;

Limiting memory consumption

The above example is OK if you are receiving a few rows.  But, if you are receiving a few Million rows, you may run into memory problems.

Instead, you'll want to LIMIT how many rows you fetch at one time.

To do this, you need to use a CURSOR and FETCH a limited number of rows at a time.
Please take the time to read this Oracle Magazine article and the Oracle Documentation.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  CURSOR C IS
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists
    WHERE
      reference_list_id   = 0000
    UNION
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists_h
    WHERE
      reference_list_id   = 0000;

  TYPE many_rows_t is table of C%rowtype;
  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

  OPEN C;

  loop
    FETCH C bulk collect into some_buffer limit 100;

    /* process data */

    exit when C%notfound;
  end loop;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
EXCEPTION
  -- Prevent cursor leakage
  when others then
    if C%isopen then
      close c;
    end if;

    raise;
END;


Does having Default Database Engine MyISAM and keeping InnoDB tags in my.ini create any problem?

Binary Logs
As for your binary logs, the bad magic number is making reference to an encoding done at the beginning of the a binary log. It is a base64 constant that looks something like : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number. If this encoding is not in this format, it would be reported as corrupt.
You should erase all binary logs and start fresh.
In order to clear them, run this in the mysql client
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;

This will erase all binary logs and show you the first binary log.
I have written posts on this issue before

Oct 28, 2012 : Binlog has bad magic number
Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

InnoDB Options
It is OK for the InnoDB options to be around. Keep in mind that every time mysql is restarted, InnoDB's infrastructure is validated

mobiledoc_InnoDb_data1 exist in the innodb_data_file_path (D:/eClinicalWorks/mysql/data)
mobiledoc_InnoDb_data1 must be at least 300M
innodb_data_home_dir has ib_logfile0/ib_logfile1
innodb_log_file_size=256M (checks if ib_logfile0/ib_logfile1 are 256M)
innodb_buffer_pool_size=256M (allocates the 256M in RAM)
innodb_log_buffer_size=8M (allocates the 8M in RAM)

If none of your tables are InnoDB but you want to keep the present InnoDB settings and corresponding files, I have a great suggestion:
Add this under the [mysqld] section of my.ini
[mysqld]
skip-innodb

then run
C:\> net stop mysql
C:\> net start mysql

This will make mysqld startup faster because all the steps for InnoDB initialization/validation are bypassed. The memory InnoDB is configured to use (264M [256 + 8]) will not be allocated.
Give it a Try !!!


I suggest you read Understanding how SQL Server executes a query.

I can't comment about what Brent's scrips, but I can tell you about the wait stats info in sys.dm_db_index_operational_stats. The per-hobt wait stats aggregate page and row lock wait info and page latch and io latch info. High numbers here indicate either contention (many concurrent transactions attempting to operate on the same page/row) or scans (operations locking and latching all pages). In your case looks like the problem is scans. As every operation is reading the entire table you cause a lot of locking, latching and possible IO. An index would dramatically reduce the numbers by turning scans into seeks.

Further read:

How to analyse SQL Server performance
SQL Server Index Design Guide


Is it possible to enforce unique values in a column, excluding nulls?

You can create a unique filtered index as below.

CREATE UNIQUE NONCLUSTERED INDEX IX ON TableA(TableBID) WHERE TableBID IS NOT NULL

For versions of SQL Server prior to filtered index support (pre 2008) see this related question.


Inserting and updating and selecting at thousands of times per second

Change the updates to use WITH (ROWLOCK).  That will reduce the locks from page level to row level.  You can try the same on the inserts as well.


SQL Server on Linux hangs on initial startup, no errors, and no new / updated ErrorLog file

This ended up as a case of not being careful when working as root.

I had been researching if SQLCLR on Linux would have access to the app.Config file like it does in Windows (sadly, it does not: SQL Server 2017 on Linux ignores app config file if it exists, or sometimes locks-up if it doesn't (SQLCLR) ) and under certain circumstances SQL Server would completely lock up. When that happened, the only way to stop it was to do a kill -9 on sqlservr. One of the times that I was starting the service again, I did so by directly executing /opt/mssql/bin/sqlservr and while I was working as root (hence the process itself was owned by root).

There were no immediate errors or odd behavior resulting from running sqlservr as root, BUT when the VM restarted and SQL Server attempted to start properly (i.e. running as the mssql user), that is when it got stuck at the very beginning.

I found that a direct consequence of running sqlservr as root was that the /var/opt/mssql/log/errorlog file (and some others that are created upon SQL Server starting) were owned by root (makes sense).

And, a direct consequence of those files being owned by root is that when the process is started properly (as mssql), then the mssql user doesn't have permission to rename the file to end in .1 (and whatever else needs to happen with any other files, such as default trace, etc). However, rather than getting a permissions error, it just hangs forever.

The primary fix is to simply run the following as root (I haven't tried running it as mssql). For both of the following commands, sudo is only needed when not currently acting as root as it will run the command that follows it as root (or some other user if you specify -u username), after being prompted to enter the root password.

sudo chown -R  mssql:mssql /var/opt/mssql

The secondary fix (to make sure that this doesn't happen again), is to start SQL Server properly ;-):

sudo systemctl start mssql-server


Partitioning a 4TB table while staying online (SQL Server Standard)

the table is append-only (new rows, no updates to existing ones)

Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition for all the existing data, but for which new inserts will land in new partitions.  Eg all row up to today land in one big partition, but new rows will fall into monthly partitions in the future.

Then perhaps later you have some time to split the big partition.  If you use a RANGE RIGHT partition scheme, then you can split small partitions off of the RIGHT side of the large partition.  If you use RANGE LEFT you can split small partitions off of the LEFT side.

Or, after you have switched to the new table you can INSERT the data from the old single partition into a new properly-partitioned staging table.  Once that insert is done you can truncate the large partition, split the now-empty partition to match the staging table, and then switch the staging table in.  Since the data in the old partition is read-only, you can perform the insert over time.  Then the truncate, split, and switch-in are all metadata operations.


Looking at the CREATE TABLE definition for 2014 and scrolling down to the box headed "--Memory optimized CREATE TABLE Syntax", there is no mention of compression.  Ergo, compression is not available for in-memory tables.

But yes, compression uses more CPU.  Our experience is that query elapsed times are still shorter because the extra compute time is more than compensated for by the reduced IO latency.

Update: in memory tables now support columnstore indexes which employ compression.


Did you see this in the log file?

160930  3:05:54 InnoDB: Fatal error: cannot allocate memory for the buffer pool

You do not have enough memory available on your host. Either decrease the amount of memory MySQL is configured to use, or increase the amount of memory the host/VM has.

The database cannot start because there is not enough memory.

Speculating as to why the database restarted in the first place, it may well have been that the operating systems OOM Killer chose the MySQL process as "the one" when the OS was exhausted of memory. See How OOM killer decides which process to kill first? on Unix.SE.

The "Unknown/unsupported storage engine" message appears because MySQL has asked the storage engine (InnoDB) to initialize itself, which it cannot do due to lack of memory.

160930  3:05:54 [ERROR] Plugin 'InnoDB' init function returned error.
160930  3:05:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160930  3:05:54 [ERROR] Unknown/unsupported storage engine: InnoDB


I would suggest running this query on the master

SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

This should give you a pretty good idea of the roles that have access to your master database and see weather or not the user has any of those roles. You can also run this against any of your other databases to check the user's permissions on a database by database level. This should be an important tool to help track this down.


You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM
        all_objects
      WHERE
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    )
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY
  r.dat ;

Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount)
              FROM returns  r
              WHERE r.dat = d.dat
            ), 0)
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount)
              FROM orders  o
              WHERE o.dat = d.dat
            ), 0)
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM
        all_objects
      WHERE
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY
  d.dat ;


What happens to a query that is performed on a view?

My answer will focus almost exclusively on SQL Server just because I'm going to give a fairly detailed answer and I don't have the same level of expertise in other platforms.

First it's important to realize that the query optimizer doesn't directly work against the SQL you write. It gets transformed into an internal format before optimization. What you have listed for potential query 1 and query 2 is pretty much the same thing, except for a view subtle differences. A similar question about the difference between query 1 and query 2 was asked and answered here. If you'd like to learn more about the internal format SQL Server uses, you can go through an excellent series of blog posts by Paul White. However, most of the time it's sufficient to just compare the query plans of two queries that you suspect might be optimized in the same way.

There are a few ways that using a view might improve performance:

It's possible to define views which are implemented as physical structures on the database. In SQL Server these are called indexed views. In Oracle these are called materialized views. In Oracle it's possible for a query not written against a materialized view to still use the materialized view. Further discussion is outside the scope of this answer.
Sometimes the same SQL query needs to run on multiple RDBMS platforms. With a view we can use syntax that is unique to each platform but the same query is sent to the databases. Without a view we might have to use user defined functions which can be bad for performance.
Sometimes people put really clever code in views. If it's better than what you would have written you can improve performance by using the view.

In general, a query written against a view will be as efficient or less efficient than a query written directly against the base tables. This is because a view definition often contains extra columns and joins that might not be needed for the specific question a query against the view is asking. To get good performance against a complex view we're hoping that three things happen:

Column elimination. If a column is presented in a view but not mentioned in the query against the view then the value shouldn't be calculated.
Join elimination. If a table is used in a view that could be safely eliminated without changing the results then it should be eliminated. Sometimes this could happen if the optimizer had more information. For example a foreign key might not be declared in the database. Other times the rule to implement join elimination might not cover a certain scenario. For example, in Oracle join elimination cannot happen for a multi-column join but it can in SQL Server.
Predicate pushdown. If I add a filter to the view and there's an index on the underlying column then I should be able to use that index. I believe that this is what your example is hinting at. Even without an index I still want filters to be pushed down as far as possible into the plan to avoid unnecessary work.

In my experience these rules are implemented pretty well by the query optimizer which is of course a good thing, but it can be bad for SE demos. However, if we write sneaky code we can end up with examples that show all of the above optimizations failing. This is because the rules that implement the optimizations are not designed to cover every possible scenario.

First I'll create some simple sample data. The data itself isn't that important, but the table definitions are.

DROP TABLE IF EXISTS dbo.BASE_TABLE;
CREATE TABLE dbo.BASE_TABLE (
ID INT NOT NULL,
ID2 INT NOT NULL,
FILLER VARCHAR(50),
CONSTRAINT BASE_TABLE_ID CHECK (ID > 0),
PRIMARY KEY (ID)
);

INSERT INTO dbo.BASE_TABLE WITH (TABLOCK)
SELECT TOP (1000000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.EXTRA_TABLE;
CREATE TABLE dbo.EXTRA_TABLE (
ID INT NOT NULL,
ID2 INT NOT NULL,
FILLER VARCHAR(50),
PRIMARY KEY (ID, ID2)
);

INSERT INTO dbo.EXTRA_TABLE WITH (TABLOCK)
SELECT TOP (1000000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 50)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.EMPTY_TABLE;
CREATE TABLE dbo.EMPTY_TABLE(
ID INT NOT NULL,
PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS dbo.EMPTY_CCI;
CREATE TABLE dbo.EMPTY_CCI (
ID INT NOT NULL
, INDEX CCI_EMPTY_CCI CLUSTERED COLUMNSTORE
);

GO

CREATE FUNCTION dbo.THE_BEST_FUNCTION () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN NULL;
END;

GO

Here is my sneaky view definition:

CREATE VIEW dbo.SNEAKY_VIEW
AS
SELECT
  ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
, dbo.THE_BEST_FUNCTION() FUNCTION_VALUE
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
LEFT OUTER JOIN dbo.EMPTY_CCI ON 1 = 0
LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID;

GO

The view is unfortunately a mess. As a human optimizer it's possible to simplify that query quite a bit. The join against EXTRA_TABLE can be eliminated because we're joining against the full primary key so the number of rows won't change. It's also not possible for any rows to match, but it's an outer join so nothing will be eliminated. The join against EMPTY_CCI can be eliminated because the join condition will never match. The join against EMPTY_TABLE can be eliminated because we're joining against the full primary key. The function also always returns NULL so there's no need to include that. So we can simplify to this:

SELECT
  ID
, 1 CNT
, NULL
FROM dbo.BASE_TABLE t;

However, we can do even better than that. ID is always positive due to the constraint and ID is always unique because it's the primary key. So the COUNT window function will always be 1. The query can be rewritten like this:

SELECT
  ID
, 1 CNT
, NULL
FROM dbo.BASE_TABLE t;

Will the query optimizer be able to simplify a query against the view to that? Let's find out by comparing the plans. Here's the plan for the simple query:

Here's the plan for SELECT * against the view:

They're quite a bit different. Let's go through more example queries to see examples of the optimizations above perhaps not working out exactly as expected.

First, scalar user-defined functions are bad for performance in SQL Server. Among other issues they force the entire query to have a serial plan. This query is eligible for a parallel plan and I get one on my machine:

SELECT *
FROM (
    SELECT
      ABS(t.ID) ID
    , COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
    FROM dbo.BASE_TABLE t
    LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
    LEFT OUTER JOIN dbo.EMPTY_CCI ON 1 = 0
    LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID
) derived_table;

The plan:

However, even if I don't select the column based on the user defined function in the view I still get a forced serial plan:

SELECT ID, CNT
FROM dbo.SNEAKY_VIEW;

So there's one difference between using a view and a derived table. Sometimes column elimination won't work in the same way.

For the second example, the EMPTY_CCI table won't affect the results of the query so let's get remove it from the derived table:

SELECT *
FROM (
    SELECT
      ABS(t.ID) ID
    , COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
    FROM dbo.BASE_TABLE t
    LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
    LEFT OUTER JOIN dbo.EMPTY_TABLE e2 ON t.ID = e2.ID
) derived_table;

Here is the query plan:

However, there's a difference in the query plan against the view:

SELECT ID, CNT, FUNCTION_VALUE
FROM dbo.SNEAKY_VIEW;

The view is able to use batch mode which is a special way of implementing query execution when clustered columnstore indexes are involved in a query. Even though the EMPTY_CCI table doesn't show up in the plan the query is still eligible for batch mode. Note that EXTRA_TABLE is needlessly queried in both queries. That's because the join condition was too complicated for SQL Server to determine that the join was safe to elimination. Also note that the EMPTY_TABLE table does not show up in either query plan. The query optimizer was able to eliminate it in both queries.

For the third example, let's look at predicate pushdown. Let's suppose that I want to filter to just include rows where ID = 500000. If I do that somewhat directly outside of the view:

SELECT
  ABS(t.ID) ID
, COUNT(*) OVER (PARTITION BY ABS(t.ID)) CNT
FROM dbo.BASE_TABLE t
LEFT OUTER JOIN dbo.EXTRA_TABLE e ON CAST(t.ID AS VARCHAR(10)) = CAST(e.ID AS VARCHAR(10)) + '|' + CAST(e.ID2 AS VARCHAR(10))
WHERE ABS(t.ID) = 500000
OPTION (MAXDOP 1);

I cannot use the index against BASE_TABLE.ID due to the ABS() function. However, the filter is pushed down into the scan and only one row is expected to be returned. That can improve performance:

With this query:

SELECT ID, CNT
FROM dbo.SNEAKY_VIEW
WHERE ID = 500000;

We get a less efficient plan. The filter on ID = 500000 is implemented at the very end of the plan, so the window function will be evaluated against almost a million unnecessary rows:

That may have been a bit more of a deep dive than what you were looking for. Going back to your original question, I would say that the query against the view is most similar to potential query 1. I have heard rumors regarding some cases in which that isn't true. For example, supposedly if you have many nested views then the query optimizer can have difficulty unpacking them and you can end up with a poorly optimized query for that reason alone. I don't know if that's true in a current version of SQL Server but it's worth avoiding simply so that other people trying to understand your code have an easier time with it.