SP2-0584: EXIT variable was non-numeric error I'm using following PLSQL loop to delete a large number of records from my table. However it is giving me an error that loop exit condition is not correct.

FOR i IN 1..100
LOOP
  DELETE
  FROM mytable
  WHERE date <= (SYSDATE - 30)
  AND ROWNUM < 5000;
EXIT WHEN (SELECT COUNT(*)
  FROM mytable
  WHERE date <= (SYSDATE - 30)) = 0;
  COMMIT;
END LOOP;

What is the problem here?

It should be a simple boolean.
This should work and be more efficient.
The SQL%ROWCOUNT attribute will be populated with the number of rows deleted. If you didn't delete any then just exit.

FOR i IN 1..100
LOOP
  DELETE
  FROM mytable
  WHERE date <= (SYSDATE - 30)
  AND ROWNUM < 5000;

  EXIT WHEN sql%rowcount = 0;

  COMMIT;

END LOOP;

If you aren't familiar with the implicit cursor attributes take a look at this in the Oracle docs - https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sql_cursor.htm

EDIT: Just noticed the title of the post. The exit that the message is referring to is the exit from SQL*Plus and as such I'm not sure you are running this the right way. Please post all the code and tell us how this is being run.


Is ALTER TABLE CHECK CONSTRAINT redundant? I created a table with a foreign key constraint on it. When I generate a table creation script through SSMS, it creates the following code:

ALTER TABLE [dbo].[MainTable]  WITH CHECK ADD  CONSTRAINT [FK_MainTable_ForeignKeyTable] FOREIGN KEY([FK_Field])
REFERENCES [dbo].[ForeignKeyTable] ([PK_Field])
GO

ALTER TABLE [dbo].[MainTable] CHECK CONSTRAINT [FK_MainTable_ForeignKeyTable]
GO

Since the first statement creates the constraint WITH CHECK, does this make the second statement redundant? If not, what is the CHECK CONSTRAINT accomplishing that the WITH CHECK does not?

I saw ALTER TABLE CHECK CONSTRAINT, but in that case, the SSMS is generating the first statement WITH NOCHECK, so it makes sense to have both statements.

The first statement alters the table to add the constraint.  The second statement enables or disables the constraint for future inserts or updates.

So, ALTER TABLE ... CHECK CONSTRAINT ... enables the constraint for future inserts/updates.  If you specify ALTER TABLE ... NOCHECK CONSTRAINT ..., that disables the constraint, thereby allowing future inserts and updates to succeed even if the column in the table referenced by the foreign key does not contain a matching value.  You can see the utility of having both statements present through this example:

USE tempdb;

IF OBJECT_ID(N'dbo.b', N'U') IS NOT NULL
DROP TABLE dbo.b;
IF OBJECT_ID(N'dbo.a', N'U') IS NOT NULL
DROP TABLE dbo.a;

CREATE TABLE dbo.a
(
    a_ID int NOT NULL
        CONSTRAINT a_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.a (a_ID, SomeData)
VALUES (1, 'aaaa')
    , (2, 'bbbb')
    , (3, 'cccc');

CREATE TABLE dbo.b
(
    b_ID int NOT NULL
        CONSTRAINT b_primary_key
        PRIMARY KEY
        CLUSTERED
    , SomeData varchar(10) NOT NULL
    , a_ID int NOT NULL
) ON [PRIMARY];

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (49, 'dddd', 1)
    , (50, 'eeee', 2)
    , (51, 'ffff', 3);

So, we created two tables that do not have a defined relationship.  Later, we decide we want to design-in the relationship, so we add a foreign key in dbo.b that references the primary key column in dbo.a.  We want the existing rows to be checked for validity, but don't care about new rows that might get added in future1.

ALTER TABLE dbo.b
WITH CHECK
ADD CONSTRAINT b_a_foreign_key
FOREIGN KEY (a_ID)
REFERENCES a(a_ID);

ALTER TABLE dbo.b
NOCHECK CONSTRAINT b_a_foreign_key;

All good.  The existing rows were validated, and new rows won't be validated against the foreign key:

INSERT INTO dbo.b (b_ID, SomeData, a_ID)
VALUES (52, 'gggg', 4);

The table contents:

SELECT *
FROM dbo.b
    LEFT JOIN dbo.a ON b.a_ID = a.a_ID

Results:

╔══════╦══════════╦══════╦══════╦══════════╗
║ b_ID ║ SomeData ║ a_ID ║ a_ID ║ SomeData ║
╠══════╬══════════╬══════╬══════╬══════════╣
║   49 ║ dddd     ║    1 ║ 1    ║ aaaa     ║
║   50 ║ eeee     ║    2 ║ 2    ║ bbbb     ║
║   51 ║ ffff     ║    3 ║ 3    ║ cccc     ║
║   52 ║ gggg     ║    4 ║ NULL ║ NULL     ║  <-- crazy stuff
╚══════╩══════════╩══════╩══════╩══════════╝

So, since the code that scripts tables in SSMS has to cover every eventuality, it spits out a ALTER TABLE ... CHECK CONSTRAINT ... even if it's not required, and in the vast majority of cases is certainly not required.

1 - why anyone would want existing rows to be validated, but not care about new rows is a little mysterious, but there you go.


Accidental deletes at SQL Server Subscriber Unfortunately, this happened twice in 2 weeks on our most critical db server; just before Christmas! Instead of running a delete script at the publisher, the script was run at the subscriber, which broke replication!

I'm exploring options to avoid this scenario in future. This is a massive DataWarehouse. Lot of transactions. Everyone and his brother in the organization has access. Any more such mistakes might trigger a resume-generating-event.

Here are some of my notes:

  Maybe I can use the deny_db_writer role at the subscriber. But I believe sysadmins will still be able to run the deletes.
  INSTEAD OF DELETE TRIGGERS could be an option; however, we have hundreds of tables, that needs these triggers. I don't know if the
  genuine deletes from replication will invoke this trigger or not. (we
  prefer NOT to. There's something called NOT FOR REPLICATION for
  triggers; we might have to use that)
  Can this be achieved by a DDL trigger? what could be the impact on the subscriber?

Has anyone done anything like this? Thank you so much for any suggestions.

Maybe I can use the deny_db_writer role at the subscriber.
  But I believe sysadmins will still be able to run the deletes.

Yes.  But they are sysadmins.  And so are responsible for their own "resume-generating-events".  If there are sysadmins who aren't trusted DBAs who should know better, then you'll need to reduce their permissions.

The right answer here is to prevent the data modification at the subscriber through security.  You can run

deny delete to public

This will override any permissions and prevent deletes database-wide for all users (public is the 'everyone' role in a SQL Server Database).

Or you can limit this to a single schema:

deny delete on schema::dbo to public

Which would allow users to delete from tables located in schemas they own.

With DELETE denied to PUBLIC, only dbo, or a sysadmin (who always connect as dbo), or a stored procedure owned by dbo will be able to delete.


Plans created several times under Forced Parameterization People use forced parameterization to save plan compilation cost. However, I found things different here.

The query I will run is as following, and under AdventureWorks2012 database:

DECLARE @Var VarChar(250), @SQL nvarChar(MAX)
SET @Var = NEWID()

SET @SQL =
'SELECT SalesOrderHeader.SalesPersonID,
       COUNT(DISTINCT SalesOrderHeader.CustomerID),
       SUM(SalesOrderDetail.OrderQty)
  FROM Sales.SalesOrderHeader
 INNER JOIN Sales.SalesOrderDetail
    ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
 INNER JOIN Production.Product
    ON Product.ProductID = SalesOrderDetail.ProductID
 WHERE Product.Name = ' + '''' + @Var + '''
 GROUP BY SalesOrderHeader.SalesPersonID'

EXEC (@SQL)

I configured the database to use forced parameterization, flushed the cache using dbcc freeproccache, and then ran the above query 3 times. I used following query to get the plan use stats:

select  bucketid, usecounts, memory_object_address, cacheobjtype, objtype, plan_handle, text
from (select * from sys.dm_exec_cached_plans ) as cacheplan
cross apply sys.dm_exec_sql_text(plan_handle)as text
WHERE text.text LIKE'%SalesOrderHeader%.%SalesPersonID%'

And below is the result I got:

Except row 1 are all about the queries I ran. Row 5 I can understand. This means database engine actually parameterized product.name and generate a plan for later use. That's why the usecounts = 3.

What I don't understand is why we still have row 2,3,4? Does that mean forced parameterization couldn't save anytime (or even cost more) when running the same type of queries?

I would really appreciate any suggestions or ideas. Thanks.

Additional Info:

I understand we can turn on "Optimized Ad hoc Workloads" option for the instance to save some space. However, I feel these ad hoc plan stubs are not supposed to be cached at all.
I know using sp_executesql would make solve above problem. But there are some queries in our production database that cannot be easily changed.

Okay, this is the reason:

From Caching Mechanisms on MSDN:
You should notice that the two individual queries with their distinct constants do get cached as adhoc queries. However, these are only considered shell queries and are only cached to make it easier to find the autoparameterized version of the query if the exact same query with the same constant is reused at a later time. These shell queries do not contain the full execution plan but only a pointer to the full plan in the corresponding prepared plan.

For more in-depth technical information, see:

4.0 Query Parameterization on the SQL Programmability & API Development Team Blog
There are benefits in caching the shell query: If the same query were to be re-executed, then we would compute the hash value of the sql text of the query and find an exact match in the cache i.e. the shell query. Since this shell query points to the compiled plan, the compiled plan is executed and we are done.
If we had not cached this shell query and if the same query was re-executed then the steps followed would be slightly different: first we would compute the hash of the sql text of the query and not find an exact match in the cache. Next, the query is auto-parameterized. Now for this auto-parameterized query we will search the cache and find an exact match in the cache avoiding the need to go to the query optimizer. Finally we execute this compiled plan and are done.
Clearly there are performance gains from caching the shell query, especially for applications that re-execute the same query with the same literal values as well. Note that we do not cache insert shell queries because the probability of re-using the exact same adhoc query is low.

For more information read the whole documents (and series of posts, in the second case).


Configuring physical storage for a Microsoft SQL Server installation I'm configuring a Microsoft SQL Server installation which will use a SAN for storage. For SAN storage I have:

A small number of SSD disks
A larger number of 10K disks

My SAN supports storage tiering.

I'm on the fence about how to use the SSD disks. Here are the options that come to mind:

Option 1: Create tiered storage with the SSDs and 10K disks and use auto tiering to move highly accessed data to the SSDs. Then break that storage pool up into several different drives.
Option 2: Place key (I/O intensive) data on the SSDs and throw everything else on the 10K disks.

Questions

I don't have enough SSDs so that everything can reside on SSDs. So what's most important performance wise for SSD storage? tempdbs? LDF files? MDF files? My database is more write intensive than read intensive (if that makes a difference).

Secondarily, are there any best practices I should follow when setting up the drives?

Should I have separate drives for each tempdb? LDF files? MDF files?

What about the system databases like master, model etc., should those go on their own dedicated drives or maybe on the C: drive?

Server disk activiity

By the way, here is a screenshot of our existing database server disk activity. It seems the tempdbs are the most read/write intensive followed by our MDF file and surprisingly the LDF file seems to have the least I/O.

Any advice would be appreciated.

First and foremost, look at your SAN vendor's documentation for recommendations regarding storage for SQL Server.  This really should be your first step as the vendor hopefully has done a lot of this general analysis for you.

If the documentation doesn't mention hosting databases, do some more digging before you choose to go with Tiered Storage.  Understand how data migration between tiers works on your SAN and how frequently it occurs.  Beware of anything that will run a scheduled job that tracks how active sectors may be during a time frame (often a 24 hour period, but this if often configurable).  I've found that in this scenario when it comes time to migrate the data, that data is often slow to access during the operation.  You'll want to insure this process runs during a period of low db activity (which means don't run this during the same time you're backing up your database).  If you have a database that's active all day, I'd recommend you NOT use tiered storage at all if this is how tiering works on your SAN.  Another problem with migrating data between tiers when this type of analysis is performed is that often times data access patterns change and are not consistent throughout the day.  This can result in data living on the faster tiers that really shouldn't be there.  For instance, when you run Index Maintenance overnight the SAN may flag that data as hot and migrate it to a higher tier.  If those indexes don't get accessed during the next migration analysis window, you're now wasting I/Os on idle data.  Depending on your database usage patterns, this could happen quite often where your data is getting migrated to the faster tier only to sit there idle.

Again, look at your vendor documentation.  I would hope they clearly outline the recommended approach needed for their hardware.  Also, give SAN Storage Best Practices for SQL Server, from Brent Ozar a read.  That goes into much more depth on some best practices and is well worth the read.


How can I find the shortest path between two currencies? I have table currency_pair(c1, c2); values are (usd,bnb), (cake,bnb), (cake,eth).
I need to find the shortest path that allows me to compare usd to eth.
The result here would be those same values. I can then use the first pair to establish a usd-bnb relationship, which I can then use to calculate a usd-cake relationship, which I can then use to compute usd-eth.
Since order is not determinate, the first step I did was create a materialized view, currency_pair_map(c1, c2), which is a union of select c1, c2 union select c2, c1. This would seem to simplify the logic.
If I am thinking about this correctly, what I need to do is use WITH RECURSIVE? I should also have some sort "depth_limit" parameter that ensures that a query fails if it is impossible to establish a pair.
Thinking about this out loud, we should always begin with:
SELECT *
FROM currency_pair
WHERE
  c1 = 'usd' AND
  c2 = 'eth'

If there is a result, we should end there.
If there is not, then we need to find all usd-* pairs and continue search until we find one that ends with eth.
Using this logic, so far I have:

WITH RECURSIVE pair_route AS (
  SELECT
    1 depth,
    cp1.id,
    cp1.c1,
    cp1.c2
  FROM currency_pair cp1
  WHERE
    cp1.c1 = 'usd'
  UNION
  SELECT
    pr1.depth + 1,
    cp2.id,
    cp2.c1,
    cp2.c2
  FROM pair_route pr1
  INNER JOIN currency_pair cp2 ON cp2.c1 = pr1.c2
  WHERE
    pr1.depth < 4
)
SELECT *
FROM pair_route;

I think this is correct. Now I just need to track path (IDs) and identify the shortest paths.

You could use this recursive query:
WITH RECURSIVE c AS (
      SELECT c1, c2 FROM currency_pair
   UNION
      SELECT c2, c1 FROM currency_pair
), curr_path AS (
      SELECT ARRAY[c1, c2] AS path
      FROM c
      WHERE c2 = 'usd'
   UNION ALL
      SELECT c.c1 || p.path
      FROM c
         JOIN curr_path AS p
            ON c.c2 = (p.path)[1]
      WHERE c.c1 <> ALL (p.path)
)
SELECT path
FROM curr_path
WHERE path[1] = 'eth'
ORDER BY cardinality(path)
LIMIT 1;

First, I calculate the symmetric closure.
Then I construct arrays of "conversion paths" by starting with the pairs we have, then prepending new currencies to arrays that can be converted to the first array element and do not yet appear in the array.
Once I have got all conversion chains that end up in 'usd', I pick the shortest chain that starts with 'eth'.


SSMS down arrow on last line I apologize if this is not the correct place to ask this but something is bugging me. I could have swore that at a previous job I could be on the last line of code in SSMS and hit the down arrow and the cursor would move to the end of the line. Now it just stays in the middle of that last line. I'm using SSMS 11.0.2100.60. Is there a setting I'm missing or did I imagine that happening? :)

