Append to Media Vs Overwrite an existing media I have close to 2 TB Database PROD and the database backup size is too huge.

Everytime I take a backup it gets appended and when I check on it Microsoft sites says

  By appending another backup set to existing media, the previous
  contents of the media remain intact, and the new backup is written
  after the end of the last backup on the media.

What is the major use of this two options.

Append to Media Vs Overwrite an existing media

WITH NOINIT allows you to retain multiple backups in a single file.  WITH INIT makes SQL Server overwrite the contents of the backup file, if it contains an existing backup.  See the Microsoft Docs for more details about the various WITH options for BACKUP DATABASE.

Personally, I prefer to keep multiple files with unique names if I'm required to keep multiple backups.  Typically, I'd append the date to the name of each file so I can easily determine which backup files are now considered "out of date".  Whether you do that should be dictated by your recovery point objectives and recovery time objectives.

So my backup command would look like:

DECLARE @date date;
DECLARE @filename varchar(260);
SET @date = GETDATE();
SET @filename = 'F:\Backups\xyz\xyz-' + CONVERT(varchar(30), @date, 112) + '.bak';

BACKUP DATABASE [xyz]
TO DISK = @filename
WITH INIT;

Each day, a uniquely named backup file will be created in F:\Backups\xyz similar to:

  F:\Backups\xyz\xyz-20170411.bak

Appending to media can make restoring multiple log backups easier since you won't need to programmatically determine the file names during the restore process.


Efficiently computing aggregate functions over subqueries with incremental data I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a boolean indicating whether an order can be marked as completed after that event, but multiple complete scans can occur. Generally speaking however, the only scans I actually care about are the first scan, and the first completed scan.

I want to know the average and standard deviation of the percentage of orders with a given creation date that have received their first scan within x days of being created.

Schema

CREATE TABLE orders (
    id character varying(40) NOT NULL,
    date_created timestamp with time zone NOT NULL
);

ALTER TABLE ONLY orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

CREATE TABLE scan_events (
    id character varying(100) NOT NULL,
    order_id character varying(40) NOT NULL,
    "time" timestamp with time zone NOT NULL
);

CREATE INDEX scan_events_order_id_idx ON scan_events USING btree (order_id);

Desired Computation

For days_elapsed values ranging from 1 to 14 days, I want the means and standard deviations of:

the set of percentages of orders that received any scans within days_elapsed days of their orders.date_createdfrom each of the past 30 days (aka grouped by DATE(orders.date_created))
the set of percentage of orders that received a scan with completed = TRUE within days_elapsed days of their orders.date_created from each of the past 30 days (aka grouped by DATE(orders.date_created))

Ideally, the output would look something like this, but honestly, anything performant is fine.

output
----------------
days_elapsed
mean_scanned
stddev_scanned
mean_completed
stddev_completed

Current Progress

I have a query that will get me the counts per day (optionally with WHERE scan_events.completed IS TRUE to get the completed scan results):

SELECT DATE(orders.date_created),
    COUNT(DISTINCT orders.id) AS total,
    COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS scanned,
    COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND scan_events.completed AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS completed
FROM orders
LEFT JOIN scan_events ON orders.id = scan_events.order_id
WHERE orders.date_created BETWEEN '2015-07-01' AND '2015-07-31'
GROUP BY DATE(orders.date_created)
ORDER BY DATE(orders.date_created) ASC;

For days_elapsed = 1, this query is roughly what I imagine should work:

SELECT AVG(counts.scanned * 1.0 / counts.total) AS mean_scanned,
    STDDEV(counts.scanned * 1.0 / counts.total) AS stddev_scanned,
    AVG(counts.completed * 1.0 / counts.total) AS mean_completed,
    STDDEV(counts.completed * 1.0 / counts.total) AS stddev_completed
FROM (
    SELECT DATE(orders.date_created),
        COUNT(DISTINCT orders.id) AS total,
        COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS scanned,
        COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND scan_events.completed AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS completed
    FROM orders
    LEFT JOIN scan_events ON orders.id = scan_events.order_id
    WHERE orders.date_created BETWEEN '2015-07-01' AND '2015-07-31'
    GROUP BY DATE(orders.date_created)
) counts

The problem with this is that it's most certainly redoing work that it doesn't have to...

Things that I imagine we can take advantage of:

AVG and STDDEV ignore null values so we can do some CASE WHEN ... THEN ... END trickery
The sets that AVG and STDDEV are calculated over could maybe be built up incrementally as we increase days_elapsed by one

