Error with sys.dm_db_index_physical_stats I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error.

    SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent
      FROM SYS.TABLES A
      INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.object_id
      CROSS APPLY SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),
        A.OBJECT_ID,B.INDEX_ID,0,DEFAULT) C
    WHERE B.NAME IS NOT NULL

And Error is

  Msg 413, Level 16, State 1, Line 1
  Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".
  Msg 413, Level 16, State 1, Line 1
  Correlated parameters or sub-queries are not supported by the inline function "SYS.DM_DB_INDEX_PHYSICAL_STATS".

If you are in 80 compatibility mode, you need:

DECLARE @dbid int = DB_ID();

SELECT t.name, i.name, ps.avg_fragmentation_in_percent
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (@dbid,NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]
AND i.name IS NOT NULL;

For 100, you need:

SELECT t.name, i.name, ps.avg_fragmentation_in_percent
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(),NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]
AND i.name IS NOT NULL;

The function posted in another answer looks nice and convenient, gets the right answer, and it's a clear winner when you measure just estimated cost, but be careful - there are lots of hidden costs in multi-statement TVFs when you actually go ahead and use them (and in this case you can't use an inline TVF for the same reason as the OP is getting an error message with the built-in function). Here is the cost comparison:

(And just to show the cost of individual lookups as well as no filters):