As already shown in the comments, I've seen/used the same feature in SSMS 2005 as shown here. But I didn't manage to see what ever editor window setting would be different than in SSMS 2012/2014 where the behavior has changed.


What is the quickest way to restore a DB Lately I have been finding myself restoring a DB to my local instance of SQL-server a lot more. I wanted to speed up the process by writing a script and then just executing it in order to restore over some X DB on my local. However, every time that I run this script I get the error saying the DB is still being used. Now this could happen because I have a query open, or because a web application is still using it, but instead of me going to track down what is using my db and stopping it. I would much rather have my script take care of this since this is not going to be affecting anyone but me.

Is there a better way to do this than querying for the processes that are using the DB and killing them? Currently I was thinking of doing the following

 select
 spid,status,loginame=SUBSTRING(loginame,1,12),
 hostname=SUBSTRING(hostname,1,12),
 dbname = SUBSTRING(db_NAME(dbid),1,10)
 from sys.sysprocesses where DBid = 17

and then running the results back through a dynamic sql with the KILL command.

You can set the database in SINGLE_USER mode. If you also specify ROLLBACK IMMEDIATE it won't wait for open transactions to finish but will just roll them back.

ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

do your actions

ALTER DATABASE [yourdatabase] SET MULTI_USER


A single window function in a subquery should do the trick:

SELECT *
FROM  (
   SELECT *, count(*) FILTER (WHERE height = 0)
                      OVER (PARTITION BY traj_id ORDER BY timestamp) AS ct0
   FROM   trajectory_data
   ) sub
WHERE  ct0 = 0;

This counts occurrences of 0 along the timeline per traj_id, and only keeps rows before the first one pops up. Voilá.

db<>fiddle here

Related:

Return counts for multiple ranges in a single SELECT statement
Select longest continuous sequence


Error in AG Dashboard when looking at a Distributed AG I have a Distributed AG, but whenever I go to look at the AG Dashboard it says "An error occurred on the Always On Dashboard".  When I click for details I get "Object reference not set to an instance of an object."

Is this a known bug?  Or is there possibly something about the setup of my DAG that's causing this?  I get this error in both

SSMS 2016 16.5.3 (build 13.0.16106.4)
SSMS 2017 17.4 (build 14.0.17213.0)

As a couple users mentioned in the comments, if you are not actually having problems with the AG, this is likely a bug with SSMS.

You mentioned you're on 17.4, and I see in the release notes for 17.6 that there are several fixes for AG issues, including:

  Fixed an issue when the primary is down and manually failover to secondary, a NullReferenceException will be thrown.

(a NullReferenceException in .NET classically includes the message you mentioned - "Object reference not set to an instance of an object")

You should try updating to 17.6 (build 14.0.17230.0) to see if the problem is resolved there.


This should do the trick.

SELECT COUNT( * ) AS  `how much` ,  `name` ,  `location`
FROM  table
WHERE  name NOT IN (SELECT name FROM table WHERE location = 'y')
GROUP BY   `name`
ORDER BY   `name`

The idea behind this is : we are counting the number of name but we have to exclude rows of name where in ANOTHER row there is location = 'y'.

If you do WHERE location <> 'y', you just exclude the line including location = 'y' but the others rows with location = 'x' are still there and used in count(*).

So, the goal is to exclude ALL names where a minimum of one row has location = 'y'. This is why we do name NOT IN (...).

