High Number of Connections The below query tells me there is a total of 520 connections to our SQL Server database. Status of all is 'sleeping' except for 4 connections. The login_time goes back to 2 days ago on some connections.

select session_id,  login_time, host_name, program_name, status, cpu_time, total_elapsed_time
from sys.dm_exec_sessions
order by total_elapsed_time desc;

I run the below query to see that there are 61 different logins...

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;

Is this standard? Or should those conenctions that are sleeping be closed?

Thanks.

Connection pooling surely has that type of behavior, but the providers I'm familiar with wouldn't typically keep those connections active to reuse for days, or even hours.  In fact, ADO.NET will eliminate the connection from the pool in about 4 to 8 minutes or inactivity.

Yes, it sounds like you have clients not properly closing and disposing of connections, which could be leading to more severe problems than just connection placeholders.  But at the same time, you should not only be verifying login_time, but you also have to consider last_request_start_time to get an idea of activity of the session (or the lack thereof).

  Is this standard?

It depends.  For that duration, no that's not typical and surely warrants the investigation of how clients are managing connections to the data source.

  should those conenctions that are sleeping be closed?

You would first need to determine that they shouldn't be currently active, and then you would need to confirm with the business and end users whether or not you can clean that up.  But that would most likely be a very temporary fix, as if this is programmatic connections (think client applications) then it will probably come back once applications and/or end users reconnect.

Take a look at this blog post I wrote about connection pooling.  It is relatively thorough and should give you a good idea on distinguishing between connection pooling, as well as poorly managed connections.


Can't create archived redo logs Oracle 11.2.0.3 in Windows Server 2008 64 bits.
I try to set up archive mode in a database:

C:\Users\Admin>SET ORACLE_SID=BDSPROD

C:\Users\Admin>SQLPLUS /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 25 12:28:59 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect sys/oracle as sysdba
Connected.
SQL> select log_mode from v$database;

LOG_MODE
NOARCHIVELOG

SQL> ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest='D:\oradata\BDSPROD\archivelogs\' SCOPE=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SHUTDOWN IMMEDIATE;
ORA-01012: not logged on
SQL> STARTUP MOUNT;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6781128704 bytes
Fixed Size 2268064 bytes
Variable Size 3539993696 bytes
Database Buffers 3221225472 bytes
Redo Buffers 17641472 bytes
Database mounted.
SQL> ARCHIVE LOG START;
Statement processed.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select log_mode from v$database;

LOG_MODE
ARCHIVELOG

However, there is no archived redo logs generated in folder: D:\oradata\BDSPROD\archivelogs\
I also tried with SET log_archive_dest='D:\oradata\BDSPROD\archivelogs' (without last "\"), but same behaviour, no archived redo log is created

Perhaps this from the Oracle Database Reference will be useful:

    Note: For Enterprise Edition users, this parameter has been deprecated
    in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise
    Edition is not installed or it is installed, but you have not
    specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid.

  LOG_ARCHIVE_DEST is applicable only if you are running the database in
  ARCHIVELOG mode or are recovering a database from archived redo logs.
  LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n
  parameters, and must be defined as the null string ("") or (' ') when
  any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.
  Use a text string to specify the default location and root of the disk
  file or tape device when archiving redo log files. (Archiving to tape
  is not supported on all operating systems.) The value cannot be a raw
  partition.

  If LOG_ARCHIVE_DEST is not explicitly defined and all the
  LOG_ARCHIVE_DEST_n parameters have null string values,
  LOG_ARCHIVE_DEST is set to an operating system-specific default value
  on instance startup.

  To override the destination that this parameter specifies, either
  specify a different destination for manual archiving or use the
  SQL*Plus statement ARCHIVE LOG START filespec for automatic archiving,
  where filespec is the new archive destination. To permanently change
  the destination, use the statement ALTER SYSTEM SET LOG_ARCHIVE_DEST =
  filespec, where filespec is the new archive destination.


What are the possible 'types' available in SQL Server as mentioned in the 'type' column in the sys.master_files view? I'm relatively new to SQL Server, and having a problem with an expanding tempdb database. As such, I'm starting to look just more generally into how database files work.

Looking at this query:

USE master;
SELECT *
FROM sys.master_files mf

I understand looking at the Microsoft documentation that the sys.master_files view keeps track of files related to databases. On my system the 'type' column is only either 0 or 1 - i.e. I only have Log and Row files. But the docs mention types 2 (FILESTREAM), 3 (...), and ,4 (Full text).

Questions:

Would .mdf files always be type 0? It seems this is the file that represents a database; I didn't realize that all database objects would be represented by 'Row'-type files
What is the type FILESTREAM?
How are tables differentiated in files of type Rows?

===== EDIT

Question 1 is a little misleading; the answer mentions that .mdf and other SQL Server file extensions are just SQL Server's default file extensions and that these can be changed. Testing this, it looks like data is written to the files in exactly the same way regardless of file extension.

You shouldn't mix file extensions with file types. You have to differentiate between file extensions and what the files actually contain.

Create Database (non-standard)

It is possible to create a database that has *.txt as the file extension for the Data files ("ROWS Data" type) which are otherwise created with an *.mdf or *.ndf extension.

It is equally possible to create a database that has a *.txt extension for the TransactionLog file ("Log" type) which is otherwise created with an *.ldf file extension.

Script to create a non-standard database

CREATE DATABASE [File_Types]
    CONTAINMENT = NONE
    ON  PRIMARY
   ( NAME = N'File_Types', FILENAME = N'C:\SQL\SQL_DATA\File_Types.txt' , SIZE = 5120KB , FILEGROWTH = 1024KB ), -- normally *.mdf
   ( NAME = N'File_Types_01', FILENAME = N'C:\SQL\SQL_DATA\File_Types_01.txt' , SIZE = 5120KB , FILEGROWTH = 1024KB ), -- normally *.ndf
    FILEGROUP [File_Types_FS_FG] CONTAINS FILESTREAM
   ( NAME = N'File_Types_FS', FILENAME = N'C:\SQL\FILESTREAM\File_Types_FS' ) -- no extension; is a directory where BLOBs are stored
   LOG ON
   ( NAME = N'File_Types_log', FILENAME = N'C:\SQL\SQL_LOGS\File_Types_Log.txt' , SIZE = 3072KB , FILEGROWTH = 10%) -- normally *.ldf
GO
USE [File_Types]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [File_Types] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

This will create a database with three files which all have the *.txt extension.

Query sys.master_files

SELECT database_id, FILE_ID, TYPE, type_desc, NAME, mf.physical_name
  FROM sys.master_files AS mf WHERE database_id = 13

Output

database_id FILE_ID type_desc   NAME            physical_name
13          1       ROWS        File_Types      C:\SQL\SQL_DATA\File_Types.txt
13          2       LOG         File_Types_log  C:\SQL\SQL_LOGS\File_Types_Log.txt
13          3       ROWS        File_Types_01   C:\SQL\SQL_DATA\File_Types_01.txt
13      65537   2   FILESTREAM  File_Types_FS   C:\SQL\FILESTREAM\File_Types_FS

To answer your questions

"DATA Rows" will always be type 1. A file with an *.mdf extension can be of any type.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data. (see: FILESTREAM Overview)
Tables are stored in pages and extents inside the "Data ROWS" database files. An explanation of how data is stored can be found here: Understanding Pages and Extents and Managing Space Used by Objects.


MySQL memory usage slowly increasing then crash - what's causing this? So I have a Server that's constantly receiving 30k clicks/day. However, in the meantime does certain insert/update/select cronjobs as well.

My server has 4CPUs, 8GM RAM. Using MySQL 5.7.18 + PHP.

I did some changes in my mysqld.cnf, however, it doesn't seem to help. Maybe the buffer pool size is too much?

Here's my configuration:

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# *
#InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
#max-connections                = 500
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 6G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

Here are two images of the memory charts:

Not entirely sure what's causing this. But what I've gathered is that it's keep increasing until it crashes.

Any thoughts on this?

EDIT - After chnaging the buffer pool size to 4G - RAM usage is 85-86% now consistently

What else is running on the machine?  There seem to be 2 or 3 jumps in memory usage.  MySQL (usually) has one jump or gradual rise.

Nothing looks wrong in my.cnf.  But change innodb_buffer_pool_size to 5G; that will probably keep it from crashing as much.

What OS are you using?  If it has "swappiness", set that to 1, not 0.


Uploading a csv and losing a connection results in lost disk space and table bloat I have a server with 150GB of diskspace. Today I uploaded a dataset of 30GB. I cancelled the import due to internet dying, then noticed there was 29GB of space missing in the database (meaning the CSV was uploaded, but not deleted when I broke the operation). When uploading the data once again, it broke again and I lost another ~25GB. Now there isn't enough free space to upload the data.

This is hosted on AWS RDS, Postgres 10.6.

Is there a way to fix this? But will this delete records? I'm hosting at the moment ~70GB of data and don't want to lose any records. What's the best way to go about this?

The tuples were created and they left dead because the transaction wasn't committed. Simply run

VACUUM and the dead tuples will be purged. Or run
VACUUM FULL and the table will be rewritten (removing the dead tuples). If you have more dead tuples then live ones this may be faster, but it'll require a lock. It's usually massively slower but it sounds like you may be an exception.


Convert Code from SQL Server to MySQL What will be the MySQL code for below code in SQL Server.

 SELECT @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(rn as Nvarchar(max)))
   from
    (
     SELECT row_number() over(partition by
    TPID,EAPID,locale,Origin_airport,
    Origin_multicity_gaia_id,dest_multicity_gaia_id_1,
    dest_multicity_gaia_id_2,dest_multicity_gaia_id_3,dest_airport_1,
                   dest_airport_2,dest_airport_3,test_keys
                   order by Origin_airport,test_keys) rn
                   from ##New_Generic
          ) d
                cross apply
                (
                  SELECT 'dest_city_1_image_url_1', 'First' union all
                  SELECT 'dest_city_2_image_url_1', 'Second' union all
                  SELECT 'dest_city_3_image_url_1', 'Third'

                ) c (col, so)
                group by col, rn, so
                order by rn, so
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