Any help would be much appreciated -- my SQL-fu is not up to snuff :(

The standard deviation can be calculated knowing the number of values, the sum of the values, the sum of the square of the values. Each of these can be updated incrementally as new values arrive and stored in a work table. The work table will look something like

DailyTotals (
  OrderDate,
  NumberOfValues,
  SumOfValues,
  SumOfSquareOfValues);

Since the work table is keyed by date, your desired fourteen day sliding window can be achieved. Since each value is a sum, summing again for each date's value is not a mathematical problem. Yes, there is a calculation at runtime. It is much lighter than the full standard deviation one, however.

When new values arrive the work table can updated synchronously (it's a 1-row update), or asynchronously or in batch depending on the application's appetite for stale data.


how to reduce mysql overhead? My machine has 2GB RAM, I have some cron job run every minute insert into some data.

My mysql always overhead. I think it is very frequently, if some cron job mysql insert run, 5-10 minutes, the db will become overhead. 12 hours later, the overhead data will increase into 1-2MB.

how to reduce mysql overhead? this is my my.cnf

key_buffer_size = 256M
key_buffer = 128M
back_log = 3000
max_allowed_packet = 4M
table_cache = 2048
table_open_cache = 512
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
thread_cache = 512
query_cache_size = 384M
set-variable =wait_timeout=60
thread_concurrency = 4
log-slow-queries = slow.log
long_query_time = 1
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 384M
ft_min_word_len=3
# log-bin=mysql-bin

here is some print in phpmyadmin.

Table    Action  Records  Type    Collation    Size       Overhead
table_nam        101,399  MyISAM  utf8_unicode_ci  112.7 MiB  1.2 MiB

You use mytop and mysqltuner to analyze using mysql resources (RAM and CPU).

Too enable the option to log slow queries:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

And check out this post about ntpd service:

https://stackoverflow.com/questions/1282232/mysql-high-cpu-usage

I have a dedicated server for a high rate of transactions:

max_allowed_packet=16M
key_buffer_size=8M
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=512M
join_buffer_size=40M
table_open_cache=1024
query_cache_size=40M
table_definition_cache=256
innodb_additional_mem_pool_size=10M
key_buffer_size=16M
max_allowed_packet=32M
max_connections = 300
query_cache_limit = 10M
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

Regards.


Writing SQL injection proof dynamic DDL in Oracle I have a need to write dynamic DDL statements such as CREATE USER ?. I know that I can use EXECUTE IMMEDIATE to do this, but so far I have found no way to incorporate dynamic parameters without simply concatenating strings, which leaves me open to SQL injection.

Coming from PostgreSQL I an used to being able to quote identifiers or using FORMAT to safely put identifiers into a formatted string that can then be executed. Is there anything like this in Oracle (particularly 12c)? If not, how does one perform such dynamic SQL safely?

DBMS_ASSERT might be what you're looking for:

╔═════════════════════════════╦══════════════════════════════════════════════════════════════════════════════════════════════════╗
║         Subprogram          ║                                           Description                                            ║
╠═════════════════════════════╬══════════════════════════════════════════════════════════════════════════════════════════════════╣
║ ENQUOTE_LITERAL Function    ║ Enquotes a string literal                                                                        ║
║ ENQUOTE_NAME Function       ║ Encloses a name in double quotes                                                                 ║
║ NOOP Functions              ║ Returns the value without any checking                                                           ║
║ QUALIFIED_SQL_NAME Function ║ Verifies that the input string is a qualified SQL name                                           ║
║ SCHEMA_NAME Function        ║ Verifies that the input string is an existing schema name                                        ║
║ SIMPLE_SQL_NAME Function    ║ Verifies that the input string is a simple SQL name                                              ║
║ SQL_OBJECT_NAME Function    ║ Verifies that the input parameter string is a qualified SQL identifier of an existing SQL object ║
╚═════════════════════════════╩══════════════════════════════════════════════════════════════════════════════════════════════════╝

That same page also links to some examples on how you can avoid SQL injection.


Do I need different linked server remote users? I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup.

There may be multiple local "machines"/databases that all are linked to the same server.

I have a script (query) that creates the linked server and the remote user.

Do I need to create a new user for each machine or can all machines use the save remote user?

The script looks like this:

USE [master]
GO
/****** Object:  LinkedServer [FOODB001]    Script Date: 2015-04-16 21:36:49 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FOODB001',
                                   @srvproduct = N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'FOODB001',
                                     @useself = N'False',
                                     @locallogin = NULL,
                                     @rmtuser = N'MyUser',
                                     @rmtpassword = 'password'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'collation compatible',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'data access',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'dist',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'pub',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'rpc',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'rpc out',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'sub',
                                @optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'connect timeout',
                                @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'collation name',
                                @optvalue = NULL
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'lazy schema validation',
                                @optvalue = N'false'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'query timeout',
                                @optvalue = N'0'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'use remote collation',
                                @optvalue = N'true'
GO
EXEC master.dbo.sp_serveroption @server = N'FOODB001',
                                @optname = N'remote proc transaction promotion',
                                @optvalue = N'true'
GO

The technical answer

To my knowledge, there's no problem in using the same user for all "client" machines. The same user account can connect to a SQL Server as many times as the licensing of that SQL Server allows.

The security perspective

However, you may want to review the security aspects of this setup - do all the remote machines have the same access requirements, or should they have different users for security reasons?

Could you perhaps even use windows/AD authentication? Remember that when you hard-code an account into the linked server, pretty much anyone who can log on to the remote server will gain the privileges on the "central" server that you've given to the user account defined on the linked server, which may lead to a privilege escalation.

Tread carefully with linked servers and permissions, is all I'm saying.


Oracle - capture row commit order in a table Is there a way to capture the order in which rows get committed in a table?

Our application has clients that query the table based on a sequence_id column of the table, that is supposed to reflect the commit order, regularly.
The largest sequence_id received in the previous attempt would be used as the filter to get the new rows.

I want to capture the order in which rows became visible to a read operation (select query). This number will be used by the read operation to decide 'upto what point it received data last time' so it will ask for sequence_id > max_sequence_id recevied_last_time to get the new data.

How do we implement the logic of inserting rows with the correct sequence_id to reflect the order of commits? Using the sequence does not seems to guarantee this.

How do rows become available to a select query? I believe it is after a row is committed, not after a row is inserted. Our tests show that order of rows become available for a read operation is different to the sequence values as the sequence.nextval is calculated at the point of insert and not at the point of commit.

It isn't possible to determine the order in which rows are committed.  Either a sequence or a timestamp can allow you to determine the order in which rows are inserted.  But neither will tell you the order in which those transactions were committed.

Since you have a process that is trying subscribe to changes in a table, however, you have a few options that don't require you to identify this order.  You can use Oracle Change Data Capture (CDC) and then have the reading process register as a subscriber.  Or you can use Streams to publish changes to the table and have the reader process act as a custom apply process.

Both CDC and Streams will send the subscriber the data in commit order.  The CDC change table will also store the commit SCN and the commit timestamp if you really want them (though I suspect you don't really care so long as the reader is sent every change in order).

Normal CDC involves the reader process controlling a window that shows one change set at a time, consuming that change set, and then advancing the window.  The reader consumes data in commit order and never needs to know specifically what the SCN of a change is.  You can hit the change tables directly as well to get the actual SCN if you want to complicate the reader's pull process.

Your reader process can hit the change tables, get the SCN from the change table, move it to the sequence_id column of your existing table, and then do whatever else you need to do with the data in the change table to move it into your existing architecture.  It's a bit messy but doable.  Your reader process could also be a Streams consumer and do the same thing (grabbing the SCN from the Streams change record rather than the CDC change table).


PostgreSQL query takes a long time (as it's not using the index) This query takes a long time, as I guess it's doing a full-table scan. It works ok in Oracle, just not in Postgres 12.
select count(*)
FROM EVENT
WHERE PK_EVENT > (select CHECKPOINT1_PKEVENT FROM SYSTEM_CONTROL)

If however, I run this one, it comes back in seconds.
select count(*)
FROM EVENT
WHERE PK_EVENT > (1000755073)

Both the PK_EVENT & CHECKPOINT1_PKEVENT columns are defined as "int8", as they could get very large. EVENT.PK_EVENT is the primary key. I've tried re-arranging the query, but so far nothing works. The count(*) only results in 2537 rows i.e. the number of rows generated today on our test Pg database.
Query plans :
using system_control:-
Finalize Aggregate  (cost=2909961.80..2909961.81 rows=1 width=8)
  InitPlan 1 (returns $0)
    ->  Seq Scan on system_control  (cost=0.00..1.01 rows=1 width=8)
  ->  Gather  (cost=2909960.58..2909960.79 rows=2 width=8)
        Workers Planned: 2
        Params Evaluated: $0
        ->  Partial Aggregate  (cost=2908960.58..2908960.59 rows=1 width=8)
              ->  Parallel Seq Scan on event  (cost=0.00..2866882.04 rows=16831415 width=0)
                    Filter: (pk_event > $0)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

using hardcoded value:-
Aggregate  (cost=11.42..11.43 rows=1 width=8)
  ->  Index Only Scan using event_pkey on event  (cost=0.57..11.36 rows=25 width=0)
        Index Cond: (pk_event > 1000755073)

The entire query is planned up front, and at the time of planning it doesn't know what value will be found in CHECKPOINT1_PKEVENT.  It makes the generic assumption that the inequality will match 1/3 of the rows, which is obviously quite wrong.  When faced with this situation, I usually just have the client software run the queries separately, stuffing the result of one into a parameter for the other, assuming I can tolerate the prospect that the value may have changed between the two executions.
That said, it should probably be doing an index only scan anyway.  Do you have a very high setting for random_page_cost?  What are you other planner settings?  Has the table EVENT been vacuumed recently?


Transferring full database to another machine oracle 10.2 xe We are having oracle 10.2 xe working on windows xp sp3(32bit). We need cloning/copying full database to windows server2008r2 machine(64bit). Any help.

You should be able to do that with an RMAN backup. You need to see what kind of backup Oracle XE supports. But you should be able to shutdown the database, startup mount, then do an RMAN backup making sure to tell Oracle where to send the backup, make sure that you include the control file with the backup. Then copy the backup over to the 64 bit server and inside RMAN use "CATALOG START WITH 'D:\backups';" or where ever the backup is. You need to copy over the init/spfile, create a service on the new server, startup nomount the new service, in rman, connect to the new database service, restore the control file, make sure that the control file restored, then restore the database. You will not need to recover the database since it was done while the database was not open.

You then need to find and run the script in %ORACLE_HOME%/rdbms/admin that will convert the database to 64 bit then upgrade the database to 64 bit. It would help if you have a 10g 64 bit home on the new server, but I was not able to install 10g on Windows 2008, it just won't install, so you probably need to run the upgrade manually. You can also upgrade as a 32 bit database on XP and then migrate the database as 11g.

Another option is to create a new 11g 64 bit database on the new server and use data pump to migrate it. Regardless you will need to test the process and probably try it more than once.


Invoke --data-checksums using pg_createcluster on Ubuntu 14.04 How do you pass the initdb argument for data checksums to pg_createcluster when creating the initial cluster?

Passing the argument as per initdb fails for me:

pg_createcluster --data-checksums --datadir=foo/cluster_name 9.5 cluster_name

After further investigation, to specify the default initdb arguments used in pg_createcluster, the below was added in /etc/postgresql-common/createcluster.conf:

initdb_options = '--data-checksums'

Then pg_createcluster was run as normal and show data_checksums displays:

data_checksums
----------------
on
(1 row)


How can I properly use a trigger inside of stored procedure to validate an email? -- Create a trigger inside of stored procedure that will enforce that Faculty's Email has to be
-- used in a standard way.

USE College ;

DROP PROCEDURE IF EXISTS Faculty_Before_Insert;

DELIMITER $$

CREATE PROCEDURE Faculty_Before_Insert()
BEGIN

        CREATE TRIGGER Review_Email_Before
        BEFORE INSERT ON Faculty
        FOR EACH ROW

      BEGIN
            DECLARE Email varchar(255);

        IF Email != '^\w+(\.\w+)*+@\w+(\.\w+)+$'  THEN
          SIGNAL SQLSTATE  'HY000'
             SET MESSAGE_TEXT= 'This email doesn\'t match FirstLast@college.edu';
        END IF ;

      END;

END
$$

DELIMITER ;

INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID)
              VALUES('Stanley', 'Mike', 'bad@bad.bad', current_date(), 20000, 1);

SELECT
Faculty.LastName,
Faculty.FirstName,
Faculty.Email
 FROM  Faculty
 WHERE ID= last_insert_id();

I am working in MySQL Workbench 6.3 CE and trying to use a trigger inside of a stored procedure to see if it will detect if my email format is not in the right format I specified in the message text error, but I am having trouble because it says a trigger can't be created inside of stored procedure. How else can I accomplish what I need to accomplish. This is a BEFORE TRIGGER since it will check the email validation before any rows are inserted into my Faculty table. What I am I doing wrong? Here is my SQL code below:

Triggers are created on actual tables, and are automatically executed when an action is performed on the table in question, they're not actually created within procedures.

So, given the table:

create table Faculty
(
LastName varchar(100),
FirstName varchar(100),
Email varchar(100),
HireDate date,
Salary integer,
DepartmentID integer
);

... your trigger code, once removed from the procedure, does work:

mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER Review_Email_Before
    ->         BEFORE INSERT ON Faculty
    ->         FOR EACH ROW
    ->       BEGIN
    ->         IF new.Email != '^\w+(\.\w+)*+@\w+(\.\w+)+$'
    ->         THEN
    ->           SIGNAL SQLSTATE  'HY000'
    ->              SET MESSAGE_TEXT= 'This email doesn\'t match FirstLast@college.edu';
    ->         END IF ;
    -> END;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> INSERT INTO Faculty(LastName, FirstName, Email, HireDate, Salary, DepartmentID)
    ->               VALUES('Stanley', 'Mike', 'bad@bad.bad', current_date(), 20000, 1);
ERROR 1644 (HY000): This email doesn't match FirstLast@college.edu
mysql>


Substantial difference between database size under MySQL and actual size on disk When I check the size of my databases under MySQL I get this:

MariaDB [(none)]> SELECT table_schema "Data Base Name",  sum( data_length + index_length ) / 1024 /  1024 "Data Base Size in MB",  sum( data_free )/ 1024 / 1024 "Free Space in MB"  FROM information_schema.TABLES  GROUP BY table_schema;
+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| alfresco           |         245.75000000 |      34.00000000 |
| drupal             |         892.15625000 |     216.00000000 |
+--------------------+----------------------+------------------+

When I check the size on the disk I get this:

$ sudo du -h --max-depth=1 /var/lib/mysql/
317M    /var/lib/mysql/alfresco
1.4G    /var/lib/mysql/drupal

If I combine both used and free space given by Maria DB and compare it with disk figures I have the following:

alfresco: DB=279MB  DISK=317MB (+14%)
drupal: DB=1100MB   DISK=1433MB (+30%)

Q: Is it normal to have that much overhead on the disk / Is there anything I can do to reduce it?

FYI I thought running a mysql optimize would help (using that command), it did reduce the size of the databases, but didn't change the size of files on the disk.

Additional info:

server:             ubuntu server 10.04 LTS
DB server:          MariaDB
DB engine:          InnoDB v10 (for all tables)
Table collation:    utf8_general_ci
Nb Drupal tables:   416  (0.80MB overhead per table)
Nb Alfresco tables: 84   (0.45MB overhead per table)

If you are using InnoDB tables, the size of your ibdata files will grow over time. So, if you issue DELETE statement, your database size will reduce, but the ibdata file will remain the same (not reduce).

If you are not using innodb_file_per_table option, the only way to reclaim the space is by dumping the database and restoring from the dumpfile.

However, if you are using innodb_file_per_table, you can issue an

ALTER TABLE foo ENGINE=InnoDB;

on tables that grow too large to reclaim the disk space.


Move local SQL Server DB to hosted SQL Server I have a SQL Server database here that contains the info for the Gemini Issue Tracking software. It is running on a Windows XP system and I have SQL Server Management Studio (SSMS) on that system. I can successfully back it up to a xxx.bak file.

Now I want to bring that database up on an instance of SQL Server at a paid hosting site. I do not have shell access. I can log SSMS into the hosted database. That one uses SQL Server username/password foo\bar. My local database uses Windows Authentication HOST\USERNAME. I cannot replicate that Windows user onto the hosted database. I can transfer the xxx.bak backup to the hosted site through their Plesk control panel and I see it in my "files". I found out the physical path to it C:\Inetpub\vhosts\xxx.yy.com\, so I can construct a TRANSACT SQL statement

RESTORE DATABASE new_db_name
FROM DISK="C:\Inetpub\vhosts\xxx.yy.com\xxx.bak"
WITH RESTORE

which forces it to wipe out the one they created with the info in the .bak.

  Msg 3110, Level 14, State 1, Line 1
  User does not have permission to RESTORE database 'new_db_name'.

Also the original backup is from a database with a different name. So I have a source database with Windows Login and one name, and I want to restore the data to another with a different SQL Server username/password and a different name (the first part of the name is fixed).

I am certain that SSMS is logged in under the SQL username/password as I can see the tables in the empty database. My SQL Server user has read/write permissions.

What  issues am I going to face assuming I can figure out how to get the RESTORE DATABASE to work? What is the best way to do this?

First, you need to find out from the hosted provider the username/password of a user with either the sysadmin fixed role, or at minimum CREATE DATABASE rights (intermediate: dbcreator fixed role).  This will let you run the restore, if SQL can access the .bak files.

Second, you need to change ALL the passwords for ALL the users on your hosted SQL Server.  Long (25+ character) random passwords stored in KeePass or similar are ideal.

Next, you need to remove all domain users from the restored database; they're worthless now.  You need to change your apps - whatever uses the database - to use the new SQL user and password rather than whatever was previously used.

Next, configure backups on the hosted site.

Next, RESTORE a backup from the hosted site - make sure it works!!!

Optionally, configure the hosted instance to force SSL connections; talk to your hosting provider about certificate generation and installation, then use sys.dm_exec_connections.encrypt to validate (it does take a service restart).

Optionally, figure out how to pull backups from the hosted site local, in case the hosted site has serious issues (possibly legal issues, for instance) and you lose everything there.


Find data size of table for several identical databases on a instance I have several identical databases with the same schema/table structure within a instance. I need to find the size of one table that they all have. For example, I have 30 databases on a instance and all the databases have a "personal information" table. I need to find a way to query the size of "personal information" table that they all have, instead of going individually to each database.

Is there a way to do this?

Similar to SP_Spaceused for size "Data Column"

DECLARE @table  SYSNAME = N'Personal Information',
        @schema SYSNAME = N'dbo';

CREATE TABLE #sz
(
  dbname     NVARCHAR(255),
  fullname   NVARCHAR(768), [rows] SYSNAME,
  reserved   SYSNAME,       [data] SYSNAME,
  index_size SYSNAME,       unused SYSNAME
);

DECLARE @sql NVARCHAR(MAX) = N'DECLARE @t NVARCHAR(512);';

SELECT @sql += N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
    + N'.sys.tables AS t INNER JOIN ' + QUOTENAME(name)
    + N'.sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE t.name = @table AND s.name = @schema)
    BEGIN
      SET @t = N''' + QUOTENAME(name) + N'.'' + QUOTENAME(@schema)
        + N''.'' + QUOTENAME(@table); INSERT #sz(fullname, [rows],
          reserved, [data], index_size, unused) EXEC '
          + QUOTENAME(name) + N'.sys.sp_spaceused @t; UPDATE #sz
            SET dbname = N''' + name + N''' WHERE dbname IS NULL;
    END '
FROM sys.databases
WHERE database_id > 4 AND [state] = 0 AND is_read_only = 0;

EXEC sys.sp_executesql @sql, N'@table SYSNAME, @schema SYSNAME', @table, @schema;

SELECT [database] = dbname,
  [schema] = @schema, [table] = @table,
  [rows], reserved, [data], index_size, unused
FROM #sz;

DROP TABLE #sz;


Finding exact statement causing error 3930 Lately I've been seeing errors in the SQL Server Log.

  Code:3930 Description:"The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

Using SQL Profiler, how can I find the exact statement that is causing this error? Which column in SQL Profiler fills the "Message" column in the SQL Error log? Unfortunately, we are only getting this in production, so I will need to run a trace there with the lightest weight trace possible. What do you folks recommend to capture the offending code?

In Profiler, click "show all events" and go to the Errors and Warnings listing.  You should be able to check Exception and User Error Message.  The Exception class will show you the actual error and User Error Message will show you the message displayed (e.g., "Incorrect syntax near ..." or whatnot).

You mention a message going into the error log; there's also an ErrorLog event in the Error Message category, so it wouldn't hurt to include that as well.

You would need to include the SQL:BatchStarting and possibly SQL:BatchCompleted as well to get the command being run at that time.

You might also look at Remus Rusanu's answer to an earlier question of this nature to see if that might help solve the problem.


SQL Server Memory Issues We have a SQL server that appears to be leaking memory.  Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues.

The SQL server houses 6 small (average than 50MB) databases and one that is a little bit larger (about 2GB).

The server is currently a virtual server inside VMware 5.1.  It has very light average workload (~1% cpu, <10 batch requests / sec, < .5 mb/s IO typical; spikes push to ~10% cpu, 300 batch request / sec, 5 mb/s IO).

Currently, it has 8GB allocated to the VM.  Of this, 4GB is set to the maximum server memory in SQL server.  The SQL server process, however, uses significantly more than that - usually around 6GB within 12 hours of a restart.

Apart from its primarily read workload, the server runs many SSIS packages to get data in and out to other systems within the corporate structure.  This utilizes MySQL via ODBC (5.2.6 drivers installed) to connect to an internal MySQL cluster and ConnX to interface with OpenVMS systems.

OS: Windows 2012
SQL: 2012 Enterprise
VM Settings: 4 CPU cores, 8GB, separate virtual volumes for OS, Data, and Logs.

Questions:
1) Any ideas on why the SQL Server process would not be following the limit set on the server?
2) Does anyone know a good way of profiling memory usage within SQL server?  We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).

You've got a couple of questions in here:

1) Any ideas on why the SQL Server process would not be following the limit set on the server?

Max server memory refers to the buffer pool, but not everything that the server uses memory for. For example, SSIS isn't part of the SQL Server engine - it's another application that just happens to come free in the box with SQL Server. (Same thing with SSAS and SSRS.) Max server memory has no impact on SSIS. This is one of the reasons you'll often hear recommendations that you should separate SSIS onto its own instance - especially if you're using SQL Server Enterprise in virtualization. At $7k USD per core, you're talking about $28k worth of licensing here, and you're working with something like $500 of RAM.

2) Does anyone know a good way of profiling memory usage within SQL server? We can see that page life expectancy is dropping and more memory is being allocated to the process, but is there any way to see what SQL is allocating the memory to (i.e. procedure cache, cached results, etc).

Yes, inside the SQL Server engine, you can use DMVs like sys.dm_exec_memory_clerks. If you're using the free Opserver tool from the StackExchange guys for SQL Server monitoring, look at the memory clerks listing. If not, you can start with the memory clerks query it uses.

However, outside of the SQL Server engine - like with your SSIS packages - SQL Server DMVs can't help because this is happening out of process. You'll need to use conventional systems administration tools to do process monitoring and watch which processes use RAM.


Can postgres make use of both GIN and BTREE indexes in a single query? Let's say I have GIN index on column A that allows me to filter table fast on that column by appropriate WHERE condition which would be slow without that index. However I would like also to order filtered rows by using ORDER BY statement on, let's say column B. Ordering without index is slow, so I use BTREE index on column B. The problem is that pg don't want to use both indexes - if both WHERE cond on column A and ORDER BY B are present in query, pg uses only GIN index. When I remove WHERE cond from query, then it uses BTREE index on col B.

What I can do to force pg to use both indexes, one to filter rows and second one to order them?

To answer your title question, PostgreSQL can make use of multiple indexes for a "bitmap" scan, but only with Boolean logic.  It can't use one to order and the other to filter within the same table scan.  It is an interesting idea, though.


MySQL restore old backup over new data We have a MySQL backup. Did some changes on our database and some data was lost. In the mean time new records were added.

We have to restore the mysql backup over our latest data.
Some of the tables have auto increment columns which means that we will end up with the same primary key but different values in some tables. The majority of the tables are from a many-to-many or one-to-many relationships.

What is the best approach to resync the same schema between the backup and the current data?

Load your data in another database.
Run LEFT JOINs one way, then the other way, to see what was deleted/new.
Run a JOIN to see if the rows that exist in both are the same; fix if necessary.
Run a multi-table DELETE to get rid of the the appropriate rows.
Run INSERT...SELECT..LEFT JOIN to copy the missing rows in


how to get table wise number of deadlocks in sql server? I am experiencing deadlocks in Sql Server 2014.

I am aware that we can get total number of deadlocks using performance counters in Sql Server Ref: Number of Deadlocks/sec from https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-locks-object

I would like to know if there is a way to get deadlock count per table?

This is pretty easy to do using the xml_deadlock_report in the system_health Extended Event session.

IF OBJECT_ID('tempdb..[#DeadlockData]') IS NOT NULL
    DROP TABLE #DeadlockData;

WITH xml
AS ( SELECT CAST(event_data AS XML) AS XMLDATA
     FROM   sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) )
SELECT xml.XMLDATA
INTO   #DeadlockData
FROM   xml
WHERE  xml.XMLDATA.value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report';

WITH death_indeed
AS ( SELECT ca.x.value('@objectname', 'VARCHAR(256)') AS object_name
     FROM   #DeadlockData AS dd
     CROSS APPLY dd.XMLDATA.nodes('//resource-list/*') AS ca(x) )
SELECT   death_indeed.object_name, COUNT_BIG(*) AS deaths
FROM     death_indeed
GROUP BY death_indeed.object_name

After creating some different deadlocks, I get this back:

object_name                deaths
StackOverflow.dbo.Users    9
StackOverflow.dbo.Posts    9


Error handling in container procedures I'm aware of and use the techniques in this question.

In my environment we have a lot of container procedures that call subprocedures, that may call other procedures, ad infinitum.

For me personally, I normally use TRY/CATCH in each level of the procedures.  This will sometimes result in multiple duplicate error messages but it's clear what happened and in what proc.

I have some coworkers who take a different approach.  Namely:
- Have each stored proc issue a return code
- Evaluate the return code for each stored proc after execution
- Raiserror in the outermost proc if a bad return code is received

I believe they do this so they can get the actual line number from the erroring procedure (instead of having it return the line number of the RAISERROR in the CATCH block).

To me this seems like it would be harder to implement and maintain, but I'm not an expert on SQL Server error handling, so I thought I would bring it up here.

Is there any material advantage to either method for handling errors in container procedures?

I use this pattern proposed in Exception Handling and Nexted Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end

This not only raises, but also can rollback to savepoint, if possible. This is very convenient in batch processing, the batch is not lost if a row has an error.

I never ever use return codes in stored procs. Returns codes beg to be ignored. I acknowledge the problem of nested errors as an annoyance, but with SQL Serevr 2012 you have the simple THROW; (no other arguments) to re-raise the original exception.


check linked servers database status I want to find out if all SQL databases registered in the database XXX on the YYY instance are available on the respective linked server and if they are online or offline.
I have the below Code, however when i run it, it Returns NULL values. What am I doing wrong?

DECLARE @STR_DBServer NVARCHAR(128)
DECLARE @STR_DBName NVARCHAR(128)
DECLARE @state_desc NVARCHAR(10)
DECLARE @table table (Server nvarchar(128), Datenbank nvarchar(128), Status nvarchar(10))

DECLARE CUR1 CURSOR FOR
        SELECT
        STR_DBServer , STR_DBName
        FROM   xxx
        where int_systemID = 1
        order by    STR_DBServer

OPEN CUR1
FETCH NEXT FROM CUR1 INTO @STR_DBServer, @STR_DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    begin try

        exec sys.sp_testlinkedserver @STR_DBServer
        If Exists ( select s.name,
        m.state_desc from master.sys.databases m, master.sys.servers s
        where is_linked= 1)
        insert into @table (Status)
        values
        (@state_desc)

    end try

    begin catch

          print 'Keine verbindung möglich mit '  + '' + @STR_DBName + ' auf ' + @STR_DBServer

    end catch

FETCH NEXT FROM CUR1 INTO @STR_DBServer, @STR_DBName
END
CLOSE CUR1
DEALLOCATE CUR1
select * from @table

You're getting NULL mainly because of this insert insert into @table (Status) values (@state_desc) since you declared the variable @state_desc and never set any value to it.  Try this instead:

DECLARE @STR_DBServer sysname;
DECLARE @SQLString nvarchar(500);
DECLARE @complete_catalog TABLE(server sysname, database_name sysname, state_desc nvarchar(120));
DECLARE @remote_databases TABLE(name sysname, state_desc nvarchar(120));

--Lists all linked servers on the current instance (except SQL Server 2000)
DECLARE LinkedServersCur CURSOR FOR
    SELECT name FROM master.sys.servers WHERE is_linked = 1 and provider <> N'MSDASQL';

OPEN LinkedServersCur;
FETCH NEXT FROM LinkedServersCur INTO @STR_DBServer;

WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SQLString = N'SELECT m.name, m.state_desc FROM [' + CONVERT(nvarchar(100), @STR_DBServer)+ '].master.sys.databases m';
        INSERT INTO @remote_databases EXECUTE sp_executesql @SQLString;

        INSERT INTO @complete_catalog(server, database_name, state_desc)
            SELECT @STR_DBServer, name, state_desc FROM @remote_databases;

        DELETE FROM @remote_databases;
        FETCH NEXT FROM LinkedServersCur INTO @STR_DBServer;

    END
CLOSE LinkedServersCur;
DEALLOCATE LinkedServersCur;

SELECT server, database_name, state_desc FROM @complete_catalog;

You can compare the result of this query with your local table DBConfig.dbo.T_Datenbanken if you wish.


Why does ORDER BY not belong in a View? I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)

I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the SELECT statement querying the view.

But being relatively new to practical SQL and the usages of views, I would like to understand why this is done so by design. If I've followed the history correctly, this was once possible and was explicitly removed from SQL Server 2008 and so on (don't quote me on the exact version).

However, the best reason I can come up with as to why Microsoft removed this feature is because "a view is an unsorted collection of data".

I am assuming there is a good, logical reason as to why a View should be unsorted. Why can't a view just be a flattened out collection of data? Why specifically un-sorted?  It doesn't seem that hard to come up with situations where (at least to me / IMHO) it seems perfectly intuitive to have a sorted view.

(Indexed views aside, of course.)

A view is not materialized - the data isn't stored, so how could it be sorted? A view is kind of like a stored procedure that just contains a SELECT with no parameters... it doesn't hold data, it just holds the definition of the query. Since different references to the view could need data sorted in different ways, the way that you do this - just like selecting from a table, which is also an unsorted collection of rows, by definition - is to include the order by on the outer query.

Also to give a little insight into the history. You could never put ORDER BY in a view, without also including TOP. And in this case the ORDER BY dictated which rows were included by TOP, not how they would be presented. It just so happened that in SQL Server 2000, if TOP was 100 PERCENT or {some number >= number of rows in the table}, the optimizer was fairly simplistic and it ended up producing a plan with a sort that matched the TOP/ORDER BY. But this behavior was never guaranteed or documented - it was just relied upon based on observation, which is a bad habit. When SQL Server 2005 came out, this behavior started "breaking" because of changes in the optimizer that led to different plans and operators being used - among other things, the TOP / ORDER BY would be ignored completely if it was TOP 100 PERCENT. Some customers complained about this so loudly that Microsoft issued a trace flag to reinstate the old behavior. I'm not going to tell you what the flag is because I don't want you to use it and I want to make sure that the intent is correct - if you want a predictable sort order, use ORDER BY on the outer query.

To summarize and just as much to clarify a point you made: Microsoft didn't remove anything. They made the product better, and as a side effect this undocumented, non-guaranteed behavior became less reliable. Overall, I think the product is better for it.


How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE? I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t ....

Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, and I update N rows in a, will that cause the trigger to be called once, or N times?

Put differently, are changes to a table cascaded by an FK constraint more like a single UPDATE, or more like a series of UPDATEs?

Foreign key constraints are currently implemented with special internal triggers. All of them are run FOR EACH ROW.

Note that these are implementation details that can change, so don't rely on it. But the basics have not changed over the last couple of major versions, so major changes are unlikely.

I ran a quick test with a simple FK constraint from tbl to tbltype. A simple FK is implemented with four simple internal triggers FOR EACH ROW in my test on pg 9.4.
Here is a quick rundown on how to investigate:

SELECT oid  -- 74791
FROM   pg_constraint
WHERE  conrelid = 'tbl'::regclass
AND    contype = 'f';

SELECT objid, classid::regclass  -- 74792,74793,74794,74795 / 'pg_trigger'
FROM   pg_depend
WHERE  refobjid = 74791
AND   deptype = 'i'

SELECT tgrelid::regclass, tgname, tgfoid, tgtype FROM pg_trigger
WHERE  oid IN (74792,74793,74794,74795) ORDER BY tgfoid;

'tbl'    ;'RI_ConstraintTrigger_c_74794';1644;5
'tbl'    ;'RI_ConstraintTrigger_c_74795';1645;17
'tbltype';'RI_ConstraintTrigger_a_74792';1654;9
'tbltype';'RI_ConstraintTrigger_a_74793';1655;17

SELECT oid, proname FROM pg_proc
WHERE oid IN (1654,1655,1644,1645);

1644;'RI_FKey_check_ins'
1645;'RI_FKey_check_upd'
1654;'RI_FKey_noaction_del'
1655;'RI_FKey_noaction_upd'

Two internal "noaction" triggers on tbltype.
Two internal "check" triggers on tbl.
All of them are run FOR EACH ROW, as indicated by odd numbers in tgtype.

The 2 bytes of the Postgres tgtype  smallint represent an int16 in C source code where the least significant bit encodes TRIGGER_TYPE_ROW. Detailed explanation here:

Meanings of bits in trigger type field (tgtype) of Postgres pg_trigger

You can easily test this with a pair of identical triggers where you only change FOR ROW / STATEMENT ...


Take 5 minute averages from second data I want to preface this post by saying that I am a complete newbie with SQL and any help will be highly appreciated.

For some project I am working with high frequency stock data, below is a small sample of what it looks like in the SQLite database

SYMBOL  DATE        TIME        PRICE
AAPL    19990602    15:59:54    46,50000
AAPL    19990602    15:59:59    46,56250
AAPL    19990602    16:00:00    46,56250
AAPL    19990603    09:30:03    46,87500
AAPL    19990603    09:30:04    46,81250
AAPL    19990603    09:30:05    46,84375

I do not want to analyze this on a second basis but rather on a 5 minute basis. I want to take the 5 minute averages from this day. So I want to take the average from

9:30:00 - 9:34:59 -> 5 min average price for 9:35:00 (first point)
9:35:00 - 9:39:59 -> 5 min average price for 9:40:00
...
15:55:00 - 15:59:59 -> 5 min average price for 16:00 (last point)

My first idea (and possibly not the most efficient one) was to make a list of each second with the corresponding index of the 5 minute interval it belongs to

TIME    SEGMENT
09:30:00    1
09:30:01    1
09:30:02    1
   …        …
09:34:59    1
09:35:00    2
09:35:01    2

I was wondering if anyone of you could answer this question or point me in the right direction towards which SQLite commands I should look into as this is my first encounter with SQlite and I feel a bit overwhelmed.

(Moved here from an edit on the question: Reddit helped me out with this already. Eventually I used this code)

fiveminaverage <- dbGetQuery(db,"
SELECT DATE, AVG(PRICE), MIN(TIME) AS firstSample, MAX(TIME) AS lastSample
FROM currentdata
WHERE DATE=19990603
GROUP BY DATE, strftime('%s', (CASE WHEN TIME LIKE '_:__:__' THEN '0' || TIME ELSE TIME END)) / (5 * 60);
")


mdf,ldf file attach problem? I've created one database on my local system. consider database name is test. This database primary files are test.mdf, test_log.ldf. and contains ndf files too.

Then i've stopped sql server service. then copied these primary files to another server machine*(not ndf files).*

Now i've to create one database 'test1' on that server machine, and have to attach these test.mdf and test_log.ldf files.

create database test1 on primary ( file name='C:\test.mdf'), ( file name='C:\test_log.ldf') for attach go

this query executed in server machine. it says error as """ Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG1_lt_4000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG2_4000_8000.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 5120, Level 16, State 5, Line 1 Unable to open the physical file "D:\data\ExistTable\FG3_8000_inf.ndf". Operating system error 2: "2(The system cannot find the file specified.)".

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'test'. CREATE DATABASE is aborted. """

now what to do???

Well, did you try:

create database test1 on primary
  ( filename='C:\test.mdf'),
  ( filename='C:\FG1_lt_4000.ndf'),
  ( filename='C:\FG2_4000_8000.ndf'),
  ( filename='C:\FG3_8000_inf.ndf'),
  ( filename='C:\test_log.ldf')
for attach;

And yes, you do need to copy the ndf files as well. These are critical parts of your database, not garbage.

Otherwise, go back to your original server, take a backup, and then do a restore with move on the new server.


ERROR: collation "database_default" for encoding "UTF8" does not exist Moving application from SQL Server to PostgreSQL Database. I am getting this error from one of the queries used in a Java file:

ERROR: collation "database_default" for encoding "UTF8" does not exist
Position: 467. Stacktrace follows:
org.postgresql.util.PSQLException: ERROR: collation "database_default" for encoding "UTF8" does not exist
Position: 467

Can you please let me know how to resolve this issue?

database_default is a SQL Server-specific collation that explicitly tells SQL Server to use the database's default collation, overriding the default collation precedence behavior. This is used in a COLLATE clause most typically to avoid collation conflict errors that arise when using temp tables in scenarios where tempdb's default collation differs from the current database's default collation and string columns are being joined or concatenated.
The Java code would need to be edited to remove the COLLATE DATABASE_DEFAULT from the query. Though if this app is coded for Microsoft SQL Server, will it work in PostgreSQL even if this one part is fixed? There are other SQL syntax and datatype differences between the RDBMSs.
For more information on migrating from SQL Server to PostgreSQL, please see:
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server
However, if you are dealing with a 3rd party app that has embedded T-SQL, then you might be forced to contact the software vendor to ask about the potential for PostgreSQL support.


Replace long GROUP BY list with a subquery This is a repost of my question on Stack Overflow. They suggested to ask it here:

I found an online article from 2005, where the author claims, that many devs use GROUP BY wrong, and that you should better replace it with a subquery.

I've tested it on one of my queries, where I need to sort the result of a search by the number of joined entries from another table (more common ones should appear first). My original, classic approach was to join both tables on a common ID, group by each field in the select list and order the result by the count of the sub table.

Now, Jeff Smith from the linked blog claims, that you should better use a subselect, which does all the grouping, and than join to that subselect. Checking the execution plans of both approaches, SSMS states, that the large group by requires 52% of the time and the subselect one 48%, so from a technical standpoint, it seems, that the subselect approach is actually marginally faster. However, the "improved" SQL command seems to generate a more complicated execution plan (in terms of nodes)

What do you think? Can you give me some detail about how to interpret the execution plans in this specific case and which one is generally the preferable option?

SELECT
    a.ID,
    a.ID_AddressType,
    a.Name1,
    a.Name2,
    a.Street,
    a.Number,
    a.ZipCode,
    a.City,
    a.Country
FROM dbo.[Address] a
INNER JOIN CONTAINSTABLE(
    dbo.[Address],
    FullAddress,
    '"ZIE*"',
    5
) s ON a.ID = s.[KEY]
LEFT JOIN dbo.Haul h ON h.ID_DestinationAddress = a.ID
GROUP BY
    a.ID,
    a.ID_AddressType,
    a.Name1,
    a.Name2,
    a.Street,
    a.Number,
    a.ZipCode,
    a.City,
    a.Country,
    s.RANK
ORDER BY s.RANK DESC, COUNT(*) DESC;

SELECT
    a.ID,
    a.ID_AddressType,
    a.Name1,
    a.Name2,
    a.Street,
    a.Number,
    a.ZipCode,
    a.City,
    a.Country
FROM dbo.[Address] a
INNER JOIN CONTAINSTABLE(
    dbo.[Address],
    FullAddress,
    '"ZIE*"',
    5
) s ON a.ID = s.[KEY]
LEFT JOIN (
    SELECT ID_DestinationAddress, COUNT(*) Cnt
    FROM dbo.Haul
    GROUP BY ID_DestinationAddress
) h ON h.ID_DestinationAddress = a.ID
ORDER BY s.RANK DESC, h.Cnt DESC;

If you change the left join with dbo.Haul to a subquery, it will calculate these distinct values of ID_DestinationAddress (Stream Aggregate) and Count them (Compute scalar) directly after getting the data from the scan.

This is what you are seeing in the execution plan:

While, when using the GROUP BY method it is only doing the grouping after data passed through the left join between dbo.Haul and dbo.[Address].

How much better it is will depend on the unique value ratio of dbo.Haul. Less unique values means a better outcome for the second execution plan, since the left join has to process less values.

The other positive result of the second query is that only the uniqueness of ID_DestinationAddress is calculated, not the uniqueness of all the columns as a whole in the group by.

Again, you should test & validate the results for your query, dataset & indexes. One of the ways to test if you are not familiar with execution plans is setting SET STATISTICS IO, TIME ON; before executing the queries and making these runtime stats more readable by pasting them in a tool such as statisticsparser.

Testing

A small test to show what differences in data can do for these queries.

If the dbo.Haul table does not have many matches with the 5 records returned by the FULLTEXT index filtering, the difference is not so big:

Group by query plan

Subquery Query plan

1000 rows could get filtered earlier, but the execution time is around 15ms for both queries anyway on my machine.

Now, if I change my data so these 5 records have many more matches with dbo.Haul on the left join:

The difference between the group by query

    <QueryTimeStats CpuTime="1564" ElapsedTime="1566" />

And the Subquery becomes more clear

and the stats:

<QueryTimeStats CpuTime="680" ElapsedTime="690"/>


Lines starting with colons in SQL Server Playing around with DB projects, Visual Studio generated a change script with the following lines:

GO
SET ANSI_NULLS, ANSI_PADDING --etc.;
SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "Foo"
:setvar DefaultFilePrefix "Foo"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO

:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

GO
USE [$(DatabaseName)];

GO
CREATE NONCLUSTERED INDEX [SomeIndex]
    ON [dbo].[SomeTable]([SomeColumn] ASC);

GO

What is the significance of the colons at the beginning of the lines? This Stackoverflow question says that the colon is for bind variables, but then what does the colon in :on error exit do?

It's SQLCMD
What you have is a sqlcmd script.
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.
And sqlcmd has some sqlcmd specfic commands that start with a colon.
Some general info on sqlcmd below.

You can write SQL statements that are executed after you type GO. In the example, first the database AdventureWorks2012 is selected and after that a SELECT is executed.
-i to include scripts
However, you also have the possibility of executing a prepared script using the option -i, for example:

-v to pass in a value
You can also create scripting varibales with the notation $(name) and passing a value from the command prompt using the option -v

Apart from the command prompt, code including scripting variables can be executed from SQL Server Management Studio, by selecting SQLCMD mode in a query window.

:SETVAR
In this environment the way you can set the values of the scripting variables is using :SETVAR command

:ON ERROR
The :ON ERROR [ EXIT | IGNORE] sets the action to be performed when an error occurs during script or batch execution.
Ignore

When the IGNOREoption is used, the error is ignored and the batch or script continues executing. However, the error message is printed.
Exit

When the EXIT option is used, the execution is stopped and exists.


How to make large "in" queries in C# for an indexed field in MSSQL? In mssql, how can I run effectively this query:
select id from table where field in (gigantic list of ordered longs)

Table schema:
table:
{
  id primary key
  field long
} index findex (field)

I think this should be really fast (since it's basically just stepping through two ordered lists of longs).  Is there a way to continuously stream the IDs in?
I can do it from a console or using Linqpad/SSMS.
Current (slow) Solution: a console app which constructs many long queries, each with 3k fieldId arguments.  The limit on argument number is the problem; how to get around it?

select id from table where field in (gigantic list of ordered longs)

Queries with long IN lists are slow, as they are parsed and compiled each call.
Either pass the list as a JSON array:
select id from table where field in (select value from openjson(@values))

Or use a Table-Valued Parameter or bulk load a temp table with the values. Ensure there is an index or primary key. The OPENJSON method will likely be slower, but the upside is that it works with all client drivers.  TVP and Bulk Insert are not always available except in .NET and Java.


Measuring the need of Capacity or scaling SQL server This would be more of General approach question rather than too technical:

I have one of my application reach out to me wandering if we need to scale up SQL server in terms of CPU memory disk or CPU as they are planning to increase the volume by double the current one.

From the current volume, i have captures which shows CPU/memory and disk running without issues.

But how can i measure with confidence what all will change if there is increased in volume which might need a bump in capacity at my end?

Need your expertise how can i be pro-active and what all can i measure to make up a decision , atleast close to what can be expected with the growth in volume?

Yes i do have SQL server monitoring in place which stores performance data for last 15 days. Most of that data comes from performance counters and DMV's.

Update- Adding more data as i get to update- Avg 800 transactions/sec i day and 400 batch req/sec in a day followed by steady 40% cpu usage over the day, includes peak day. Disk response are well under 5 ms for both read/write. PLE throughout the day is over 10K.

Any help on how to approach this is greatly appreciated.I know answer is broad to this question, but some great ideas can really help me not only for this server but something similar in future also comes up , thanks!

As others have said, there is no magic formula that will tell you how your database server will respond to a sudden "increase the volume by double the current one" (volume of what exactly, by the way).

The two things that can help you to get a general idea (neither of which you have, apparently):

Historical (over many months) data that show you the correlation between your server load, the volume of data, and the application workload (concurrent connections, user sessions, registered users, daily orders, or whatever makes sense in you case). "[P]erformance data for last 15 days" is completely useless.
Performance testing environment that's configured meaningfully similar to your production. In this day and age spinning up a virtualized test environment on one of the cloud providers and running it for a week costs peanuts, compared to the price you will pay for a production outage caused by unexpected performance problems. Performance burn-down tests should be a part of your release process.

If you start preparing both of these tomorrow, in half a year you will be able to answer your own question with some degree of confidence.


How to design a partitioning strategy for a VLDB OLTP solution (SQL Server 2016, >100TB init. size) I am currently working to design a MSSQL 2016 based platform to handle a dataset (OLTP based) that will grow above the PetaByte level. It will be used for specific types of analysis that will require trends to be discovered using various methodes and tool (incl. R). There will be various sources feeding the database(s) on a 'live' basis as well as batches of data that will be ingested on a batch basis. Due to the high volumes of transactions, that number of concurrent users projected (>250) and the way data will be consumed by the users (later more), we need this solution to be high performant and scalable. It is obvious the data needs to be partitioned on a few levels to support the data consumers.

The users will be running trend analysis type workloads on daily, weekly, monthly and multi year ranges. Most data will be supplied with date fields, but customer name, account numbers and transaction types, are also in scope for doing trend analysis.

My question to you all is as follows, what would your strategy be for designing a proper partitioning solution? What questions would you ask and what would you look for in the answers? How would you handle maintenance on indexes and such.... What would you factor into the design?

Oowww and dropping everything into a datalake (read: swamp) or going for a different platform is not an option. Also, I am not at liberty to discuss the particulars of the project or the data involved so please don't ask. Just know it is highly confidential financial and personal data and we will be doing forensic analysis (using R, PowerBI and/or other BI tooling) in compliance with lawful requirements that have been imposed on us. I will not share any other details beyond this, sorry.

I would suggest you to go through the article describes some important prerequisites and suggestion for OLTP databases.

http://nerdtechies.com/2016/12/05/improve-write-performance-sql-server-database/

For the loading process use BULK INSERT and for normal insert user WITH(ROWLOCK).

https://technet.microsoft.com/en-us/library/dd425070.aspx

Partitioning

What you need to know.

What would be retention of online data?
What is growth/day?
On what basis reports users pull maximum (daily/weekly/Monthly or Yearly)?
Archiving Policy.

--I have experience on 2 TB table with 50GB growth/day, one month data on production rest on WH. So suggesting accordingly.

If 70-80 % Usage of daily basis analytical reports. I would suggest to go for daily basis partition as there would be huge amount of data.
It will perform faster but to generate weekly, monthly and yearly report you'll have lengthy query.

If there is 50-50 ratio between daily,weekly and monthly analytical then go for monthly partitioning. In this case Daily and Weekly basis reports will perform slower than day basis partitioning, because there would be lot of records to filter from the month. But you'll have quite simple query.

Partitioning by considering retention of online data make Archiving Policy easier.

Index

As table will be partitioned you should create partitioned indexes on the table.To create partitioned index you need to include partition base column in the indexes. Until you don't create partitioned indexes on the partitioned table you wouldn't get performance benefits.

Creating Indexes on separate filegroups will result in good performance for reports. So create separate Partition scheme,function, on separate filegroupes for indexes same as created for table.

Better to go for Column Stored Index on (Base_Partition_Column,customer name, account numbers, transaction types, Financial Column) on Index_Partition_Scheme.

Create indexes with FILLFACTOR=80

Creating partitioned indexes make index maintenance easier. Instead of rebuilding or reorganizing complete index you can perform maintenance task for particular partition of the index which minimize maintenance duration for big tables.

To do it you can track index fragmentation and row count of the partitions. It will help you to find out indexes on which partition should be rebuild.

Maintenance schedule depends on data size, how much off duration you have to perform the maintenance activities and how long SQL Server takes to finish the task.
It would be better to test you maintenance plan on test environment first with same amount of data then move for production if it finish within the off hour you have.

Thanks


How, if possible, can an .sql file be executed against an oracle database from a bash script Is it possible to execute an sql file (a text file containing several SQL statements) against an oracle database from a bash shell command line (e.g. no graphical GUI and no interactive clients)

Something like:
sqlplus -f queries.sql ...

The command is expected to execute all the statements in the queries.sql file and exit with an appropriate exit code (e.g. 0 if all queries executed correctly, nonzero otherwise)

In a shell script:

#!/bin/bash

sqlplus user/pass@server/DATABASE<<THEEND

-- Change "1" to the desired fatal return code

whenever sqlerror exit 1;

@yoursqlscript.sql

quit;

THEEND

Or you can just run:

sqlplus user/pass@server/DATABASE @yoursqlscript

... and put the whenever sqlerror exit 1; at the top of your .sql script(s).


UUID in mysql database I have a problem, I have two servers  where I have to save my data. For persistent data I run mySql inno DB. I have a lot of entries, so I need fast (CRUD operations).

For indexing I was thinking to use UUID() or UUID_SHORT(). Auto increment is out of the options, because the data will be replicated.

The problems are that, at UUID() the DB will become very slow. At UUID_SHORT() the problem arise that it's not true unique, if I won't often restart mySql server. According to documentation, mySql DOC I can create only 16000000 entries.

What should I use for indexing?

Any help would be appreciate.

The docs on UUID_SHORT() are a little bit short on actual detail, and I suspect you are not seeing the complete story on how this function works.

As long as you do not have more than one server in the system with the same bits in the low-order byte of the @@server_id, and you don't set the system clock back in time and then restart MySQL server, and don't generate more than 16.7 million calls to UUID_SHORT() per second for every second of every minute that the server is running, then the values will be unique.

--- 64 bits of bigint unsigned comprising the initial value ----
6666555555555544444444443333333333222222222211111111110000000000
3210987654321098765432109876543210987654321098765432109876543210
|------||------------------------------||----------------------|
@@server_id     unix_timestamp of          monotonic counter
low-order      server startup time         starts at 0, increments
byte                                       up +1 with each call,
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< overflows this way
                                           when it rolls over
                                           past 24 bits

The counter is initialized as shown above with each server start, and generates a value incremented by 1 each time UUID_SHORT() is called.  It does not increment the unix_timestamp portion of the value for each tick of the wall clock -- it overflows the counter up into that portion of the value, effectively conserving this resource (of usable values) and avoiding collisions caused by a normal server restart.

Thus, if you don't generate more than 2^24 (16,777,216) calls per second every second, then the next time the server starts, it will initialize at a much higher value (relatively speaking) than where it left off.  Setting the system clock back in time would also be unlikely to be problematic unless you set it back close enough to the time of the prior server restart to cause overlapping values.


update statement with self join I needed to copy information from one row of a table to another.
I noticed I could do the following:

update address
set col1 = a2.col1,
col2 = a2.col2,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from

However, the above SQL seems kind of ambiguous.
How do I know it's going to update row @to with data of @from and not the otherway around?

Edit: Note: while I have not done extensive testing, this query does seem to work as I try it against our development database. It simply updates the one @to row with results from the @from row.

To be less ambiguous you can prepend the alias in the set clause. (Taken from 孔夫子 in the comments, who did not submit an answer.)

update address
set a1.col1 = a2.col1,
a1.col2 = a2.col2,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from


SQL Server recreating plans each day We have this problem in our production environment.

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1).

SQL Server is dropping all (almost 100% of) the old execution plans and recreating them everyday overnight (from 11:00 PM to 8:00 AM). This was even happening when the 'auto update stats' was at disabled state. We've turned the 'auto update stats' on for the last 2-3 weeks. But it's still happening.

We don’t really know what triggers this re-generation of plans but we are sure we don’t do it manually.

The only thing that really coincides with the timing of the plans being regenerated is a DB maintenance job that we have: the daily index reorganizing (when fragmentation is 5-30%), and the daily index rebuilding (when fragmentation is more than 30%) job. Usually this daily maintenance job only does reorganizing (as the index fragmentation is never more than 30% on daily basis).

Impact:

These newly created plans make some UDF calls/query calls (that are called from UI/web pages) take way longer (minutes as opposed to less than 1 second), and so the sessions just get piled up taking the CPU close to 90%.

The problem goes away the moment those stuck sessions are forcefully deleted (on the DB side), and 1) when all the corresponding execution plans are cleared manually (for queries) or 2) when the UDFs are altered (for functions). Any new plans created by SQL server from that moment work just perfect throughout the day until it ends up having the same issue next morning. Also, this behavior is not 100% consistent, we're not really seeing it each and every morning. But there have been periods of time where we've been seeing it consistently for 4-5 days in a row.

The problem happens on business mornings, that's when UI/web pages are accessed more intensively, it seems.

Does anyone have a clue what is causing this and how to solve this problem? Any help would be much appreciated.

Well I have some ideas which could cause this behavior.

Do you monitor your memory pressure? Maybe your queries raise a certain limit which will cause the flush of the plan cache. I don't know your application, but does this correspondent with your logs from your frontend servers? Is there pressure too during this time?
Do you have a dedicated SQL Server or does the server share it's hardware with other processes/services? If not, try to consider to outsource your SQL Server to a dedicated machine instead. This will reduce side effects from other services.
You may want to use optimize for ad hoc workloads, which will just save a plan stub and compile it if it's needed. This will reduce the load of your plancache, which will lower the chance of an plancache flushing. You can enable it using sp_configure 'optimize for ad hoc workloads',1; reconfigure. This can be done if you have enabled the advanced options using sp_configure 'show advanced options',1; reconfigure.
Another idea can be backups. Just simple backups. If they are aggressively, it may occur that your machine gets under pressure too. The time your mentioning just sounds like a good timespan for planning a backup.
Maybe it's quite simple a bug in your maintenance script. Have you checked if there is a logical issue which causes your script to rebuild all indices instead of just those who matches the criteria. This maybe can cause it too.

Just beside all of this possibilities, it may be useful to check the log files for some changes to the options affinity mask, affinity I/O mask and their x64 partners. Another thing can be a change of the MAXDOP option of your instance. Please check the logs for them too. They will need to flush the plancache too.

Last but not least, you can still run a serverside trace (just setting it up using the profiler, start it, stop it and using sql command to start it again on the serverside). Beside that perfmon is your friend. It can watch and monitor your performance values for a time. Maybe you can see parallels in the pressure with certain actions on your server which may cause those flush.

Hopefully this will help you, even if the answer come a bit later.


Get current table name trigger was fired on My system: Db2 11.1 on Linux.
1.Create some table:
create table admin.tab (a int not null primary key, b int) @

2.Create audit table:
create table admin.audit (table_name varchar(2000), datetime timestamp) @

3.Very very simple sample, when insert into source table admin.tab I would like to insert into admin.audit:
create trigger admin.mytrig
after insert on admin.tab
referencing new as new
for each row
begin
    insert into admin.audit (table_name, datetime)
       values ('admin.tab', current_timestamp);
end
@

4.Above trigger works fine, but is there any way in above trigger I can replace 'admin.tab' constant with current table name that was fired using trigger, like CURRENT_TABLE_TRIGGER_WAS_FIRED_ON or something?
create trigger admin.mytrig
after insert on admin.tab
referencing new as new
for each row
begin
    insert into admin.audit (table_name, datetime)
       values (CURRENT_TABLE_TRIGGER_WAS_FIRED_ON, current_timestamp);
end
@

I need this kind of solution, because I need to create hundreds of such triggers and to share as much code as possible saves a lot of time.

Is there any way I can obtain inside a trigger the name of the table on which the trigger is defined?

No, there is no such capability in any of the current (as of year 2020) Db2 versions, for reasons that I won't go into. For a comparison, consider why a C function cannot know its name or the name of the function that called it.
For each trigger that you write you must know the corresponding table name when you write it, simply for the fact that it is part of the trigger definition:
CREATE TRIGGER <name> ... ON <table name>

and it is trivial to also add that name, as a literal, to the INSERT statement.
If you face a tedious task of creating an inordinate amount of nearly identical triggers, use your favourite scripting language and one of the template engines available for it to define a trigger template and generate DDL statements for all your triggers from it.
Alternatively, consider using the Db2 audit facility, which is perfectly capable of doing what your triggers are meant to do.


MySql Replication Failure on primary secondary We have experienced replication failure between primary - secondary databases.

I have tried various scenarios to get replication to work again, which included deleting secondary database and restoring it from primary backup. However, replication still fails.

Would it be best to follow steps:

On Slave

mysql> STOP SLAVE;

On Master

mysql> RESET MASTER;
mysql> FLUSH TABLES WITH READ LOCK;
mysqldump -u root -p mydb > mydb-dump.sql
mysql> UNLOCK TABLES;

On Slave

mysql -u root -p mydb < mydb-dump.sql
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
mysql> START SLAVE;
mysql> show slave status\G

Is it safe to execute

mysql> RESET MASTER;
mysql> RESET SLAVE;

as those commands deletes the binary log files. Or would it be better to use

mysql> PURGE BINARY LOGS

Any thoughts would be greatly appreciated.

Cheers,
Roland

In the master server, you don't need to issue either RESET MASTER nor PURGE BINARY LOGS to extract a DB copy... when you need to extract a master copy to create a slave, just do it with the current binary log file and position.

Since you will need the current binary log file and position to start replication on the slave, just include the --master-data (more info) option to mysqldump which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process.
(Just open the mydb-dump.sql file after backup is completed and copy the "CHANGE MASTER TO" info.

In slave is perfectly safe to use RESET SLAVE after a backup restoration to start the replication.


Understanding BinLogDiskUsage Metric behavior We are using AWS-RDS. The following is the screenshot of how the binary log disk usage has varied over the last 6 weeks.

The log retention period is 3 days. Tables are optimized and archived once a week. We were expecting the BinLogDiskUsage to be almost constant(assuming periodic purging of logs by AWS ) with spikes during archival tasks.

Can anyone explain this wavy nature of the graph?

(Long-winded, but incomplete, answer)

The graph moves up and down rather smoothly.  This puzzled me.  With your latest numbers, I can explain some of what is going on.

Data is written to the binlog until it exceeds max_binlog_size (128M, in your case).  At that point, a new binlog file is created.

Slave(s) pull the data from the binlogs continually, and usually keep up.  There is, however, no feedback to let the Master know when all the Slaves are finished with a binlog file.  So, instead, there is some other mechanism for deleting old binlogs.

With expire_logs_days (3 in your case), binlog files older than that many days will be deleted.

Normally your 2GB (1447*1396626) would occupy about 16 binlog files (that /134217728).  And the graph would show distinct drops of 128MB every so often.

The only thing I can think of for leading to 1447 binlogs instead of 16 is spurious PURGE commands.  Please run these:

 SHOW GLOBAL STATUS LIKE '%purge%';
 SHOW GLOBAL STATUS LIKE 'Uptime';

The quotient will say how many purges per second.  Normally, this will be close to zero.  But your system seems to have several per hour.  So, I have converted the mysterious graph into "Why is PURGE being performed so often."

(See http://mysql.rjweb.org/doc.php/mysql_analysis#tuning if you would like deeper analysis of STATUS and VARIABLES.)

More

1447 binlogs with purge_logs_days_seconds = 500 (5 minutes).  This says there are usually 1 or 2 binlogs being purged each time.  The graph shows a weekly pattern.  One part of the week is quite busy -- adding a lot to the binlogs while purging 3-day-old files that have much less.  This makes the graph go up.  Ditto for down.  Flat says that you are adding at the same rate you are deleting.

I would guess there was a lull in traffic, or a national holiday, during the last week of May?

There are still contradictions in what little data you have provided.  1447 and purge after 3 days would say 3 minute interval, not 12/hour.  Or 5 days, not 3.  Also, max binlog size of 70MB (out of 128MB max) would imply that a purge cycle never purges more than 1 file.

The system has a rhythm; nothing seems terribly wrong.


How to export an ER diagram from an Oracle DB? I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many tables and creating the ERD in visio manually is out of the question.

I know Toad has Database > Export ER Diagram option, but the result is not exactly presentable because of ugly layout and difficult to edit. Do you know an alternative way which can create a presentable ER diagram? Preferably it will be nice if the resulting diagram is editable and also if the tool is free.

Have you got access to Microsoft Visio?

If you can get an ODBC connection to your Oracle DB then you can use Visio to scan the database.  It will display all the entities, relationships, etc.

Have a look at this on Stack Exchange
https://stackoverflow.com/questions/4891387/how-can-i-import-the-contents-of-an-oracle-database-into-visio-to-create-an-enti

Hope that helps


How to Split 24 hours into hour slab in Sql Server '1-2 AM
2-3 AM
3-4 AM
4-5 AM
5-6 AM
6-7 AM
7-8 AM
8-9 AM
9-10 AM
10-11 AM
11-12 AM
12-1  PM
till
11-12 PM'

Total 24 row show.

Parameter is date like='07/06/2017'

what i am trying...

(convert(varchar,case when DATEPART(HOUR, START_TIME)%12<>0 then DATEPART(HOUR, START_TIME)%12 else 12 end) +' - '+ convert(varchar,DATEPART(HOUR, START_TIME)%12+1) + case when DATEPART(HOUR, START_TIME)>=12 then ' PM' else ' AM' end)

result is as per my needs but when any hours have no data then these hour not in output.
I want all hour slab whether that hour have data or not.

According to above output,there are shown only two hour's slot because only these hour's have a data.

So if I'm right you always want to return 24 rows, one for each hour slot, regardless of data existing for that slot?  I assume you only have one row per hour slot in your data?

Based on this answer here, create a table with a row for each hour slot then outer join it to your results table, which will always give you a time row regardless of rows existing in your table

WITH Hours AS
(
  SELECT 0 as hour
  UNION ALL
  SELECT  hour + 1
  FROM    Hours
  WHERE   hour + 1 < 24
)
SELECT  CONVERT(varchar, h.hour) + ' - ' + CASE WHEN h.hour + 1 = 24 THEN 'Midnight' ELSE CONVERT(varchar, h.hour + 1) END + CASE WHEN h.Hour >=12 THEN ' PM' ELSE ' AM' END, y.*
FROM    Hours h LEFT JOIN yourtable y ON DATEPART(HH, y.START_TIME) BETWEEN h.hour AND h.hour + 1

You will need to add a clause to the join to restrict yourtable to one days data

Note: you could replace the table in the WITH statement with a real table containing 24 rows, one for each hour if you want.


Problem with "not" in full text search I don't understand why I'm getting these results with my full-text search query in SQL Server 2014.

The following query returns results with "Supervisor" in the Title field and "Tokyo" in the HTML_Description field.

SELECT
    *
FROM post
    JOIN CONTAINSTABLE([post], (Title, HTML_Description), 'Supervisor AND NOT Tokyo')
        AS tb1 ON tb1.[Key] = post.ID

If I remove the HTML_Description field in the CONTAINSTABLE like this:

SELECT
    *
FROM post
    JOIN CONTAINSTABLE(post, (Title), 'Supervisor AND NOT Tokyo')
        AS tb1 ON tb1.[Key] = post.ID

The result is the same.

If I use only the HTML_Description field in the CONTAINSTABLE like this:

SELECT
    *
FROM post
    JOIN CONTAINSTABLE(post, (HTML_Description), 'Supervisor AND NOT Tokyo')
        AS tb1 ON tb1.[Key] = post.ID

There aren't any results.

How can I apply the NOT operator to all fields?

I'm not super familiar with full text searches but I think this StackOverflow answer gives us the answer which is also supported by this StackOverflow answer.  The solution was to create a computed column containing what is essentially a concatenation of all the columns you want to apply your search terms to and do your full text search on that column.

Alternatively you can use the AND, OR, NOT logic in separate expressions like the OP in the StackOverflow answer I linked to.

That being said someone here might have a better solution.


Binary tree "get open list" SQL query I have a node table that looks like this, plus it has an inserted_at column:

id        |  parent_id
----------+-----------
1         |  nil
2         |  1
3         |  1
4         |  2
5         |  2
6         |  3
7         |  4
8         |  5
9         |  6
10        |  3

It's a representation of a binary tree that looks like this:

             1
          /     \
        2         3
     /   \       /   \
    4    5      10    6
   /     /             \
  7     8               9

I'm trying to write a recursive SQL query that gets a list of nope nodes - nodes that have 1 or 2 open places below them - that is the list of nodes that aren't listed as a parent_id to other nodes 2 times.

This "open_list" would contain 4,5,10,6,7,8,9 because all those nodes have less than two children. So is this possible in SQL? One query that produces the "open list" of a binary tree.

Maybe I'd have to be a recursive CTE table using a WITH statment that some how has a join on itself ?

With recursive (
   select *
   join on self... recursive
)
select * from recursive

That's where my totally amateur intuition goes. Can someone who has a bit more experience guide me to the answer?

In this case I think you don't need a recursive query, simply get those records that do not have two child records.

select *
from   mytable
where  id not in(select   parent_id
                 from     mytable
                 group by parent_id
                 having   count(*) > 1);

id | parent_id
-: | --------:
 4 |         2
 5 |         2
 6 |         3
 7 |         4
 8 |         5
 9 |         6
10 |         3

dbfiddle here


Config settings in MySQL so that it saves data on disk monthwise/yearwise I have billions of rows in my MySQL table. So when I do a select for a specific date range , it takes minutes to return results, or sometimes even hangs up.

Is there any config settings in MySQL so that it saves data on disk monthwise/yearwise?

I know that MySQL saves table data in disk in table.myd file. I am looking for some config changes in MySQL by which we have multiple myd files for the same table for each month/year.

You can use MySQL Partitioning.
You can partition your data on the basis of month/year.

Example: I have a table which I have partitioned by year

CREATE TABLE `RTestAll` (
  `recordDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `networkId` bigint(11) NOT NULL DEFAULT '0',
  `publisherId` bigint(11) NOT NULL DEFAULT '0',
  `feedId` bigint(11) NOT NULL DEFAULT '0',
  `subPublisherId` bigint(11) NOT NULL DEFAULT '0',
  `subId` varchar(100) NOT NULL DEFAULT '',
  `searches` bigint(20) DEFAULT NULL,
  `matches` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`recordDate`,`networkId`,`publisherId`,`feedId`,`subPublisherId`,`subId`),
  KEY `K_networkId` (`networkId`),
  KEY `K_publisherId` (`publisherId`),
  KEY `K_feedId` (`feedId`),
  KEY `K_subPublisherId` (`subPublisherId`)
) ENGINE=InnoDB CHARSET=latin1
 PARTITION BY RANGE (YEAR(recordDate))
(
 PARTITION p0 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2013) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2014) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2015) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2016) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN MAXVALUE ENGINE = InnoDB
) ;

For reference please have a look at Partitioning Types in MySQL.

For use of partitioning, your SELECT queries should be designed in such a way that they use the partition key.

Going ahead please also have a look at Limitations of partitioning.


Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP In one Database table, created_at & updated_at are defined as:

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

And in another database table, it's defined as:

`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Updated At',

created_at -> stores datetime when the data is created.
updated_at -> stores the datetime when data is updated.

Which definition among above two would you prefer & why?

Tested on 5.5.34

I looked at this in detail (I'm interested for an upcoming project, and it's been a while since I've "got my hands dirty"...)

It appears that datetimes are hard to set defaults for - see here. From what I've read, things have got better in 5.6 - I'll see if I have time to test tomorrow.

I think that your best solution is as below. The answer to your question about the best way to CREATE your TABLE is shown (IMHO). It involves inversing the ON UPDATE clause, putting it (logically enough) with the updated_at field.

mysql> CREATE TABLE mt8
    -> (
    ->   billy int,
    ->   created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created At',
    ->   updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Created At'
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql>
mysql> DELIMITER $$
mysql> create trigger my_ins8_trigger
    -> before insert on mt8
    -> for each row
    -> begin
    ->   set new.created_at = current_timestamp;
    -> end $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql>
mysql>
mysql> insert into mt8 values(123, null, null);
Query OK, 1 row affected (0.08 sec)

mysql> insert into mt8 values(454, null, null);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mt8 values(1676, null, null);
Query OK, 1 row affected (0.06 sec)

mysql> insert into mt8 (billy) values(34334);
Query OK, 1 row affected (0.07 sec)

mysql> insert into mt8 (billy) values(3506);
Query OK, 1 row affected (0.05 sec)

mysql> insert into mt8 (billy) values(3435454);
Query OK, 1 row affected (0.05 sec)

mysql> select * from mt8;
+---------+---------------------+---------------------+
| billy   | created_at          | updated_at          |
+---------+---------------------+---------------------+
|     123 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|     454 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|    1676 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|   34334 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
|    3506 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
| 3435454 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
+---------+---------------------+---------------------+
6 rows in set (0.00 sec)

mysql> update mt8 set billy = 455 where billy = 454;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mt8;
+---------+---------------------+---------------------+
| billy   | created_at          | updated_at          |
+---------+---------------------+---------------------+
|     123 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|     455 | 2014-06-12 01:53:40 | 2014-06-12 01:54:26 |
|    1676 | 2014-06-12 01:53:40 | 2014-06-12 01:53:40 |
|   34334 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
|    3506 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
| 3435454 | 2014-06-12 01:53:56 | 2014-06-12 01:53:56 |
+---------+---------------------+---------------------+
6 rows in set (0.00 sec)
mysql>

For the row with billy = 455, the update timestamp has changed, but not the created one - exactly as you'd want!


How colums are read in database? Say I have 10 columns in Employee table where i just need to use two colums i.e. salary and name in below query

 select salary from Employee where name = 'Scott';

After going through Video from 6.30 to 8.30(2 mins), My understanding is under RDBMS database(say oracle/Mysql etc) even i have to read two columns DBMS will read complete row from disc in memory then it will filter rows based on name coumn and finally read column salary. Is that correct  ?

For MySQL's InnoDB Engine...

Case 1 (slowest):  No index.  It will scan the entire table (all rows) to find one the row(s) with name = 'Scott' and fetchsalary` from them.

Case 2:  INDEX(name).  This will use the index to very efficiently the Scott row(s), then fetch the rows and get the salarys.

Case 3 (fastest):  INDEX(name, salary).  Now the entire query can be performed inside just the index -- without reaching over to the rest of the columns in the data.  More  This is called a "covering" index.  More precisely:  If all the columns needed for the entire SELECT are found in the INDEX, then only the index need be touched, not the data.

The Data is in one BTree, ordered by the PRIMARY KEY.  An INDEX is in a different BTree, ordered by the index.

BTrees are very efficient for finding a specific row or reading consecutive rows based on the index.  See the Wikipedia article on BTree.  This is also a good reference:  https://www.percona.com/files/presentations/percona-live/london-2011/PLUK2011-b-tree-indexes-and-innodb.pdf

For other databases, there could be differences.

How a row is stored

Case 1 (most situations):  All the columns of a given table "record" (aka "row") are stored together in one contiguous set of dozens, maybe hundreds, of bytes.  And this is all sitting in the same "block" along with perhaps a hundred other rows.  So, when you grab any column from the row, all the rest of the columns are already readily available.  Your example of an Employee table probably falls into this Case.  RDBMS optimization focuses on being able to get an entire row quickly, but sacrifices in not being able to get an entire column quickly.

Case 2:  In some DB implementations (including MySQL's InnoDB) big columns (TEXT and BLOB) may be stored 'off record'.  That is, they are put in some other block(s).  Advantages () it allows for scanning rows quickly when you don't need those bulky pieces; () typically you will fetch the bulky columns for only one row at a time.

Case 3:  There is a different type of database organization, with names like "Columnstore".  This pivots the data so that it is easy to get all the values for a column quickly, but loses the ability to quickly get all the columns of a single row.

Focus on Case 1, since it is very common.


Create a copy of a SQL Server Job Using T-SQL or Powershell, how can I create a stored procedure that performs the following:

Copy a SQL Server Agent Job to another backup Job on the same server (with a new name)
Disable the backup job so that it is not scheduled.

USE msdb;

IF object_id('[backupSQLServerJob]') IS NULL EXEC ('create procedure [backupSQLServerJob] as select 1')
GO

ALTER PROCEDURE backupSQLServerJob
    @JobName varchar(50)
AS
BEGIN

    DECLARE @BackupName VARCHAR(100) = CONCAT(@JobName, '_bak_', CONVERT(VARCHAR(10), GETDATE(), 112));

    IF EXISTS (SELECT * FROM dbo.sysjobs WHERE name = @JobName)
    BEGIN
        -- What goes here?
    END
END

The solution needs to work on SQL Server 2008 - 2014

It would be a large script as a Job consists of data entries in multiple tables/views:

msdb.dbo.sysjobs_view
msdb.dbo.sysjobsteps
msdb.dbo.sysjobschedules
-- you probably dont need these as you are copying to the same server
msdb.dbo.sysschedules
msdb.dbo.syscategories
sys.server_principals
msdb.dbo.sysOperators

Create Jobs using following SPs:

msdb.dbo.sp_add_job
msdb.dbo.sp_add_jobserver
msdb.dbo.sp_add_jobstep
msdb.dbo.sp_add_jobschedule

Look in MSDN to work out what arguments to pass