The part between the brackets select all name where location = 'y'. This outputs all names we want to exclude from our COUNT(*). We just have to exclude all these results from our query.

That's all folks !


Making an Oracle XE instance network wide available Im fairly new to Oracle so please bear with me.
I want to install an Oracle 18c XE DBMS on a server and access it's databases remotely via DBeaver. First of all there is some confusion on which client to use.
So far I've read the documentation and this is what I found regarding finding a client:

Oracle Client Software
The remote computer must have Oracle client software installed. It is through Oracle client software >that Oracle command-line utilities (and your applications) connect to the database. Oracle Database XE >accepts connections from all of the following types of Oracle client software:

Oracle Database Express Edition Client (Oracle Database XE)

When you install Oracle Database XE, Oracle Database Express Edition Client is also installed on the >same computer. You can install Oracle Database XE separately on remote computers. It is available at
http://www.oracle.com/technetwork/database/express-edition/Opens a new window

Instant Client

Instant Client is available at
http://www.oracle.com/technetwork/database/features/instant-client/index-100365.htmlOpens a new window
Oracle client software for Oracle Database Enterprise Edition or Standard Edition (all supported >releases of Oracle Database)

All Oracle client software includes Oracle Net, which is the Oracle network software that enables >client >applications on one computer to connect to databases on another computer over a network.

Question 1:
Since Im not going to access the databases programmatically and actually want to use a GUI or SQLPLUS, does that mean I have to fully install Oracle 18c XE on all clients?
For testing purposes I have set up two devices:

Device1 - private installation of Oracle 19c (IP: 192.168.178.201)
Device2 - future server with an installation of Oracle 18c XE (IP:
192.168.178.54)

Device1 is connected with network 192.168.178.0 via vpn and both devices can ping each other.
Now from my understanding I should only have to configure my DBeaver client connection like this:

But the connection times out...
Question 2:
Is my configuration of the tsnames.ora and listener.ora wrong?
tsnames.ora:
 # tnsnames.ora Network Configuration File: C:\app\install\product\18.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = xepdb1)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora
# listener.ora Network Configuration File: C:\app\install\product\18.0.0\dbhomeXE\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\install\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\install\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.178.54)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

You should only install server software (i.e. Oracle 18c XE) on the server. You should install client software (i.e. Instant Client or full OCI Client) everywhere else. While the server software includes the client, it is intended for local access, or to allow servers to talk to each other. There is no need to (indeed every reason not to) install the full XE server on every client host.
For your server network configuration files, the defaults associated with an XE setup should be fine. The only thing you'd need to double check is that you don't have a firewall blocking access to the server port (1521 by default) for remote clients.


Composite key as foreign key of multiple tables On Entity1 table, could I merge the two "a" field in a single field ?

It depends on the data model.

If it is possible and correct for a row in table 1 to have a relationship with rows in tables 2 & 3 with different values of a then you must keep the two values separate as you potentially need to different values.

If this should not be possible (i.e. makes no sense for the situation you are modelling) then not only could you use the same column, but you should as keeping two copies of data that should be identical can lead, via bugs or other human error, to consistency errors. The same column can participate in any number of keys and indexes so that will not block you from doing this.

Without more information on what you are modelling, it is not possible to give a more definite answer.


SQL Server - Snapshot Agent won't start I have successfully set up Replication. The problem is Snapshot Agent won't start. When I try to start it manually I always end up with "The agent has never been run."

Publication runs under the service account - db_owner in source db for the Publication, msdb, distribution db and with read/write permissions to snapshot folder.

I'm asking for suggestions how to deal with this situation.
Thank you.

It was my mistake. Service account was not db_owner in distribution db after all.


How do I fix the naming of columns in Metabase? Metabase currently imports the column names and tries to figure out the wording. This convention is fundamentally broken in my schema. For example, when it scans my PostgreSQL database it'll bring in the columns as,

As St Tre As Appoint Or Name First
As St Tre As Appoint Or Name Last
As St Tre As Name First
As St Tre As Name Last
As St Tre As Name Organization
As St Tre As Name Prefix Cd
As St Tre As Name Short
As St Tre As Name Suffix Cd
As St Tre As Per Sent Type Cd
As St Tre As Primary Phone Ext
As St Tre As Primary Phone Number

In reality they look like,

assttreaspersenttypecd
assttreasnameorganization
assttreasnamelast
assttreasnamesuffixcd
assttreasnamefirst
assttreasnameprefixcd
assttreasnameshort
assttreasstreetaddr1

Where assttreas is a shorthand for "Assistant Treasurer". Is there any easy way to fix this or disable this "feature".

You can disable "Friendly Table and Field Names" in Admin -> Settings -> General:

After you do it, go to Databases -> your_db_name and trigger "Sync database schema now" and "Re-scan field values now" actions:


Failed to start MySQL Community Server A lot of stuff stopped working on my server because the sda3 partition became full. After fixing that issue, I cannot start the mysql service:

root@abcd:~# sudo service mysql start
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.
root@abcd:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2018-09-28 22:44:46 UTC; 4s ago
  Process: 9276 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=1/FAILURE)
  Process: 9255 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Sep 28 22:44:46 abcd systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Sep 28 22:44:46 abcd systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Sep 28 22:44:46 abcd systemd[1]: Stopped MySQL Community Server.
Sep 28 22:44:46 abcd systemd[1]: mysql.service: Start request repeated too quickly.
Sep 28 22:44:46 abcd systemd[1]: mysql.service: Failed with result 'exit-code'.
Sep 28 22:44:46 abcd systemd[1]: Failed to start MySQL Community Server.

If anybody could help me with this problem, I would be greatly appreciative. Thanks!

Seems to be many errors
do this and then have a look on log again
Decrease the value of innodb-buffer-pool size in the config file /etc/mysql/my.cnf

innodb_buffer_pool_size = 10M

Looks like /var/lib/mysql is owned by the mysql user and the group is set to mysql as well.

$ sudo chown -R mysql /var/lib/mysql
$ sudo chgrp -R mysql /var/lib/mysql
$ sudo chmod 755 /var/lib/mysql

Have you checked that the directory /var/lib/mysql does actually exist and corresponds to your data directory? If it does not, then you probably must specify otherwise the datadir parameter under the mysqld section

[mysqld]
datadir=/var/lib/mysql

Then you can chose between configuring apparmor to allow MySQL to read\execute\modify the new data directory and sub directories or, at your very own risk, you can remove apparmor. You may need to be root to do the following:

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot

Source:
https://dba.stackexchange.com/questions/106085/cant-create-file-var-lib-mysql-user-lower-test


VMWare VMotion causing performance to increase? We've been experiencing strange issues for awhile now with our virtual environment running SQL Servers.

We randomly get calls from users about very poor performance on the SQL boxes. Sure enough when I look I see the CPUs are pegged at 100%. I perform a VMotion to another host and as soon as it finishes moving over to another host performance immediately returns to normal.

I've been working with the VMWare admins and they have assured me that VMotion would not affect anything on the SQL Servers. It's almost as if the move to another host is causing an execution plan change or the like. I don't understand, however, why out of nowhere CPU usage jumps through the roof unless it's a bad query plan recompile due to parameter sniffing, but I would think that a VMotion would not fix that since it's supposed to be transparent.