At a guess - STUFF, QUOTENAME, and FOR XML PATH are being used to build an aggregated string out of the inner query - specifically, to build a column list for use in a later dynamic query. MySQL uses backticks (`) to "quote" DB object names, and there's a GROUP_CONCAT aggregate function built-in.

The CROSS APPLY isn't actually using anything from the query to determine its values - it appears to be a hard-coded sub-query, in which case you should be able to just put it in as a CROSS JOIN instead.

You can search this site or use your favorite search engine to find out how to do row numbers in MySQL. In fact, here's an answer I provided a while ago that shows you how to generate a row number that would've been done with a PARTITION BY in MS SQL.

Basically, you set up a variable to hold the row number. For each row, the variable's starting value will be the number of the previous row. You also store the current value of each partition column. If any of the partition columns change, set the row number variable to 1, otherwise, add one to it. Note that the query (or subquery) you generate the row number in must be sorted first by all the partition columns, then by the columns that would be in the OVER clause's ORDER BY using the MS SQL functions.

The one thing I can't see a way around easily is your use of a global temporary table (##NewGeneric). Technically that's not really a part of this code (it assumes it already exists and just uses it), but it's something you should at least be aware of. In MS SQL Server, a global temp table is created in one session, goes away when that session either drops it or closes (mostly), but allows other sessions to access it (and, if another session is using it when it should go away, it holds off until that session's current statement (transaction?) is over).

If you are really having other sessions access it, you'll have to use a regular table. The biggest differences should be that you have to be sure to drop it yourself, and you won't be able to drop it immediately if it's involved in a current transaction; your session will have to wait around until it's free to drop it.


How to set a value if certain columns are the same across multiple rows I am doing a data migration and I end up with a temporary table as follows:

curid cuid  rtid    cd          dd      rm
10     4    4   2016-01-02  2016-07-02
16     4    4   2016-06-12  2016-12-12  Remarks Jun 12
18     5    3   2016-07-18  2017-07-31
8      5    3   2015-06-21  2016-06-30  Add some test
11     6    4   2017-01-01  2017-07-01
9      7    3   2017-01-01  2018-01-31

I need to split the data into two tables.
Based on that same data, it should look like this:

Table A
    id  curid cuid  rtid
    1    10    4    4
    2    18    5    3
    3    11    6    4
    4     9    7    3

That's one row per distinct (cuid, rtid) plus a curid value picked from each set of duplicates. id is just a sequential number.

Table B
    id curid    cd           dd         rm
    1   10     2016-01-02   2016-07-02
    2   10     2016-06-12   2016-12-12  Remarks Jun 12
    3   18     2016-07-18   2017-07-31
    4   18     2015-06-21   2016-06-30  Add some test
    5   11     2017-01-01   2017-07-01
    6    9     2017-01-01   2018-01-31

The actual curid is irrelevant as long as the records in Table B match the associated record in Table A (so we could even use a temp sequence or something to set the curid).

Your test setup

(Best provided this way in your question - hint!)

CREATE TEMP TABLE tmp (
  curid int
, cuid  int
, rtid  int
, cd    date
, dd    date
, rm    text);

INSERT INTO tmp VALUES
  (10, 4, 4, '2016-01-02', '2016-07-02', NULL)
 ,(16, 4, 4, '2016-06-12', '2016-12-12', 'Remarks Jun 12')
 ,(18, 5, 3, '2016-07-18', '2017-07-31', NULL)
 ,(8 , 5, 3, '2015-06-21', '2016-06-30', 'Add some test')
 ,(11, 6, 4, '2017-01-01', '2017-07-01', NULL)
 ,(9 , 7, 3, '2017-01-01', '2018-01-31', NULL);

Solution

Create target tables if they don't exist:

CREATE TEMP TABLE a (
   id    serial
 , curid int  -- UNIQUE?
 , cuid  int
 , rtid  int
);

CREATE TEMP TABLE b (
   id    serial
 , curid int
 , cd    date
 , dd    date
 , rm    text
);

Use DISTINCT ON for table A:

INSERT INTO a (curid, cuid, rtid)
SELECT DISTINCT ON (cuid, rtid)
       curid, cuid, rtid
FROM   tmp
ORDER  BY  cuid, rtid, curid  -- pick smallest curid per group
RETURNING *;

id | curid | cuid | rtid
-: | ----: | ---: | ---:
 1 |    10 |    4 |    4
 2 |     8 |    5 |    3
 3 |    11 |    6 |    4
 4 |     9 |    7 |    3

Detailed explanation here:

Select first row in each GROUP BY group?

Use a simple window function for table B:

INSERT INTO b (curid, cd, dd, rm)
SELECT min(curid) OVER (PARTITION BY cuid, rtid), cd, dd, rm
FROM   tmp
ORDER  BY cuid, rtid  -- optional
RETURNING *;

id | curid | cd         | dd         | rm
-: | ----: | :--------- | :--------- | :-------------
 1 |    10 | 2016-01-02 | 2016-07-02 | null
 2 |    10 | 2016-06-12 | 2016-12-12 | Remarks Jun 12
 3 |     8 | 2016-07-18 | 2017-07-31 | null
 4 |     8 | 2015-06-21 | 2016-06-30 | Add some test
 5 |    11 | 2017-01-01 | 2017-07-01 | null
 6 |     9 | 2017-01-01 | 2018-01-31 | null

curid is guaranteed to match since we picked the smallest per group in both queries.

dbfiddle here


SQL SERVER 2014 - SELECT INTO, ORDER BY - automatic clustered index? When I create a table using SELECT INTO A, and it has ORDER BY x, does SQL server automatically make x the primary key of A (thus auto create a clustered index)?

No, the ORDER BY applies to the SELECT query and not to the table created by SELECT INTO.
Using ORDER BY is unnecessary in this statement, because it doesn't even guarantee the rows are inserted into the new table in that order.
See INTO Clause (Transact-SQL)


SQL Server Backup and Query Execution Is SQL Server Backup affects the query execution performance? Is there an effect while running the SQL Server backup in remote query execution? Thanks.

Yes backing up a database will affect performance if you run queries against the server / database.

Your server no matter how amazing it is has a limited amount of resources; backing up the database will add load to the disks and cpu; the amount depending on the size and the options that were chosen for the backup (e.g. compression or verification will add more load.)

What you can do is use a backup strategy that does a full backup at a low utilisation time and smaller incremental transaction log backups during the day to minimise the backup time window if you are backing up in operational hours. Or you could investigate alternatives like database mirroring which may help you remove the need to backup the active database; at the cost in terms of extra server resource and a small increase in load on the server for all transactions.


Recover Oracle database after drive loss Interview Question How would you answer this as an interview question?

  The database crashed and you lost a
  drive containing one of your control
  files and another drive containing an
  entire redo log group. You have a
  level 0 backup from yesterday morning,
  a differential backup from yesterday
  evening, and a cumulative backup from
  this morning. In addition three days
  of redo logs are on disk. You backed
  up the control file to trace just an
  hour before the crash. How do you
  recover the database?

The very first thing I'd try is copying the surviving control file then restarting the DB - the loss of the controlfile is what caused the DB to crash, and if the redo log group happened to be INACTIVE at the time, then I'm pretty much OK. I'd find this out from the alert log. So long as there is a disk in the right place, I can issue CLEAR LOGFILE and they will just be recreated.

If not then we have a problem - the SCN in the controlfile is ahead of the DBFs and we've no way to get there. We have no choice but to perform a point-in-time-recovery, and we have to discard those DBFs as well. In RMAN, restore database (assuming controlfile autobackup), recover until the highest SCN in the archived redo logs, then OPEN RESETLOGS. Since I have the cumulative incremental, I don't need the differential incremental (I'm not sure why you'd mix the two types? Also why if we had several disks for redo why we'd put an entire group on one disk rather than multiplexing all groups).

(There is probably a much better answer involving flashback, but I have to confess that I have only used explicitly created flash restore points so far, never run through a DR scenario).


Oracle how to make primary key index usable from unusable? I have table where it's primary key index got unusable due to partition merge ( I may have done it wrong).

select INDEX_NAME, STATUS from ALL_INDEXES WHERE TABLE_NAME = TRANSACTION' AND INDEX_NAME = 'PK_TRAN';

Status from above query is N/A (unusable)

Then I tried to drop the index

drop index PK_TRAN;

resulted following error.

SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key

Then i tried to drop the constraint

alter table TRANSACTION drop constraint PK_TRAN;

resulted following error,

SQL Error: ORA-02273: this unique/primary key is referenced by some foreign keys

Then  i tried to drop the problematic reference constraints

select CONSTRAINT_NAME from all_constraints where constraint_type='R' and r_constraint_name='PK_TRAN';

alter table A drop constraint FK_1;

which resulted following error.

SQL Error: ORA-14650: operation not supported for reference-partitioned tables

How can i make transaction table's pk index valid(usable). Currently operation on this table fails with

ORA-01502: index 'PK_TRAN' or partition of such index is in unusable state

01502, 00000, "index '%s.%s' or partition of such index is in unusable state"

  MERGE: 1489 RENUMBERED TO 1502

  Cause: An attempt has been made to access an index or index partition
          that has been marked unusable by a direct load or by a DDL
          operation.

  Action: DROP the specified index, or REBUILD the specified index, or
          REBUILD the unusable index partition.

Now, you have to rebuild index(es) according to their type, Global or Local.

  You can rebuild global index partitions in two ways:
  Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (you can run the rebuilds concurrently).

  Drop the entire global index and re-create it. This method is more efficient because the table is scanned only one time.

  Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:

  ALTER INDEX REBUILD PARTITION/SUBPARTITION

  This statement rebuilds an index partition or subpartition unconditionally.

  ALTER TABLE MODIFY PARTITION/SUBPARTITION REBUILD UNUSABLE LOCAL INDEXES

  This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.

Documentation:About Rebuilding Index Partitions


Insert Only first Occurence in Oracle table i have a table

CREATE TABLE PEOPLE
(
NAME VARCHAR(64 BYTE)PRIMARY KEY NOT NULL,
TIME TIMESTAMP(6),
NICKNAME VARCHAR2(16 BYTE),
STATE VARCHAR2(50 BYTE),
CITY VARCHAR(128 BYTE)
)

I want to insert in this table values from other tables, but for the columns NAME, the reference table can have duplicate NAME, in my TABLE PEOPLE, i want it to INSERT only the first occurence of NAME, and discard when it already exist.
I tried this

INSERT
INTO PEOPLE( NAME, TIME,NICKNAME, STATE)
SELECT
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME
LEFT JOIN LIEN l ON l.NAME = p.NAME
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00'
order by p.TIME desc

But i still get oracle error : Duplicate Key Errors (ORA-00001)
.
Thx for any help

MERGE INTO PEOPLE e using
(SELECT
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME
LEFT JOIN LIEN l ON l.NAME = p.NAME
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00') d
on (e.name = d.name)
WHEN NOT MATCHED THEN
  INSERT  (NAME, TIME,NICKNAME, STATE) VALUES (d.name, d.time, d.nickname, d.state);

Or use the ignore_row_on_dupkey_index hint.
INSERT /*+ ignore_row_on_dupkey_index(people(name)) */
INTO PEOPLE( NAME, TIME,NICKNAME, STATE)
SELECT
DISTINCT p.NAME,
p.TIME,
l.NICKNAME,
pt.STATE
from PBO p
LEFT JOIN PASST pt ON pt.NAME = p.NAME
LEFT JOIN LIEN l ON l.NAME = p.NAME
WHERE not exists (
    select * from PEOPLE f
    where  f.NAME= pt.NAME
  )
AND
p.TIME> '25/05/20 00:00:00'
;


Where can SSISDB actually reside? Trying to wrap my head around what's going on under the hood when integration services is installed, and how that relates to SQL instances on the same machine.

Currently we are aiming to have a full BI stack with each component (SQL Server containing DW, SSAS and SSIS) on separate VMs so resource requirements can be siloed. We want to use the project deployment model for SSIS so are targetting SSISDB rather than MSDB, but the server with SSIS installed doesn't have a SQL instance to host SSISDB, so currently it's installed on the SQL box.

My question is, is it necessary to install a SQL instance on the SSIS box in order to host SSISDB or can it reside just within the integration services instance somehow? If so, does that mean that connecting to the SSIS instance through SSMS is just a legacy feature to manage MSDB or file system packages?

This isn't a question about licensing, it's about infrastructure.

If you want to run packaged in the project deployment mode (SSISDB), you need to install SQL Server DB engine on the SSIS box.
If you want to execute packages from files system or other stores, you can skip DB engine.
In both cases you need a license for the SSIS box.

In SSISDB mode you will manage packages using SSMS connection to the DB engine.

Steps how to install SSISDB:
1. Open SQL Server Management Studio.
2. Connect to the SQL Server Database Engine.
3. In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
4.Click Enable CLR Integration. The catalog uses CLR stored procedures.

From:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017


How do I check for a null or empty table-valued parameter? I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty.  I have seen this StackOverflow post that says that null/empty TVPs should simply be omitted from the calling parameter list.  My problem is that I can't figure out how to check for empty or null inside the stored procedure as "IF (@tvp IS NULL)" fails on procedure creation with the message 'Must declare the scalar variable "@tvp"'.  Do I have to do a SELECT COUNT(*) on the TVP and check for zero?

Code excerpt:

CREATE PROCEDURE [foo] (@tvp [TvpType] READONLY) AS

IF (@tvp IS NOT NULL) -- doesn't work
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...

A table can't be NULL, nor can a TVP. How do you check if a table is empty? You certainly don't say IF Sales.SalesOrderHeader IS NULL. :-)

IF EXISTS (SELECT 1 FROM @tvp)
BEGIN
  -- lots of expensive processing
END
ELSE
BEGIN
  -- a little bit of cheap processing
END
...


Join that returns always 1 record I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value).

I need a join to this table on the test and type but so that if no type is specified I get the generic tests (That is no problem as both sides have NULL) but if a type is specified I get the test of that type if there is one, or the generic test when there exist no specific one.

    TestTable
    ... Test   Grp  Type  Description ...
    ... TestA  G1   NULL  TestA_Gen    ...
    ... TestA  G1   M     TestA_M     ...
    ... TestB  G1   NULL  TestB_Gen   ...
    ... TestB  G1   X     TestB_X     ...

    BaseTable
    ... Pers  Type ...
    ... P_A   Null ...
    ... P_B   M    ...
    ... P_C   X    ...

Select B.PERS, T.DESCRIPTION
from BaseTable B
Join Testtable T on ??? and T.Grp = 'G1'

should give
P_A TestA_Gen
P_A TestB_Gen
P_B TestA_M
P_B TestB_Gen
P_C TestA_Gen
P_C TestB_X

How do I do that in MySQL?

Many thanks in advance,

According to given sample data there is "Type" which is common in both table

Select B.PERS, T.DESC
from BaseTable B
right outer Join Testtable T
on ( T.type is null or B.Type=T.type or B.Type is null)
and T.Grp = 'G1'
where T.DESC<>''
order by B.PERS asc


linux mysql case insensitive lower_case_table_names=1 make server cannot be started I use mysql in linux ubuntu an I want to set lower_case_table_names = 1 in /etc/mysql/my.cnf file. But after I inserted the variable, the mysql server cannot be started.

o

Is there any way to set lower_case_table_names in my.cnf file or an other way to set it?

I have done this before, but it was in centos and mysql server was manual started.

Thank you.

Config installation by download file mysql-apt-config_0.8.14-1_all.deb from mysql website (option, if you want to install mysql 8).
Install mysql sudo apt-get install mysql-server.
Open file my.cnf in /etc/mysql, use sudo nano /etc/mysql/my.cnf.
Insert the lower_case_table_names = 1, like bellow:

[mysqld]
lower_case_table_names = 1
Save file my.cnf.
Backup the folder /etc/mysql, like sudo cp -r /etc/mysql /etc/mysql.bak
Remove mysql installation use sudo apt-get remove -y mysql-* and sudo apt-get purge -y mysql-*.
copy back the folder /etc/mysql.bak, like sudo cp-r /etc/mysql.bak /etc/mysql.
back to step 1 and 2. In step 2, the installation will ask, if you keep the config file in folder /etc/mysql or use default from installation. So, I use the config file from previous installation (N).
Finish installation
check file my.cnf. It should be the file with lower_case_table_names = 1, like step 4.

After installation, mysql will read lower_case_table_names = 1.

Hopefully this can help.


MySQL crashing randomly We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and is killed by OOM killer. Currently if I look for memory consumption for mysql using top command, it says that mysql process is consuming 17% of memory. Normally the server shows upto 45% of total memory used. But when mysql crashes, system memory is recorded to be between 70% to 95% or sometimes 100%.

I have a feeling that its mysql memory settings (I think we have set huge values for different variables) which cause it to consume that much memory at peak traffic hours. Or it is the myisam tables in Joomla 2.5 that are causing it to crash due to locking of tables. Please suggest your thoughts.

Error from mysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.
140720 17:07:42  InnoDB: Initializing buffer pool, size = 512.0M
140720 17:07:42  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140720 17:07:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140720 17:07:42  InnoDB: Started; log sequence number 2 2605701970
140720 17:07:42 [Note] Event Scheduler: Loaded 0 events
140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-0ubuntu0.10.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000
RAM : 1Gb
Hdd : 50Gb
OS Ubuntu 10.04.4 LTS

my.cnf :

max_connections = 250
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
key_buffer = 16M
read_buffer_size = 128K
join_buffer_size = 128K
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 16M
tmp_table_size = 16M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=40M
innodb_log_buffer_size=40M
innodb_thread_concurrency=2
innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB

the Out-of-memory process (oom) killer terminated your mysql process because it was trying to use more memory than was available. Calculating memory usage in MySQL exactly is very difficult, but once you understand how MySQL allocates memory system-wide and to each connection, you can get a pretty good idea of how much memory is likely to be used. Keep in mind that a big challenge here is figuring out how many connections are open simultaneously and how much each one is doing, since the amount of time a connection hangs around waiting to be reused is settable with server system variables as well.

There are a few tools out there that can help you get a good overview of what the server memory allocation should look like and where potential problems may lie in your current configuration. So long as you're not 100% reliant on any one. mysqltuner.pl will give you a great starting point, and possibly get you close enough to resolve your issue completely. Percona's MySQL Configuration Wizard is very good at generating optimal initial configurations. Run the wizard, generate a configuration, install it, and see how mysqltuner.pl disagrees with it.

You will immediately see that you're miles ahead of where you started in terms of a good configuration, and that it's a complex enough topic that there are many different preferences, priorities, levels of expertise, and areas of focus in deciding what a good configuration looks like, but there's also some numbers that are unquestionably better than others.

As others have mentioned, the reality is that to get good performance you will need a server with more RAM, but setting it up will be easier with the Percona wizard as well as mysqltuner.pl.

Finally, here is a great introduction to what MySQL does with memory. It's from some MySQL documentation I saved years ago, but I could not find the original source just now:

    Mysql Server Memory Usage = Sum of Global Buffers +
        (number of Connection * Per thread memory variables).
    Global buffers include:
        key_buffer_size: key_buffer_size is the size of the buffer used
            for index blocks.
        innodb_buffer_pool_size: The size in bytes of the memory buffer
            InnoDB uses to cache data and indexes of its tables.
        innodb_additional_mem_pool_size: The size in bytes of a memory pool
            InnoDB uses to store data dictionary information and other
            internal data structures.
        innodb_log_buffer_size: The size in bytes of the buffer that InnoDB
            uses to write to the log files on disk.
        query_cache_limit: The amount of memory allocated for caching query
            results.
    Each thread for client connection uses:
        thread_stack – The stack size for each thread.
        net_buffer_length – connection buffer
        max_allowed_packet – up to this size net_buffer_length can grow
        read_buffer_size – used for sequential table scan
        rean_rnd_buffer_size – used for random read buffer / sorting
        tmp_table_size – temporary / hash tables in mysql
        sort_buffer_size – for sorting
    Per thread variables include:
        read_buffer_size: Buffer memory used for sequential table scan.
        read_rnd_buffer_size: Memory used for random read buffer / sorting.
        sort_buffer_size: Memory allocated for sorting, Group By, Order By.
        join_buffer_size: The size of the buffer that is used for plain index
            scans, range index scans, and joins that do not use indexes and
            thus perform full table scans.
        thread_stack: The stack size for each thread.
        net_buffer_length: Connection buffer
        max_allowed_packet: Up to this size net_buffer_length can grow.
    Note that,
        If size increases or if table have blob columns, instead of heap
            tables on-disk tables created.
        Memory for variables read_buffer_size, sort_buffer_size,
            read_rnd_buffer_size, tmp_table_size are allocated as & when
            required. They are also de-allocated once the task is
            accomplished.


Transaction Log usage fluctuation The Transaction Log Usage fluctuating quite frequently.

When I run DBCC SQLPERF('LOGSPACE') it shows Log Space Used (%) as 3% which is like 650MB while at very next minute or two when I run it again it might show as 0.0123% without doing any kind of Log backup or anything and it happened few times.

Just wondering what it could be? I was trying to analyse Log usage pattern over the period of time.

Thanks in advance

The information in your question leads me to believe that some kind of log truncation is occurring between your first invocation of DBCC SQLPERF('LOGSPACE') and your second invocation.  According to The Transaction Log, (specifically the section Factors that can delay log truncation),  there are numerous things that can prevent log truncation.  You mentioned the fact that the database is in a High Availability Group.  I'd suggest periodically querying the sys.databases catalog view (log_reuse_wait and log_reuse_wait_desc columns).  Perhaps "a secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. and when that completes, the VLF's are marked for reuse."


MySQL Insert into two tables using new IDs I'm trying to select data already in the database and insert it into two tables using the generated ID from table one.

table1 contains configuration options, table2 contains pricing data. Table2 uses the auto ID from table1.

I have all these products in Table1 and I have to use the same config options in another group in that table, but WHMCS doesn't have copy for config options.

I was thinking of going into the table and running a select query like so:

INSERT INTO tblproductconfigoptionssub
    (
        id
        , configid
        , optionname
        , sortorder
        , hidden
    )
SELECT
    NULL
    , 76
    , optionname
    , sortorder
    , hidden
FROM tblproductconfigoptionssub
WHERE configid = 41

That works for data from the first table, by selecting all records from config group id 41 and reinsert them, except now they are linked to config group 76.

Table2 contains the pricing data and it has a column called relid that is linked to the ID generated from tblproductconfigoptionssub above.

Table2 already has pricing for the items in table1 under their original IDs. The records from Table2 would have to be copied, but with the new IDs generated by the query above.

How do I modify the statement above to insert the new data into Table2, along with the prices?

I don't really want to do a PHP script for this.  However, I think I need to make a MySQL function to select all items from config group 41 and then store values in variables. Then insert into two tables.

Is there a way to do this with INSERT statements?

After your query runs, you'll have a set of copied rows (with configid=41) and an identical set of pasted rows (except for the configid=76 and the auto-created id).

Since, these ids are not known in advance, you'll need another way to identify rows of the config table, e.g. a unique key (besides the auto-incrementing one), so you can match (join) the newly created rows with the old ones.

If, for example, the (configid, optionname) is unique, then the following would work:

INSERT INTO pricing
    ( relid, price, ... )          --- relid and all the other columns,
                                   --- except any autoincrement you may have
SELECT pasted.id, p.price, ....    --- and the same columns here
FROM
    pricing AS p
  JOIN
    tblproductconfigoptionssub AS copied
      ON  copied.id = p.relid
      AND copied.configid = 41
  JOIN
    tblproductconfigoptionssub AS pasted
      ON  pasted.optioname = copied.optioname
      AND pasted.configid = 76 ;


How to avoid Forced reboots using unattended upgrades to SQL Server 2014 Upgrading seven instances from SQL Server 2008R2 to 2014 via the ADMINISTRATOR command line using a configuration file. The upgrade runs fine but when I move on to upgrading the next instance I get the following error.

From : C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150105_152519\Summary.txt

Error result: -2067919934
Result facility code: 1214
Result error code: 3010

Exception type: Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException
    Message:
        A computer restart is required. You must restart this computer before installing SQL Server.
    HResult : 0x84be0bc2
        FacilityCode : 1214 (4be)
        ErrorCode : 3010 (0bc2)
    Data:
      SQL.Setup.FailureCategory = RuleViolationFailure
      DisableWatson = true
    Stack:
        at Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction.ExecuteAction(String actionId)
        at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

I am able to continue after restarting, but something is very wrong if I have to restart after every upgrade. Can someone help me figure out, why I have to restart after upgrading every instance?

Here is the configuration file I'm using

INSTANCENAME="SOME_INSTANCE"
SQMREPORTING="False"
INSTANCEID="SOME_INSTANCE"
IACCEPTSQLSERVERLICENSETERMS="True"
ACTION="Upgrade"
ENU="True"
QUIET="True"
UpdateEnabled="True"
ERRORREPORTING="False"
USEMICROSOFTUPDATE="False"
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
FAILOVERCLUSTERROLLOWNERSHIP="2"

Here is the command being used to upgrade :

setup.exe  /ConfigurationFile="Confguration_Upgrade.ini"

Update

More information from detail.txt

# (01) 2015-01-05 15:45:00 Slp: File operations are pending for "SQLCTR100.DLL"
# (01) 2015-01-05 15:45:00 Slp: File operations are pending for "SQLAGENTCTR100.DLL"
# (01) 2015-01-05 15:45:00 Slp: Rule 'RebootRequiredCheck' results: IsRebootNotRequired=False
# (01) 2015-01-05 15:45:00 Slp: Evaluating rule : RebootRequiredCheck
# (01) 2015-01-05 15:45:00 Slp: Rule running on machine: G06SQL001
# (01) 2015-01-05 15:45:00 Slp: Rule evaluation done : Failed
# (01) 2015-01-05 15:45:00 Slp: Rule evaluation message: A computer restart is required. You must restart this computer before installing SQL Server.

Hi Craig,

I may have a solution for you.

You should go to the registry and delete the registry key below:
HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

I've created PS scripts for upgrading SQL Server Instances.

Here an example:

# Select the source path and the Instance name.
# Launch the script on a PowerShell console as Administrator.

$SourcePath = "\\MYNAS\Sources\SQL Server 2014"
$InstanceName = "MYINSTANCE1"

Set-Location $SourcePath

$exists = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"  -ErrorAction SilentlyContinue
    If (($exists -ne $null) -and ($exists.Length -ne 0)) {
        Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"
    }

.\setup.exe /ACTION="Upgrade" /Q /INDICATEPROGRESS="True" /INSTANCENAME=$InstanceName /IACCEPTSQLSERVERLICENSETERMS

$InstanceName = "MYINSTANCE2"

Set-Location $SourcePath

$exists = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"  -ErrorAction SilentlyContinue
    If (($exists -ne $null) -and ($exists.Length -ne 0)) {
        Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"
    }

.\setup.exe /ACTION="Upgrade" /Q /INDICATEPROGRESS="True" /INSTANCENAME=$InstanceName /IACCEPTSQLSERVERLICENSETERMS

I hope this will help.

Max.


Formal Database Modeling Technologies at Wikipedia I don't understand this table from Wikipedia completely:

row1 1      1   one-to-one                               person ←→ weight
row2 0..1   1   optional on one side one-to-one          date of birth ←→ person
row3 0..*   0..* optional on both sides many-to-many     person ←→ book
row4 1      1..* many-to-one                             person ←→ birth place

row2: I think every person has exactly one date of birth. I don't understand this

row4: I think every person has exactly one birth place. I don't understand this.

What am I missing?

The Wikipedia examples are not the best.

Relationships are between entities. The person <-> weight is a poor examples of a 1-1 relationship because weight is not an entity but an attribute. A better example might be employee <-> cube (assuming one-person cubes). Each employee is assigned one and only one cube and each cube be is assigned to one and only one employee.
In practice, there are few places where you will find 1-1 relationships between tabled entities. You might find them for some PII data that is moved to a separate table for security reasons.

Again a poor example and for the same reason: a date is an attribute, not an entity. Use again the employee <-> Cube number example. The optional quality is imparted by making the Cube number field nullable, meaning the cube may not apply or may not be known, at least at the time the record was created. However, it makes more sense to have the optional component on the cube side as a cube could well be empty, not assigned to any employee.

A better example. A person may be the author of 0, 1 or many books and a book may be authored by 0 (such as a phone book), 1 or many authors. The optional quality on either side could also mean the data is just not known.

Also a fairly good example. A person can only be born in one and only one place -- say, a city. A city, however, can be the birthplace of many people.


Postgres 12: Are there performance differences between PRIMARY KEY(A, B) and PRIMARY KEY (B, A) on a table PARTITION BY (A)? Given a table this table:

CREATE TABLE tbl (
  a int not null,
  b int not null,
  created timestamp not null default now(),
  primary key(a, b)
) PARTITION BY LIST(a)

The query plan for a lookup on columns a and b looks like this:

EXPLAIN ANALYZE SELECT EXISTS(SELECT * FROM tbl WHERE a = 1 AND b = 1)

Result  (cost=2.37..2.38 rows=1 width=1) (actual time=0.013..0.013 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Index Only Scan using pkey on partition  (cost=0.15..2.37 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)
          Index Cond: ((a = 1) AND (b = 1))
          Heap Fetches: 0
Planning Time: 0.860 ms
Execution Time: 0.033 ms

I'm wondering if defining the primary key in reverse column order (PRIMARY KEY (b, a) ) PARTITION BY LIST (a)) results in better performance as the index used in the lookup contains the interesting column first, as filtering by a is already achieved by the query planner picking a single partition to run the query on.

No, that will make no difference; the order of the columns in the index doesn't matter if both columns are compared with = in the WHERE condition (of course only if there is AND between the conditions).

The index will be scanned using both columns at the same time, and all that matters is the number of index entries that match the condition, which will be the same in both cases:

  a  |  b           b  |  a
-----+-----       -----+-----
   1 |  -1          -1 |   1
   1 |   1  <---    -1 |   2
   1 |   1  <---     1 |   1
   1 |   2           1 |   1  <---
   2 |  -1           1 |   2  <---
   2 |   1           2 |   1
   2 |   3           2 |   3

index on (a,b)   index on (b,a)


How to query the mongoDB admin database? I'm trying to execute the below commands taken from the mongoDB docs

use admin
db.system.version.find( { _id: "authSchema" })

But running those in Robo 3t gives the following output

switched to db admin

Error: error: {
"$err" : "not authorized for query on admin.system.version", "code" : 13
}

The user I'm logged in with has the dbOwner role.

Some people suggest granting the root role to the user, but this fails, saying 'not authorized'

db.grantRolesToUser(
    "fooUser",
    [ "readWrite" , { role: "root", db: "admin" } ]
)

It seems like a bad idea to grant root access just for this one query anyways, but I don't see any better solution.

So, how does one query the admin database in mongoDB?

(Current mongoDB version is 2.6.11)

As per your log statement you are getting the error message such as

Error: error: {
"$err" : "not authorized for query on admin.system.version", "code" : 13
}

As github mongodb documentation here error_code("Unauthorized", 13).

As per MongoDB documentation here MongoDB grants access to data and commands through role-based authorization and provides built-in roles that provide the different levels of access commonly needed in a database system. You can additionally create user-defined roles.

A role grants privileges to perform sets of actions on defined resources. A given role applies to the database on which it is defined and can grant access down to a collection level of granularity.

You need to assign the read role or readWrite to the user.

For example :

Create Administrative User with Roles

use admin
db.createUser(
   {
     user: "appAdmin",
     pwd: "password",
     roles:
       [
         { role: "readWrite", db: "config" }

       ]
   }
)

For further your ref here, here and here


Should I backup Master key / Service master key if I'm using TDE? I've done this in the past, and now I need to do it again. activate TDE.
I remember that I didn't need to restore the master key on the secondary server to restore the database.
And now, as I have bad memory, I'm doing dozens of tests again with sintax, backup and restore and etc to be sure we will not mess with anything after enabling TDE in the database.
after creating the MASTER KEY, then CERTIFICATE, then the DATABASE ENCRYPTION KEY , I just backup the CERTIFICATE as below:
USE MASTER
GO
BACKUP CERTIFICATE CERTIFICATE_I_HATE_TDE
    TO FILE ='C:\tde_backups\CERTIFICATE_I_HATE_TDE.CER'
    WITH PRIVATE KEY ( FILE ='C:\tde_backups\CERTIFICATE_I_HATE_TDE_PRIVATE_KEY.PVK',
    ENCRYPTION BY PASSWORD='123superHardPassword')

then, I enable the encryption with ALTER DATABASE SET ENCRYPTION ON.
After this I backup the database, and to restore in the other server, I create a certificate FROM FILE pointing the old certificate (that was CTRL+C / CTR+V to the new server).
After this I'm able to restore the database, without restoring the MASTER KEY or the SERVICE MASTER KEY.
I'm asking this because I'm reading this redgate link and it says to backup the master key, but where would I use it?
and another question. whats the difference between creating the master key and certificate in the MASTER database, and creating the master key and certificate in the database itself ( the one i'm going to use TDE)?
Edit: Deleted by mistake.

The Master Key is used to protect all your certificates' private and asymmetric keys of each TDE database. It's pertinent it exists on your server for best security and prevention of someone gaining access to the keys of one of your TDE databases, and by Microsoft's design the Master Key lives in both the TDE database and master database.
The certificate also gets created in the master database so that it can be used by the user databases that you want to apply TDE to. The database encryption key gets created in the user database you're applying TDE to and uses that global certificate from the master database.
For more information, these Microsoft Docs - SQL Server and Database Encryption Keys (Database Engine) clarify the purposes of the Master Key and where it's stored, specifically the Database master key section. Additionally the documentation on Transparent Data Encryption provides good details and shows an example of creating the Master Key in the master database but the certificate in the user database they're applying TDE to.


Alter existing partitioned table I have a large table that is already partitioned by an identifier (CustomerId). I have created new customers, and want to add new partitions to the table, and want to confirm the correct way to do this. My predecessor set this up and is no longer available so I cannot confirm.

There are currently partitions for 60 distinct CustomerIds (let's say 1-60), and a filegroup and file for each. I have created new filegroups and files for the new customers (61-68) and need to partition the existing data already in this table. So, the data that's already in the table, with customerId 61-68 needs to be moved into separate partitions.

I tried to use the partition wizard, but couldn't find an option for what I wanted. As far as I can tell, I need to run something like:

ALTER PARTITION SCHEME PS_Details  NEXT USED Filegroup61
ALTER PARTITION FUNCTION PF_Details ()  SPLIT RANGE (61)

I'm looking for someone to point me in the right direction as to best practice for this sort of thing. Do I take the data out of the table, into a new table stored in the Filegroup61 then switch the partition in? Not really sure how to approach this.

Splitting a non-empty partition requires about 4x logging as normal DML.  Assuming the table and indexes are aligned, you can avoid excessive logging with large tables by creating a partitioned staging table with the original boundaries.  Then switch in the problem partition, split the empty partitions of the original function, repartition the staging table, and switch the remediated partitions back in.  Below is an example script.  You'll need to tweak this if you use a RANGE LEFT partition function and specify your actual filegroups.

--create staging partition function and scheme
--with same boundaries and filegroups as original
CREATE PARTITION FUNCTION PF_CustomerId_Staging (int)
    AS RANGE RIGHT FOR VALUES();
ALTER PARTITION FUNCTION PF_CustomerId_Staging()
    SPLIT RANGE(NULL);
DECLARE @CustomerId int = 0;
WHILE @CustomerId <= 60
BEGIN
    ALTER PARTITION FUNCTION PF_CustomerId_Staging()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
CREATE PARTITION SCHEME PS_CustomerId_Staging
    AS PARTITION PF_CustomerId_Staging ALL TO ([PRIMARY]);
GO

--create staging table with same schema
CREATE TABLE dbo.CustomerData_Staging(
      CustomerId int NOT NULL
    , CustomerDataId int IDENTITY NOT NULL
    , CustomerData int NOT NULL
    , CONSTRAINT PK_CustomerData_Staging PRIMARY KEY CLUSTERED (CustomerId, CustomerDataId)
        ON PS_CustomerId_Staging(CustomerId)
    );
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData) ON PS_CustomerId_Staging(CustomerId);
GO

--switch partition with multiple customers to staging table
ALTER TABLE dbo.CustomerData
SWITCH PARTITION $PARTITION.PF_CustomerId(60)
TO dbo.CustomerData_Staging PARTITION $PARTITION.PF_CustomerId_Staging(60);

--split original partition function (empty partitions)
DECLARE @CustomerId int = 61;
WHILE @CustomerId <= 68
BEGIN
    ALTER PARTITION SCHEME PS_CustomerId NEXT USED [PRIMARY];
    ALTER PARTITION FUNCTION PF_CustomerId()
        SPLIT RANGE(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--repartition staging table and indexes with same function/scheme
CREATE UNIQUE CLUSTERED INDEX PK_CustomerData_Staging ON dbo.CustomerData_Staging(CustomerId, CustomerDataId)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);
CREATE INDEX idx_CustomerData_Staging_CustomerDataId ON dbo.CustomerData_Staging(CustomerData)
    WITH (DROP_EXISTING=ON)
    ON PS_CustomerId(CustomerId);

--switch remediated partitions back into main table
DECLARE @CustomerId int = 60;
WHILE @CustomerId <= 68
BEGIN
    ALTER TABLE dbo.CustomerData_Staging
        SWITCH PARTITION $PARTITION.PF_CustomerId(@CustomerId)
        TO dbo.CustomerData PARTITION $PARTITION.PF_CustomerId(@CustomerId);
    SET @CustomerId += 1;
END;
GO

--drop staging objects
DROP TABLE dbo.CustomerData_Staging;
DROP PARTITION SCHEME PS_CustomerId_Staging;
DROP PARTITION FUNCTION PF_CustomerId_Staging;
GO


Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file Example in the EF6 migration file Up method.

I wish to add a new column B in existing table Country.

Sql("ALTER TABLE [dbo].[Country]
     ADD [B] INT DEFAULT((0)) NOT NULL;")

Then I will need to add this new column B to it's VCountry view and I did the following:

DropView("VCountry")

Sql("CREATE VIEW [dbo].[VCountry]
         AS
         SELECT A, B FROM Country")

Everything looks good until I run the Add-Migration XXX command and the Invalid column name 'B'. prompt out.

What I trying to figure out is how to add the new column to the table and it's relevant view at the same migration file?

Any help would be much appreciated!

I have tried your method and it's working on my computer. Have you checked the column name having wrong spelling?


SQL Server 2012 indirect checkpoint I am trying to understand where to draw a fine line for Indirect Checkpoint that was introduced in SQL Server 2012.

Based on my understanding

SQL Server does checkpoint to flush dirty pages out to the disk, but frequency or time interval between checkpoints depends on many factors. It is controlled by server level configuration called recovery interval and default value for this settings is zero which means in a fairly busy system SQL server can perform checkpoint every minute and can result in database recovery time of less than a minute.

SQL Server 2012 introduced indirect checkpoint – which in turn can allow you to control recovery interval for individual databases.

At a high level this setting appears to a good thing, it allows to even out disk IO by performing more frequent checkpoints instead of doing a periodic checkpoint which can result in flooding the underlying IO subsystem

Now my questions are

a) Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?

b) I keep finding in many blogs that indirect checkpoint setting is very dangerous as
It can keep your IO system very busy. Does below statement is true from above blog?

  Checkpoint normally writes large blocks of data to disk optimally in
  single write operation, up to 256KB, depending on the number of
  contiguous dirty pages in cache that needs to be flushed to disk. Once
  you turn on the indirect checkpoint feature by setting target recovery
  time to a non-zero value, the checkpoint writes will turn into single
  page writes, 8KB writes.

c) Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP  type workload? What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?

UPDATE (April 14 2016):

From SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default on the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support:

  Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016.

Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?

Once we have set the target_recovery_time, SQL Server internally calculates the Target Dirty Buffer threshold. As the transactions are logged in the transaction log, a Dirty Page List keeps track of the LSNs and dirty buffers which are modified by the transaction. So for indirect checkpoints, the dirty buffers from each transaction along with the LSN are being tracked.

The Recovery Writer (a new background process in 2012) periodically polls the Dirty Page List and if it finds a number of Dirty Pages in the Buffer Pool greater than the Target Dirty Buffer threshold it flushes the Dirty Buffers and moves the minLSN forward.

Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP type workload?

From BOL, an online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?

This depends on many factors as each environment is different interms of hardware, memory, transactions occuring, etc . I would recommend you to TEST and double TEST this particuliar feature as it can be helpful to you or else can cause detrimental effect on your workload and possibly jeopardise your databases as well. By default, target Recovery time is set to ZERO meaning, change it if you have tested it thorougly and its behaviour is acceptable to you ...

Advantages :

It may improve database recovery time
It may reduce checkpoint I/O as it writes continuously pages to the disk in the background

Disadvantages :

In OLTP workload it can increase overall writes on server by writing continuously pages to the disk in the background which may reduce the performance.

Ref:

Setting indirect checkpoints by database in SQL Server 2012

How do checkpoints work and what gets logged ?


SQL Server 2005, Foreign key check against part of a table I have an articles table that references a categories table.  I'm defining the foreign key like this:

constraint fk_categoryid foreign key (categoryid) references categories (categoryid)
on update no action
on delete no action

Is it possible in the foreign key definition to restrict the categories that can be referenced based on another column in the categories table?  For example, say there is a hasarticles column in the categories table.  I'd like the foreign key constraint to be restricted to those categories where hasarticles = true.

No this isn't possible.

I'd probably create a separate table categoriesWithArticles that holds the relevant categoryids (instead of having the flag) and have the FK reference that table instead.

Another more convoluted way to enforce it and keep the column would be

CREATE TABLE categories
(
categoryId int primary key,
hasarticles bit not null,
unique(categoryId, hasarticles)
)

CREATE TABLE articles
(
categoryId int,
hasarticles as cast (1 as bit) persisted,
FOREIGN KEY (categoryId, hasarticles) REFERENCES categories(categoryId, hasarticles)
)

But this requires the creation of a technically redundant unique constraint as well as the additional persisted column in the articles table.


SSRS purge rows from dbo.catalog We have a third party software that generates reports by adding a new "temp" report to the SSRS catalog every time a user prints a report so now the dbo.catalog table is almost 3 GB and has 16K+ rows worth of one time only reports.  Is there a way to safely purge these rows from this table?

To be clear, I am talking about ReportServer not ReportServerTempDB so EXEC FlushReportFromCache @Path and EXEC CleanExpiredCache are of no help.

From what I am reading online Microsoft does not want you messing with this table directly so there is little documentation.  I can't find any sort of official maintenance function that works on dbo.catalog.  I don't expect many people want to mass delete their reports so this does not surprise me.

Check and see if these temporary reports show up through the web service interface. You should be able to setup a powershell script to call the deleteitem web service method to delete the reports.
That or unhide them in the report manager interface and manually delete them.
Those would be the supported ways to do it.


Trigger SQL Agent job based on text in error log for an AG failover I have a 2 node AG setup (Not an FCI). I need to configure an alert to trigger a job based on a string found in the error log showing that the node is now the new primary.
The string I want to use is:

  'primary_pending' to 'primary_normal'

This is what shows in the error log when the secondary node becomes primary. I want the alert to kick off a SQL job I created to automate our SSRS to use the new primary replica.

Below is the alert I setup, but it is not triggering properly. I do have the alert configured to execute the job when this alert is triggered.

When a failover happens, the job does not kick off. I'm guessing it may be due to the error number used. I found a blog post about using this, but can't seem to find it again. The error log does not display any error number in the line with this text, so I think that is the issue.

How can I get an alert to trigger based on text it finds in the error log, or is there a better way to do this?

I do have conditional logic to check for primary / secondary status in all my jobs, but I can't use that for this job as I want it to only execute 1x (when it first becomes primary).

You need to select <all databases> in the database name selection.  The error you are checking for is not related to a specific database, so having a database specified is effectively filtering it so the alert doesn't care about it.

I just tested this using RAISERROR to log a message that included "'this' or 'that'" and it worked fine.  At first I thought you may need to delimit the single quotes, but in my test I didn't and it worked.


DBCC CheckDB space requirements? Ran into the following error...

K:\Microsoft SQL
Server\Data\MyAudit3_Dat.NDF_MSSQL_DBCC50: Operating system error
112(There is not enough space on the disk.) encountered.

In this case the disk space was very low which needed remediation... However, it lead me to ponder
what are the disk space requirements for dbcc checkdb?

It depends on the database size.
You can run
dbcc checkdb ('master') WITH ESTIMATEONLY

To get an estimation of the space requirement. Usually, this is not really accurate but I guess it's better then nothing.


SQL Login currently in use or not Recently we did a change on a client's production server. The change was to rename an sa login. Once we renamed the sa login we found out the client was having a problem because sa login was used in the connection string for a few applications which the client didn't mentioned to us. We were able to roll back the rename.

In the future, is there any way or query which we can execute so that we can know if sa login is currently used by any application or user before we renamed it or delete the login? Basically we want to check if any login is actively used by anyone before renaming or deleting it.

You run a server-side trace or Extended Event session to capture login activity.

I also would not have just renamed it in production. If you are referring to the sa account itself, disable it first to verify if anything breaks; and leave that disabled for a day or up to a week. How long you leave it is based on knowledge by the application owner. If they are not confident leave it disabled for at least a month.

If you are renaming just any old login to something else for a purpose, you obviously cannot go with the disable route. In that case you just have to let your trace run for a period of time to capture the activity.

One other option you might try is to just put in a server trigger that just writes an audit of login activity. There are a few examples of this on the web.


Efficiency for contact-contact relationships schema I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the people table has about 90k records with 80k relationships to 10k organisations.

New model:

Contact           Relationship              (Contact table again)
--------          -------------             ---------------------
cid      11----0< cid_a              /---11 cid
name              cid_b          >0-/       name
                  details
                  start_date
                  end_date
                  relationship_type

If I want to be able to query for Wilma's current relationships (let's say Wilma has cid = 2) I can set up 2 keys on relationship, one (cid_a, cid_b) and (cid_b, cid_a).

SELECT friend.name FROM contact friend, relationship
WHERE
     (
       ( cid_a = 2 AND cid_b = friend.cid )
       OR
       ( cid_b = 2 AND cid_a = friend.cid )
     )
     AND
     ( start_date IS NULL OR start_date <= CURRENT_DATE )
     AND
     ( end_date IS NULL OR end_date >= CURRENT_DATE )

But I'm not sure it's efficient as the duplicate keys would be quite long.

A contact will likely have 3, 4 or more relationships to various organisations, other contacts etc. such as

Wilma is a student at X University-
Wilma is a member of Y organisation
Wilma was previously a contact at Z organisation
Wilma is married to Fred.

Is this the One True Way? Or nothing like it?!

If you can guarantee that each person can only belong to a single organisation there is no reason why you could not add an extra column to store the organisation_id to your table. Then you could use a self join in the query to get the organisation per person.

If each person can belong to multiple organisations then you would need to maintain the list of relationships in a separate table which has person_id and organisation_id columns and then join off this to produce the information you require.

EDIT

Based on the additional information provided it looks like you will need to implement the second method where relationships are stored in a separate table. This table should contain at least two columns which should both be foreign keys to the primary key in your contacts table. This way a contact can be related in anyway to another contact whether that be an organisation, a person, or any other object you class as a contact. Using this design you can link the two keys by joining the contact table from each column to get the information for both sides of the relationship.

I hope this helps you.


How to make Event (like a Party) Sources mutually exclusive? I'm designing an entity-relationship diagram (ERD) for a marketplace of events. An event can be created by a user, in which case the Event table must have owner_id or we pull it from an API in which case the Event table must have api_id.

However, it can only be one way or the other. So, an event can be created by a user or pulled from API, never both.

How do I express owner_id and api_id as mutually-exclusive in ER diagram and how will it translate in tables?

This is the ERD I have developed so far:

One possible solution to this is move the API entity up next to Admin, User, Biz User, and make a relationship User ISA (is a?) API.


Your JOIN is behaving perfectly normally.

If you have one airports row, and no matching contacts or runways, you would get something like this:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol |   NULL  |  NULL

If you had one airport, one contact, and one runway, you'd get:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R

However, when you've got multiple matching contacts and runways, and there's no connection between a contact and a runway, then each contact will get paired with each runway, as you've seen:

 Airport  | Contact | Runway
----------+---------+--------
 Schiphol | Ruppert |  28R
 Schiphol | Ruppert |  12L
 Schiphol | Ruppert |  14R
 Schiphol | Griffin |  28R
 Schiphol | Griffin |  12L
 Schiphol | Griffin |  14R

This is called a cross-product. You're getting double the rows you think you want because you're crossing 2 rows with 3 rows (2 * 3 = 6). If you had 7 contacts and 5 runways, you'd be getting 35 rows.

What your best solution is will depend on what you're DBMS is capable of.

Worst case scenario, run three queries: one with the airport results, one with the contacts results, and one with the runways results. Pull all three results sets into PHP. If you're bringing back results for more than one airport, you'd loop through the airports results, and (inside that loop) loop through the other two result sets to pull out the contacts and runways for the current airport.

Many DBMSes provide functions that would let you combine the rows for each airport's contacts or runways into XML or JSON. In that case, you'd have one main query, but you'd have two sub-queries within it to generate the two objects for the other tables. It's be something like (pseudo-code only!):

SELECT airport_name, airport_iata, airport_icao
      ,(SELECT <XML/JSON func>(first_name, last_name, email_address, phone_number)
          FROM contacts
         WHERE airport_id = airports.id
       ) as contacts_obj
      ,(SELECT <XML/JSON func>(designator, length, surface, landing_system)
          FROM runways
         WHERE airport_id = airports.id
       ) as runways_obj
  FROM airports
 WHERE airports.id = 1;

Again, pseudo-code only; you'll need to figure out the details of the function for your DBMS.

Barring an explicit XML or JSON function, most DBMSes have the ability to create string aggregates. This would work much like the function above, except more manually; you'd probably build a string for each row (first_name + '|' + last_name + '|' + ...), and use the aggregate function to combine the multiple contacts into a single string (with a different separator, of course).


If my Data, Log and Tempdb drives are on the same LUN, are they using 1 physical drive? My SQL Server Data, Log and TempDB resides on different logical drives.  But when I check the drive properties under Hardware tab, their location are located on the same Port, Bus, Target ID and LUN.  Does this mean they are physically residing on the same disk ?  How can I check LUN properties properly ?   We are using VM on SAN.

To expand on Aaron's comment of:

  A LUN on a SAN is usually made up of a number of drives. Your data
  will be striped across all of them in some configuration, e.g. RAID 5
  or RAID 10. You'll have to ask your SAN administrator for more
  details; we can't possibly tell you about your physical
  implementation.

Each SAN implements this differently. For example, some can define exact RAID groups, type, etc. Some you just put disks in and they create their own internal sets and configuration - you just tell it how big of a LUN you want and where to zone it. Some SANs will automatically tier your data and you may be on many different sets of drives. Others will yet again operate completely in cache and only flush to disk like a checkpoint operation would in SQL Server.

This also doesn't take into account anything in the fabric that may be acting as yet another intermediate tier such as SVCs.

Really, the question you should be asking is, "Is one LUN allowing me the throughput I require in order to properly run this instance of SQL Server and all of the associated databases."

If there is only one LUN, most drivers for the HBAs won't multipath. This may or may not matter depending on your fabric - but most of the time it helps. Additionally we don't know the queue depth of the HBAs (though not all implementations have such a configurable item) and don't know the typical profile of IO for that server.

Just some extra items to think about :)


How to find documents that is equal or includes today in its date range? Given the date, how can I find and return only documents that are in the range or equal to the start or end date in Mongo DB?

"startDate" : ISODate("2020-01-01T00:00:00.000+0000"),
"endDate" : ISODate("2020-01-01T00:00:00.000+0000"),

"startDate" : ISODate("2020-04-24T00:00:00.000+0000"),
"endDate" : ISODate("2020-05-23T00:00:00.000+0000"),
.........

Here it is the solution:

$and: [
        { "startDate": { $lte: new ISODate("2020-01-01T00:00:00.000+0000") } },
        { "endDate": { $gte: new ISODate("2020-01-01T00:00:00.000+0000") } }
]

Just use ISODate(...) (which is an alias for new Date(...))

You can skip $and, if you like:

{
   "startDate": { $lte: ISODate("2020-01-01T00:00:00.000+0000") } ,
   "endDate": { $gte: ISODate("2020-01-01T00:00:00.000+0000") }
}

A more dynamic and easier way would be to use moment.js library:

{
   startDate: { $lte: moment().startOf('day').toDate() },
   endDate: { $gte: moment().startOf('day').toDate() }
}


How do I write an SQL script to copy the securables, memberships and owned schemas of a current database user? How do I write an SQL script to copy the securables, memberships and owned schemas of a current database user?

I currently have to repeatedly restore databases on my local machine for development work. I then have to manually add database users to the master level login section so that I can allow my web app to authenticate with the database. I can see that you can right-click on the user -> Script User as -> Create To -> New Query editor window. The resulting SQL does not artificially mimic any of the securables, memberships, or owned schemas.

I already found that you can programmatically add a user to the database that uses the Master login information.

CREATE USER [user_name] FOR LOGIN [user_name] WITH DEFAULT_SCHEMA=[dbo]
GO

When you restore a database all the users and privilege assigned to users (including database role membership) from that database get restored at the destination server.  If you already have a login in the destination server with matching Server-Level Identification Number (SID) then you do not have to do anything.

If you do not have a login you will need to create one:

Copy login from source to destination using this or this.

You have have a login with different SID you can match those using solution from here.


Database design: Dividing multiple identical tables, good or bad? I am very new at SQL and databases in general. I only use them for the occasional homework so I haven't even tried to master them.

I have seats at a theater, the seats are divided into 4 main areas (A, B, C, D). Each area has the same number of rows and the same number of seats per row.

In my database, I'd like to have Row + SeatNumber as a compound primary key, and to have one table for each area.

Now, I don't yet know how I'll do my selects, but what I want to ask: If I do it this way, will my selects be doable ? I want to, for example, select an exact position within the theater (where I know the area, row and seat number).

Would the 4 tables be a hindrance ? Could you give an example of how such a "select" might look ?

P.S. This is my first time at the site, if the question does not belong here, please direct me to a more suitable site within stack exchange.

You should use one table for this, unless you need more stringent constraints.

  Each area has the same number of rows and the same number of seats per row.

Let's say there are 5 rows in every area, and 6 seats in every row. You'd want to use something along these lines.

create table seats (
  area char(1) not null check (area in ('A', 'B', 'C', 'D')),
  row integer not null check ( row between 1 and 5 ),
  seat integer not null check ( seat between 1 and 6 ),
  primary key (area, row, seat)
);

To select a single seat, put three values in the WHERE clause.

select *
from seats
where area = 'A' and
       row =  1   and
      seat =  2;

To use a table like this to model seat reservations, populate it with every possible area, row, and seat. Then set a foreign key reference to it.

create table reservations (
  performance_time datetime not null,
  party_name varchar(40) not null,
  area char(1) not null,
  row integer not null,
  seat integer not null,
  primary key (performance_time, party_name, area, row, seat),
  foreign key (area, row, seat) references seats (area, row, seat)
);

You can see all the seating for a performance with this query.

select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
       on r.area = s.area and
          r.row  = s.row  and
          r.seat = s.seat and
          r.performance_time = '2013-04-30 08:00 pm'

And you can get all the available seats for a performance with something along these lines.

with seating as (
  select s.area, s.row, s.seat, r.performance_time, r.party_name
  from seats s
  left join reservations r
         on r.area = s.area and
            r.row  = s.row  and
            r.seat = s.seat and
            r.performance_time = '2013-04-30 08:00 pm'
)
select *
from seating
where performance_time is null

By default, SQL Server will create a clustered index for a primary key constraint. You'll want to give careful thought to the order of columns in your primary key constraints, and consider adding other indexes as well. (Especially since your outputs will often need to be ordered by area, row, and seat.)


SQL Server 2008 Index What would be the best index(es) to add to the tables in order to make this query as fast as possible. The sd.incidents tables is about 2.5M records, problems about 8K, and changes about 120K.

select
  --bunch of fields
from
    sd.incidents a
    left join sd.problems b on a.pr_number = b.pr_number
    left join sd.problems c on b.pr_number = c.parent_pr_number and c.is_root_cause = 1
    left join sd.changes d on b.caused_by_change_number_clean = d.change_number
where
    a.severity in (1,2,3) and
    (a.pr_number is not null or a.parent_number is null)

If you have a really large number of fields in the SELECT and/or the fields are wide, you should make sure the JOIN fields are your clustered indexes as well.

Pulling a few dozen fields via a key lookup can be the most expensive part of a query like this.  Clustering on your JOIN keys forgoes that step.

The only real way to know is to run the query, check the execution plans, and see where your bottlenecks are.  For the WHERE clause you could make a filtered index that just includes the cluster key of that table.


UPDATE performance where no data changes If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update?

For example would there be any difference in execution speed between UPDATE 1 and UPDATE 2 in the following:

CREATE TABLE MyTable (ID int PRIMARY KEY, Value int);
INSERT INTO MyTable (ID, Value)
VALUES
    (1, 1),
    (2, 2),
    (3, 3);

-- UPDATE 1
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2
    AND Value <> 2;
SELECT @@ROWCOUNT;

-- UPDATE 2
UPDATE MyTable
SET
    Value = 2
WHERE
    ID = 2;
SELECT @@ROWCOUNT;

DROP TABLE MyTable;

The reason I ask is that I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. As such I used the UPDATE 2 form. If there is a performance benefit to using the UPDATE 1 form, is it possible to get the row count that I need somehow?

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state), is there any performance benefit in putting a check in the where clause to prevent the update?

There certainly could be as there is a slight performance difference due to UPDATE 1:

not actually updating any rows (hence nothing to write to disk, not even minimal log activity), and
taking out less restrictive locks than what are required for doing the actual update (hence better for concurrency) (Please see Update section towards the end)

However, how much of a difference there is would need to be measured by you on your system with your schema, and data, and system load. There are several factors that play into how much impact a non-updating UPDATE has:

the amount of contention on the table being updated
the number of rows being updated
if there are UPDATE Triggers on the table being updated (as noted by Mark in a comment on the Question). If you execute UPDATE TableName SET Field1 = Field1, then an Update Trigger will fire and indicate that the field was updated (if you check using either the UPDATE() or COLUMNS_UPDATED functions), and that the field in both INSERTED and DELETED tables are the same value.

Also, the following summary section is found in Paul White's article, The Impact of Non-Updating Updates (as noted by @spaghettidba in a comment on his answer):

  SQL Server contains a number of optimisations to avoid unnecessary logging or page flushing when processing an UPDATE operation that will not result in any change to the persistent database.

  Non-updating updates to a clustered table generally avoid extra logging and page flushing, unless a column that forms (part of) the cluster key is affected by the update operation.
  If any part of the cluster key is ‘updated’ to the same value, the operation is logged as if data had changed, and the affected pages are marked as dirty in the buffer pool.  This is a consequence of the conversion of the UPDATE to a delete-then-insert operation.
  Heap tables behave the same as clustered tables, except they do not have a cluster key to cause any extra logging or page flushing.  This remains the case even where a non-clustered primary key exists on the heap.  Non-updating updates to a heap therefore generally avoid the extra logging and flushing (but see below).
  Both heaps and clustered tables will suffer the extra logging and flushing for any row where a LOB column containing more than 8000 bytes of data is updated to the same value using any syntax other than ‘SET column_name = column_name’.
  Simply enabling either type of row versioning isolation level on a database always causes the extra logging and flushing.  This occurs regardless of the isolation level in effect for the update transaction.

Please keep in mind (especially if you don't follow the link to see Paul's full article), the following two items:

Non-updating updates still have some log activity, showing that a transaction is beginning and ending. It is just that no data modification happens (which is still a good savings).
As I stated above, you need to test on your system. Use the same research queries that Paul is using and see if you get the same results. I am seeing slightly different results on my system than what is shown in the article. Still no dirty pages to be written, but a little more log activity.

  ... I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. ... is it possible to get the row count that I need somehow?

Simplistically, if you are just dealing with a single row, you can do the following:

UPDATE MyTable
SET    Value = 2
WHERE  ID = 2
AND Value <> 2;

IF (@@ROWCOUNT = 0)
BEGIN
  IF (NOT EXISTS(
                 SELECT *
                 FROM   MyTable
                 WHERE  ID = 2 -- or Value = 2 depending on the scenario
                )
     )
  BEGIN
     INSERT INTO MyTable (ID, Value) -- or leave out ID if it is an IDENTITY
     VALUES (2, 2);
  END;
END;

For multiple rows, you can get the information needed to make that decision by using the OUTPUT clause. By capturing exactly what rows were updated, then you can narrow down the items to look up to know the difference between not updating rows that don't exist as opposed to not updating rows that exist but don't need the update.

I show the basic implementation in the following answer:

How to avoid using Merge query when upserting multiple data using xml parameter?

The method shown in that answer doesn't filter out rows that exist yet do not need to be updated. That portion could be added, but you would first need to show exactly where you are getting your dataset that you are merging into MyTable. Are they coming from a temporary table? A table-valued parameter (TVP)?

UPDATE 1:

I was finally able to do some testing and here is what I found regarding transaction log and locking. First, the schema for the table:

CREATE TABLE [dbo].[Test]
(
  [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED,
  [StringField] [varchar](500) NULL
);

Next, the test updating the field to the value that it already has:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117

Results:

-- Transaction Log (2 entries):
Operation
----------------------------
LOP_BEGIN_XACT
LOP_COMMIT_XACT

-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
8 - IX          6 - PAGE
5 - X           7 - KEY

Finally, the test that filters out the update due to the value not changing:

UPDATE rt
SET    rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A'
FROM   dbo.Test rt
WHERE  rt.ID = 4082117
AND    rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';

Results:

-- Transaction Log (0 entries):
Operation
----------------------------

-- SQL Profiler (3 Lock:Acquired events):
Mode            Type
--------------------------------------
8 - IX          5 - OBJECT
7 - IU          6 - PAGE
4 - U           7 - KEY

As you can see, nothing is written to the Transaction Log when filtering out the row, as opposed to the two entries marking the beginning and ending of the Transaction. And while it is true that those two entries are almost nothing, they are still something.

Also, the locking of the PAGE and KEY resources is less restrictive when filtering out the rows that haven't changed. If no other processes are interacting with this table then it is probably a non-issue (but how likely is that, really?). Keep in mind that that testing shown in any of the linked blogs (and even my testing) implicitly assumes that there is no contention on the table since it is never part of the tests. Saying that non-updating updates are so light-weight that it doesn't pay to do the filtering needs to be taken with a grain of salt since the testing has been done, more or less, in a vacuum. But in Production, this table is most likely not isolated. Of course, it could very well be that the little bit of logging and more restrictive locks don't translate into less efficiency. So the most reliable source of information to answer this question? SQL Server. Specifically: your SQL Server. It will show you which method is better for your system :-).

UPDATE 2:

If the operations in which the new value is the same as the current value (i.e. no update) out number the operations in which the new value is different and the update is necessary, then the following pattern might prove to be even better, especially if there is a lot of contention on the table. The idea is to do a simple SELECT first to get the current value. If you don't get a value then you have your answer regarding the INSERT. If you do have a value, you can do a simple IF and issue the UPDATE only if it is needed.

DECLARE @CurrentValue VARCHAR(500) = NULL,
        @NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A',
        @ID INT = 4082117;

SELECT @CurrentValue = rt.StringField
FROM   dbo.Test rt
WHERE  rt.ID = @ID;

IF (@CurrentValue IS NULL) -- if NULL is valid, use @@ROWCOUNT = 0
BEGIN
  -- row does not exist
  INSERT INTO dbo.Test (ID, StringField)
  VALUES (@ID, @NewValue);
END;
ELSE
BEGIN
  -- row exists, so check value to see if it is different
  IF (@CurrentValue <> @NewValue)
  BEGIN
    -- value is different, so do the update
    UPDATE rt
    SET    rt.StringField = @NewValue
    FROM   dbo.Test rt
    WHERE  rt.ID = @ID;
  END;
END;

Results:

-- Transaction Log (0 entries):
Operation
----------------------------

-- SQL Profiler (2 Lock:Acquired events):
Mode            Type
--------------------------------------
6 - IS          5 - OBJECT
6 - IS          6 - PAGE

So there are only 2 locks acquired instead of 3, and both of these locks are Intent Shared, not Intent eXclusive or Intent Update (Lock Compatibility). Keeping in mind that each lock acquired will also get released, each lock is really 2 operations, so this new method is a total of 4 operations instead of the 6 operations in the originally proposed method. Considering this operation is running once every 15 ms (approximately, as stated by the O.P.), that is about 66 times per second. So the original proposal amounts to 396 lock/unlock operations per second, while this new method amounts to only 264 lock/unlock operations per second of even lighter-weight locks. This is not a guarantee of awesome performance, but certainly worth testing :-).


How do I achieve this in subquery I have table A :

genre   collection
horror   20
action   50
rom-com  30
sci-fi   90

and table B:

MovieId   genre     performance
1         sci-fi     Hit
2         rom-com    Hit
3         sci-fi     Flop
4         rom-com    Hit
5         rom-com    Hit
6         horror     Flop
7         horror     Flop
8         sci-fi     Hit
9         rom-com    Hit

I am trying to get a count of hits per genre and also sum of box office collection of movies which were a Hit .

So I trying to get something like:

genre     hit_movies     total_collection
horror     0              0
action     0              0
rom-com    4              120
sci-fi     2              180

I am trying this by using a subquery:

select genre, sum(total_collection) as total_collection
    ,(select count(*) from  A  where genre = A.genre and A.performance = 'Hit') as hit_movies
    from
    (select A.genre as genre ,
        (select sum(B.collection) from B  where B.genre = A.genre and A.performance = 'Hit')
        as total_collection from A
    )x

group by 1

But I am not getting desired results. I am using postgresql. Any help is really appreciated.Thanks in advance.

SELECT tableA.genre,
       COUNT(tableB.genre) hit_movies,
       SUM(CASE WHEN tableB.genre IS NULL
                THEN 0
                ELSE tableA.collection
                END) total_collection
FROM tableA
LEFT JOIN tableB ON tableA.genre = tableB.genre AND tableB.performance = 'Hit'
GROUP BY tableA.genre

fiddle

Option proposed by a_horse_with_no_name (PostgreSQL-specific):

SELECT tableA.genre,
       COUNT(tableB.genre) hit_movies,
       SUM(tableA.collection) FILTER (WHERE tableB.genre IS NOT NULL)  total_collection
FROM tableA
LEFT JOIN tableB ON tableA.genre = tableB.genre AND tableB.performance = 'Hit'
GROUP BY tableA.genre

The difference - first query outputs zeroes for the genres without hits whereas second one outputs NULLs. May be changed by replacing zero with NULL (or by replace the whole CASE expression with CASE WHEN tableB.genre IS NOT NULL THEN tableA.collection END) in the first query or by wrapping the expression with COALESCE() in the second query.