As an aside, please be careful about case (don't upper-case system objects or column names), use sensible aliases, and get in the habit of using statement terminators.


GROUP BY a number of DAY in SQL Server I have this query

    SELECT  dbo.f_DateWithoutHour (0,DATEADD (DAY, 3, TheDate)) AS Date
    FROM (
        SELECT TheDate
        FROM xxxxxxx.dbo.dim_date (NOLOCK)
        WHERE  TheDate >= '2019-02-09 00:00:00' AND TheDate < '2019-04-07 00:00:00'
    ) AS dim_date
    GROUP BY dbo.f_DateWithoutHour (0,DATEADD (DAY,  3, dim_date.TheDate))
    ORDER BY Date

which return this

But the thing is that i want to GROUP BY every 3 days so i get in return

       DATE
2019-02-12 00:00:00.000
2019-02-15 00:00:00.000
2019-02-18 00:00:00.000
2019-02-21 00:00:00.000
2019-02-24 00:00:00.000
          .
          .

Thanks

Add to your WHERE clause: AND DATEDIFF(DAY,'2019-02-09',TheDate) % 3 = 0

This will evaluate the number of days between your initial date and the current value of TheDate. The modulus (%) operator will return 0 for every 3 days in the timeline. Note that performance may take a hit evaluating this expression for every row, depending on the number of members in the dimension.


Does the relationship between teams and matches have a many-to-many cardinality ratio? Diagram
I created the following diagram:

Considerations
So I will create two tables, one called Match and the other one called Teams. In Match there will be data about two teams, Away and Home.
Let us take into account that each team can have take part in many matches, and a match can have exactly two teams.
Questions

Is this considered a many-to-many (M:N) relationship?
Is there anything I can do to change it?

Is this your full design or just a trivial example?

Suppose you want to store information about a team in a match

who was captain of the team during the match
any kind of stats of the team during the match
and so ..

If this would be the case, I would opt for a many-to-many design.


Shrinking large DB file not working I have a 5 TB database file (don't ask why) that I am cleaning up. I was already able to free over a TB and I will probably end up at 2.5 TB.

All best-practice advice aside, I need to now shrink this file. I am currently running this statement:

DBCC SHRINKFILE(1,4000000)

I can see that it is doing something, as it is sometimes waiting for a pageiolatch_sh and sometimes for a pageiolatch_ex. However, after 24 hours of execution time, the database has not shrunk by a single page. Usually when executing a file shrink like the above, you can see an impact on the file size immediately. Before, I tried to shrink the database by just a single GB and that also did not cause the file size to change at all. I stopped it after 24 hours.

This problem has been lingering for a while but we are at a point were we need to make progress, as the drive space is urgently required for other databases.

BTW, DBCC CHECKDB does not find any problems with that database, so this behaviour can't be caused by some circular page linkage.

A colleague of mine had a similar-ish problem with long-running shrinks.  He took some time to investigate it and found that he had lots of indexes at the 'end' of the file.

A little index maintenance, and then the shrink operation ran fine.  You can read his blog about it here: http://sqltrees.wordpress.com/2013/08/11/shrinking-databases/


Why does my SSIS package produce different results when executed from a maintence plan I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file or overwrites an existing file in another location. The package works as expected from visual studio and when executed manually (double click in explorer). When run from a maintenance plan or scheduled through the SQL server agent job menu the file is always blank. The process will overwrite existing files and create files when no file exists. I believe this indicates it is not a permissions issue.

Running MSSQL Server 2008r2
SQL Server Agent is running as NT AUTHORITY\System with privileges provided to the computer$ on the network share.

Any ideas what is going on and why?

It's not that it's executed from a maintenance plan. Rather, it's that the user executing the package is different. NT AUTHORITY\SYSTEM is god on the machine. However, outside the machine, the account is meaningless.

In your case, it attempts to talk to a foreign machine and that request is denied as the share is unlikely to allow Everyone read/write access to it.

I know of at least 3 ways to resolve the problem. The first and worst is to grant Everyone full control of the network share. That would probably allow the local account to do what it needed to on the remote machine but I'm embarrassed to even list that as an option.

The real two options are to change the execution account for SQL Agent to a domain account with sufficient privileges in both the SQL Server Instance and has network access to the share.

A more precise and less risky fix would be to create a create a Credential and then under the SQL Server Agent, create a Proxy that uses the Credentials for job steps of type SSIS Package Execution and then update the job to use the Credentials it is now empowered to access.

As @Shawn Melton pointed out in the comments, a fourth option would be to grant the computer account permissions to the other network resource https://serverfault.com/questions/135867/how-to-grant-network-access-to-localsystem-account


How to aggregate the number of indexes on a table and columns per index I have a simple query that returns every table, index and the columns of the index in a database. I'd like to add two aggregate columns that return the number of indexes on a table and then the amount of columns on each index.

I'm looking to build a report of every database on a server, each table in there and each index and column to look for tables with too many indexes and large indexes with more than 2-3 key columns.

 SELECT
 @@SERVERNAME as server_name
,DB_NAME() as database_name
 ,TableName = t.name
 ,IndexName = ind.name
 ,ColumnName = col.name
 ,ic.key_ordinal
 ,ind.is_disabled
 ,IndexId = ind.index_id
 ,ColumnId = ic.index_column_id
 FROM
 sys.indexes ind
 INNER JOIN sys.index_columns ic ON  ind.object_id = ic.object_id and
 ind.index_id = ic.index_id
  INNER JOIN sys.columns col ON ic.object_id = col.object_id and
  ic.column_id = col.column_id
  INNER JOIN sys.tables t ON ind.object_id = t.object_id
  WHERE t.is_ms_shipped = 0
  ORDER BY database_name, t.name, ind.name, ind.index_id

If you want a stable, reliable, and free script, try sp_BlitzIndex. As a disclaimer, it's part of an open source project I contribute to.

If you run it like so, it will go through all the databases on your server and produce the information you asked for, plus a lot of other nice details.

EXEC sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2;


CHECKPOINT stealing connection in SINGLE_USER mode I have a console application that was written to automatically run several long running scripts.  Before running the scripts, it will take a database backup by using the BACKUP DATABASE command.  It will then place the database in single user mode:

ALTER DATABASE databaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The application will then begin running the sql scripts.  This part all works great.  The problem that I am having is if a script fails, we are doing a restore from the backup taken before beginning the scripts.  The reason for this is because a transaction rollback will take far longer than simply doing a restore.

I've tried a few different ways:

-works but periodically gets its connections stolen

USE master RESTORE DATABASE databaseName FROM DISK=N'fileLocation'

-doesn't work, database already in use

EXEC master..sp_executesql N'RESTORE DATABASE databaseName FROM DISK=N''fileLocation'''

I've also tried using the .ChangeDatabase() method of the SqlConnection object (VB.NET).  This behaves much the same as the first script above in that it periodically will get it's connection stolen.

In code I am taking pains to ensure I never drop/close my connection and watching the Activity Monitor, it appears that the CHECKPOINT process is what is stealing my connection.

The errors I'm seeing are these:

Database 'databaseName' is already open and can only have one user at a time.

ALTER DATABASE failed because a lock could not be placed on database 'databaseName'. Try again later.

Cannot open database "databaseName" requested by the login. The login failed.

Is there a way to keep CHECKPOINT from stealing my connection?

Your script is failing on the ALTER DATABASE not on the RESTORE. Try setting it to RESTRICTED_USER first:

EXEC('
USE master;
ALTER DATABASE '+@db_name+' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
USE '+@db_name+';
ALTER DATABASE '+@db_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE master;
DROP DATABASE '+@db_name+';
');

This script so far has never failed me.

There are two differences to your script. The main difference is that setting the database to RESTRICTED_USER first allows the script to get executed even if the database is in SINGLE_USER to start with.

The second is that it drops the database instead of restoring it. You could try to replace the DROP with your restore directly, but that might open a new "gap" for something else to sneak in. As you are restoring from a file and not from the database-backup-history, dropping the database first should not be a problem for you. Just execute the restore after the database is gone.


Delete Query takes a long time I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. The delete operation runs for 2 days now and I got an error "Lost connetcion to mysql server during query" a few hours ago. The mysql processlist command shows that delete operation with status "Updating", so it seems like the query is still running though the command line has shown me this error. How can I trace that down? I basically don't know if it is still deleting or got stuck. I am afraid killing the process because I do not want to trigger a rollback.

If it's showing in your processlist then it's still running.

In the future, for situations like this we're you're pruning 90%+ of the table consider this approach:

create table new_table like old_table;
insert into new_table (select * from old_table where <what you want to keep>)
rename table old_table to old_table_drop_prep, new_table to old_table;
drop table old_table_drop_prep;


Is an X lock on an "application" a bad thing? I am digging into sys.dm_tran_locks and have found a single, persistent X lock of resource_type = APPLICATION that looks odd.  The database which on which the lock exists is rarely used.  The resource_description tells me it's db_owner and the naming convention looks to be related to our replication processes.

I would like to know if this is normal behavior and nothing to worry about or if I need to dig deeper to remove the lock.

(We are experiencing a variety of intermittent performance issues on this server and have yet to determine why - I have no reason to think this is related, but I did want to mention it.)

EDIT - I would also like to know what a lock on APPLICATION is - the MSDN descriptions are vague to me...

These locks are created by a call to sp_getapplock.

It will only cause blocking for other processes that also call sp_getapplock and request the same lock resource.

You could poll sys.dm_os_waiting_tasks to see if anything is ever blocked waiting for this but any such blocking would probably be desirable anyway as presumably the whole point of taking the lock is precisely to ensure this mutual exclusion.


How to set CHECK_POLICY OFF in the sql script While installing our software in a HP laptop, we got a SQL error stating "The password does not meet windows policy requirements because it is too short."

When I checked, the local security policy in my machine has minimum 8 characters and in that laptop it has 12 characters. Our SQL password has 11 characters. That is why it is not installed in that laptop alone.

We can increase the password more than 12. But maybe in future the minimum password requirement can even change to 20 characters. So we thought of disabling the password check. Since I am new to this SQL, I don't know where to add the condition check. I have two SQL scripts. The code snippet for the 2 scripts is shown below. Please let me know where to add it.

Script1:

ALTER LOGIN [sa] WITH PASSWORD=N'MSSql2008!' GO

IF EXISTS (SELECT * FROM syslogins WHERE name = 'teradyne')
    BEGIN ALTER LOGIN [teradyne] WITH PASSWORD=N'SQL_PWD' END

GO

Script2:

GO

EXEC ('IF NOT EXISTS (SELECT * FROM syslogins WHERE name = ''clientsoftware'')
           EXEC sp_addlogin @loginame=''clientsoftware'', @passwd=''TER_SQL_PWD''
     ') GO

CHECKPOINT

GO

Where SQL_PWD = software1! for both scripts.

Please let me know where to add that CHECK_POLICY and also whether I need to add that CHECK_EXPIRATION

You have to do

Alter login login_name with CHECK_POLICY = { ON | OFF }
Alter login login_name with  CHECK_EXPIRATION = { ON | OFF }

Refer: http://technet.microsoft.com/en-us/library/ms189828.aspx


Do you need Table Lock when you have multiple OLE DB connections inserting to the same table? SSIS is not really my forte. I noticed you have a table lock option on OLE DB destinations where the entire table becomes locked in what I assume is during the transaction of inserting data.

What happens if you have a flat file data source with a conditional split that is parsing out data into 5 or more OLE DB destinations that are targeting the same table with table lock on? Would each OLE DB destination get blocked by one another in this scenario if data is being fast loaded (inserted) into the destinations themselves?

I removed the table lock in my instance and everything seemed fine. It was splitting the data and inserting records at about 1 million records per minute.

The table lock option facilitates minimal logging, reducing transaction log space requirements under certain circumstances.  Below are relevant excerpts from the Prerequisites for Minimal Logging topic in the SQL Server Books Online.

  The table is not being replicated.
  Table locking is specified (using TABLOCK).
  Table is not a memory-optimized table.
  If the table has no indexes, data pages are minimally logged.
  If the table has no clustered index but has one or more nonclustered    indexes, data pages are always minimally logged. How
  index pages are    logged, however, depends on whether the table is
  empty:

  If the table is empty, index pages are minimally logged.
  If table is non-empty, index pages are fully logged.

  If you start with an empty table and bulk import the data in multiple    batches, both index and data pages are minimally logged
  for the first    batch, but beginning with the second batch, only data
  pages are    minimally logged.
  If the table has a clustered index and is empty, both data and index    pages are minimally logged. In contrast, if a table has a
  clustered    index and is non-empty, data pages and index pages are
  both fully    logged regardless of the recovery model.

The table lock will block other processes using the table, including the other OLEDB Destinations within the SSIS package. You could set the Maximum Insert Commit Size to improve concurrency but with the consideration that file will be partially imported in the event of a failure.


Shrinking T-Log file every weekend basis Recently our SQL Server Log drive which has all T-Log files starting growing rapidly.

Considerations

We expanded drive space to 100 GB.
It is AOAG environment, SQL Server 2014.
We have regular backup of T-Log every 15 minutes (but no log drive space is freed up and log drive is increasing more and more each week 50 GB at a lot).
We cannot afford to expand drive each time.
Found the reason to be Rebuild-Index task.
I have deployed Ola Hallengrens code for Index optimization for this.
Re-build task occupies much space and occupies the drive in close to 15 minutes is 50 GB.

I have been thinking of doing this immediately after the index optimization job completes (Ola Hallengrens code for Index Optimization). I am planning to shrink all the log files on a weekly basis.

Every Sunday @10:00 PM:

Index Optimization of all AVAILABILITY_GROUP_DATABASES
Shrink all AVAILABILITY_GROUP_DATABASES log files.

Please correct my thinking before I get in a trouble. Because even transaction log backup does not help us anyway as the space is keep on increasing.

What should be the course of action in this case apart from shrinking log file (as it not recommended).

Please any expert advice is highly appreciated.

Thanks for your help.

I would recommend properly sizing the log file and not shrink it on a regular basis.

Because you are rebuilding the index you will grow your logfile as the index will be written to the transactionlog file. This means that your largest index should fit inside the transaction log file.

If your database is in full recovery mode, you can try to take logbackups more frequently to prevent it from growing out of hand.(Multiple index rebuilds stored in the transactionlog file)

If your database is in simple mode, this will not be the case.

When your transaction log file grows you will create something called Virtual Log Files (a structure inside the transaction log file) this can cause several problems. (more information: https://blogs.msdn.microsoft.com/saponsqlserver/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery/)

I would first shrink your file, and then grow it to a proper size to avoid the creation of too many VLF's. But do not shrink them on a weekly basis.


How to remove duplicates and keep the one with the most additional information I have a postgres table (postgres version 9.3.4) of addresses with duplicate addresses:

          Column           |            Type             |                        Modifiers
---------------------------+-----------------------------+---------------------------------------------------------
id                        | integer                     | not null default nextval('properties_id_seq'::regclass)
street_number             | character varying(255)      |
street_name               | character varying(255)      |
street_direction          | character varying(255)      |
unit_number               | character varying(255)      |
city_id                   | integer                     |
postal_code               | character varying(255)      |
year_built                | integer                     |
floor_area                | integer                     |
bedroom                   | integer                     |
bathroom                  | numeric                     |
lot_size                  | integer                     |
den                       | integer                     |

The duplicates are based on the addresses containing of street_number, street_name, street_direction, unit_number and city_id. The table has additional information such as # of beds and # of baths etc. This additional information is not populated for all rows of the table, which makes removing the duplicates tricky as I do not want to lose any information. I would like to mark the duplicates with the least amount of information for deletion (add a field with 1 for delete and 0 for keep). I can see 2 ways of doing this:

Duplicate the information of the entry with the complete information to the other entries and randomly mark all but one entry for deletion
Identify the row with the most information and mark the other rows for deletion.

Ideally, I would like to just identify the rows with the most information and then delete the other rows. Is there anybody who could help me with a query for this?

Your question is a bit light on definitions. Assuming:

You define the "least amount of information" with how many of the relevant columns are NULL.
Primary key is adr_id.
Duplicates are marked with a column dupe_id to indicate groups of duplicates.

Since it's also vague what to do exactly, I create a separate table with the dupe ranking:

CRATE TABLE adr_dupe_rank AS
SELECT adr_id, dupe_id
     , rank() OVER (PARTITION BY dupe_id
                    ORDER BY (nr_of_beds  IS NULL)::int
                           + (nr_of_baths IS NULL)::int
                        -- + (...)::int  -- more terms
                   ) AS rnk
FROM   address;

false translate to 0, true translates to 1. So rows with the fewest empty columns are ranked first. Master rows end up with rnk = 1. Dupes get higher rnk numbers.

The window function rank() assigns 1 to all rows sharing the lowest score per dupe_id. Add enough columns or other terms to break ties and get one winner per dupe_id. Or deal with multiple winners separately.

You can then do as you please. To just delete dupes:

DELETE FROM address a
USING  adr_dupe_rank d
WHERE  a.adr_id = d.adr_id
AND    d.rnk > 1;

Alternatively you can use the above query as derived table in the DELETE directly.


What is this type of query called, and how can I formulate it? I have following tables:

  events(event_id, name, description)

  groups(group_id, event_id, title, description)

  event_group(event_id, group_id)

  users(user_id, name, email)

  event_user(event_id, user_id)

  group_user(group_id, user_id)

As an example, how can I return the users who belong to a certain group which belongs to a certain event?

I'm not sure if there's a name for this, but you really just want to traverse your relationships with JOINs:

SELECT DISTINCT
    U.*
FROM
    Users U
INNER JOIN
    Group_user GU
        ON GU.user_id=u.User_id
INNER JOIN
    Event_Group eg
        ON eg.group_id = gu.group_id
WHERE eg.event_id = 1234

I added a DISTINCT in case a user was in more  than one group that was in the event.

You can also add a JOIN to the events table if you need another filter (like events taking place on a certain date or whatever).

This is SQL Server syntax but I think it should be basically the same for MySQL, JOIN syntax may vary.


How to balance SQL Server memory with SQL Server Analysis Services memory We have many systems that have the same configuration

One Server (virtual or physical)
Running SQL Server (SQL) and SQL Server Analysis Server (AS)
multiple cores
16gb RAM

Each night the SQL Server will do about 2-3 hours processing, followed by 2-3 hours of AS processing. Then throughout the day only the AS are queried.

Assuming this is a dedicated server, and no other apps are of concern, and that  the two sets of processing are completely synchronous - no overlaps always one after the other - how can I best set the SQL and AS server memory limits.

The reason for asking is that if I don't set a limit for SQL it will grab all the memory it can. However - my understanding is that SQL will happily relinquish this memory if:

It's not using it and
Another service/program requests it.

So from a logical perspective I believe allow SQL to take as much as it needs, but I'm not so sure about AS' TotalMemoryLimit. I'm not sure if AS will relinquish it's memory. In fact reading more leads me to believe that it is wrong to let it take it all.

Does this mean that I need actually set limits for both? I'm confused as to what the best practises should be, and what we need to be measuring considering the processes don't overlap.

Hope this makes sense.

I think it is feasible to force both services' hands by having a scheduled task or service that:

(a) before SQL Server's nightly processing, shuts down the SSAS service, and increases the memory allotted to SQL Server

(b) after SQL Server has done its processing, reduces max server memory and restarts SQL Server

(c) starts SSAS

This assumes that SSAS isn't required to be online while SQL Server is processing. Otherwise change (a) from "shuts down" to "restarts"... as long as SSAS isn't actively doing anything while SQL Server is processing, there shouldn't be any concern that it will steal memory back.

This also assumes that you want each service to overlap memory consumption, and use the same memory at different times - I'm sure there are ways with affinity / NUMA etc. to completely segregate their operations space or, in the worst case, to host them on different machines.


Oracle RAC 18c Patching - datapatch After patching GI Home and DB Home simultaneously using command:
.oraenv
+ASM1
opatchauto apply /patchlocation

Should i run ./datapatch from DB home or GI home? Or it doesn't matter from which home? Thanks

For the Grid Infrastructure Management Repository (GIMR) a.k.a. MGMTDB (if you have it), datapatch is automatically executed as part of patching.
For regular databases, execute datapatch from the database home.


Incorrect results using WITH RECOMPILE option If there are multiple connections concurrently executing a query with different parameters in a filter condition and the WITH RECOMPILE option is used, is there any chance that the query may produce incorrect results?

This ought not happen, but there have been several bugs that mean it might, depending on your version of SQL Server, and which patches you have applied.

The relevant Microsoft KB articles are listed below:

FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 or SQL Server 2014

FIX: A query that uses parameters and the RECOMPILE option returns incorrect results when you run the query in multiple connections concurrently in SQL Server 2008

FIX: Different results may be returned when you concurrently run the same query with the RECOMPILE option in SQL Server 2008


Xtrabackup corrupt incremental backup I have 3 nodes PXC 5.6 cluster. Xtrabackup 2.3.10. Centos 7.6

Percona-XtraDB-Cluster-56-5.6.41-28.28.1.el7.x86_64

I am using only single Node-1 for both read and write.

And I use to take backup from another Node-2 which is not used for read or write.

Yesterday, my Node-1 went down and read/write switched to Node-2. Then we continued to take incremental backup from another unused Node-3.

On my backup machine, I decided to extract and prepare backup. My backup preparation log shows everything fine until those backups that were taken from Node-2. All the incremental backups from Node-3 had errors.

Why does it seems like incremental backup should always be from the same node where the full backup was taken? Percona XtraDB Cluster is master-master and so all nodes must be same. Then why did this happen?

I have attached my logs here:

https://www.percona.com/forums/questions-discussions/percona-xtradb-cluster/53285-xtrabackup-corrupts-incremental-backup-when-node-changes

LSN (Log Sequence Number), which incremental backup is based on, is specific to an instance. That's why knowing full copy LSN means nothing on other node.


Moving data from one database to another database with auto incriment keys I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id).

The questions is, I have 2 databases(db_a and db_b) that have the same schema (tables 1,2,3 with same relations). Now how can I insert data from db_b into db_a in a way that I can keep track of the correct relations between table3 and the table1 and table2.

EDIT

This is not a one time thing, it will reoccur aperiodically.

New data is inserted into db_a on daily basis, however data inserts on db_b can happen with different regularity. The issue is how to keep track of the auto generated keys for table1 and table2 when inserting table3 data into db_a

Just follow this... steps

These are the tables for database1

    create table if not exists table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

There are the tables for  database2

    create table if not exists 2table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists 2table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists 2table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

Here we have temp data for database1

    insert into database1.table1 (data1)
        values('table_1_data_1'),
        ('table_1_data_2'),
        ('table_1_data_3');

    insert into database1.table2 (data2)
        values('table_2_data_1'),
        ('table_2_data_2'),
        ('table_2_data_3');

    insert into database1.table3 (data3,t1_id,t2_id)
        values('table_3_data_1',1,1),
        ('table_3_data_2',1,2),
        ('table_3_data_3',2,1),
        ('table_3_data_4',2,2),
        ('table_3_data_5',2,3);

temp data for database2

    insert into database2.2table1 (data1)
        values('2table_1_data_1'),
        ('2table_1_data_2'),
        ('2table_1_data_3');

    insert into database2.2table2 (data2)
        values('2table_2_data_1'),
        ('2table_2_data_2'),
        ('2table_2_data_3');

Now come to the point

Here we are copying data
from database1s table1 to database2s 2table1

and

from database1s table2 to database2s 2table2

    insert into database2.2table1 (data1)
        select data1 from database1.table1;
    insert into database2.2table2 (data2)
        select data2 from database1.table2;

Now we are going to insert data into database2s 2table3

    insert into database2.2table3 (t1_id,t2_id,data3) select
      (select 2t1.id from database2.2table1 2t1 where 2t1.data1 =t1.data1 ),
      (select 2t2.id from database2.2table2 2t2 where 2t2.data2 =t2.data2 ),
      t3.data3
    from database1.table3 t3
    left join database1.table1 as t1 on t1.id = t3.t1_id
    left join database1.table2 as t2 on t2.id = t3.t2_id;

I hope it helps...


Insert into select into multiple related tables using INSERT_IDENTITY Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source.
So for every row in DataTable I want a row in Table1 and Table2, and Table2 needs to have the inserted id (PK) from Table1...

If I were to do this...

INSERT INTO Table1 SELECT A, B, C FROM MyTable
INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable

I'd get the ID of the last inserted record into Table1.

Is a CURSOR or WHILE loop the only ways to do this?

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves.

Anyway, the solution would look something like this:

MERGE INTO Table1 AS t1
USING MyTable ON 1=0 -- always generates "not matched by target"

WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into Table1:
    INSERT (A, B, C) VALUES (t1.A, t1.B, t1.C)

--- .. and INSERT into Table2:
OUTPUT inserted.ID, MyTable.D, MyTable.E, MyTable.F
INTO Table2 (ID, D, E, F);

MERGE, as opposed to the other DML statements, can reference other tables than just inserted and deleted, which is useful for you here.

More: http://sqlsunday.com/2013/08/04/cool-merge-features/


How to use tsvector_update_trigger with jsonb column? I need to create cached ts_vector column from jsonb value. I try to create a trigger
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON tags FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(
    tsv, 'pg_catalog.english', "translations#>>'{en,name}'"
  );

But in a result I get an error
ERROR:  column "translations#>>'{en,name}'" does not exist

You don't.  You will need to write your own trigger function.  The built in one only works for text columns, not for jsonb columns and not for functions over columns.
On newer versions, an alternative would be use a generated column.
create table tags (
    translations jsonb,
    tsv tsvector generated always as (to_tsvector('pg_catalog.english',translations#>>'{en,name}')) stored
);


SQL log file growing exponentially We have a SQL database on FULL back up with a client  and they have NOT implemented a Log file maintenance and noticed that the log file grows exponentially, filling the 20GB they have allocated to it in a matter of days.

Once they switch into 'maintained log file mode' this problem goes away.

As we are not SQL experts, I am writing to you as to whether exponential growth is normal if you do not maintain the log file.

Thanking you in advance,

Steve

To answer your question, in FULL recovery mode, growth of the log file is absolutely normal, and of course it will grow to either the limited size or the size of the disk unless it is maintained. The rate of growth will be determined by the type of / frequency of / load of transactions hitting your database. The rate of growth, will also determined by your auto-growth settings, i.e. grow by fixed value or percentage etc.

Logs are typically maintained by performing a log backup at suitable intervals. This truncates the data within the log file itself allowing the space to be re-used for new transactions.

What is important to note is that the problems you might be facing due to a large log file (i.e. where the size has gotten out of hand due to lack of suitable maintenance) is not necessarily remedied by a transaction log backup alone. You may need to shrink the file to free up disk space, for example.


How to show all system privileges for all users in Oracle database I need a query how to list all system privileges only (not the object ones) which are granted to each user in database. I need listed system privileges whatever they granted thru role or direct.
Thank you

"Retrieving all user privileges within Oracle can range from a simple task using a basic SQL query to an advanced script, depending primarily on how involved the roles and privileges are configured within the server."
https://chartio.com/resources/tutorials/oracle-user-privileges--how-to-show-all-privileges-for-a-user/


2 Different database or 1 Single database I have a Windows 7 machine running MySQL database and I have a Linux machine running PostgreSQL database.

Preferably I would like to have only 1 machine housing both database as currently they are co-located in a datacenter which counts the electricty power and space used.

Would it be favorable to run 2 different database (MySQL and PostgreSQL) or convert the MySQL to a PostgreSQL?

Following up on @JonathanFite's comment, current and future resource usage is critical to answer this question, specifically memory capacity and I/O write performance.

Rule of thumb: if both databases can fit in RAM with room to spare, run both engines. If memory is limited, consider migrating the smaller database of the two, to the engine running the larger one.

Because you're most concerned about hosting costs, it will probably work out cheaper to consolidate, but you may have to increase your memory down the line.


SQL Server 2012 Transparent Data Encryption and indexes Does SQL Server TDE allow a query like this:

     select * from Patient where lastname = 'Smith'

to occur without a full-table scan if the lastname column is indexed?

Yes. Transparent Data Encryption doesn't change any of the internal processing of queries.  Database pages are transparently encrypted/decrypted during I/O (i.e. when they are read from or written to disk). In-memory query processing is therefore unaffected, so indexes continue to function exactly as they did without TDE.

TDE is designed to negate the need for application developers to make any changes to their applications. http://msdn.microsoft.com/en-us/library/bb934049.aspx contains a salient piece of information:

  This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

In short, queries operate no differently with TDE enabled or disabled, aside from the performance impact of the encryption and decryption activities.


SQL Server Express 2008: Can I store database files (.mdf, .ldf) in directory other than the default DATA directory? I have a SQL Server Express 2008 instance of which I am attaching databases from development systems quite often. Typically I transfer the .MDF and .LDF files to the instance's DATA folder and reattach, and everything is fine. In the past when we used Access instead of SQL Server Express, I would keep the database file on a separate drive (SSD) which tended to increase performance, as well as let me use BitLocker drive encryption on the whole SSD for easy encryption and whole drive backups. I would like to do the same with SQL Server Express.

Can I put the .mdf and .ldf files on a separate drive (not in the instances DATA folder) without any ill effects? Do I need to change settings through the Management Studio to do this? Can I still use BitLocker Encryption?

Thanks very much!

You can place your database files anywhere you want, you just have to specify these locations within SQL Server itself.  There are two ways to relocate those files.

Create in a new location

When you create a database or add a file to the database, you can specify the new location and the file will be created there.  For example, if I wanted to add a new data file to my database, I would do the following

ALTER DATABASE [foo]
ADD FILE (name=foo_data_02, filename='X:\YourCustomLocation\foo_data_02.ndf');

As long as your SQL Server Service Account has access to the path, whatever you put after 'filename' will be where your file is created.

Move to a new location

You can also move an existing data file to a new location.  This requires a couple steps.  The first is to change the path within SQL Server first, like so:

ALTER DATABASE [foo]
MODIFY FILE (name=foo_data_01, filename'X:\YourCustomLocation\foo_data_01.mdf`)

By modifying this, the next time this database starts up it will look for the file in that location.  Once you've changed it, you will actually need to move the file.  To do this, take the database offline:

ALTER DATABASE [foo]
SET OFFLINE WITH ROLLBACK IMMEDIATE;

Once the database is offline, then copy the file to the new location using your preferred method.  Once it has been copied, bring your database online:

ALTER DATABASE [foo]
SET ONLINE;

At this point, your database will now be running with your files in the new location.


How are page splits determined internally in MySQL? Going through this answer for a stack-overflow question, I found a fact that

  In The physical structure of InnoDB index pages I describe the Last Insert Position, Page Direction, and Number of Inserts in Page Direction fields of each index page. This is how the tracking for ascending vs. descending order is done (as left vs. right, though). With each insert, the last inserted record is compared to the currently inserted one, and if the insert is in the same "direction", the counter is incremented. This counter is then checked to determine the page split behavior; whether to split in half or create a new, empty page

It is understood that page split up is based on Number of Inserts in Page Direction

But , I wonder whether there will be threshold for "Number of Inserts in Page Direction" to initiate page split up?  Any particular logic behind setting "Page Direction" flag?

I am curious in getting these concepts with a working example with asap details!!! All efforts welcome.

The author is talking about PAGE_N_DIRECTION. This has nothing to do with triggering splitting, which is performed by page_dir_split_slot. Splitting/merging is determined by a MIN and MAX on the directory slot

/* The maximum and minimum number of records owned by a directory slot. The
number may drop below the minimum in the first and the last slot in the
directory. */
#define PAGE_DIR_SLOT_MAX_N_OWNED 8
#define PAGE_DIR_SLOT_MIN_N_OWNED 4

You don't split when you fall under MIN, you merge (via page_dir_balance_slot).

PAGE_N_DIRECTION is for an optimization,

  Try a search shortcut based on the last insert.


Querying AD from SQL Server I've read tons of questions and answers about my error but none of them helped me ( from PINAL website, microsoft links and everything ). questions from 9 years ago to today.
I created a Linked server with proper user/pass to read info from our AD.
but it doesn't matter what user I use ( we tried everything, even with the most powerfull user we have in the network that can make it rain if it wants ) and I still get the same error:
Msg 7399, Level 16, State 1, Line 16
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

i tried to run SSMS with the user with no success. I set the secutiry tab to run with the specific user that can read the AD.
these are the queries i'm trying :
SELECT top 100 * FROM OpenQuery (
ADSI, 'SELECT displayname FROM
''LDAP://myDomain.local/OU=Usuarios'''
)

SELECT TOP 100 * FROM OPENQUERY
(ADSI,'SELECT displayname FROM
''LDAP://myDomain.local/OU=USUARIOS,DC=MyDomain,DC=LOCAL'''
)

SELECT * FROM OpenQuery (
ADSI,
'SELECT *
FROM ''LDAP://myDomain.local/DC=MyDomain,DC=local''
WHERE objectClass = ''User''
')

I opened management studio with this powerfull user and it still doesnt work.  what can be the error here?
I'm querying the AD server remotely from my machine from SSMS.

You might be better off and find it easier to query Active Directory with a CLR stored procedure or CLR function. See this walkthrough on how to do so, as this is how I've done it in the past. Also someone else made one already you can look into (but be careful installing CLR assemblies from outside sources).


How to set column DEFAULT using value of a variable In a script for setting up a database which uses random UUIDs for primary keys, I have:

CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT);
CREATE TABLE beta (aref UUID REFERENCES alpha (id));

INSERT INTO alpha (value) VALUES (42);

Then over a foreign key, to have that record in "alpha" be the default target for insertions into the "beta" table table:

DO $$
    DECLARE l_id UUID;
    BEGIN
        SELECT alpha.id FROM alpha INTO l_id;
        ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id;
    END;
$$ LANGUAGE plpgsql;

ERROR:  column "l_id" does not exist
CONTEXT:  SQL statement "ALTER TABLE beta ALTER COLUMN aref SET DEFAULT l_id"
PL/pgSQL function inline_code_block line 1 at SQL statement

How do I use the value of a variable as the default value for a column?

You seem to assume variable substitution for an SQL utility command like ALTER TABLE, but that's not implemented.

Also not possible to pass values for utility commands to EXECUTE with the USING clause in plpgsql.
Detailed explanation for both in this related answer on SO:

Error when setting n_distinct using a plpgsql variable

Concatenate the statement, then execute it - like @Abelisto hinted. But you don't need a variable, you can concatenate the lookup value directly:

DO $$
BEGIN
EXECUTE format('ALTER TABLE beta ALTER COLUMN aref SET DEFAULT %L'
             , (SELECT a.id::text FROM alpha a));
END $$;

The SELECT must return a single value.
I cast to text explicitly, which is not strictly necessary. Casting the string literal to the target type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

CREATE SEQUENCE using expressions with psql variables for parameters


Vehicle database design - Type, Make, Model, Vehicle I kindly ask you for advice, if I am correct with my DB design. I am writing an app in Django (it has its own ORM), which will use PostgreSQL.
My expectation is, that I will specify:

Type (example: Automobile, Motorcycle, Boat, ...)
Make (example: Honda, BMW, Audi, ...)
Model (example: CBR1000, A6, A8, 535i, ...)
Vehicle - all other info, like VIN, price, ...

So I think that the best way is to have Makes unique. I don't know, what to do with a Type. I think that the best way is to have a foreign key of a Type in a Model - because for example Honda can produce Automobiles and also Motorcycles. So by Model we can found, what vehicle type is it.
I want to make a web form filter (some kind of three linked dropdowns), where the user will select a Type, then it will filter out all Makes for that selected type. Then in another dropdown, he will select a Make and in the third dropdown, there will be shown all Models, which is available in that selection.
Something like a Copart has - Vehicle Finder on the right side of web page https://www.copart.com/vehicleFinder/
I hope that it makes sense, I have created a schema like this, and I am not just sure if it is usable and it is right. Here it is:

link to editor: https://dbdesigner.page.link/gS62T3GYPth2TAAbA
Thank you very much for any advice!

Why does 'type' have to be a separate table, and not just be an enum on 'model'? Then, when a user is doing a search of any type (via a dropdown or other method) it's just a filter on model (... where type = ANY( VALUES ('BOAT'), ('MOTORBIKE')) ...).


Should I represent two distinct relationships via only one associative table? I have a database to store the news of my website. The news entity type is associated with two different ones; images (which from the point of view of the app users are grouped in a gallery) and categories.

To represent this situation this, I have some tables like this:

image
id | src

category
id | name

news
id | title | text | author | ...

It is important to mention that each news may have multiple images and belong to multiple categories. So, to create the relationship between images, categories and news, I'll use another table, but my question is:

Should I have (a) two tables (one for image rows and other for category rows) or (b) just one with multiple columns? For example:

(a) Two distinct tables, one called news_relationship_category, with the column headings

id | id_news | id_category

and another one, named news_relationship_gallery set up as follows

 id | id_news | id_gallery

(b) One single table, entitled news_relationships with the headings

id_news | id_category | id_gallery

and INSERT INTO it rows with some columns that accept NULL marks, for instance:

10 | NULL | 3
---+------+------
10 | 27   | NULL
---+------+------

These are simple examples, but imagine if I have an e-commerce table, with products involved in multiple relationships. What is the best way to approach this case?

To delete a news-category pair in schema B, you have to do either a DELETE or an UPDATE depending on whether the relevant row has id_gallery specified or not. How about inserts, are you going to just INSERT or check first for rows with nulls to UPDATE?

So, I strongly recommend option A. However, I would drop the surrogate id columns and use (id_news, id_category) and (id_news, id_gallery) as PK.


SQL Select query with postgresql I'd like to find all rows if is shows even once a particular value  with postgresql

For example :

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
  3  M.        BOUVIER   Alain       V
  4  M.        DUBOIS    Paul        D
  5  M.        DREYFUS   Jean        C
  6  M.        FAURE     Alain       C
 11  M.        DUPONT    Alain       B
 12  M.        DUVAL     Arsène      B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

And I want to have all rows where CLI_ENSEIGNE = 'A' but not only I want also the rows where CLI_NOM had a CLI_ENSEIGNE = 'A' even only one time:

We see here the Dupond had an A the a B

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
 11  M.        DUPONT    Alain       B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

I tried

SELECT
  *
FROM
  table
WHERE
CLI_ENSEIGNE = 'A'
IN (CLI_NOM)

But it's saying that CLI_NOM doesn't exists

You where clause is incomplete and wouldn't work that way anyway.

You need a subselect for this:

SELECT   *
FROM the_table
WHERE cli_nom in (select cli_nom
                  from the_table
                  where CLI_ENSEIGNE = 'A' );

alternatively a co-related sub-select with an exists clause:

SELECT t1.*
FROM the_table t1
WHERE EXISTS (select 42
              from the_table t2
              where t2.cli_nom = t1.cli_nom
                and CLI_ENSEIGNE = 'A' );


sql query to calculate a difference between cols in two different tables, grouped by common identifiers on the row This is probably an amazingly dumb question, but hopefully someone kind can help out while laughing :D
I'm doing this on postgres 9.5

I have two tables, all data types can int in this example
table1 has:

id     val

1      10

2      20

3      30

table2 has:

id     value

1      15

3      15

5      50

I would very much like a query that calculates the diff between the two tables (table2.val - table1.val) for each id, so that the result is:

id     diff

1      5

2      -20

3      -15

5      50

My difficulty is in figuring out how to do it when some ID's only exist in one table...

Please help :)

So to create my best attempt:

create table table1 (id int, value int);
create table table2 (id int, value int);
insert into table1 values
(1,10),
(2,20),
(3,30);
insert into table2 values
(1,15),
(3,15),
(5,50);

select table1.id, table2.value - table1.value as diff
from table1
inner join table2 on table1.id=table2.id
group by table1.id, diff;

However this leads to :

id      diff
3       -15
1       5

If I try a full outer join I get:

select table1.id, table2.value - table1.value as diff
from table1
full outer join table2 on table1.id=table2.id
group by table1.id, diff;

which gives

id      diff
null    null
2       null
3       -15
1       5

To get results even if only one of the two tables has data, then you need FULL JOIN. The COALESCE() function can be used for the result when there are umatched rows (and you want the produced NULL to become 0 for example).

Assuming that (id) is UNIQUE in each table, I don't see why you think you need GROUP BY at all.

The query:

select
    coalesce(t1.id, t2.id) as id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     on t1.id = t2.id ;

and slightly simpler, with USING:

select
    id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     using (id) ;

Test at dbfiddle.uk.


ASM 12c compatibility Can I use ASM 12c version with database 11g? This link and MOS Doc ID 337737.1 says I cannot use 12c version of ASM with a lower database version. Am I right? Or is it otherwise?

You can use 12c ASM for 11g databases (even 10g), neither the MOS note nor the forum thread says you can not use it.

As far as the table in the MOS is concerned, for 12c, just ignore the first column. Starting with 11.2, the clusterware and ASM come together integrated, called Grid Infrastructure, and not separately as in earlier releases, so they will always be the at the same version. The table clearly shows that 10.2 - 12.1 databases are all certified with 12.1 ASM.


MongoDB how to achieve asynchronous geo-replication I have read a lot of background about MongoDB replication and sharding. None of the default solutions that I know can actually do what I'm trying to achieve.

Current situation

We have a time critical application balanced over three geographically seperated locations (called A, B and C) that generates reports as JSON documents and writes them on a centralized (external) MongoDB server. We have a different application that reads and processes these reports from the centralized MongoDB server. This works pretty well.

  Application (A) ---->  +---------------------+
                         |                     |
  Application (B) ---->  | Centralized MongoDB | --> Processing
                         |                     |
  Application (C) ---->  +---------------------+

The problem with our current setup is that if the centralized MongoDB server is unresponsive for some reason, the reports are lost and the application gets a nice performance penalty when it's waiting for a response. So basically three independant redundant locations are all relying on a single location for report storage. We are not able to fix this problem in the application.

Desired situation

We would like to have a local MongoDB service listening in each geographically seperated location, so that our application is always able to delegate the reports locally. But eventually we still want to have all reports from these three locations merged on the centralized MongoDB server.

                       +---------------+       +-------------+
  Application (A) -->  | Local MongoDB | ----> |             |
                       +---------------+       |             |
                       +---------------+       | Centralized |
  Application (B) -->  | Local MongoDB | ----> |             | --> Processing
                       +---------------+       |   MongoDB   |
                       +---------------+       |             |
  Application (C) -->  | Local MongoDB | ----> |             |
                       +---------------+       +-------------+

So the way I see it, this is kind of reversed sharding.

Some side notes:

This is a one way street. Our application does only inserts. It does not need to read data from storage. On the processing side is only reading required. It does not insert any data.
It would be acceptable if some reports are missing during an outage
It would be acceptable if the reports are out of order in the centralized storage
The replication should be asynchronous, but near real-time. We need to process the reports in a matter of seconds
If the local MongoDB process is also using local storage, that would be fine but we don't require it.
Any open source or commercial solution would be acceptable, also third-party solutions.
It's critical that the local MongoDB process always accepts the report immediately, so the application can do something else right away
The application requires MongoDB, so we need to stick with this.

Any help or thoughts would be highly appreciated.

To do oneway replication from multiple "Local MongoDB" sources to one "Centralized MongoDB" realtime is possible with mongo-connector product. Just start one mongo-connector per every Local MongoDB.

I have used this product to replicate the one sharded collection (from nine shards) to one single mongodb.


How to Find Consecutive count of a column based on a their status I have a table containing various sites and billing information of those sites.
Ex:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    6
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    7

What I need to find:
I need to find consecutive count for each site based on their Reason_Type and billing_date.
For example:If I receive any bill on 2015-10-06 whose Reason tyoe is RIGHT_BILL. So I need to find how many past consecutive cases were there for this scenario.
For example If I take billing_date =2015-03-01 its showing me rank as 2 which is right but again If i go and check for 2015-04-03 its showing 2 which is wrong it should also show as one since the last month billing_date was having Zero_Bill.

Expected output:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    6

I tried:

Select site_key,
       billing_date,
       Reason_type,
       Dense_rank() over (partition by site_key,billing_date,reason_type order by billing_date) as rank
from indus_Dev_analytics.AVG_SCN
where site_key=3
order  by 1,2;

The problem in your query actually comes from the first row.
It has the same Reason_type so it gives the extra ranking to the bottom records.

I think you can use CONDITIONAL_CHANGE_EVENT, maybe like:

select *,
    dense_rank()
        over (partition by site,change_num,reason_type order by billing_date) as rank
from (
   Select site,
     billing_date,
     Reason_type,
     CONDITIONAL_CHANGE_EVENT(reason_type)
         over (partition by site order by billing_date) as change_num
   from AVG_SCN
   where site=3
) foo
order  by 1,2;


Will Temporal Tables be included in the Standard Edition of SQL Server 2016? The new version of SQL Server 2016 wil support so called Temporal Tables.

I can't find any documentation that would say whether this feature would be Enterprise-only or available in Standard edition as well.

On the one hand, Temporal Table Considerations and Limitations suggests it will be Enterprise-only:

  By default, the history table is PAGE compressed.
  An optimal indexing strategy will include a clustered columnstore index [...] The default history table has a clustered rowstore index

As far as I know, data compression is an Enterprise-only feature, but technically Standard edition could create a non-compressed history table.

Columnstore index is an Enterprise-only feature, but by default it will create a rowstore index, so there is still hope.

On the other hand, documentation for sys.dm_db_persisted_sku_features is the same for 2014 and 2016 and the list of Enterprise features doesn't have Temporal Tables (maybe the documentation is not updated yet?).

Does anyone know, maybe unofficially, what is the likely variant?
If someone has the latest 2016 RC installed, could they tell us what sys.dm_db_persisted_sku_features returns if the database has Temporal Tables?

If there is a good chance that this feature will be included in Standard Edition, I could wait for half a year until the release of 2016 before migrating my 2008 database to the latest version. If there is no hope, I would migrate it to 2014. If I migrate to 2014 it will likely stay on this version for the next 10 years.

Regarding putting this question on hold:

I don't want an "opinion-based" answer to this question, I'd like to know the facts. I had hoped that this kind of information would have been published somewhere, and I just didn't know where to look. For example, until today I didn't know about sys.dm_db_persisted_sku_features.

The questions could be adjusted to:

How and when are SKU decisions made?
How would one know that decision was made?
If not for this version, how was it done for previous versions?
Is this kind of information published when the RTM version is released or earlier?

If a hard fact is not known (the decision is not made yet and even SQL Server developers don't know), an educated guess would be also great, for example, based on how this decision was made in previous versions.

Community Wiki answer generated from question comments. Feel free to edit it to improve it.

  Anthony Faull: The Temporal Tables feature is available in all editions of SQL Server 2016 including Standard Edition, Web Edition and Express Edition. It is not limited to Enterprise Edition.


Why is postgreSQL using 1-based arrays? Why is PostgreSQL using 1-based arrays (first element being at index 1) and not 0-based arrays (first element being at index 0)?

While I think Postgresql has had arrays since before SQL-99, the SQL-99 standard specifies 1-indexed arrays, and it's consistent with other areas in SQL where the first element is 1 instead of 0.

PostgreSQL actually has support for custom array start indexes, but I strongly advise you not to use it.


Spatial Index ignored when using additional non-spatial conditions Why does SQL Server ignore my spatial index when I use additional conditions? If I add an INDEX hint it will use the correct index, and runs a lot faster. I would like to avoid using the hint if possible.

Here is my SQL:

        PRINT CHAR(13) + 'Distance (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK INDEX(IX_location))
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

And here are the results:

Distance (No Hint)

(2206 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 295 ms.

Distance + Gender (No Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3323 ms,  elapsed time = 9183 ms.

Distance + Gender (Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 307 ms.

It would appear bitwise not in p1.isMale = ~p2.isMale causes the optimiser to pick wrong.
Try p1.isMale <> p2.isMale instead


Parallel Plan Selection I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple tables, dbo.CostCard where we have 43258326 rows, and dbo.CostType, where we have 150 rows for both databases.

We have been doing some application tests for the past few weeks against DB1. As a result of these test, data has changed for both tables. Currently, table dbo.CostCard increased to 43379268 (An addition of 120942 rows), and table dbo.CostType increased to 199 (Addition of 49 rows). We also have implemented a maintenance strategy that uses logic to reorganize/rebuild indexes based on fragmentation, and also update statistics if data has been changed, where we're updating with full scan.

Currently, only DB1 has this maintenance routine setup, and we've noticed that stats and indexes for both tables have been updated correctly. So far so good!

Now here comes the weird part. We have a fairly simple statement that we've noticed a pretty big performance degradation. Here's the statement:

SELECT DISTINCT TOP 100
    n1t1.Description
FROM
    CostCard AS n0t0
JOIN CostType AS n1t1
    ON ( n0t0.CostType ) = ( n1t1.Code )
WHERE
    ( ( n1t1.Description ) LIKE ( '%legal research%' ) )
    AND ( ( n1t1.Description ) IS NOT NULL )
ORDER BY
    n1t1.Description

What we noticed is that the query optimizer is creating a Serial plan (We've recompile it many times) for DB1, where we know stats and indexes are being regularly updated, and is using a Parallel plan (Better Plan, and yes, we've recompiled many times as well) for DB2 which has been sitting idle for the past few months!!!

How is this possible? I've been trying to figure this out for the past couple weeks but have ran out of ideas. Can someone shed some light here?

P.S.: I have attached a compressed file with all the info, including the query plans and statistics info.

https://dl.dropboxusercontent.com/u/72497299/Terrible%20Bad%20Query%20Plan.zip

Thanks and REALLY, REALLY appreciate any help!!!

From the SQL Server query optimizer's point of view, there is not much to choose between the parallel and serial execution plans in this case.

In general, the optimizer's cost model reduces the CPU cost (not the I/O cost) of operators in a parallel plan in proportion to the estimated degree of parallelism available. This CPU adjustment explains why the optimizer ever chooses a parallel plan (which will generally consume more resources) over a serial plan.

Unfortunately, the cost model does not apply this CPU reduction to the inner side of a nested loops join. It makes no sense to me (because the inner side still uses parallelism efficiently), but I didn't design the cost model.

Anyway, because the majority of the CPU cost in this execution plan is associated with the Clustered Index Scan (for which CPU reduction does not apply), the choice between serial and parallel is a close one. In broad terms, to be selected, a parallel plan must save enough using the local/global Stream Aggregate to compensate for the extra exchanges (Distribute and Gather Streams). The costs involved in that decision depend sensitively on the distribution of row values, as well as the number of rows. With relatively few rows and low-CPU operators, the trade-off can easily go either way.

In short, this query suffers from a debatable design choice applied to the costing of parallel nested loops joins. You can force the selection of a parallel plan using a plan guide, or by using the undocumented trace flag 8649. In SQL Server 2016 SP1 CU2 onward, you can also use the undocumented ENABLE_PARALLEL_PLAN_PREFERENCE hint.


Unpredicatable single insert performance on SQL Server table I have inherited a system that sometimes exhibits extremely poor single record insert performance.

The table is something like 8 million rows, so is relatively large but not exceptionally so. The following simple insert took over 7 minutes and blocked the entire system up in the process:

   INSERT INTO dbo.Communication(comm_action,comm_channelid,...)
   VALUES (N'Deskwork',11,...)

The overall database design is fairly poor. For example, although this particular table does have a Clustered Index, it has 34 other indexes as well despite being an OLTP system with heavy insert/update activity.

There are no triggers that I can see and the performance of inserts for the most part is reasonably good. Good being a relative term.

Could the number of indexes really account for such poor performance and is there any approach I could take to prove this before undertaking work.

Are there any other areas I could look into if the consensus is not the indexes being a problem?

The customer is very sensitive to changes, so I will need evidence before being able to make any changes.

I'm using SQL Sentry to highlight the blocking and the insert is the first block that shows up, followed by many others.

It's actually weirder than just there being another process blocking the inserter. I have a process (51) blocked by process 105. However, when I check what process 105 is about, there is no process 105.

I've repeated this several times, each time with a different process saying it's being blocked for a prolonged period of time, but the "blocker" not really existing. Does anyone know why sys.dm_exec_requests can say something is being blocked, but the same DMV say the blocker isn't really there?

What happens is, we get rows in dm_exec_requests with a blocking session id of, say 127. However, although there is a session in dm_exec_sessions, it says it's sleeping and there are no equivalent rows in the requests DMV.

I'm not clear on how a session can block something for 30 minutes despite seemingly not doing any requests?

For the blocked requests, in the case I had this morning it was waiting for a LCK_M_S. Any ideas?

I suspect this isn't a case of "the insert took 7 minutes" but rather "the insert was blocked for over 6 minutes." When an insert normally runs fine and then suddenly takes a long time, it is likely there is another cause, and highly unlikely that SQL Server suddenly got overwhelmed by your large table with too many indexes.

I treat this in more detail here but, essentially, you need to examine the system while this query is running long. Identify the session_id (SPID) and check sys.dm_exec_requests for blocking_session_id, which I think is most likely the issue; after that, check wait_type and last_wait_type. Most of the time, for duration outliers like this, one of those columns is going to help you track down the reason.

Sometimes, they might not have an active query in sys.dm_exec_requests - as I explain in the comments below, this is possible if someone started a transaction, made some statements against a table, then walked away without committing. You can see what object(s) that session has locked even if they aren't actively running a query.

DECLARE @SPID INT = <blocking_session_id from your "hung" query>;

-- see the command they ran last (might be related, might not)
DBCC INPUTBUFFER(@SPID);

-- see what that session has locked
SELECT s.name, o.name
  FROM sys.dm_tran_session_transactions AS tst
  INNER JOIN sys.dm_tran_locks AS tl
  ON tst.transaction_id = tl.request_owner_id
  INNER JOIN sys.objects AS o
  ON tl.resource_associated_entity_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE tst.session_id = @SPID;


Are scripts like sp_BlitzIndex and the others accurate on restored backups? I am a developer trying to get a better grasp of things.  I am wondering if using the scripts from BrentOzar.com will work on restored backups?  For example if I have the DBA give a backup of production, then restore that on my dev box, then run the scripts, will I get accurate information or is vital information lost during that process?

I am guessing that the answer is both yes and no.  It looks like a good bit of the information that sp_BlitzIndex provides will work regardless since it seems to be based on the structure of the database.  I am not so sure about sp_BlitzCache, etc.

I guess what I am asking is what types of things in these scripts won't work when running them on a restored backup and just as important, why not?

Thanks.

BlitzIndex will return some useful information on a non-production server as it is analyzing various things, some of which have nothing to do with what queries are running.  It should be clear to you when viewing the output what is relevant and what is not.

BlitzCache will not return useful information (or not much, at best) on a non-production server unless you run the same queries and load that are on the production server.

To answer your question as to "why not?", run them, review the output, and read the info at the URL returned in the URL column.  There is way too much info returned by these tools to give you a line-by-line description of why it would or wouldn't be relevant on a non-production server.


Assigning blank values on joins_query design I am using query design to join data, however, when I use the sql code below, where ever there is no value, the query assigns a value to it (see image below). see the blue section where the AIBL code should only have one residential land use for 500m, yet it duplicates the data to align with the other aibl code (with more land use types).

SELECT *
FROM dbo_AIBLVIC_LUM_PORTION_500 LEFT JOIN dbo_AIBLVIC_LUM_PORTION_1600
         ON dbo_AIBLVIC_LUM_PORTION_500.AIBL = dbo_AIBLVIC_LUM_PORTION_1600.AIBL;

If the aim of the query is to join rows from the tables that match the "land_use" as well, then you need to include those columns in the join condition:

SELECT ...
FROM dbo_AIBLVIC_LUM_PORTION_500 AS alp500
    LEFT JOIN dbo_AIBLVIC_LUM_PORTION_1600 AS alp1600
        ON  alp500.AIBL = alp1600.AIBL
        AND alp500.LAND_USE_500 = alp1600.LAND_USE_1600 ;     -- added condition


How are DB locks tied to connections and sessions? I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set at the connection or session level. Is this true, or did I misunderstand him?

I didn't realize that locks could be set when a connection is made (or when a session is started) and then released when that connection/session ends. If this is true, what benefits does this have over row-/page-level locks? Thanks in advance.

If this is true, what benefits does this have over row-/page-level locks?

You may have misunderstood what he meant.

A client connection and a session are essentially the same thing.  A session starts after a client authenticates and ends when the client disconnects.  (These are also referred to in the documentation as threads; each connection/session typically has a dedicated server thread servicing it.)

All locks in MySQL -- row locks, table locks, and named locks (which are advisory mutexes, not data structure locks) -- are always associated with, and held by, the session that created the locks.

The following statement is taken from the MySQL documentation for table-level locks, but it is true of all locks:

  A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

If not explicitly released, all locks are always automatically released when the session holding the locks ends.  A session ends when the client disconnects, the client thread is killed, or times out due to expiration whichever of the interactive_timeout or wait_timeout timers applies to the session.

So I suspect that in these senses, it could be said that locks are "scoped to" the session; however, locks are always global in their scope and impact.  If that weren't the case, there wouldn't be much point in locking -- which exists precisely to keep other sessions from doing things that conflict with what your thread is doing.

  InnoDB supports multiple granularity locking which permits coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in InnoDB. The idea behind intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later for a row in that table.
  — http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html

This is referring to the SELECT ... LOCK IN SHARE MODE (obtains "Intention Shared" row locks) and SELECT ... FOR UPDATE (obtains "Intention Exclusive" row locks) statements, which do allow you to deliberately obtain locks on rows... however, to be precise, these are not at the session level -- they are even more granular: they're a the transaction level, which is a subset of a session.  As soon as you COMMIT or ROLLBACK the current transaction, these are released.  Of course, if your session terminates unexpectedly, the transaction rolls back, which releases the locks also.


Secondary replica indexing I have a question, about a question, that is part of the 70-462 Microsoft exam.

I had this question today, and I'm not completely sure of the correct answer. While I did pass the exam, I would like to understand the correct solution given this situation.

If I recall the question correctly, you administer a 2012 SQL Server, the requirements are to offload up to the minute reporting, for an OLTP database, to another server, and also be able to add indexes to the secondary database.

The answers for the question were along the lines of:

Two servers, different data center. AG group with synch commit. Active secondary.
Two servers, same subnet. AG group with synch commit.
Two servers, log shipping every 10 minutes.
A couple diffrent answers with a-synch commit.
SQL Server that includes application database configured to perform transactional replication.
SQL Server that includes application database configured to perform snapshot replication.
Two servers in a windows failover cluster. SQL Server configured as clustered instance.

Initially I ruled out the a-synch commit options (latency), the synch commit AG group in different data centers (latency?), log shipping (delay), and snapshot replciation options.

This left me with the synch commit AG group on the same subnet, or SQL server with transactional replication.

Based on https://msdn.microsoft.com/en-us/library/ff878253.aspx, I read that if you want indexing on your active secondaries, you have to create them in your primary. I interpreted the question as requiring to be able to create indexes on the seconday, that do not exist on the primary (covering indexes for reporting, or what not).

The other answer, refers to SQL Server singularly, which doesn't sound like we're adding "another server" to offload the work to.

Have I misinterpreted the question/requirements? What should the correct answer/solution be?

#5: transactional replication.

Why is this an option? Because the documentation explains exactly why it is possible there - once initialized, index changes are not replicated in either direction.

  Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

I also think you are reading the text far too literally. If you have configured replication at all, it is highly unlikely you should assume that it would be configured to replicate to another database inside the same instance of SQL Server - I'm not even sure if that is possible, or if there really is a plausible use case for that, but I don't think the wording of the question was slanted to point you that way. To be honest, I just think the quality of questions in those exams, in general, is shady. I wouldn't read too much into them.

Why are none of the other options possible? Because:

In no Availability Groups configuration can you create indexes on the secondaries, since the secondaries are essentially read only (though you can create statistics; these, however, live in tempdb). IIRC there have been requests to support something like this for indexes too, but no plans. This rules out #1, #2, and #4.
Log shipping isn't a good fit for the requirement, since you can't change the destination database and continue applying logs. So - since the question states that logs are restored every 10 minutes - you'd have to restore the database with recovery every 10 minutes, create the indexes, then kick everyone out 10 minutes later to do it again. This is not "up-to-the-minute" reporting. This rules out #3.
Snapshot replication can't be updated once initialized, since it is literally a snapshot - in order to create indexes, you'd have to do it every time you restored a snapshot, since this is an all-or-nothing operation. It is not possible to provide "up-to-the-minute" reporting in this model. This rules out #6.
In failover clustering, there is only one copy of the data, and only one instance of SQL Server, and the data is only visible to any workload (read or write) on the primary ("active") node. So, by definition, it is not possible to make changes to a copy of the data, since there is no "copy." This rules out #7.


Sql-Server Unique Constraint but with NON-unique data I've inherited this database which has a table defined as such :

CREATE TABLE home_tickets(
    id int IDENTITY(1,1) NOT NULL,
    identifier nvarchar(45) NOT NULL,
    start_date_time datetime NOT NULL,
    revised bit NOT NULL,
    agency_code nvarchar(100) NOT NULL,
    archived tinyint NOT NULL,

    CONSTRAINT PK__home_tic__3213E83F236943A5 PRIMARY KEY CLUSTERED (id ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY,

    CONSTRAINT UQ__home_tic__969168722645B050 UNIQUE NONCLUSTERED (identifier ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY

However when I select from the table given an identifier I am getting multiple rows returned.

select * from home_tickets where identifier = 'TI54815780';

id      identifier  start_date_time         revised archived
415044  TI54815780  2013-10-24 00:00:00.000 0       0
652845  TI54815780  2014-10-24 00:00:00.000 0       0
171642  TI54815780  2012-10-24 00:00:00.000 0       0
945275  TI54815780  2015-10-24 00:00:00.000 1       0
1220327 TI54815780  2016-10-24 00:00:00.000 1       0

Why would this happen with a unique constraint on identifier ?

It seems the underlying constraint index is disabled.  You can verify this with:

SELECT is_disabled
FROM sys.indexes
WHERE
    object_id = OBJECT_ID(N'home_tickets')
    AND name = N'UQ__home_tic__969168722645B050';

After the bad values are remediated, rebuild the index to enable the constraint:

ALTER INDEX UQ__home_tic__969168722645B050 ON home_tickets REBUILD;


Is there a better way to create dynamic time averaged series over a dynamic frequency? I've got a series of tables with lots of high precision data collected from various devices.  The intervals that they were collected on varies and even wanders over the time series.  My users want the ability to pick a date range and get an average / min / max over these variables at a specific frequency.  This is the second stab I've taken at this, and it works, but I wonder if there is a better/faster way to accomplish this?

declare @start datetime
declare @end datetime
set @start = '3/1/2012'
set @end = '3/3/2012'
declare @interval int
set @interval = 300

declare @tpart table(
dt datetime
);

with CTE_TimeTable
as
(
select @start as [date]
union all
select dateadd(ss,@interval, [date])
from CTE_TimeTable
where DateAdd(ss,@interval, [date]) <= @end
)
insert into @tpart
select [date] from CTE_TimeTable
OPTION (MAXRECURSION 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)  from clean.data c ,
@tpart t
where
ABS(DateDIFF(ss, t.dt , c.Date) ) <= @interval /2
and
Date >= @start
and
Date <= @end
group by t.dt

Right now over 32721 rows for this 3 day period this query takes about 43 seconds to run and gives me the 577 rows I expect but I'd like to get this faster.  The big hit comes from the nested loop to do the inner join.

Your join between the tables is embedded in a function making it really hard for the optimizer to do anything smart with it. I guess it has to compare every row in one table against every other row in the other table.

Rewriting your join with a range check should be a lot faster. I also added a primary key to your table variable to remove a sort operation from the query plan and I turned your table variable into a temp table instead. The difference in my tests was that the query plan started to use Parallelism.

declare @start datetime;
declare @end datetime;
set @start = '20120301';
set @end = '20120303';
declare @interval int;
set @interval = 300;

create table #tpart
(
  dt datetime primary key
);

with CTE_TimeTable
as
(
  select @start as [date]
  union all
  select dateadd(second ,@interval, [date])
  from CTE_TimeTable
  where dateadd(second, @interval, [date]) <= @end
)
insert into #tpart
select [date]
from CTE_TimeTable
option (maxrecursion 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)
from clean.data c
  inner join #tpart t
    on c.Date >= t.dt and
       c.Date < dateadd(second, @interval, t.dt)
group by t.dt;

drop table #tpart;

Note: This query does not return exactly the same intervals as your query does. It will divide the date range into equally sized parts where your query had one half interval at the start and one half interval at the end of the range. It is of course possible to modify the query to do be equivalent to your query if that is desired.

Update

I tested on a table with a total of 1036801 rows and with 34560 in the interval 2012-03-01 to 2012-03-03. In my tests the original query takes 4.1 seconds. The query above takes 0.1 seconds.

Script to generate the test data:

create table clean.data
(
    Date datetime primary key,
    x1 int,
    x2 int
);

go

with C as
(
  select cast('20120201' as datetime) as D
  union all
  select dateadd(second, 5, D)
  from C
  where D < '20120401'
)
insert into clean.data(Date, x1, x2)
select D, checksum(newid()) % 1000, checksum(newid()) % 1000
from C
option (maxrecursion 0);


Performance considerations for Caching Aggregate counts We have an InventoryActivity table that holds transactional changes to item quantity:

CREATE TABLE dbo.InventoryActivity(
    InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Organization_uid int NOT NULL,
    MasterInventory_uid int NOT NULL,
    AdjustmentType_cd varchar(20) NULL,
    AdjustmentReason_cd varchar(20) NULL,
    Quantity int NULL
)

We would like an InventorySummary which should aggregate to the current quantity for each. The summary counts should always be derivable from the the sum of the transactional records, but we have several different approaches on how to calculate aggregate counts:

Stored Procedure
Separate Summary Table
Indexed View

What performance considerations should tip the scales in favor of a particular strategy?
What best practices exist?  *(I know best practices borders on discussion, but I want to know what considerations would come into play to help make the decision)

Here's a fiddle with some sample code and data

Stored Procedure

Simplest option is just perform the SUM operation fresh everytime. But involves no caching and would likely cause performance issues over time.

CREATE PROCEDURE dbo.GetInventorySummary
AS
SELECT Organization_uid,
       MasterInventory_uid,
       SUM(Quantity) AS Quantity
FROM dbo.InventoryActivity
GROUP BY Organization_uid, MasterInventory_uid

Separate Table

We could create a table to store the current quantities.  The plus side is fetching this data would be trivial.  The downside is we would have to manually maintain it and keep the records in sync everytime we do a write to the InventoryActivity table.

CREATE TABLE dbo.InventorySummary(
    Organization_uid int NOT NULL,
    MasterInventory_uid int NOT NULL,
    Quantity int NOT NULL,
    PRIMARY KEY (Organization_uid, MasterInventory_uid)
)

Triggers could help alleviate some of that maintenance.

CREATE TRIGGER dbo.InventoryActivity_I
ON dbo.InventoryActivity
AFTER INSERT
AS

    CREATE TABLE #InsertSummaryTemp
    (
      Organization_uid int,
      MasterInventory_uid int,
      Quantity int
    )
    INSERT INTO #InsertSummaryTemp
    SELECT Organization_uid,
           MasterInventory_uid,
           SUM(Quantity) AS Quantity
    FROM INSERTED
    GROUP BY Organization_uid, MasterInventory_uid

    -- UPDATE EXISTING RECORDS
    UPDATE InventorySummary
    SET Quantity = s.Quantity + i.Quantity
    FROM InventorySummary s
    JOIN #InsertSummaryTemp i ON s.Organization_uid = i.Organization_uid AND
                                 s.MasterInventory_uid = i.MasterInventory_uid

    -- INSERT NEW RECORDS
    INSERT INTO InventorySummary
        (Organization_uid, MasterInventory_uid, Quantity)
    SELECT i.Organization_uid, i.MasterInventory_uid, i.Quantity
    FROM #InsertSummaryTemp i
    LEFT JOIN InventorySummary s ON i.Organization_uid = s.Organization_uid AND
                                    i.MasterInventory_uid = s.MasterInventory_uid
    WHERE s.MasterInventory_uid IS NULL

Indexed View

Borrowed from this and this we could create an Indexed View.  Which lowers the maintenance cost associated with option 2.  However there is a concern about performance that we are still aggregating all records through the entire history.  As opposed to a simple read from a table.

CREATE VIEW dbo.InventorySummaryView
WITH SCHEMABINDING
AS
    SELECT
      Organization_uid,
      MasterInventory_uid,
      SUM(Quantity) AS Quantity,
      COUNT_BIG(*) AS Count
    FROM dbo.InventoryActivity
    GROUP BY Organization_uid, MasterInventory_uid
GO

CREATE UNIQUE CLUSTERED INDEX PK_InventorySummaryView ON dbo.InventorySummaryView
(
      Organization_uid,
      MasterInventory_uid
)

What performance considerations should tip the scales in favor of a
  particular strategy?

Essentially, as any form of cache, these strategies allow to increase performance of reads at the expense of writes and disk space.

So, you should consider the following :

The expected number of writes/updates vs. number of reads.
What is more important: fast writes or fast reads.
Whether extra disk space is available and how "cheap" it is.
Whether the summary has to be always up to date, or it can be delayed.

The first strategy is a baseline. You don't duplicate the data, writes are as fast as they can be.

The last strategy (indexed view) slows down writes immediately (as they happen), but the aggregated amounts are always up to date.

The second strategy with an explicit summary table gives you greater control over when to perform the aggregation. You can delay the aggregation and perform it when the server is under less load. If you accumulate a bunch of pending changes for calculating the summary and perform these calculations in bulk, it may be more efficient than updating the summary after every single change of the source data.

On the other hand, the engine that maintains the indexed view during updates should be smart enough to update the summary by applying only the changes to the summary without reading through the whole table each time. For example, if you update only, say, 2 rows in 10M table, then to calculate the new SUM(Quantity) the engine can subtract two old values of Quantity and add two new values. I don't have an authoritative source at hand that would confirm that the engine indeed works like this, but it should be fairly easy to test and verify by measuring the amount of reads and writes of a few test statements on a large table.

This leads to another thing to consider:

The type of aggregation that is done in the indexed view and whether the engine is smart enough to keep it up-to-date efficiently. For example, it is easy to keep SUM up-to-date efficiently and not so easy for MIN.
How big is the table that is being aggregated and what percentage of this table changes with each update.


Why do we add NO_TRUNCATE and NORECOVERY options while taking tail log Backup? Assume that my mdf file is deleted and I set my database into single user mode. The first thing that I do is take tail log backup. My question is do I need to add NORECOVERY option while taking tail log backup? if so, then why? My point of view is if my db is set into single user mode which means that no one except me accessing database, then I can omit writing NORECOVERY as the main function of this keyword is to stop the subsequent transactions.

I would like ask the same question about NO_TRUNCATE keyword. As I know, when we take t-log backup (in full recovery model) we actually truncate t-log file and by writing NO_TRUNCATE keyword, SQL Server will not truncate t-log file after backup. Why do I need to keep that inactive part when I have already taken tail-log backup successfully?

According to Tail-Log Backups (SQL Server) - (highlighting mine)

  If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To
  avoid an error for an online database, you must use the ... WITH
  NORECOVERY option of the BACKUP Transact-SQL statement.
  If a database is offline and fails to start and you need to restore the database, first back up the tail of the log. Because no
  transactions can occur at this time, using the WITH NORECOVERY is
  optional.

According to SQL Server expert Paul Randal regarding Disaster recovery 101: backing up the tail of the log

  Use the NO_TRUNCATE option, which allows the log backup even if the
  database files aren’t there

In your case, the required database files do not exist which would require you to use NO_TRUNCATE


Cannot browse for Backup files using SSMS I'm facing a strange problem.
When I try to backup or restore any database using a SQL Server user (not sa) that belongs only to "public" server role, i have the following error when i click the browse button on the "Select backup destination" window:

D:\SQLServerData\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
...

The user is db_owner for the database I try to backup, and SQLServer service is running with administrator account, so it should have no problems with access to the D:\SqlServerData\ folder.

I tried to add the user to any other server role, but none worked except "sysadmin". But having the user in sysadmin server role means that the user can do anything in any database, and I do not want that.

Any idea on what's going on?

Most likely the problem is that the Windows or ADS account the is the service account for the SQL Server Engine doesn't have access to the directory mentioned. Give that user (note that this is not going to be a SQL Server user) access to read/write to the directory and your problem should be resolved.


could sql create view with fix group even without data Let say that I have a table as below :

day       | Qty
---------------
Sunday    | 10
Monday    | 5
Tuesday   | 20
Sunday    | 17

as I have another table that day value could only be 'Sunday' till 'Saturday', I would like to have a resume table as below:

day       | Sum(Qty)
---------------
Sunday    | 27
Monday    | 5
Tuesday   | 20
Wednesday | 0
Thursday  | 0
Friday    | 0
Saturday  | 0

The value for Wednesday till Saturday have zero value because of no data. Is there any solution?

create table t1(day varchar2(20),qty number);

create table t2(day varchar2(20));

insert into t1 values('Sunday',10)

insert into t1 values('Monday', 5);

insert into t1 values('Tuesday', 20)

insert into t1 values('Sunday',17)

insert into t2 select trim(to_char(sysdate+level,'Day')) WeekDay from dual connect by level<=7;

select t2.day, nvl(sum(t1.qty),0) Sum_qty from t1 right outer join t2 on(t1.day=t2.day) group by t2.day;

DAY       | SUM_QTY
:-------- | ------:
Tuesday   |      20
Sunday    |      27
Monday    |       5
Wednesday |       0
Thursday  |       0
Saturday  |       0
Friday    |       0

OR

create view sum_table as
select t2.day,
       nvl(sum(t1.qty),0) Sum_qty
from t1
right outer join
       (select trim(to_char(sysdate+level,'Day')) Day
                from dual
                connect by level<=7)t2
on(t1.day=t2.day)
group by t2.day;

dbfiddle here