The VM farm is comprised of 19 Dell servers (sorry I don't know the exact model) with 2 physical sockets and 12 cores on each socket.

Has anyone else observed this behavior before? I'm wondering if it's something to do with capacity as there are some large VMs for the hosts to handle it seems (there are 14 80GB, 12 core VMs floating around). Even with those VMs on the farm I can see in the Vsphere console that the hosts aren't being over-utilized (memory does creep up to the 80% mark a lot of the time, but no ballooning).

Also, this occurs on all different versions of SQL (2008, 2008R2, 2012, and 2014).

Thanks a lot for any insight!

VMWare VMotion isn't going to reboot your server, restart any services or drop caches. The VM stays live during the VMotion, so you shouldn't lose cache or plans, unless the host you are moving to is under severe memory pressure and ballooning is active.

What does happen during VMotion is increased network latency and maybe a dropped ping while migrating, but that effect is completely gone once the migration is over and should not affect CPU usage inside the guest.

However what you need to understand is that the %CPU use inside the guest is the % you are consuming from the pool of resources that has been allocated to you by the hypervisor (not the underlying CPU) so if you move from a host allotting you 4Ghz to a host allotting 2Ghz the CPU usage inside the guest would double.

There are a few performance counters you could monitor inside the guest VM to see the actual CPU time you are getting from the Hypervisor such as:

% Processor Time
Effective VM Speed in MHz
Host processor speed in MHz
Limit in MHz

See here for a start

Which could give you an idea of the actual MHz you are getting, any limitations imposed by the VMWare configuration etc.

If you have determined the Hypervisor isn't allocating enough cycles to your VM you could set reservations to guarantee a certain amount of MHz or add a cpu weight to the VM giving precedence to your VM over others.

If you have access to esxtop (not the flattened sampled averages charts from vCenter) you should keep an eye on %RDY (indicating your VM has threads waiting for a physical cpu) or %CSTP (indicating co-scheduling issues). For more information read through this yellowbrick post

Since you are saying the host is having other high load VM's you also need to consider that VMWare is trying to allocate resources to the most demanding VM's when configured with defaults. A sudden increase in load in another VM could have dramatic (temporary) effects on your VM's cpu allocation.

Unless there are severe memory pressure issues I don't see how you could get cache flushes unless the new host reclaims a lot of the memory through the balloon driver or dynamic memory settings resulting in cache flushes. Otherwise the machine stays live and memory is copied over in lockstep


Queries for unique names from a site table using multiple criteria So I'm just learning SQL but have wound up as a database manager. The software they use is overall easy but is very restrictive in the reports it can create, though it does allow custom SQL reports.

As such, I need to find a way to do one (or all) of several things, #4 now being the most important:

Select unique site names which have been referred to at some point.
Select unique site names and how many times they have been referred to.
Select unique site names and the last date they were referred to.
Select unique site names which have never been referred to.

And do each of these without including sites which have been deleted.
Being new to SQL, I haven't even figured out #1 yet.

Relevant tables and columns:

╔══════════════╦═════════════════╦════════════════════════╗
║    Table     ║    Columns      ║    Settings            ║
╠══════════════╬═════════════════╬════════════════════════╣
║ site         ║ site_id         ║ PK, int, not null      ║
║ 207 columns  ║ name            ║ varchar(100), not null ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ site_service ║ site_service_id ║ PK, int, not null      ║
║ 192 columns  ║ site_id         ║ in, not null           ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ calls        ║ call_id         ║ PK, int, not null      ║
║ 183 columns  ║ call_num        ║ char(10), not null     ║
║              ║ call_date       ║ datetime, null         ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ referrals    ║ referral_id     ║ PK, int, not null      ║
║ 5 columns    ║ call_id         ║ int, not null          ║
║              ║ site_id         ║ int, null              ║
║              ║ site_service_id ║ int, null              ║
╚══════════════╩═════════════════╩════════════════════════╝

I just found this working query in the custom reports of the program. It is labeled "Referral statistics-Existing sites".

SELECT rl_ss_s.name, COUNT(cl.call_num) AS [NumberOfCalls]
FROM calls cl
LEFT JOIN referral rl ON cl.call_id=rl.call_id
LEFT JOIN site_service rl_ss ON rl.site_service_id=rl_ss.site_service_id
LEFT JOIN site rl_ss_s ON rl_ss.site_id=rl_ss_s.site_id
WHERE (cl.call_date BETWEEN 'ASK LATER: DATETIME' AND 'ASK LATER: DATETIME')
AND cl.deleted IS NULL
AND rl_ss_s.deleted IS NULL
AND cl.client_group_id='1'
GROUP BY rl_ss_s.name

I removed the WHERE filter for cl.client_group_id (as I'm not sure how it helps), then ran it. It does appear to meet goals 1 & 2, but has a glitch. Among the records contained in the report is a single blank-name site with roughly 4 times as many calls as the most referred to named site. As such, I'm not sure if it's combining all the deleted sites or something else. I can easily accommodate for it in the generated Excel file by deleting that line if it is combining deleted sites, I just have to figure out what it's doing.

I know what most of the functions in that example do, I just haven't figured out how to put it together to do what I need.

If you need any more information I have not included or clarification on anything, please let me know.
Any ideas how I put this together to achieve one (or all) of these goals?

1 Select unique site names which have been referred to at some point.

Get rows from site where site_id exists in referrals. This assumes site name is unique in site. If not you can add a distinct.

select s.name
from dbo.site as s
where s.deleted = 0 and
      exists (
             select *
             from dbo.referrals as r
             where s.site_id = r.site_id
             )

  2 Select unique site names and how many times they have been referred
  to.

Join site to referrals and count the number of rows grouped by site name.

select s.name,
       count(*) as times_referred
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
where s.deleted = 0
group by s.name

  3 Select unique site names and the last date they were referred to.

Add a join to calls and get the max call_date for each site name.

select s.name,
       count(*) as times_referred,
       max(c.call_date) as last_call_date
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
  inner join dbo.calls as c
    on r.call_id = c.call_id
where s.deleted = 0 and
      c.deleted = 0
group by s.name

  4 Select unique site names which have never been referred to.

Same as first query only you get the sites that does not exist in referrals.

select s.name
from dbo.site as s
where s.deleted = 0 and
      not exists (
                 select *
                 from dbo.referrals as r
                 where s.site_id = r.site_id
                 )


Securely manage and dynamically create multiple databases Background

I have inherited a system that has several hundred databases.

The primary "Clients" Database represents all clients with several tables like:
  WebUsers = username | password | server_id
  Servers = server_id | ip_address |

The remaining "ClientInfo" databases are intended for one client. Tables like:
  Customers = id | first_name | last_name

I am not a DBA. I'm a web developer who inherited a rough built system. So I apologize ahead of tie if this question seems silly.

The process

The client visit a login server: login.example.com. This server has access to the "Clients" database. They login to their account which takes them to the correct web application server, sass1.example.com, that has credentials found in the "Servers" database and grants them a secure session.

I think this is a pretty basic SASS web application setup. Think sales-force, get-satisfaction, that kind of thing.

My Problem

All databases are managed under a single database login. So the login server technically has access to all the client databases. And each application server has access to all databases. The only limiter being a single string in the session that identifies the user.

From a security standpoint it is my understanding that these databases should each have their own credentials. So that if one is breached via code injection remaining databases are secure.

For example my code logs into the client db using environment variables. Then selects the database name that is stored in the secure session. A code injection could easily submit a query that changes the active database.

What is the secure way to manage this system?

My rough plan was to have the login server point to the "Clients" database through a unique db user that has access to only that database. Then I would store the db user/password for each clients database in a "ClientList" table.

Then on login the client specific database credentials would be pulled from an encrypted value in the "server" table. So the session would check if it's in a valid login state then instead of the string pointing to a database name it has an id that points to the "ClientList" table that has encrypted login credentials to just their database.

But this doesn't seem much different than just managing all db's on the same login since the clients database still has all the passwords. A code injection would have access to the decrypt algorithms and just log in with different credentials. What is the right (secure) way to manage this system?

It seems like a limiting factor here might be that we don't want to make a connection string for each client and separate the user used to connect to the DB back end that way.  As a result we are using a SQL Database user that has full access to all the databases, with them really only needing access to 1 DB that being their own.  In the current security model, if a SQL injection attack were to occur, they would be be able to have access to all databases, instead of just the 1 user database they need due to the connection string.

To resolve this issue you're proposing that you create a database to hold customer information such as login ID and password to their SQL Server database user which has access to read/write/execute, and want to know if it provides any benefits over the current model.

You could do as you proposed by having a user that can only accept sanitized and paramterized stored procedure queries with being granted only execute permissions on the master users/login table.  Once you get that value based on your uniqifier (be it ID, company name, etc.), you are mostly protected from a SQL injection attack.  An extra level of defense would be to encrypt the value within the DB and have the app decrypt the username and password before passing it in.  This would fully protect you from someone getting your username/password from SQL Injection even if they did manage to pull out the raw value from the DB engine.  They would have to also get the decrypting private key off the web or key server before they could read the credentials needed to access the databases.

By doing this you are at least protecting your company from having someone make a change on every single DB in the enterprise if they were to break in, and they would be limited to just messing up 1 customer; unless of course they got your private keys or the decrypted password list some other way.

This is of course just a quick review of what you proposed.  There are tons of different ways to do security and this might not be the most ideal in your scenario, I don't know what would be best from the limited info, but it would work to answer your question and give you that extra line of protection of encryption, multiple places they'd have to break in, and separate users owning each DB which is always nice.

SQL 2016 has some great new security enhancements you could utilize too if you want such as always encrypted data and saving your keys elsewhere if you wish to take this to the next step.


#1064 - You have an error in your SQL syntax; check the manual for the right syntax to use near ''&lt)' at line 1 DATABASE SERVER
Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.56-cll-lve - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)
WEB SERVER
cpsrvd 11.66.0.18
Database client version: libmysql - 5.1.73
PHP extension: mysqli, curl, mbstring
PHP version: 5.6.30
phpMyAdmin
Version information: 4.7.3
I am using phpMyAdmin to update the column text which type is text with the following query:
UPDATE `oc_product_tab` SET `text` = '&lt;p&gt;&lt;u&gt;&lt;strong&gt;Ελλάδα:&lt;/strong&gt;&lt;/u&gt;&lt;br /&gt;\r\nΗ αποστολή - αφού έχει επιβεβαιωθεί η παραγγελία σας - πραγματοποιείτε &lt;strong&gt;σε όλη την Ελλάδα&lt;/strong&gt; με ταχυμεταφορά courier και η παράδοση γίνετε σε 1-3 εργάσιμες ημέρες στη διεύθυνση που θα δηλώσετε.&lt;br /&gt;\r\nΤο κόστος των μεταφορικών είναι &lt;strong&gt;3 ευρώ&lt;/strong&gt; για παραγγελίες κάτω των 15 ευρώ.&lt;br /&gt;\r\n&lt;strong&gt;Για παραγγελίες άνω των 15 ευρώ η αποστολή είναι δωρεάν Πανελλαδικά.&lt;/strong&gt;&lt;br /&gt;\r\nΣτις περιπτώσεις όπου η πληρωμή γίνεται με αντικαταβολή η &lt;strong&gt;χρέωση αντικαταβολής&lt;/strong&gt; είναι &lt;strong&gt;2€&lt;/strong&gt; επιπλέον.&lt;br /&gt;\r\n&lt;u&gt;&lt;strong&gt;Εξωτερικό:&lt;/strong&gt;&lt;/u&gt;&lt;br /&gt;\r\nΣε όλες τις χώρες της Ευρωπαϊκής Ένωσης η &lt;strong&gt;χρέωση&lt;/strong&gt; αποστολής είναι &lt;strong&gt;12€&lt;/strong&gt; για παραγγελία βάρους έως 3 κιλών ανεξαρτήτου της ποσότητας παραγγελίας και φτάνει στην πόρτα σας μεταξύ 5 έως 8 εργάσιμων ημερών.&lt;br /&gt;\r\nΓια παραγγελίες άνω των &lt;strong&gt;150€ εντός Ευρωπαϊκής Ένωσης&lt;/strong&gt; τα έξοδα αποστολής είναι &lt;strong&gt;ΔΩΡΕΑΝ!&lt;/strong&gt;&lt;br /&gt;\r\nΣτις υπόλοιπες χώρες η αποστολή γίνεται μέσω των δημόσιων ταχυδρομείων. Η χρέωση αποστολής καθώς και οι ημέρες παράδοσης εξαρτώνται από τη ζώνη αποστολής. Θα σας ενημερώσουμε μέσω e-mail ή τηλεφωνικά αφού ολοκληρώσετε τη διαδικασία παραγγελίας σας και πριν σας αποσταλούν τα εμπορεύματα.&lt;/p&gt;\r\n' WHERE `oc_product_tab`.`tab_id` = 2 AND `oc_product_tab`.`language_id` = 2;

and the error is
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''&lt)' at line 1

I have changed the name of the column from text to mytext (because text is a reserved word for MySQL) and the error shows up again.
If I use this query:
UPDATE `oc_product_tab` SET `text` = '' WHERE `language_id` = 2 AND `tab_id` = 2

It works.
What am i doing wrong?

I'll bet one of those semicolons is really a Greek question mark (utf8 hex CDBE).  I point out the visual problem in http://mysql.rjweb.org/doc.php/charcoll#other_natural_languages


How to refresh reference to objects in Visual Studio SSDT? I have an issue related to missing reference to tables or views which exist in Visual Studio SSDT. For example I have two SSDT projects. One for database X second for database Y. In project X I've created a reference to database Y and a stored procedures to tables from project Y. Everything worked well till last time I've created next table "abc" in Project Y. Now when I'm trying to create a stored procedure in Project X with this "abc" table from Project Y I cannot resolve its name. On the dropdown list in the Project X (FROM clause) I can see all tables from project Y except the last one and I get the blue underscore and unresolved reference warining.

Is there a way to refresh this dropdown list with tables somehow (maybe similar like I'm refreshing IntelliSense cache in SSMS ?

Ok, I've find out the answer for my question. One man had similar case and put it on MSDN. And Microsoft answer was that Visual Studio will not implement Visual Studio SSDT InteliSense reference refreshment feature. The workaround they've proposed is to remove and recreate the "database reference" in particular SSDT project. So I've removed old database reference and next create it back again. After such modification InteliSense drop-down list for references for TSQL code has been refreshed and works well. So when you add new objects to the referenced database you have to remove and create again database reference to be able to see newly created objects in InteliSense drop-down list when coding.


What is the difference between ALTER INDEX and DBCC DBREINDEX? Is the only difference between

ALTER INDEX [index_name] on [object_name] REBUILD with (ONLINE=OFF, FILLFACTOR=90)

and

DBCC DBREINDEX([dbname], 90)

just that the DBCC command will reindex all of the indexes on all of the tables in the database?

The DBCC command is a consistency checker utility inside of SQL Server whereas ALTER is a DDL SQL command.  The DBCC REINDEX command was deprecated in a previous version so it is not as robust as the new ALTER INDEX command is today.  There is more functionality in the ALTER INDEX than the DBCC REINDEX commands:

http://msdn.microsoft.com/en-us/library/ms188388.aspx

Happy indexing!


How to delete rows from a table when we're joining and return data? I have two queries:

The first one is as below:

SELECT *
FROM batch bp
  INNER JOIN sender_log sl ON sl.id=bp.log_id
    AND bp.protocol='someprotocol'

The second query is used to delete rows:

delete from batch where protocol='someprotocol'

I know that in postgres we have RETURNING * to return deleted rows. What I want to achieve is to delete rows and return them in one query. How can I accomplish this?

Very simple indeed, but you do need to include the other WHERE clause as well:

DELETE FROM batch bp
USING  sender_log sl
WHERE  bp.log_id = sl.id
AND    bp.protocol = 'someprotocol'
RETURNING bp.*, sl.*;

And to actually return what your question outlines, you need to include both tables in the RETURNING clause.


Importing from Excel I am attempting to import excel sheets into SSMS and I am having trouble with data types.  I was running into problems with columns with both text and numbers in them.  I want these columns to import as varchar, but some of the columns were importing as "float" and I was getting an "unknown type conversion" error that would crash the package.  Changing the mapping seemed to cause more problems than it solved.  The process that I have found that seems to work most of the time is as follows:

Import a sheet with only headers and sample data (to "seed" the data type) into a new table
Use Design view to correct data types as necessary
Import the sheet with all the headers and data

This has worked for 9 tables.  However, I am now running into the following problem: I followed steps (1) and (2) above.  When executing step (3), I am getting "unknown column type conversion" errors for columns without any data in them.  For some reason, the Import tool interprets a couple of empty columns as "DateTime" when the data type of the table column is "float".  This is causing the import to fail.  I changed the data type in Excel properties to "Number" but that does not seem to make a difference.  I have two questions:

How can I get the data imported with the correct type?
Why does the Importer get it wrong sometimes?  Other columns without data are recognized as "float".

As you said

  1) Import a sheet with only headers and sample data (to "seed" the
  data type) into a new table
  2) Use Design view to correct data types
  as necessary
  3) Import the sheet with all the headers and data

I would like to say that if you want to import excel sheet with header data then through TSQL is the best option to do that.

First you have to check in SSMS tools there is ODBC driver are there or not in Server Objects->Linked Servers->MICROSOFT.ACE.OLEDB.12.0 or other microsoft ACE OLEDB driver. it depends on your SQL Server version. In My case it is SQL Server 2012.

Suppose that if you are using SQL Server 2012 and there is MICROSOFT.ACE.OLEDB.12.0 then make sure 'Allow inprocess' is Enable.

How You shall do that

Right click over MICROSOFT.ACE.OLEDB.12.0 driver and in new window there will be
'Allow inprocess' option. check to that option like that

write down in your SSMS this TSQL statement like that, through this query you shall import your execl column header with your data.

Use DatabaseName;
        Go
        select * INTO [User]
        from openrowset('MICROSOFT.ACE.OLEDB.12.0',
        'Excel 12.0; Database=C:\TestFolder\User.xlsx; HDR=yes; IMEX=1',
        'select * from [User$]');
        Go

Here DatabaseName is the Name of Database.
User is the Table Name .
User.xlsx is the Excel sheet , which you will import.
Make sure that HDR='yes' which will contains your header column names.

I hope so that it will helpful to you . To import your Excel sheet data in SQL Server through SSMS tools & TSQL Script.


Optimize query with join and null predicate [SQL Server] I have the following query which I am having problems optimizing:

SELECT TOP (100) this_.Id AS Id80_1_,
   this_.UserAgent AS UserAgent80_1_,
   this_.IP AS IP80_1_,
   this_.Guid AS Guid80_1_,
   this_.CreatedOn AS CreatedOn80_1_,
   this_.UpdatedOn AS UpdatedOn80_1_,
   this_.IsDeleted AS IsDeleted80_1_,
   this_.AnalyticsUserId AS Analytic8_80_1_,
   this_.SiteId AS SiteId80_1_,
   analyticsu1_.Id AS Id15_0_,
   analyticsu1_.Email AS Email15_0_,
   analyticsu1_.DateLastChecked AS DateLast3_15_0_,
   analyticsu1_.Guid AS Guid15_0_,
   analyticsu1_.CreatedOn AS CreatedOn15_0_,
   analyticsu1_.UpdatedOn AS UpdatedOn15_0_,
   analyticsu1_.IsDeleted AS IsDeleted15_0_,
   analyticsu1_.UserId AS UserId15_0_
FROM [AnalyticsSession] this_
INNER JOIN [AnalyticsUser] analyticsu1_ ON this_.AnalyticsUserId=analyticsu1_.Id
WHERE (analyticsu1_.Email IS NULL
       AND analyticsu1_.UserId IS NULL)
  AND this_.CreatedOn < '2017-04-01'

I have tried adding an index in multiple ways including:

CREATE NONCLUSTERED INDEX [IX_AnalyticsUser_UserEmailIsNull1] ON [dbo].
[AnalyticsUser]
(   [Email],    [UserId])
INCLUDE (Id, DateLastChecked, Guid, CreatedOn, UpdatedOn, IsDeleted)
WHERE (Email IS NULL and UserId IS NULL)
WITH (online=on)
GO

However my query plan shows sql server scanning and joining 113,000 rows to get the 100 rows returned: (both ActualRows="112806" ActualRowsRead="112806" on the scan operator)

This database is on azure in compatibility mode 130.

What is the best index to add to optimize this query? There are 2355366 rows in the AnalyticsUser table.

You have a two table query here with a join. One of the tables needs to be the outer table for the join and one of them needs to be the inner table. With appropriate indexes on both tables, as a general rule I would want the most selective table to be the outer table for a TOP 100 query. As far as I can tell, SQL Server picked the correct table for the outer table, AnalyticsSession. The problem appears to be that there isn't a selective enough index defined against that table.

Let's consider what would happen if the optimizer used the AnalyticsUser table for the outer table. That filtered index looks to be the best possible index for the query. However, the index isn't selective. In the comments you indicated that 97% of the rows in the table match the filtering that you have on [Email] and [UserId]. So SQL server might end up scanning a lot of rows from the index until it found the first 100 that satisfied the filter conditions for both tables.

Compare that to using the AnalyticsSession table as the outer table. The filter seems to be pretty selective (you had to process 113,000 rows just to get 100 matches), but the index used is not selective at all. A clustered index scan will just go in whatever order the data is logically or physically defined on disk. If you created an index similar to the following:

CREATE NONCLUSTERED INDEX [IX_AnalyticsSession__CreatedOn_Id] ON [dbo].
[AnalyticsSession]
(   CreatedOn, AnalyticsUserId)
INCLUDE (id, UserAgent, IP, Guid, CreatedOn, UpdatedOn, IsDeleted, AnalyticsUserId, SiteId)
WITH (online=on);

I suspect that would help query performance quite a bit. SQL Server would be able to do a scan on that index to immediately jump to the first possibly relevant row from AnalyticsSession. If 97% of the rows from AnalyticsUser match the filter condition and the filters between the two tables are independent, then you should be able to get the first 100 rows with a scan of 110 rows or less from the new index on AnalyticsSession.

If the filters are not independent then your query may scan more rows than expected. For example, suppose that users who registered near the beginning of the site are more likely to have an email or an id. With the index as defined above, SQL Server is likely to scan the oldest rows first by CreatedOn. As you described in your comment you may get better performance by getting the scan to go in descending order rather than ascending order. The best way to do this is to add an ORDER BY at the end of your query:

WHERE
(analyticsu1_.Email IS NULL
AND analyticsu1_.UserId IS NULL)
AND this_.CreatedOn < '2017-04-01'
ORDER BY this_.CreatedOn DESC

Otherwise you aren't guaranteed to get the scan in the order that you're expecting. Note that TOP 100 without ORDER BY is nondetermistic and may return different results from one execution to the next. Consider adding an ORDER BY to your query if that makes sense to do so.

If you're interested in learning more about how SQL Server optimizes queries with TOP in general I recommend Inside the Optimizer: Row Goals In Depth by Paul White.


How to count occurrences of date only in a field that contains datetime data? I have a MariaDB database/table that contains log entries. For the purpose of example and broader help to other users, let's call the table FOO_TABLE and the field FOO_TIMESTAMP.

The FOO_TIMESTAMP field had a data type of datetime.  Examples of content include:

select FOO_TIMESTAMP from FOO_TABLE limit 10;
+---------------------+
| FOO_TIMESTAMP       |
+---------------------+
| 2013-01-21 12:28:17 |
| 2013-02-06 08:54:05 |
| 2013-02-06 08:59:55 |
| 2013-02-06 09:05:46 |
| 2013-02-06 11:47:38 |
| 2013-02-06 12:14:04 |
| 2013-02-06 12:15:05 |
| 2013-02-06 12:42:45 |
| 2013-02-06 13:01:49 |
| 2013-02-06 13:20:50 |
+---------------------+
10 rows in set (0.00 sec)

I want to get a count of how many rows were created per day.  I'm not interested in the specific time.  So for example, I'd like the following information from the data set above:

2013-01-21     1
2013-02-06     9

I've searched prior stack exchange sites and see plenty of references to the GROUP BY option but I'm not sure how to only filter it on the date... and not have it look at the full time too.

I can get the date by itself by running select date(FOO_TIMESTAMP) from FOO_TABLE; but I still can't get a count of the occurrences of that.

I thought of trying something like select date(FOO_TIMESTAMP) as DATEONLY from FOO_TABLE group by DATEONLY;  but that still doesn't give me count of date occurrences.

What am I missing here?

You just need to format the timestamp so it only shows the day, then group by that:

select date_format(foo_timestamp,'%Y-%m-%d'), count(*)
from foo_table
group by 1
order by 1;


Determine whether mixed mode auth is enabled without logging in? Is it possible in SQL Server to determine whether mixed mode authentication is enabled without logging on to SQL Server?

You can check it in the registry:

HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer

The current mode is recorded in the LoginMode key.

Values (DWORD) can be:

1 = Windows Authentication mode
2 = SQL Server and Windows Authentication mode

Note:

0 is equivalent to 2
You still need access to the Registry (and the server)


Manage billions lines of sensors data in mariaDB I'm the new "IT guy" in a small company that managed ecological footprint reduction in industries, for contracts reasons they need to keep every data changes of each sensors(some changed every seconds some  every minutes) they put in their installation for 5 years. They are doing some calculations on these data to prove the ecological footprint reduction each year and to detect some errors/illogical things on the installation.
Currently we have one project ongoing and two other coming in next weeks.
The previous IT guy have set a MariaDB server on a VPS with this structure:
CREATE TABLE `machine` (
  `Name` varchar(100) CHARACTER SET utf8 NOT NULL,
  `Site` int(11) DEFAULT NULL,
  `Emplacement` varchar(200) DEFAULT NULL,
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Name`),
  UNIQUE KEY `ID` (`ID`),
  KEY `FK_machine_site` (`Site`),
  CONSTRAINT `FK_machine_site` FOREIGN KEY (`Site`) REFERENCES `site` (`ID`)
) ENGINE=InnoDB;

CREATE TABLE `mesure` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Machine` varchar(100) NOT NULL,
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Valeur` decimal(18,5) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `machine_timestamp` (`Machine`,`Date`) USING BTREE,
  KEY `Date` (`Date`),
  CONSTRAINT `FK_valeur_machine` FOREIGN KEY (`machine`) REFERENCES `machine` (`Name`)
) ENGINE=InnoDB;

CREATE TABLE `site` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Nom` varchar(50) NOT NULL,
  `Ville` varchar(100) DEFAULT NULL,
  `Code_Postal` varchar(100) DEFAULT NULL,
  `Rue` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB;

In about 7 months  we got about 600 millions rows inside the "mesure",80 rows inside the "machine", just one inside the "site" table and the database is about 40GB
This working quite well for the moment, data can be accessed in a good time for the monthly data extraction (I have done a script that recover and generate an excel per week with all calculations done inside per seconds).
we have decided to set a new server on a new VM per project for the moment.
To summarize the context

One database and server per project, for the moment MariaDB
About 100 sensors on each project with variable updates, about 600 millions records in 7 months for the current project, 40GB
Data cannot be reduced, normalized, and we need to keep these data available for 5 years
low interactions with the DB, only writing inside with the application reading sensors data. And extracting one time each month to recover monthly data.
Limited budget for IT, cannot affrod big servers. I'm working on 4 cores, 8GB RAM, SSD VPS, only used for the data recovery app(low ressources usage) and the DB

My questions

It seems that we're going to reach max int value for the mesure ID PK fast, do I need the set it to bigint ? was thinking about change the PK to (Machine,Date) couple is this a good idea or not ?
Do we will face any limitations in the future with this way of doing ?
Is it a good idea to stay on MariaDB or do I need to look to other DB ? was looking  for TimeScaleDB, any comments, positive/negative reviews on this ?
Any optimisations that I can do to reduce size of the DB for example ? was thinking about yearly "archive" compressed DB dumps for each project inside a unique less costly server with big HDD(these dumps will be backup locally and on a cloud drive), to reduce size of backups we're doing on mariaDB servers and only restore dump on local DB if data need to be retrieve in very rare cases. Any comments on this ?

Since you are likely to have a billion rows in this one table, I will focus on it:
CREATE TABLE `mesure` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Machine` varchar(100) NOT NULL,
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Valeur` decimal(18,5) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `machine_timestamp` (`Machine`,`Date`) USING BTREE,
  KEY `Date` (`Date`),
  CONSTRAINT `FK_valeur_machine` FOREIGN KEY (`machine`) REFERENCES `machine` (`Name`)
) ENGINE=InnoDB;

Each row is taking about 70 bytes (including overhead); multiply that by billions of rows over 5 years, and... Get the picture?  We need to shrink this table.
decimal(18,5) -- That seems much too big.  It takes 9 bytes.  Consider using FLOAT (no, not FLOAT(18,5)).  That takes 4 bytes and has about 7 significant digits.  That is more than adequate for any sensor I know of.
Machine varchar(100) -- Shrink that down to an "id" that joins to the machine table.  How many machines might you eventually have?  Probably between 256 and 64K?  Use a 2-byte SMALLINT UNSIGNED both here and in the machine table.
To allow for the unlikely duplicate, do INSERT IGNORE instead of simply INSERT.
Can you get 2 readings for the same machine in the same second?  Probably not.  Or even if you did, you can (should?) throw away the redundant reading?  So, I recommend  PRIMARY KEY(machine_id, date) (and eliminate the existing UNIQUE with the same columns).
The change to the PK eliminates the 4 byte ID that you currently have.  INT is a disaster that will occur in less than a year -- when it hits the limit of about 2 billion.
Revised schema
CREATE TABLE `measure` (
  `MachineId` SMALLINT UNSIGNED NOT NULL,
  `Date` datetime NOT NULL,
  `Valeur` FLOAT NOT NULL,
  PRIMARY KEY(`Machine`,`Date`),
  KEY `Date` (`Date`),
  CONSTRAINT `FK_valeur_machine`
        FOREIGN KEY (`machineId`) REFERENCES `machine` (`ID`)
) ENGINE=InnoDB;

Those changes might cut the table's disk footprint in half.  You say the queries are fast enough now, but they will get slower over time.  This schema will help keep them "fast enough".
Summary table(s)
You will probably want "reports" or graphs or other queries that do a big SELECT ... WHERE date between... GROUP BY machineId.  And you will find that this is slower than you like.  See http://mysql.rjweb.org/doc.php/summarytables ; we can discuss that in another Question.
Done properly, you can consider not saving the raw data for 5 years, while keeping the summarized data "forever".  The summary should be one-tenth the size and 10 times as fast to query.  (YMMV - Your Mileage May Vary.)
Deleting old data
If you keep the data for only 5 years, how will you delete the "old" data?  You will find that a big DELETE is grossly inefficient.  Plan ahead by PARTITION BY RANGE(TO_DAYS(date)) now.  Have monthly partitions.  More:  http://mysql.rjweb.org/doc.php/partitionmaint .  DROP PARTITION is much faster.


How to Skip Existing Data While Restoring mysqldump File? I use this query to import data using MySQL Dump (Command Prompt / Command Line)
mysql -u root -p database_name < database_name.sql

I want to import the large database (only new deltas) as of now I drop database and re-import but I want to import only new deltas.

I have four(4) suggestions:
SUGGESTION #1 : Recreate dump with INSERT IGNORE
Use --insert-ignore

Write INSERT IGNORE statements rather than INSERT statements.

What happens on reload ???

New data gets inserted.
Already existing primary keys are skipped

SUGGESTION #2 : Change INSERT to INSERT IGNORE on reload
Instead of loading normally
mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:
cat database_name.sql|sed 's/^INSERT/INSERT IGNORE/'|mysql -u root -p database_name

SUGGESTION #3 : Recreate dump with REPLACE INTO
Use --replace

Write REPLACE statements rather than INSERT statements.

What happens on reload ???

New data gets inserted.
Already existing primary keys will be deleted and re-inserted

SUGGESTION #4 : Change INSERT INTO to REPLACE INTO on reload
Instead of loading normally
mysql -u root -p database_name < database_name.sql

just pipe the dump into sed and reload like this:
cat database_name.sql|sed 's/^INSERT INTO/REPLACE INTO/'|mysql -u root -p database_name

SUMMARY

Use suggestions 1 or 3 if you can recreate the dump
Use suggestions 2 or 4 if you cannot recreate the dump

GIVE IT A TRY !!!


A transport-level error has occurred when sending the request to the server In our Vb.net application(userd within a local network connection inside a office), which is used from long time now some of the users get the errors only sometimes a day

  A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.

I have already checked following

All databases are set to autoclose OFF
Didn't find much network problems and network speed is good.
When I checked in SSMS the under server properties remote query time-out set to 600 seconds
I checked sql server log and no errors found relating to it or same error message

So what may be the reason for this error? And how to troubleshoot it?

If an application opens a connection (successfully) to SQL Server, and leaves it open in anticipation of sending a (or possible 'another') query, and in the meantime, SQL kills that connection for some reason (eg, service restarts, or a kill command is sent), then when the connection (which thinks it's open) tries to send a command, you'll get this error.

So catch the error in your application, and reconnect. Then try the command again.


Is having a 92MB MDF with a 58MB LDF cobo ok? How do I manage the log to keep things running well? I have jobs in place to trim tables used for history and logging so to keep them trim. I want to make sure I don't neglect the log file.

How and what can I or should I do to keep the log file in check?

The sql script I have running on a nightly schedule is:

declare @DBname varchar(500)
set @DBname = 'E:\Database\backup\PMIS_backup_'+ convert(varchar(MAX), getdate(), 23 ) +'.bak'

BACKUP DATABASE [PMIS] TO  DISK = @DBname
WITH NOFORMAT, NOINIT,  NAME = @DBname
, SKIP, REWIND, NOUNLOAD,  STATS = 10

(recovery mode is simple)

If your database is in FULL or BULK_LOGGED recovery mode, you need to backup your database and log files on a regular basis. If your database is in SIMPLE recovery mode, then you only need to backup your database on a regular basis.

Please read the following articles for more info:

http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx
http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
http://www.sqlskills.com/blogs/paul/post/Why-you-should-not-shrink-your-data-files.aspx


adding an index on different fields I have a query like this:

SELECT "btr_orders".*
FROM "btr_orders"
WHERE "btr_orders"."user_id" = 40000000
  AND (
    premium_until > now()
    OR intermediate_premium_until > now()
  )
  AND (
    active_since IS NULL
    OR active_since <= now()
  )
  AND "btr_orders"."temporary" = false
ORDER BY "btr_orders"."id" DESC
LIMIT 1;

this query takes around one two seconds to run, and we have at least 10 to 20 clients running this query over and over

the output of the EXPLAIN ANALYZE is this:

Limit  (cost=6192.97..6192.98 rows=1 width=244) (actual time=1086.744..1086.744 rows=0 loops=1)
  ->  Sort  (cost=6192.97..6192.98 rows=1 width=244) (actual time=1086.743..1086.743 rows=0 loops=1)
        Sort Key: id
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on btr_orders  (cost=0.00..6192.96 rows=1 width=244) (actual time=1086.736..1086.736 rows=0 loops=1)
              Filter: ((NOT temporary) AND ((active_since IS NULL) OR (active_since <= '2017-06-13 14:59:29.528395'::timestamp without time zone)) AND (user_id = 40000000) AND ((premium_until > '2017-06-13 14:59:29.527964'::timestamp without time zone) OR (intermediate_premium_until > '2017-06-13 14:59:29.527964'::timestamp without time zone)))
              Rows Removed by Filter: 154734
Total runtime: 1086.789 ms

although the rows filtered is actually 1, this query is really slow, probably because we are doing seq. scan. I tried to create an index for this query:

 create index CONCURRENTLY btr_orders_search_index
   on btr_orders (temporary, user_id, active_since, premium_until, intermediate_premium_until)
   where temporary = false;

but of course didn't worked. I have a very limited knowledge on postgres, actually in database administration (I'm a backend developer) and we do not have a DBA.
My question is: how can I create an index that fits this query?

Edit

\d btr_orders

zenguard_api=# \d btr_orders
                                             Table "public.btr_orders"
           Column           |            Type             |                        Modifiers
----------------------------+-----------------------------+---------------------------------------------------------
 id                         | integer                     | not null default nextval('btr_orders_id_seq'::regclass)
 active_since               | timestamp without time zone |
 affiliate_cookies          | hstore                      |
 affiliate_net_amount       | integer                     |
 affiliate_partner_id       | integer                     |
 closed_since               | timestamp without time zone |
 country_code               | character varying(255)      |
 created_at                 | timestamp without time zone |
 discount_id                | integer                     |
 first_charge_at_activation | boolean                     | default false
 intermediate_premium_until | timestamp without time zone |
 ip_country_code            | character varying(255)      |
 merchant_account           | character varying(255)      | default 'ZenGuard'::character varying
 order_reference            | character varying(255)      |
 paid_premium_until         | timestamp without time zone |
 premium_since              | timestamp without time zone |
 premium_until              | timestamp without time zone |
 price_id                   | integer                     |
 product_id                 | integer                     |
 purchase_platform          | character varying(255)      |
 temporary                  | boolean                     | default false
 updated_at                 | timestamp without time zone |
 user_id                    | integer                     |
 migrated_from_old          | boolean                     | default false
Indexes:
    "btr_orders_pkey" PRIMARY KEY, btree (id)

As already mentioned in the comments by @JacobH, consider putting btr_orders.id in an index.

I have assumed you have a scenario similar to this one:

 CREATE TABLE users
 (
     user_id integer PRIMARY KEY
 ) ;

 CREATE TABLE btr_orders
 (
     id SERIAL PRIMARY KEY,
     active_since timestamp without time zone,
     premium_until timestamp without time zone,
     intermediate_premium_until timestamp without time zone,
     user_id integer NOT NULL REFERENCES users(user_id),
     temporary boolean NOT NULL DEFAULT true,
     some_cargo text
 ) ;

My assumption is that the column named id is actually your PRIMARY KEY, and declared it as such. By doing so, I automatically get an index on that column.

I simulate users and orders:

 INSERT INTO users (user_id)
 SELECT generate_series(1, 5000) AS user_id ;
 INSERT INTO users (user_id) VALUES (40000000) ;

5000 rows affected
1 rows affected

 INSERT INTO btr_orders
     (active_since, premium_until, intermediate_premium_until, user_id, temporary)
 SELECT
     active_since,
     active_since + interval '1 day' AS premium_until,
     active_since + interval '12 hours' AS intermediate_premium_until,
     case when random() < 0.5 then 40000000 else random()*4000+1 end AS user_id,
     random() < 0.5 AS temporary
 FROM
     generate_series(date '2017-01-01', date '2017-12-31', interval '3 minutes') AS x(active_since);

174721 rows affected

The execution plan for your query is:

 | QUERY PLAN                                                                                |
 | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | Limit  (cost=0.42..201.70 rows=1 width=65) (actual time=25.518..25.519 rows=1 loops=1)                                                                                |
 |   ->  Index Scan Backward using btr_orders_pkey on btr_orders  (cost=0.42..11876.08 rows=59 width=65) (actual time=25.517..25.517 rows=1 loops=1)                                        |
 |         Filter: ((NOT temporary) AND (user_id = 40000000) AND ((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now()))) |
 |         Rows Removed by Filter: 96067                                                                                |
 | Planning time: 0.143 ms                                                                                |
 | Execution time: 25.536 ms                                                                                |

It takes miliseconds.

You can improve the performance by using a relatively simple partial index:

 CREATE INDEX quite_simple_idx ON btr_orders
     (user_id, id)
 WHERE NOT temporary ;

This index will take care of:

Filtering out all rows where temporary is true. The index will only be used if you filter by temporary = false as one of the factors in your WHERE conditions.
Letting the database quickly seek for user_id = xxxxx.
Once this user_id is found, results will retrievable sorted by id.

This changes the execution plan to this one:

 | QUERY PLAN                                                                                |
 | :---------------------------------------------------------------------------------------------------------------------------------------------- |
 | Limit  (cost=0.29..19.15 rows=1 width=65) (actual time=9.681..9.681 rows=1 loops=1)                                                             |
 |   ->  Index Scan Backward using quite_simple_idx on btr_orders  (cost=0.29..1546.49 rows=82 width=65) (actual time=9.679..9.679 rows=1 loops=1) |
 |         Index Cond: (user_id = 40000000)                                                                                |
 |         Filter: (((active_since IS NULL) OR (active_since <= now())) AND ((premium_until > now()) OR (intermediate_premium_until > now())))     |
 |         Rows Removed by Filter: 23963                                                                                |
 | Planning time: 0.179 ms                                                                                |
 | Execution time: 9.700 ms                                                                                |

If (and that's a big if) the simulated data I put in for test is similar enough to your actual scenario, you can improve by a factor of (about) 2. In any case, by just having an index on id, you already move from "seconds" to "miliseconds".

If you might have queries with both temporary = true and temporary = false (or temporary and NOT temporary), you can have two partial indexes, one with WHERE temporary and another with WHERE not temporary. You can also have one single index where you would add also the temporary column. Some experimentation is needed to find which is the best performing combination for every case (it depends a lot on the ratio of false/true and how often you change this value).

You can check all these simulations at dbfiddle here


master to multi-slave replication I am looking at replication of the master database (PostgreSQL) and this is the configuration I have to setup

master -- hot-standby (hs)
|
|
 --- read only slave (r1)
|
|
 --- read only slave (r2)

My requirement is that the either of master or standby should be available all the time and r1-r2 should be available most of the time.

I have setup streaming replication with recovery on hs and without recovery on r1,r2. All these 3 get the WAL logs from master.

My question is, when the master goes down and we trigger the failover on standby, what should I do to r1 and r2 ? re-parenting them to standby would mean a full base-backup and when eventually master comes up, i may need to re-parent again.

This is my first attempt at replication-setup. So if there is anything obvious I am missing or doing wrong, please let me know. If there are tools/solutions which cater to this sort of a thing, I would be very glad to know and learn.

There are some tricks to make r1/r2 follow new parent, but generally I see them as tricks, which means I wouldn't really depend on them in production environment.

You might want to check this blogpost: http://www.pateldenish.com/2010/11/possibility-of-graceful-switchover.html


What happens to a query that is performed on a view? What optimizations and/or changes occur to a query that is performed on a view? (It would be great to also know if each of the major DBs - Oracle, SQL Server, Postgres, MySQL/MariaDB - handle this differently).

For example, let's say that I have the following view:

CREATE VIEW my_view AS
  SELECT
      id,
      val0,
      val1,
      val2
    FROM my_table
    WHERE val0 = 42;

...and I want to perform the following query on that view:

SELECT
    id,
    val1
  FROM my_view
  WHERE val2 = 'Fish fingers and custard';

...does the database optimize the query or are two selects essentially performed? I.E. is it essentially the same as performing potential query 1 or 2 below or neither?

Potential Query 1:

SELECT
    id,
    val1
  FROM (
    SELECT
        id,
        val0,
        val1,
        val2
      FROM my_table
      WHERE val0 = 42
  )
  WHERE val2 = 'Fish fingers and custard';

Potential Query 2:

SELECT
    id,
    val1
  FROM my_table
  WHERE val0 = 42
    AND val2 = 'Fish fingers and custard';

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.


Correcting French Characters in MySQL I Have a huge MySQL table with a long list of charities. Some of the charities have French characters in them which appear incorrectly.

For example I'm getting uppercase 'É' instead of 'é' in the middle of some names.

I'm wondering how I do a simple find change to correct this. I'd tried a few scripts but they don't execute due to errors.

IN SHORT:
In this table find 'É' and replace with 'é'.

Many Thanks.

UPDATE tbl
    SET col = REPLACE(col, 'É', 'é') COLLATE utf8_bin;

I don't know if the COLLATE clause is needed at all.
If col is not CHARACTER SET utf8, then use the appropriate "_bin" collation.
Test before applying:

SELECT col, REPLACE(col, 'É', 'é')
    FROM tbl
    WHERE col LIKE '%É%'
    LIMIT 22;


Can you create a table param WITHOUT a pre-defined TVP in a SP? In the body of an SP I have the following line of code:

DECLARE @assetCode NVARCHAR(50) = ( SELECT DISTINCT [Asset Code] FROM <some table> WHERE hProp = xxx )

Is it possible to do the same with a TABLE variable type? i.e. something along the lines of:

DECLARE @tbl TABLE = (SELECT x,y,z FROM ....)

I have seen something similar to this in a Table-valued function:

RETURNS TABLE
AS
RETURN
(
    SELECT
    *
    FROM
    (   SELECT
        ... etc

No, that's not possible at this time.

Microsoft recently added the Create Table As syntax to Azure SQL Data Warehouse, but it has not made it to Azure SQL, or the boxed product yet (AFAIK, it's not even in vNext at the moment). Even if it does, it's not clear if it would be supported for table variables like in your example.

Further, SELECT INTO is also not available for table variables, as it is for temp tables and permanent tables.

SELECT TOP 1000 *
INTO @dingdong --Doesn't work
FROM dbo.Users AS u

SELECT TOP 1000 *
INTO #dingdong --Works
FROM dbo.Users AS u

Edit to answer the edit to your question: The syntax you're talking about is for an inline table valued function, which looks like this (from the docs).

  -- Transact-SQL Inline Table-Valued Function Syntax
  CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name    ( [ {
  @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
      [ = default ] [ READONLY ] }
      [ ,...n ]     ]   )   RETURNS TABLE
      [ WITH  [ ,...n ] ]
      [ AS ]
      RETURN [ ( ] select_stmt [ ) ]   [ ; ]


How long has it been since last card top-up? We have a table for our stored-value cards:

CardNo   TransactionType   Amount    TransactionDate(datetime2)
         (1 for Top-up, 0 for purchasing products)
=========================================================
0001        1              1000.00    20170809(only date is displayed for brevity)
0001        0              200.00     20170810
0001        0              300.00     20170811
0001        1              200.00     20170811
0001        0              200.00     20170812

We would like to know for each card (customer), the time between each purchase and the most recent top-up at that time. So the intended result is:

RowID   GapDays
================
1       Null (or 0)
2       1
3       2
4       Null
5       1

  Why is there a gap of 2 days for a row id of 3?

The purchase happens before the top-up.

The date column is of type datetime2, has a "time" portion (omitted here for brevity), and the data is ordered by that. You may utilize any index. Non-existent index will be added if necessary.

I've added time part to the TransactionDate just to get the correct order for the transactions:

create table cards(CardNo varchar(10), TransactionType int, Amount money, TransactionDate datetime2);
create index ix_cards on cards(CardNo, TransactionType, TransactionDate);
insert into cards values
('0001',        1,              1000.00,    '20170809 00:00:00'),
('0001',        0,              200.00,     '20170810 00:00:00'),
('0001',        0,              300.00,     '20170811 00:00:00'),
('0001',        1,              200.00,     '20170811 00:00:01'),
('0001',        0,              200.00,     '20170812 00:00:00');

GO

5 rows affected

Then you can get it by using an APPLY operator with all records where TransactionType = 1. Note you must limit to one record the rows returned by the subquery.

set statistics xml on;

SELECT t1.CardNo, t1.TransactionType, t1.Amount, t1.TransactionDate, t3.TransactionDate,
       DATEDIFF(DAY, t3.TransactionDate, t1.TransactionDate) GapDays
FROM   cards t1
OUTER APPLY (SELECT TOP 1 t2.TransactionDate
             FROM   cards t2
             WHERE  t2.CardNo = t1.CardNo
             AND    t2.TransactionType = 1
             AND    t2.TransactionDate <= t1.TransactionDate
             ORDER BY t2.CardNo, t2.TransactionDate DESC) t3
ORDER BY t1.CardNo, t1.TransactionType, t1.TransactionDate
set statistics xml off;
GO

CardNo | TransactionType | Amount    | TransactionDate     | TransactionDate     | GapDays
:----- | --------------: | :-------- | :------------------ | :------------------ | ------:
0001   |               0 | 200.0000  | 10/08/2017 00:00:00 | 09/08/2017 00:00:00 |       1
0001   |               0 | 300.0000  | 11/08/2017 00:00:00 | 09/08/2017 00:00:00 |       2
0001   |               0 | 200.0000  | 12/08/2017 00:00:00 | 11/08/2017 00:00:01 |       1
0001   |               1 | 1000.0000 | 09/08/2017 00:00:00 | 09/08/2017 00:00:00 |       0
0001   |               1 | 200.0000  | 11/08/2017 00:00:01 | 11/08/2017 00:00:01 |       0

dbfiddle here