What is the quickest way to purge data? Scenario:

We have two tables Tbl1 & Tbl2 on the Subscriber Server. The Tbl1 is being replicated from Publisher Server A and it has two triggers - insert and update. The triggers are inserting and updating the data into Tbl2.

Now, we have to purge (approx. 900 million records) from Tbl2 which has total 1000+ million record. Below is the data distribution for one month to one minute.

One Month - 14986826 rows
One day - 483446 rows
One hour - 20143 rows
One minute - 335 rows

What I am looking for;

The fastest way to purge that data without any production issue, data consistency, and possibly no downtime. So, I am thinking to follow the below steps but stuck :(

Steps:

BCP Out the required data from the existing table Tbl2 (around 100 million records, it may take approx. 30 mins).

Let’s assume I started doing the activity on 1Fab2018 10:00PM, it finished at 1Fab2018 10:30PM. By the time activity will be completed, the table Tbl2 will get new records that becomes delta

Create a new table in the database with name Tbl3
BCP in the exported data into the newly created table Tbl3 (around 100 million records, it may take approx. 30 mins)
Stop the replication job
Once BCP-in get completed, use tsql script to insert the new delta data.
The Challenge is - How to deal with delta “update” statement?
Start the Replication

Additional Question:

What is the best way to deal with scenario?

Since you are deleting 90% of the rows, I'd recommend copying the rows you need to keep into a new table with the same structure, then use ALTER TABLE ... SWITCH to replace the existing table with the new table, then simply drop the old table.  See this Microsoft Docs page for the syntax.

A simple test-bed, without replication which shows the general principle:

First, we'll create a database for our test:

USE master;
IF (SELECT 1 FROM sys.databases d WHERE d.name = 'SwitchTest') IS NOT NULL
BEGIN
    ALTER DATABASE SwitchTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SwitchTest;
END
CREATE DATABASE SwitchTest;
ALTER DATABASE SwitchTest SET RECOVERY FULL;
BACKUP DATABASE SwitchTest TO DISK = 'NUL:';
GO

Here, we create a couple of tables, with a trigger to move rows from table "A" to "B", approximating your setup.

USE SwitchTest;
GO
CREATE TABLE dbo.A
(
    i int NOT NULL
        CONSTRAINT PK_A
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , d varchar(300) NOT NULL
    , rowdate datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.B
(
    i int NOT NULL
        CONSTRAINT PK_B
        PRIMARY KEY CLUSTERED
    , d varchar(300) NOT NULL
    , rowdate datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

GO
CREATE TRIGGER t_a
ON dbo.A
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE
    FROM dbo.B
    FROM dbo.B b
        INNER JOIN deleted d ON b.i = d.i
    INSERT INTO dbo.B (i, d, rowdate)
    SELECT i.i
        , i.d
        , i.rowdate
    FROM inserted i;
END
GO

Here, we insert 1,000,000 rows into "A", and because of the trigger, those rows will also be inserted into "B".

;WITH src AS (
    SELECT i.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))i(n)
)
INSERT INTO dbo.A (d, rowdate)
SELECT d = CRYPT_GEN_RANDOM(300), DATEADD(SECOND, s6.n + (s5.n * 100000) + (s4.n * 10000) + (s3.n * 1000) + (s2.n * 100) + (s1.n * 10), '2017-01-01T00:00:00.000')
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
    CROSS JOIN src s5
    CROSS JOIN src s6;

Clear the transaction log, to avoid running out of room.  DO NOT RUN this in production since it sends the transaction log data to the "NUL" device.

BACKUP LOG SwitchTest TO DISK = 'NUL:';
GO

This code creates a transaction to ensure none of the affected tables can be written to while we're migrating rows:

BEGIN TRANSACTION
EXEC sys.sp_getapplock @Resource = N'TableSwitcher', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = '1000', @DbPrincipal = N'dbo';
BEGIN TRY
    -- create a table to hold the rows we want to keep
    CREATE TABLE dbo.C
    (
        i int NOT NULL
            CONSTRAINT PK_C
            PRIMARY KEY CLUSTERED
        , d varchar(300) NOT NULL
        , rowdate datetime NOT NULL
    ) ON [PRIMARY]
    WITH (DATA_COMPRESSION = PAGE);

    --copy the rows we want to keep into "C"
    INSERT INTO dbo.C (i, d, rowdate)
    SELECT b.i
        , b.d
        , b.rowdate
    FROM dbo.B
    WHERE b.rowdate >= '2017-01-11T10:00:00';

    --truncate the entire "B" table
    TRUNCATE TABLE dbo.B;

    --"switch" table "C" into "B"
    ALTER TABLE dbo.C SWITCH TO dbo.B;

    --drop table "C", since we no longer need it
    DROP TABLE dbo.C;

    --shows the count of rows in "B" which were retained.
    SELECT COUNT(1)
    FROM dbo.B
    WHERE b.rowdate >= '2017-01-11T10:00:00';

   --look for rows in "B" that should no longer exist.
    SELECT COUNT(1)
    FROM dbo.B
    WHERE b.rowdate < '2017-01-11T10:00:00';

    --release the applock and commit the transaction
    EXEC sys.sp_releaseapplock @Resource = N'TableSwitcher', @LockOwner = 'Transaction', @DbPrincipal = N'dbo';
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    DECLARE @message nvarchar(1000) = ERROR_MESSAGE();
    DECLARE @severity int = ERROR_SEVERITY();
    DECLARE @state int = ERROR_STATE();
    RAISERROR (@message, @severity, @state);
    EXEC sys.sp_releaseapplock @Resource = N'TableSwitcher', @LockOwner = 'Transaction', @DbPrincipal = N'dbo';
    ROLLBACK TRANSACTION;
END CATCH
GO

The sp_getapplock and sp_releaseapplock prevent multiple instances of this code running at the same time.  This would be helpful if you enable this code to be re-used through a GUI.

(Note that app locks are only effective if every process accessing the resource implements the same manual resource locking logic explicitly - there is no magic that "locks" the table in the same way that SQL Server automatically locks rows, pages, etc. during an insert/update operation.)

Now, we test the process of inserting rows into "A", to ensure they're inserted into "B" by the trigger.

INSERT INTO dbo.A (d, rowdate)
VALUES ('testRow', GETDATE());

SELECT *
FROM dbo.B
WHERE B.d = 'testRow'

+---------+---------+-------------------------+
|    i    |    d    |         rowdate         |
+---------+---------+-------------------------+
| 1000001 | testRow | 2018-04-13 03:49:53.343 |
+---------+---------+-------------------------+


how to check if a stored procedure has been called from another stored procedure in mysql? So here I am using MysqlYog / Phpmyadmin for my database management.
Now I have lots of store procedure written and now I want to delete a store procedure as it don't have any direct use but I can not because I don't know if this procedure is being called from another procedure or not.
So how can i check that.
FYI :
DB- MYSQL
Scripting Language : Php

ok .I found it with select query

SELECT COUNT(1)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%procedureName%'
AND ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA = 'dbName';


Quality Database Security Metrics Fellow DBAs and Engineers,
I'm looking for few quality Database security metrics. Purpose of these metric is to demonstrate level of security provided to database schemas in the organisation. Specifically, I'm looking for security metrics which can show state of confidentiality and integrity provided to the DBs in the environment.
As an example:

Percentage of DBs encrypted and Redacted.
Percentage of DBs hosting PII information.

Metric request is platform agnostic but mostly pertaining to Oracle environment in hosted DB environment.
Let me know if you have any questions.
Regards
~ RS

The only way to get to what you're looking for is through an audit process of some kind, using a pre-defined checklist like the DISA STIG or CIS or some other recognized benchmark. These will all include a variety (even a majority) of "metrics" about documentation and policy that can't be measured through automation, so don't think there's an easy way to do this to get meaningful results.
There are whole areas of research and organization dedicated to how to approach this on a complete infrastructure or system level, like the Risk Management Framework. There are also legal requirements to evaluate against if your system is covered by the Fair Credit Reporting Act, Graham-Beach-Bliley Act, Children's Online Privacy Protection Act, The Health Information Technology for Economic and Clinical Health Act, HIPPA, the Federal Information Security Act of 2002, or GDPR. These carry their own auditing requirements.
It's also important to recognize that a system or application's security is about all layers of that system: database; operating systems; application servers; web servers; application code; network infrastructure; storage; authentication and authorization; and even user and admin training. All must be evaluated from top to bottom to get anything close to a complete picture. Even then with these checklists all you're really demonstrating is what you've prepared for / hardened against; there's no absolute measure of a system's security, per se.


Select two aggregate functions on the same column using different criteria - performance Dear stack exchange users, first question here.

The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many finished, unfinished, total money earned, total money pending from a couple of tables

I'm a bit worried about if this is the optimal way to write this query. It looks like I should be able to shorten this since i'm selecting on the same tables, shouldn't it be possible to reuse that in some way?

            SELECT finished, notfinished, totalamount, areas, pendingamount
            FROM
            (
            SELECT COUNT(finished) as finished, COALESCE(SUM(cost),0) as totalamount FROM HiresService as hs
            JOIN ServiceProvider USING(serviceproviderid)
            JOIN BusinessUser USING(businessuserid)
            WHERE BusinessUser.businessuserid = ? AND hs.finished = true
            ) t1
            JOIN
            (
            SELECT COUNT(finished) as notfinished, COALESCE(SUM(cost),0) as pendingamount
            FROM HiresService as hs2
            JOIN ServiceProvider USING(serviceproviderid)
            JOIN BusinessUser USING(businessuserid)
            WHERE BusinessUser.businessuserid = ? AND hs2.finished = false
            ) t2
            JOIN
            (
            SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
            FROM ServicesArea
            JOIN Area USING(areaid)
            WHERE serviceproviderid IN(
                 SELECT serviceproviderid
                 FROM ServiceProvider
                 JOIN BusinessUser USING(businessuserid)
                 WHERE businessuserid = ?)
            ) t3

Table structure looks like this:

EDIT: (accepted answer)

SELECT finished, notfinished, totalamount, areas, pendingamount
FROM ( SELECT
sum(case when finished = 1 THEN 1 ELSE 0 END) as finished,
sum(case when finished = 1 THEN cost ELSE 0 END) as totalamount,
sum(case when finished = 0 THEN 1 ELSE 0 END) as notfinished,
sum(case when finished = 0 THEN cost ELSE 0 END) as pendingamount
FROM HiresService
JOIN ServiceProvider USING(serviceproviderid)
JOIN BusinessUser USING(businessuserid)
WHERE BusinessUser.businessuserid = ?
) t1
JOIN
(
SELECT COALESCE(GROUP_CONCAT(Area.name SEPARATOR ', '), 0) as areas
FROM ServicesArea
JOIN Area USING(areaid)
WHERE serviceproviderid IN(
SELECT serviceproviderid
FROM ServiceProvider
JOIN BusinessUser USING(businessuserid)
WHERE businessuserid = ?)
) t2

There is a trick you can use to filter aggregates:

select
 --...
 , sum(case when CONDITION THEN 1 ELSE 0 END) AS FilteredCount
 , sum(case when CONDITION THEN SomeCol ELSE 0 END) AS FilteredSumOfSomeCol

If you can fit your aggregates into this scheme you can avoid the need for repeated query parts like in the current code. It looks like you can use this trick.


Does the length of a table name affect performance? PostgreSQL table names are limited to 63 chars.

Is there a performance gain to be had if I limit my table names to something like 5 chars or can I use the full 63 chars without consequence?

I understand it will be longer to type long names when creating queries, but that's what aliases are for.

Table names have to be stored just like any other data (in the system catalog pg_class to be precise), and there are multiple objects that are typically named matching the table name (constraints, indexes, sequences, ...) so there is a marginal cost for longer strings. But you will not be able to notice any difference in performance. Internal references are implemented with oid numbers (object identifier types), so independent of the string used as name.

The more important consideration is that your code becomes longer and harder to read and type. Typos more likely. Aliases are nice, but you still have to spell out the name in many places. So the rule of thumb should be:

As short as possible.
As long as necessary to be clear.


How to calculate a percentage over a dimension attribute and get correct aggregates? We have a fact table with the weight trasported by truck/driver/day.

And a dimension with the max weight per truck.

We would like to have the % of weight transported, so we started with

create MEMBER CURRENTCUBE.[Measures].[% WT]
 AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("key"),
FORMAT_STRING = "Percent";

This only works for individual rows where one truck has only one leaf for the specific day and driver.

It doesn't work if a truck does two trips in a day, instead of sum(weight)/sum(max_weight) or if you prefer sum(weight)/(n*max_weight) what we get is sum(weight)/max_weight

Exactly the same occurs at any aggregate level. For example, month or year or total. Or simply at truck level.

We have been playing with all sorts of formula and scopes without success.

Any idea how to define it so the aggregates work as expected?

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in the fact table.

Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension table so you can create a real measure.

Depending on your situation you could also add a new measure group.


Postgres: trying to fix some unicode encoding issues I've got a postgres 8.4 database with a few mangled characters in one of the columns on a table. For example, the word "don't" gets displayed within psql as:

donÃ¢\u0080\u0099t

I want to be able to dig out these odd characters and replace them with simple ' or " alternatives. However, I'm having a bit of a job getting either ~ or LIKE to be able to pull the correct rows in, let alone be able to replace them.

The database is UTF8 and the client_encoding is also set to UTF8. I've tried selecting \u string and \x strings, but can't seem to get anything reliable. For example, all of the following queries return 0 rows or ERROR:  invalid byte sequence for encoding "UTF8": 0x80

select col from tab where col like '%\u0080\u0099%';
select col from tab where col ~'\u0080\u0099';
select col from tab where col like '%\x8099%';
select col from tab where col ~'\x8099';

Any ideas?

If what you see is donÃ¢\u0080\u0099t then those are literal backslashes in your table, not escape sequences.

Try to double the backslashes to get literal ones:

select col from tab where col ~~ E'%\\u0080%';

And use the proper E'' syntax for PostgreSQL 8.4 with that.


Permission denied in file trying to import When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and when I type \i "C:\Users\Work\Desktop\School Work\load_database.sql" it says C:: permission denied. How can I fix this?

I found a work around here where you copy the .sql file to the same folder as psql.exe is in.

By the way, does \i stand for import?

You've just run into a peculiarity of psql on Windows.

In my comment I just wanted to exclude the possibility that you really didn't have the necessary permission.  Then it turned out that even on Windows, you have to use 'normal' slashes instead of backslashes, that is:

\i 'C:/Users/Work/Desktop/School Work/load_database.sql'

should work fine regardless which folder you start psql from.  Notice that I used single quotes - with double quotes you'd got

"C:/Users/Work/Desktop/School Work/load_database.sql": Invalid argument


SSIS package blocks itself if uses TRUNCATE There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data:

Here the Delete block deletes everything from six tables, and the Parse block loads six files to the six tables.

If the Delete uses delete from dbo.table, then everything works fine, but is slow.
But if Delete uses truncate table dbo.table instead, the deletion takes no time, but the package becomes blocked when it comes to the Parse task. Would just sit around doing nothing.

At this moment on the server side I can see an SSIS spid being blocked by spid -2, which is "Orphaned distributed transaction." It would appear that used to represent the truncating connection. This connection hangs around, and the package is blocked by the locks it placed.

If I switch the only used SQL Server Connection manager to RetainSameConnection = True, then the package fails as soon as it enters the Parse task. The task asks the manager for a connection, and the manager responds with

  [SQL Server Destination [471]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Connection Manager" failed with error code 0xC001A004.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Is there a way to set up the package so that it uses truncate and does not block itself?

This is due to blocking caused by the delete task accessing the metadata from the database during the Pre-Execution phase.

The locks can be found using sys.dm_tran_locks and it will have -2 value as OP founded. More details could have been found out using Profiler.

The blocking can be resolved by changing the ValidateExternalMetadata property to FALSE or as OP mentioned using a DELETE statement.

Reference: KB2253391


Why does Management Studio not automatically create FK column in the parent table? When creating a FK in SQL Server 2008 Management Studio using a single column, one has to first create a column manually then select the PF/FK relationship from a dialog box.

I am wondering why is there no option to let the software automatically create the column with the correct data type in the parent table as soon as the drag operation completes?

The comments on the question explain some of the perils of using Management Studio to make schema changes, so I won't get into that.

What I want to show is how to accomplish what you're asking using T-SQL:

ALTER TABLE [dbo].[MyTable]
    ADD NewColumn int NOT NULL
        CONSTRAINT FK_MyTable_MyOtherTable
            FOREIGN KEY REFERENCES [dbo].[MyOtherTable](Id);

The syntax is extremely powerful as you can create multiple constraints (DEFAULT, PRIMARY KEY, etc.) on the same column using this syntax (even named constraints as I demonstrated above). The best part is that it's an atomic operation, so if any part of it fails, the whole thing rolls back automatically.

See the ALTER TABLE documentation on MSDN for more information.


Integrating large PostgreSQL table as partition for a new table I currently have a relatively large table containing timeseries data (628+ mil. live rows). The table definition (some names changed slightly) at the bottom of question.
I want to attach the existing table as a partition to a new (partitioned) table. However, the existing table has a singular id primary key (mainly used because Django requires it). Attaching the table would require me to update the primary key constraint to (id, timestamp) on the old table.
Because id is unique this isn't an issue, but given the size of the table I wonder if this constraint is checked upon creation (leading to quite the query) or whether only newly added/updated rows are checked. Stopping read/writes to the table for a few minutes is possible, but I can't wait for multiple hours.

Intended new table
Like the old table the id column is mainly required by our ORM sadly. A similar (prop_id, "timestamp", value) index would be used on the partitioned table as well.
CREATE TABLE "newtable" (
    "id"            bigserial NOT NULL,
    "timestamp"     timestamp with time zone NOT NULL,
    "prop_id"       integer NOT NULL,
    "value"         double precision NOT NULL,
    PRIMARY KEY ("id", "timestamp")
) PARTITION BY RANGE ("timestamp")
;

Old table definition
The "id" primary key is an artifact of our ORM (Django) and is inconsequential for any queries we do. We use the (prop_id, "timestamp", value) index 99.9% of the time for index-only scans.
  Column   |           Type           | Collation | Nullable |                         Default                     | Storage | Stats target | Description
-----------+--------------------------+-----------+----------+----------------------------------------------------------+---------+--------------+-------------
 id        | bigint                   |           | not null | nextval('tablename_id_seq'::regclass) | plain   |              |
 timestamp | timestamp with time zone |           | not null |                     | plain   |              |
 value     | double precision         |           | not null |                     | plain   |              |
 prop_id   | integer                  |           | not null |                     | plain   |              |
Indexes:
    "tablename_pkey" PRIMARY KEY, btree (id)
    "tablename_prop_id_timestamp_value_b9bc8326_idx" btree (prop_id, "timestamp", value)
Foreign-key constraints:
    "tablename_prop_id_67f339b0_fk_othertable" FOREIGN KEY (prop_id) REFERENCES othertable(id) DEFERRABLE INITIALLY DEFERRED

Proceed like this:

Don't create a primary key constraint on the partitioned table for now.
Rather, create unique indexes on the new partitions that will later become partitions of the primary key index.

As soon as the large table is old enough that you don't need the data any more, drop it.

Then you can create a primary key on the partitioned table that will use the already existing unique indexes as partitions.


Bulk-Insert Select performance over individual Inserts I am using sequelize as ORM for my nodejs application.

The scenario is, I have to insert a Poll and its Options and get their objects.

Approach 1:

1 - Insert Poll:

INSERT INTO `polls` (`id`,`start_at`,`expire_at`,`options_count`) VALUES (DEFAULT,1487945658,1487945965,3);

2 - Insert Options (bulk insert):

INSERT INTO `poll_options` (`id`,`poll_id`,`text`) VALUES (NULL,{id},'red'),(NULL,{id},'green'),(NULL,{id},'blue');

3 - Select Options (I have to select because I don't get id of inserted Option due to bulk insertion):

SELECT `id`, `poll_id`, `text` FROM `poll_options` WHERE `poll_id` = {id};

Approach 2:

1 - Insert Poll

2 - Insert each Option one by one (this way I can get inserted id as well, so no need of explicit SELECT at the end)

Question:

Which approach would be better?

Both tables have referential integrity enabled and using innodb engine.

If we're talking performance, first approach is better. Here is why:

Whenever new data is inserted into a table, the indexes will also be updated, and physically reordered. In the case on InnoDB, not only are the index files updated, but the data file itself could be reordered, it is clustered based on the primary key. So, re-ordering/updating is an expensive operation, and you want to minimize it.

When you do bulk insert, the indexes are updated at when the statement is finished. i.e. after inserting all rows.
On the other hand, when you do individual inserting, physical files are updated after each insert.

The overhead of the last select statement in the first approach is negligible.


Allow multiple users to change functions in MySQL I want to make multiple users and allow them to change  procedures,functions and triggers in same DB. There must be user devel for    developers and admin for admin, both can change developers DB, but admin is not root, i.e. he can't change everything, only some databases.
User admin have following permissions:

GRANT ALL PRIVILEGES ON some_db_prefix%.* TO 'admin'@'%' WITH GRANT OPTION

All fine except definers in triggers and procedures. If I set definer in database to devel@'%', i can't edit procedures as admin, but root user can, of course.
How can I allow to modify triggers,functions and procedures to multiple users?

UPD: I can't edit procedures as admin even when definer is `%`@`%`.

UPD2: MySQL version:
Server version: 5.6.23-ndb-7.4.5-cluster-gpl-log MySQL Cluster Community Server (GPL)

UPD 3: (moved to new question Allow multiple users to see procedures and functions )

UPD 4: Manuals say that only way to change body of procedure is to drop it and create new. So, I can do it, and my question is incorrect, I want not only change functions/procedures using different users, but also see body (code) of funtions/procedures. That's why I have created new question.

As MySQL's documentation says:

  The ALL or ALL PRIVILEGES privilege specifier is shorthand. It stands
  for “all privileges available at a given privilege level” (except
  GRANT OPTION). For example, granting ALL at the global or table level
  grants all global privileges or all table-level privileges.

Just use:

GRANT ALL ON test.* TO 'admin'@'%';
FLUSH PRIVILEGES;

Test:

mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'devel1'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'devel1'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'devel1'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for devel1@%                                                                                |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devel1'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'devel1'@'%'                                                      |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'admin'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'%'                                                      |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


Optimizing MySQL 5.6 for unit tests We have a MySQL instance for unit tests. Our unit tests work very heavily on MySQL. Therefore, we put /var/lib/mysql dir in tmpfs file system because we don't need persistent data in unit tests.

Our performance peaked when we did this; however, I would like to consult with you what's the best configuration for MySQL in this case.

It's very common to put MySQL in tmpfs (memory) and run non-important stuff like unit tests. The question is: What's best configuration for InnoDB to run in tmpfs?

These are the configs we have in my.cnf:

max_connections=350
innodb_fast_shutdown=2
innodb_log_file_size=1048576
innodb_doublewrite=0
innodb_flush_method=O_DIRECT

All our databases are in InnoDB. We don't mind losing some of the data.

P.S. You might ask at this point, why not MEMORY engine? That's because MEMORY has table-level locking only, and part of our unit tests are based on this.

EDIT:
We've finished this project, we based our configuration from this blogpost:
http://jotschi.de/2014/02/03/high-performance-mysql-testdatabase/

the innodb_log_file_size looks rather small for what I assume is bulk loaded  unit test data with lots of writes.

A decent innodb_buffer_pool_size needs to be set that accounts for the active test size.

Because crash safety isn't a concern, like innodb_doublewrite=0  innodb_flush_log_at_trx_commit=0, will speed up write transactions.

max_connections implies you are doing multithreaded clients and if those are connecting/disconnecting maybe set thread_cache_size to the maximum number typically used.

A SHOW GLOBAL STATUS after a test run might give a hint as to what other aspects could be tuned.


Is there a faster way of moving data between databases than CTEs? I use the following query on SQL Server 2012 Express to move data that is older than 4 days old out of one [primary] database and into another [secondary database] for archiving purposes. The only issue is that this takes the database offline as it is usually moving about 500k rows of data, and that figure is climbing with more data points being added to the database.

The result is that my web based app is not able to access the database for (in most cases) about 2 hours, and this stalls a lot of other processes, as well as the app.

DECLARE @4daysago datetime
SELECT @4daysago = DATEADD(d, -4, GetDate());

SET IDENTITY_INSERT [activetrackarchivedb].dbo.[Data Import] ON;

--Transfer from current (production) DB to Archive DB
WITH CTE as (
    SELECT TOP 1000000 *
    FROM [activetrackdb].dbo.[Data Import]
    WHERE [activetrackdb].dbo.[Data Import].[Receive Date] < @7daysago
    ORDER BY [Receive Date] ASC)
DELETE CTE
  OUTPUT DELETED.id,
  DELETED.[Company id],
  DELETED.[Site id],
  DELETED.[Site name],
  DELETED.[Receive date],
  DELETED.[Detect date],
  INTO  [activetrackarchivedb].dbo.[Data Import]
  (id,
  [Company id],
  [Site id],
  [Site name],
  [Receive date],
  [Detect date]);

Is there a better method I could use to 'transfer' these rows? Even if the new method was slower, if it could at least still allow access to the database. This one took me a week to implement (I'm a novice) and involved the StackOverflow community's help. It's been great up until now, but as amount of data has increased, its become a very cumbersome process.

This is not for backup purposes either. The database the rows are transferred to simply have a higher capacity and the archived data is still used in the production environment (it just makes the primary database more maintainable and responsive when combined with other maintenance and indexing scripts)

Any help would be greatly appreciated.

This doesn't have anything to do with using a CTE specifically, as your question title implies. It is probably a combination of at least one or more of the following:

an expensive scan (or seek + a million lookups) to identify the rows to delete (I assume [Receive Date] is not the clustering key)
many underlying non-clustered indexes that also must be updated
blocking (both on the delete and on the target/archive table)
a batch size too large that causes too much transaction log activity (which can be especially bad if you have poor t-log configuration)

See this blog post but generally you may want to delete in smaller chunks and inject some artificial delays and/or checkpoints / log backups in between each individual delete.


Specifying Order of Executing Stored Procedures Issue:

The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error:

Msg 2627, Level 14, State 1, Procedure sp_ETL_S1_tbl1, Line 40
Violation of PRIMARY KEY constraint 'PK__tbl1__EE1C17C10200000'. Cannot insert duplicate key in object 'S1.tbl1'. The duplicate key value is (0).
The statement has been terminated.

Hypothesis:

I believe this is due to the first stored procedure containing the DROP TABLE statements, and for some reason it doesn't get called first when all the stored procedures are run together.

Offending Code:

mssql
execute_sps.sql

USE db1;
GO
EXEC sp_ETL_db1_CreateSchemas;
   EXEC sp_ETL_dbo_tbl1;
   EXEC sp_ETL_dbo_tbl2;

Procedures above defined:

Stored procedure to create schema and tables.

sp_ETL_db1_CreateSchemas.sql

USE db1;

DROP PROCEDURE sp_ETL_db1_CreateSchemas;
GO
CREATE PROCEDURE sp_ETL_db1_CreateSchemas
AS

GO
DROP TABLE S1.tbl1
DROP TABLE S1.tbl2

DROP SCHEMA S1;
GO
CREATE SCHEMA S1;
GO
CREATE TABLE S1.tbl1(
    SalesGroupID       varchar(20)
        NOT NULL         PRIMARY KEY
,   SalesGroupType     int
,   SalesGroup         varchar(20)

)
;
GO
CREATE TABLE S1.tbl2(
    [NameID]                    int
        NOT NULL                    PRIMARY KEY
,   [FirstName]                 varchar(50)
,   [MiddleName]                varchar(50)
,   [LastName]                  varchar(50)
,   [LastUpdated]               datetime
)
;

Stored procedures to ETL data from (olddb.tbl1, olddb.tbl2) to (db1.S1.tbl1 , db1.S1.tbl2)

USE db1;
GO
DROP PROCEDURE sp_ETL_S1_tbl1;
GO
CREATE PROCEDURE sp_ETL_S1_tbl1
AS

SET NOCOUNT ON
IF object_id('tempdb.dbo.#SalesGroup') IS NOT NULL
BEGIN DROP TABLE #SalesGroup END

SELECT
    [SalesGroupID]
,   [SalesGroupType]
,   [SalesGroup]
INTO #SalesGroup
FROM olddb.tbl1
;

INSERT INTO db1.S1.tbl1(
    [SalesGroupID]
,   [SalesGroupType]
,   [SalesGroup]
)
SELECT
    [SalesGroupID]
,   [SalesGroupType]
,   [SalesGroup]
FROM #SalesGroup

USE db1;
GO
DROP PROCEDURE sp_ETL_S1_tbl2;
GO
CREATE PROCEDURE sp_ETL_S1_tbl2
AS

SET NOCOUNT ON
IF object_id('tempdb.dbo.#Names') IS NOT NULL
BEGIN DROP TABLE #Names END

SELECT
    [NameID]
,   [FirstName]
,   [MiddleName]
,   [LastName]
,   [LastUpdated]
INTO #Names
FROM olddb.tbl2
;

INSERT INTO db1.S1.tbl2(
    [NameID]
,   [FirstName]
,   [MiddleName]
,   [LastName]
,   [LastUpdated]
)
SELECT
    [NameID]
,   [FirstName]
,   [MiddleName]
,   [LastName]
,   [LastUpdated]

FROM #Names

SQL Server will execute the statements in a batch one at a time, from top to bottom. Each statement will complete before the next is executed. In your case the this means the first SP runs to completion, then the second one starts and runs to completion, then the third starts and runs to completion. There is no circumstance in which anything else can occur.
Your error is with how you define stored procedure sp_ETL_db1_CreateSchemas. You use the GO command as if it were part of the stored procedure definition. It is not. As the documentation says

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server.

When you run file sp_ETL_db1_CreateSchemas.sql through SSMS (or any other utility) the statements from the beginning of the file to the first GO are sent to the SQL Server engine. When that call returns the statements between the first GO and the second GO are sent, and so on. Eventually all the batches in that file are processed and the result is what you expect.
Or it is mostly what you expect. The definition of the stored procedure is just a stub consisting of the three lines
CREATE PROCEDURE sp_ETL_db1_CreateSchemas
AS

You can verify this by looking at what SQL Server has stored internally as the definition of that SP:
exec sp_helptext 'sp_ETL_db1_CreateSchemas';

You'll see just those three lines.
When you run the batch consisting of the three SPs together it is this empty stub which is executed. Syntactically it is correct so you do not get an error. But it has no statements so no tables are dropped and all the data remains in tact. Hence when a subsequent SP tries to re-load the same data there are key violations.
If you need to batch statements within a stored procedure you can use the EXECUTE('...'); syntax.


How to use a column's latest date in query? SELECT
smart_allusers_karen.lastlogin,
smart_allusers_karen.supemail,
smart_allusers_karen.regionname
FROM
smart_allusers_karen
WHERE lastlogin < MAX(smart_allusers_karen.lastlogin) - INTERVAL 30 DAY AND
      lastlogin > MAX(smart_allusers_karen.lastlogin) - INTERVAL 60 DAY
ORDER BY lastlogin

Getting error when trying to get the MAX date.  Also not sure if this is the fastest way to do this.

Perhaps you can split the query up to catch the DateTimes in separate variables

SELECT lastlogin INTO @LastLogin FROM smart_allusers_karen ORDER BY lastlogin DESC LIMIT 1;
SET @ThirtyDaysAgo = @LastLogin - INTERVAL 30 DAY;
SET @SixtyDaysAgo  = @LastLogin - INTERVAL 60 DAY;
SELECT
    smart_allusers_karen.lastlogin,
    smart_allusers_karen.supemail,
    smart_allusers_karen.regionname
FROM
    smart_allusers_karen
WHERE lastlogin < @ThirtyDaysAgo  AND
      lastlogin > @SixtyDaysAgo
ORDER BY lastlogin;

Please make sure lastlogin is indexed. If it is not, run this first:

ALTER TABLE smart_allusers_karen ADD INDEX (lastlogin);

Give it a Try !!!


Where is the default location for the SQL Agent Job step output file? How do I update it? I recently migrated my system databases to a new location.

I then experienced failures of my SQL Agent jobs with the following error:

2015-10-03 01:53:10 - ! [136] Job  -     reported: Unable to open Step output file.

I can set the location for the step output file by accessing the 'Job Step Properties - Advanced' tab, but it is blank.

Where is the default location for this?

I have found resources which specify it is
The default location depends on the location of your

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\xxxxx.txt

but this is not the case on my server.  Is there an environment variable that holds the path to MSSQL\Log as I changed the directory and this would explain why the failures occurred.  I would like to update the default without having to enter a static location for every job.

I initially thought this could be set by using the sp_set_sqlagent_properties store procedure like so:

USE MASTER
GO
EXEC msdb.dbo.sp_set_sqlagent_properties
@errorlog_file=N'U:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'
GO

But this still allows existing jobs to fail.

Is there a way to script the update of the step file location from one drive to another but using the same file paths and names?

If the Job Step runs a Maintenance Plan, then the logging is perhaps being done in the "Maintenance Plan" which is somewhat common. Choose the specific Maintenance Plan (from SSMS -> Management -> Maintenance Plans) that is referenced by the Agent Job, click on the circled icon for "Reporting and Logging" and check the logging location on it as shown in the dialog below:


Unknown column in Having Clause Getting this apparently nonsensical error
Error code: 1054 Unknown column in having Clause

while running this query
Select date(START_TS), resource_key, count(INTERACTION_RESOURCE_ID)
From tableName
Group By date(START_TS), resource_key
Having date(START_TS) > '2020-08-27' and resource_key in(772122, 772134);

Although the objective has been achieved by using Where Clause before Group By, but this error is still bothering. Any help would be appreciated.

Havin can only use colmuns that are in the GROUP By or SELECT. But START_TS as columns doesn't exist, so you must use an ALIAS  to get your dates.
Schema (MySQL v5.7)
CREATE TABLE tableName (
  `START_TS` date,
  `resource_key` int,
  `INTERACTION_RESOURCE_ID` varchar(4)
);

INSERT INTO tableName
  (`START_TS`, `resource_key`, `INTERACTION_RESOURCE_ID`)
VALUES
  ('2020-08-27' , 772122, 'test'),
    ('2020-08-27' , 772122, 'test')
;

Query #1
Select date(START_TS) date_ts, MIN(resource_key), count(INTERACTION_RESOURCE_ID)
From tableName
Group By date(START_TS), resource_key
Having date_ts >= '2020-08-27' and resource_key in (772122, 772134);

| date_ts    | MIN(resource_key) | count(INTERACTION_RESOURCE_ID) |
| ---------- | ----------------- | ------------------------------ |
| 2020-08-27 | 772122            | 2                              |

View on DB Fiddle


How to recreate Oracle db from .ora and .dbf? I have a number of files left from a previous Oracle db and I would like to create a new one from the files.

The files include:

init_locdb.ora
16 .dbf files
spfilelocdb.ora
log files
.ctl

Could you tell me is it possible to recreate a new Oracle database from the files so it is identical (almost identical) to the previous one?

The various data files that comprise the physical storage of the Oracle database instance can vary depending on the operating system, endian format, and pretty sure some of the files may have a hostname hiding in them somewhere.  And if those files were obtained from a database instance that was running, there is no way those files could be used to recreate a usable database instance, even on the exact same server as the files came on.

The *.ctl files are updated every 3 seconds or so with timestamps and pointers (oversimplifying but fairly accurate), so sorry, no way.


sp_BlitzIndex "Missing Index" section has columns but no values/results when I run sp_BlitzIndex @Version = '7.5', @VersionDate = '20190427', on my Microsoft SQL Server 2017 v14.0.3048.4, I have some tables where the missing indexes reports one column Finding with the value "No Missing Indexes".  This is perfect.

But in many cases I have what appears to be result columns with no values. It looks like this:

Finding | URL | Estimated Benefit | Missing Index Request | Estimated Impact | Create TSQL

And there is nothing in the rows.  My fear is that I actually have some missing indexes but some wire is crossed and the output isn't being produced.  I have been using this tool for years and find it invaluable, so I want to be certain I am actually seeing the correct results.

That just means SQL Server's not recommending indexes for that table, or the index recommendations aren't a significant benefit. We filter out trivially insignificant missing indexes, like those with a very low benefit that have only been run a few times.

If you'd like to try other scripts, you can use any number of them.


Pass an array or record to a function in PostgreSQL? I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.

Postgres has very flexible handling of arrays and composite types. This may be the sort of thing you are trying to do:

create type my_type as (val1 integer, val2 integer);

create function my_function(arr my_type[]) returns text language plpgsql as $$
begin
  return arr::text;
end;$$;

select my_function(array[row(1,2),row(3,4)]::my_type[]);

| my_function       |
| :---------------- |
| {"(1,2)","(3,4)"} |

dbfiddle here


Snapshot_isolation_state_desc on a Read only/Stand by Database I have configured Log Shipping where the primary server database has snapshot_isolation_state_desc reported as off.  I tried to turn it off on the secondary which is a standby read only by using the GUI and by using the T-SQL statement to disable:

ALTER DATABASE ABC SET ALLOW_SNAPSHOT_ISOLATION OFF;

However, it turns back on every time the SQL Server restarts.

Is this expected behaviour? If so, why?

SQL Server always seems to set snapshot isolation (SI) on when a database is made read only. I have no idea why it does this, but it does.

You can try to turn it off, and the ALTER DATABASE statement will succeed, but snapshot isolation remains enabled while the database is read only.

One theory is that setting SI prevents unexpected errors for people running an SI transaction and accessing the read-only database, but it seems like there should be a deeper technical reason.

In any case, the behaviour is "normal", so you should not worry about it.


Reporting Services service restart fixes slow reports SQL 2012, the reports are displayed on a WEB page or rendered to PDF

I have an ongoing problem with reports suddenly slowing down. The only way to "fix" this problem is to restart the service. This occurs after an unknown number of days passing, seemingly at random one of my reports will start timing out in the application (must be under 5 minutes). The report that starts failing may normally run in two to three seconds or one to two minutes but now Reporting Service does not reply with in the available window. When profiling the procedure call being executed by the report is returning the data in a timely fashion but then nothing is returned to the application.

The last time this happened I started a profile and found this, I see the procedure call for the report return in 400 milliseconds. The report is then rendered about five minutes later. In this interval Report server queries for the parameters of the requested report 4 to 6 times, I see sessions for the report created and deleted, checks for snapshots (snapshots turned off for this report).

As soon as the Reporting Services service is restarted the report returns to its normal behavior Request to Render 3 seconds. I looked at the execution logs and found that from the service side reports it spent all of its time getting the data from the database but the profiler is telling me a different story. If I run the same report from Reporting Server Management I get the same slow response so I do not think it has anything to do with the app that is requesting the report.

Things I have tried; uploading the report again, rewrite the procedure to improve performance, for the currently miss performing report. Nothing helps but restarting the service.

Has anyone seen this, if so how did you address this issue? This issue happened in SQL 2008R2 as well but not as often about ever 6 months and to make matters worst it is happening more frequently. Over the last month it occurred 3 times.

The stats are up to date. The procedure ran at normal speed in SSMS, about one second for the procedure for the report. I did not look at waits. It may be that the reports database was experiencing waits but I am not sure why restarting the service would resolve the problem. Other reports were not experiencing any slow down so it seems unlikely there were issues with waits.

The report server does not slow down just one report does. Other report requests are completed normally. Performance monitor shows no increase in CPU usage, no uptick in IO, network activity stays normal.

I believe problem was that the report definitions no longer matched the stored procedures that were called. After I refreshed all of my reports the problem seems to be resolved.

Unfortunately I can not say for sure that is the final answer as it could be up to 90 days between the last restart and as little as 4 days, so I am still waiting to remove this from my task list.

These are old reports that were originally in version 2005.


Freeing up disk space in 'mysql\data' I am trying to free up some space used by a MySQL instance, but I know pretty much nothing about MySQL.

I notice that in the mysql\data folder there are lots of localhost-bin.0001** files, as illustrated in this screen shot:

What are these? Can they be backed up/moved/deleted?

Edit

It seems from look here that these files are log files and can be deleted.

The post mentions:

As long as you do not have mysql replication installed, then [you can delete the files].

How do I check this?

First, you should run the following:
SELECT COUNT(1) ReplicationUserCount
FROM mysql.user WHERE Repl_slave_priv = 'Y';

If ReplicationUserCount > 0, then the DB Server can be used as a Master. Ask your sysadmins or DBAs if there are any active or dormant Slaves.
If ReplicationUserCount = 0, then the DB Server is standalone. You could then just delete all or some of your binlogs using these methods:
METHOD #1
RESET MASTER;

When you run this, all binlogs are erased and localhost-bin.000001 is created.
ALTERNATIVE: Shutdown mysql, delete localhost-bin.*, and startup mysql.
METHOD #2
PURGE BINARY LOGS TO 'localhost-bin.000190';

When you run this, all binlogs before localhost-bin.000190 are erased.
METHOD #3
PURGE BINARY LOGS BEFORE DATE(NOW());
PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 3 DAY;
PURGE BINARY LOGS BEFORE '2014-03-26 15:30:00';

When you run this these, this is what they do

all binlogs before today are erased
all binlogs before midnight 3 days ago are erased
all binlogs before 3:30PM on Mar 26, 2014 are erased

CAVEAT #1
If methods 2 or 3 fail, this indicates that there may be a problem with the file localhost-bin.index. It keeps a text file with the list of the bin logs. If the file is out of sync, use METHOD #1. It will recreate localhost-bin.index.
CAVEAT #2
Do not erase the binlogs from the Linux command line with rm or from the Windows Explorer. Doing so will throw the localhost-bin.index out of sync. If you do that, just do METHOD #1 and mysqld will clean it all up.
CAVEAT #3
If you set expire_logs_days = 7 in your my.cnf (or my.ini), it will automatically run this
PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 7 DAY;

on every log rotation or mysql restart.
EPILOGUE
Since you are interest in recovering space, choose one of the three methods. You can place expire_logs_days = 7 in your my.cnf (or my.ini) if you want. If you do not want it to grow past2 or 3 binlogs, then set this in your config file:
[mysqld]
expire_logs_days=1

and run
SET GLOBAL expire_logs_days=1;

to limit the number of binlogs to a single day.
Keep in mind that setting expire_logs_days does not work if localhost-bin.index is out of sync.


Recommended page file size for SQL 2008R2 on Windows 2008R2 This Microsoft article - How to determine the appropriate page file size for 64-bit versions of Windows Server 2008 and or Windows 2008 R2 provides guidance for calculating the page file size for 64 bit Windows 2008 and Windows 2008R2. This no doubt works fine for general purpose servers. I'm wondering what the guidance is for SQL Server 2008R2 running on Windows 2008/R2 64 bit?

I'm presuming we want as little in memory data to be hitting the page file otherwise SQL could be hitting the disk twice for data. Does SQL Server even allow data in memory to hit the page file? I've hunted through SQL Server 2008 R2 Books Online for guidance but haven't yet found any mention of page file use.

Here's a potential usage scenario: Given a physical server with 64GB of RAM, is a pagefile necessary for the entire 64GB of RAM? Should we gear it up for 96GB of pagefile? That does seem a bit excessive for a single file. I know conventional wisdom has been that Windows couples pagefile to memory in an attempt to make swapping out apps easier on RAM, but is that true? Will a less than 64GB pagefile hinder performance here?

There are no special settings for SQL Server which only uses physical memory normally

Just do what MS say for Windows and that's it

Oh, and buy more RAM anyway while we're one the subject... ;-)


How can I append a new datafile in an existing InnoDB tablespace? I want to add a new data file in an existing InnoDB tablespace so that entry of data is smooth in case it hits its 4GB limit.

Plan A:  Reformat the disk to be NTFS.  Caution:  This destroys all data, so you would need to do a major backup and restore.

Plan B:  Turn off innodb_file_per_table, go into my.cnf and configure the main tablespace in a way I have not seen used in more than a decade -- namely something like ibdata1:3G,ibdata2:3G,ibdata3:3G,ibdata4:3G,ibdata5:3G.  That way, you are pre-specifying the partitions and InnoDB (unless it has forgotten how) will be able to handle tables of arbitrary size (> 4GB) if they are in that tablespace.  Finally, ALTER TABLE foo ENGINE=InnoDB to move the table into that tablespace.

Yuck.  Turn back the clock because of Windows.


SSIS: Slowly changing dimension with lists (one-to-many relationship) Slowly changing dimension (specifically type 2) is a brilliant concept for being able to keep historical periodic (e.g. daily) data without the storage overhead of taking an entirely new snapshot of the data every time it is updated. However, all examples I have seen so far assume my data for every element I am modelling is contained in one single row. For example, a person, and their name, age, and address. Now what happens when my source data has a multi-valued dimension (or in other words, a list)? Let's say I want to keep track of the number of cars someone owns over time. Expressed as tables, my source data would look like:

                         Car
Person                   ---
------                   CarID
PersonID <-------------> PersonID
First Name               Year
Last Name                Make
Age                      Model
Street Address           Color
City
State

Yesterday, a person had 3 cars. Today, they have four. Maybe in the future, they have 2, but 2 that are completely different from the original 4 cars. How can I use SCD2 (Historical) to track this one-to-many relationship?

I can't find a lot online about how to do this, which probably means I am not using the correct search terms because I am positive others have solved this problem. Thinking of my own solution, I could imagine my Target SCD data is modeled as such:

PersonScd
---------               CarScd
PersonBK                ------
CarListSK <-----------> CarListSK
First Name              CarID
Last Name               Year
Age                     Make
Street Address          Model
City                    Color
State
EffectiveDate
ExpirationDate

This is a hybrid approach where if any item in a list changes from one time to the next, the list is completely rewritten with all the new details, and each list has its own ListSK. Therefore, in this example design:

Yesterday a person had 3 cars. This person has one row in the PersonScd table with an ExpirationDate of 9999-12-31. The CarScd table has three rows, each with the same CarListSK. The other details of each row in the CarScd table match those of the source Car table for this person. The person's CarListSK column holds the value of CarListSK that all three of the cars have in the CarScd table.
Today, this person now owns a fourth car. This means yesterday's row for this person expires (the ExpirationDate is now set at yesterday's date), and a new row is written to the PersonSK table for that person - the EffectiveDate is set to today and the ExpirationDate is 9999-12-31. Now, 4 new entries are written to the CarScd table, and all of these entries share a new CarListSK. Aside from the effective and expiration dates, the only difference between this person's row yesterday and today is the CarListSK column.
Now, as long as I join the PersonScd table to the CarScd table using the CarListSK column, I can know what cars a person owned on a certain date.
If any other attribute about this person changes in the future, such as his/her address, the new row written will retain the same CarListSK with 4 corresponding entries since his/her cars did not change.
If in the future the person now owns only 2 cars (whether or not they were the same as the previous 4), 2 totally new rows will be written in the CarScd table with a new common CarListSK, which the new PersonScd row will have for that person.

Assuming this is a feasible design, how do I build this behavior in SSIS? SCD in SSIS compares one row at a time, not lists at a time. I imagine I'd have to just use a script component.

If this design is flawed, what is the correct way to use Slowly Changing Dimension Type 2 with one-to-many lists?

I think that in order to keep the logical aspect of a dimension you should use a third table to relate both dimensions:

PersonScd                     GroupOfCars               CarScd
---------                     -------------              ------
PersonBK      <--------->                       <----->  PK_Car
Unique_Person_code                                       Unique_Car_code
First Name                     Unique_Car_code            Year
Last Name                      Unique_Person_code         Make
Age                                                       Model
Street Address                                            Color
City                                                      EffectiveDate
State                                                     ExpirationDate
EffectiveDate
ExpirationDate

*
Unique_Car_code and Unique_Person_code are not foreign keys but unique_numbers used to identify a unique car or a unique person.


Mysql query - find out 1000th row from a table for each user Lets say I have a table where we log activities of user, I need to find out created_at of the 1000th activity of each user..

Following query works fine for one user..

select `created_at` from `user_activity` where `user_id` = 2 order by `created_at` limit 1 offset 1000

But how do i run it for all users..

I have tried

where user_id in (select DISTINCT(user_id) from user_activity)

OR

GROUP BY user_id

To get all the (1000th) rows, using the primary key of the table:

SELECT du.user_id
     , ua.*                               -- any other column you need
FROM
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua
      ON ua.pk =                          -- the PRIMARY KEY of the table
         ( SELECT ai.pk
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at
               LIMIT 1 OFFSET 999
         ) ;

or using the (user_id, created_at) combination:

SELECT du.user_id
     , ua.*
FROM
    ( SELECT DISTINCT user_id
      FROM user_activity
    ) AS du
  LEFT JOIN
    user_activity AS ua
      ON  ua.user_id = du.user_id
      AND ua.created_at =
         ( SELECT ai.created_at
           FROM user_activity AS ai
           WHERE ai.user_id = du.user_id
           ORDER BY created_at
               LIMIT 1 OFFSET 999
         ) ;

I guess the number of rows in the table per user is high, so an index on (user_id, created_at, pk) would be good for efficiency.


Update strings for all cmdexec Jobs Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?

We'd like to change our logging location, and in principle I'd think this could be done very simply by changing the /O "\\LOCATION\file.log" strings.

I can see how you'd do that in fact

USE MSDB
GO
update dbo.sysjobsteps
set [command] = replace([command], '\\OLDLOCATION\... ', '\\NEWLOCATION\... ')
WHERE...

BUT I've had it drummed into me that you should never update the sys tables manually, so the idea of doing this makes me beyond nervous. For anything else I'd use the dbo.sp_update_job proc (http://msdn.microsoft.com/en-gb/library/ms188745.aspx for those who've strayed here looking for something else job related), but that procedure doesn't seem to be able to handle this.

I have a hunch that this might possibly be an exception to the "Never Update the Sys Tables" rule though? I can't imagine that the cmdexec string is implied in any further relations in the way, for example, enabled/disabled status evidently is. Wishful thinking?

So I don't know for sure, and I can't seem to find out. Anyone know or have any experience with this?

Thanks
Dan

In my opinion, this is one of those operations that would definitely benefit from PowerShell.  All you'd need to do is gather the list of job steps that have your old string (in this case, your old location) and replace it with the new string (read: new location).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$OldLocation = "<YourOldLocation>"
$NewLocation = "<YourNewLocation>"

$SqlServerName = "localhost"    # by default this will look at the local default instance
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

# loop through all of the SQL Server Agent Jobs on the instance
foreach ($Job in $SqlServer.JobServer.Jobs) {

    # loop through each Job Step on the current Job
    foreach ($JobStep in $Job.JobSteps) {

        # test to see if the Job Step Command text contains your original string
        if ($JobStep.Command.Contains($OldLocation)) {
            $JobStep |
                Select-Object Parent, Name, Command

            # the below two lines would make the change to the new string and apply them
            #$JobStep.Command = $JobStep.Command.Replace($OldLocation, $NewLocation)
            #$JobStep.Alter()
        }
    }
}

NOTE:  This is sample code and should thoroughly be tested in a non-production environment.  Ensure that your previous data is backed up, and that you are positive the change is the desirable outcome.

All this code does it loops through all your jobs on a specific instance.  If the job step command contains a certain string, then it will show you what job it belongs to, the name of the step, and the command containing the string.  I have deliberately commented out the command modification lines so that the code only actually identifies which steps contain your string.

You can also narrow your search by adding additional conditional clauses to the if block.  For instance, if you just want to check CmdExec jobs, add this with an -and conditional check:

$JobStep.SubSystem -eq [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::CmdExec

This is simply one way to do this.  I will stress my point again: make sure you test out this theory and execution far away from production, and ensure that you can revert changes with the necessary backup.


Can Postgres use an index-only scan for this query with joined tables? This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns?

Consider the inverse of the schema in the other question where you filter in the joined-on table:

CREATE TABLE thing_types(
   id              INTEGER PRIMARY KEY
 , first_lvl_type  TEXT
 , second_lvl_type TEXT
);

CREATE TABLE things(
   id         INTEGER PRIMARY KEY
 , thing_type INTEGER REFERENCES thing_types(id)
 , t1c1       INTEGER
);

And a query like so:

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

Is it madness to have an index like:

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

which covers the primary key for use in that join? Will the index be used as a covering index to help the JOIN in the above query? Should I change my indexing strategy to cover the primary key more often when I know the table is going to be JOINed on like this?

If additional preconditions for an index-only scan are met, it makes perfect sense to append the column id as trailing column to the index (not as leading column):

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

Postgres 11 introduces actual covering indexes with the INCLUDE keyword.

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type) INCLUDE (id);

Only a small benefit for your case, but it's a great option to add columns to a UNIQUE or PK index or constraint.

About index-only scans:

The Postgres Wiki
The manual

The most important precondition: The visibility map of table thing_types has to show most or all pages as "visible" to all transactions. I.e. the table is either read-only, or your autovacuum settings are aggressive enough to continuously clean up after writes to the table.

Every additional index adds costs. Mostly to write performance. But also side effects, like exhausted cache capacities. (Multiple queries using the same indexes have a better chance for them to reside in cache.) So it's also a question of size. id is typically a very small column integer or bigint. Makes it a good candidate for the use case.

In particular, adding a column to an index disables the option for H.O.T. updates involving the column. But since id is indexed anyway and typically not updated (being the PK) this is not a problem in this case. Related:

Redundant data in update statements

If you actually get index-only scans out of these indexes most of the time, it typically makes sense to use them. Test with EXPLAIN.

There were limitations for partial indexes in older versions. Quoting the release notes of Postgres 9.6:

  Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed (Tomas
  Vondra, Kyotaro Horiguchi)

  For example, an index defined by CREATE INDEX tidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that
  specifies WHERE a > 0 and does not otherwise use a. Previously
  this was disallowed because a is not listed as an index column.


How to design a database for financial bond prices? I have following flat file table composed of bond deal names and asociated prices.

Each deal name is identified by its unique CUSIP and ISIN codes and each deal name is classified with a certain deal type.

One deal name can have many prices over the course of a day.  Each price is classified with a different price type; as in the following table:

field names are shown in brackets like (XXX).

CSAMF 2A PREF (name) 12629B309 (cusip) 3,000,000 (size) M50S (original price) 55.00 (price) TALK  (price_type) CLO (deal_type)  23/09/2012 (date)
CSAMF 2X PREF (name) KYG2577W2005 (isin) 2,000,000 (size) M50S (orig_price) 55.00 (price) TALK  (price_type) CLO (deal_type) 23/09/2012 (date)
FN 465343 (name) 31381M5C5 (cusip) 2,613,100 (size) 80a (origin_price) 80a (cmbs_spread) 80.00 (cmbs_price) TALK (price_type) CMBS (deal_type) 24/09/2012 (date)
FN 465273 (name) 31381M2E4 (cusip) 1,760,000 (size) MH70s (origin_price) MH70s (Cmbs-spead) 77.00 (cmbs_money)TALK (price_type) CMBS (deal_type) 24/09/2012 (date)

Each deal name has either one CUSIP or one ISIN or both.  Prices with deal type of CMBS (Commercial Mortgage Backed Securities) are recorded from the original price into CMBS spread and prices with deal type other than CMBS are converted into money prices.

I have constructed two possible solutions for the relational database, as shown below:

Design 1:

Deals (deal_ID, name, CU_code, IS_code, deal_type)

Price (price_id, orginal_price, money, cmbs, cmbs_money)

Deal_price (deal_id, price_id, date, size, price_type)

Design 2:

 CREATE TABLE [dbo].[deal_2](
[cusip] [varchar](100) NULL,
[isin] [varchar](100) NULL,
[name] [varchar](100) NOT NULL,
[deal_type] [varchar](100) NOT NULL
 ON [PRIMARY]

 CREATE TABLE [dbo].[price_2](
[price_id] [int] NOT NULL,
[cusip] [varchar](9) NULL,
[isin] [varchar](12) NULL,
[orig_price] [varchar](50) NOT NULL,
[price] [decimal](18, 2) NULL,
[cmbs_spread] [varchar](50) NULL,
[cmbs_price] [decimal](18, 2) NULL,
[size] [int] NOT NULL,
[price_type] [varchar](50) NOT NULL,
[date] [date] NOT NULL
ON [PRIMARY]

Is it possible to construct a relational database design for this domain?

Which DDF would be appropriate for this domain?

Not strictly an answer... but... my 2cents (not interest bearing, might interest ing).

So, Security X has identifier (ISIN or CUSIP).
This identifier doesn't identify the trade, exchange, currency - just the security.

This Security has value, established by trades - which fluctuates over time, place...
This is tracked by deals. A deal requires a Security.

Price is parcel of deal.

I would create a table of Securities - Security (it has an ID int identity(1,1) PK), Code Unique constraint, CodeType (CUSIN/ISIN), linksToID (here is a funny: ISIN can have CUSIP too so link both to each other so you can identify the securities by either code)
etc.

I would create a table of Deals - Deal (it has an ID int identity(1,1) PK), time, place, and Security.ID, and TYPE).

I would create a table of Prices - Trade (it has an ID int identity (1,1) PK, DealID, Value, currency, TIME, etc)

So, I would create a unique/distinct list of securities, and insert that into Security.
Matching the Security to TradeReport (assuming the trade report to be the data you want to capture contains either ISIN or CUSIP but not neither) insert the ID of the matching ISIN of the trade data, and then inserting the matching ID of the CUSIP matching if a linked item isn't already there! to get a unique deal list.

Then I would reference the different deals back to the same import data and capture the prices of the deals to the prices table.

See the hierarchy?

from there you can splice and dice your analysis...

Yeah, that about covers my thinking... hope it helped.


Multi master single slave replication mysql I have a scenario in which there are two masters master 1 and master 2 and i want to add slave to master 2 , so when i update in master 1 it will replicate for master 2 so how do i get slave of master 2 to update form it ? What is the  parameter to be set for it?

I am assuming you already have Master1 and Master2 acting as Slaves of each other?

The slave will be replicating from only one of the Masters.  It will get data from the other Master via that one.  But you need (and probably already have):

log_slave_updates = ON  -- on all 3 servers
server_id -- distinct values for all 3 machines


Import csv file into sqlite3 table - ERROR "Insert failed: datatype mismatch" I am using sqlite3 and I am writing my scripts in two files. The first one is called "Books.sql"

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    books_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    author TEXT,
);

The second one is called "Import_books.sql "

.mode csv Books
.import C:/Users/Jim/books_data.csv Books

My goal is to import in "Import_books.sql", the csv file "books_data.csv" that contains a list of books into the table Books that I created in "Books.sql"

I made sure the csv matches exactly the structure of the table but I get this error
INSERT failed: datatype mismatch. How can I solve this?

I tried to import using your csv format

    CREATE TABLE Books1 (
    books_id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    author TEXT
    );
    sqlite> .mode csv
    sqlite> .separator "-"
    sqlite> .import books1.csv books1
    books1.csv:1: unescaped " character
    books1.csv:2: unescaped " character
    books1.csv:2: unescaped " character
    books1.csv:3: unescaped " character
    books1.csv:1: expected 3 columns but found 1 - filling the rest with NULL
    books1.csv:1: INSERT failed: datatype mismatch

I fixed csv removing quotes for each record and comma that separates records

sqlite> .import books1.csv books1
sqlite> select * from books1;
337864-" Pride and Prejudice "-" Jane Austen"
783356-" Ivanhoe "-" Walter Scott"
987786-" Harry Potter and the Goblet of Fire "-" J.K. Rowling"

Did you notice hyphens after first two columns not neat.

I fixed csv file to correct format and this is the result

sqlite> select * from books;
337864,"Pride and Prejudice","Jane Austen"
783356," Ivanhoe","Walter Scott"
987786," Harry Potter and the Goblet of Fire","J.K. Rowling"


Sharding a MongoDB instance by a spatial key and a date This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries that I'm doing to retrieve some of these objects.

This database will have to be sharded, so sharding according to the spatial key sounds like the first step. It's basically a given that this index will be part of the sharded key. The other key should be time, and I'm pretty sure that hashing the time will not be adequate, as the queries are looking for date interval for the objects. So hashed will make force accessing all chunks (with the proper spatial constraints), which I don't want.

So my question is to know if this is feasible and also if mongodb will be clever enough not to split according to the spatial key unless I manually ask for it?

my question is to know if this is feasible

A shard key index cannot be multi key, text, or geospatial, so part of your theoretical approach is definitely infeasible. You also mention considering hashed sharding on a datetime field, which could provide better data distribution for otherwise monotonically increasing values like a timestamp. However, a tradeoff is that hashed sharding does not support range queries since adjacent source values will now have distributed shard key values.

  also if mongodb will be clever enough not to split according to the spatial key unless I manually ask for it?

Since a shard key cannot be an array or geospatial value this specific question doesn't really apply.

The default behaviour of MongoDB sharding is to allow shard key range splits (aka "chunk splits") to happen automatically so that the sharded cluster balancer will redistribute data between shards based on chunk imbalances. It is possible to adjust the default behaviour (for example, disabling autosplits for a cluster or balancing for a collection), but you should only do this with careful consideration. Micromanaging a sharded cluster can be counter-productive.


Where deadlock happen in mysql I am unable to read below logs of mysql after executing SHOW ENGINE INNODB STATUS command. Can anyone please help me out where the deadlock has happen?

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-10 11:39:35 7f12f044d700
*** (1) TRANSACTION:
TRANSACTION 364307, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 1066 lock struct(s), heap size 112168, 71366 row lock(s), undo log entries 12
MySQL thread id 16407, OS thread handle 0x7f12f28a3700, query id 176797 localhost 127.0.0.1 synapqadb updating
DELETE FROM E_CUST WHERE M_ID=7419
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139 page no 4 n bits 384 index `PRIMARY` of table `xyz`.`E_CUST` trx id 364307 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 800000e8; asc     ;;
 1: len 6; hex 00000000196a; asc      j;;
 2: len 7; hex f2000001a00110; asc        ;;
 3: len 4; hex 80000053; asc    S;;
 4: len 7; hex 43555354303038; asc CUST008;;
 5: len 14; hex 435553545f504f5354414c5f4344; asc CUST_POSTAL_CD;;

*** (2) TRANSACTION:
TRANSACTION 364314, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
259 lock struct(s), heap size 46632, 77517 row lock(s), undo log entries 7
MySQL thread id 16405, OS thread handle 0x7f12f044d700, query id 176810 localhost 127.0.0.1 synapqadb updating
UPDATE R_CUST SET RES_LABEL= '' , DESCRIPTION='Postal Code Missing|Address, Phone And Email Are Invalid / Missing|Error Record From Wiley', COMMENTS=CONCAT(IFNULL(COMMENTS,''),'') WHERE STATUS='0' AND TYPE='DATA VALIDATION' AND M_ID=19191
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 139 page no 4 n bits 384 index `PRIMARY` of table `xyz`.`E_CUST` trx id 364314 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Thank you.

In short: SHOW INNODB STATUS will give you the last detected deadlock.
 use SHOW ENGINE INNODB STATUS for MySQL 5.x

Line 1 gives the time when the deadlock happened. If your application code catches and logs deadlock errors,which it should, then you can match this timestamp with the timestamps of deadlock errors in application log. You would have the transaction that got rolled back. From there, retrieve all statements from that transaction.

Line 3 & 11, take note of Transaction number and ACTIVE time. If you log SHOW ENGINE INNODB STATUS output periodically(which is a good practice), then you can search previous outputs with Transaction number to hopefully see more statements from the same transaction. The ACTIVE sec gives a hint on whether the transaction is a single statement or multi-statement one.

Line 4 & 12, the tables in use and locked are only with respect to the current statement. So having 1 table in use does not necessarily mean that the transaction involves 1 table only.

Line 5 & 13, this is worth of attention as it tells how many changes the transaction had made, which is the “undo log entries” and how many row locks it held which is “row lock(s)”. These info hints the complexity of the transaction.

Line 6 & 14, take note of thread id, connecting host and connecting user. If you use different MySQL users for different application functions which is another good practice, then you can tell which application area the transaction comes from based on the connecting host and user.

Line 9, for the first transaction, it only shows the lock it was waiting for, in this case the AUTO-INC lock on table t1. Other possible values are S for shared lock and X for exclusive with or without gap locks.

Line 16 & 17, for the second transaction, it shows the lock(s) it held, in this case the AUTO-INC lock which was what TRANSACTION (1) was waiting for.

How to avoid a MySQL deadlock
There are things we could do to eliminate a deadlock after we understand it.

– Make changes to the application. In some cases, you could greatly reduce the frequency of deadlocks by splitting a long transaction into smaller ones, so locks are released sooner. In other cases, the deadlock rises because two transactions touch the same sets of data, either in one or more tables, with different orders. Then change them to access data in the same order, in another word, serialize the access. That way you would have lock wait instead of deadlock when the transactions happen concurrently.

– Make changes to the table schema, such as removing foreign key constraint to detach two tables, or adding indexes to minimize the rows scanned and locked.

– In case of gap locking, you may change transaction isolation level to read committed for the session or transaction to avoid it. But then the binlog format for the session or transaction would have to be ROW or MIXED.


Is it possible to create a multi-layer schema in SQL Server 2008 R2? For instance so you can specify a department, and then maybe have finer levels of granularity?

CREATE SCHEMA hr;
CREATE SCHEMA hr.reportl;
CREATE SCHEMA hr.report2;

The last two statements fail.

No, that is not possible.  You can wrap those names in square brackets, but that is not recommended, since it will likely cause a lot of confusion.  For instance:

CREATE SCHEMA [hr.report];

SELECT statements against objects in the schema would then look like:

SELECT * FROM [hr.report].SomeTable;


How to support multiple updates in this trigger? Below is the code of a trigger, basically it calls a function in case the record that is being updated does not have other row associated by it's Id in another table.
In a multiple row update this trigger fails, how can I edit it to make compatible?

declare @slugExists int =(select Id from [dbo].[UrlRecord]
                              where EntityId = (select Id from inserted)
                              and EntityName = 'Category')

    if(@slugExists is null)
    begin
        declare @slug nvarchar (200) = (select Name from inserted)
        EXEC @slug = dbo.GenerarSlug @str= @slug

        insert into [dbo].[UrlRecord] ([EntityId],[EntityName],[Slug],[IsActive],[LanguageId])
        values ( (select Id from inserted) ,'Category',@slug,1,0)
    end

EDIT

Based in comments, here is the code of the function GenerarSlug :

ALTER FUNCTION [dbo].[GenerarSlug]
(
    @str VARCHAR(500),
    @modificador VARCHAR(3) = NULL
)
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @str = LOWER(@str)
SET @IncorrectCharLoc = PATINDEX('%[^0123456789abcdefghijklmnopqrstuvwxyz ]%',@str)
WHILE @IncorrectCharLoc > 0
BEGIN
SET @str = STUFF(@str,@incorrectCharLoc,1,'')
SET @IncorrectCharLoc = PATINDEX('%[^0123456789abcdefghijklmnopqrstuvwxyz ]%',@str)
END
SET @str = REPLACE(@str,'  ','')
SET @str = REPLACE(@str,'   ','')
SET @str = REPLACE(@str,' ','-')
IF(@MODIFICADOR IS NOT NULL) SET @str = @str + @MODIFICADOR
RETURN @str
END

It transforms 'Especial Chárs' to 'especial-chrs'
The reason I put this in a function is that it will be called from a few triggers in database so I don't repeat it each time

If dbo.Generarslug is a scalar function, then you could use the following:

INSERT[dbo].[UrlRecord] ([EntityId],[EntityName],[Slug],[IsActive],[LanguageId])
SELECT  i.id,
        'Category',
        dbo.GenerarSlug(i.Name),
        1,
        0
FROM    inserted AS i
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    [dbo].[UrlRecord] AS r
            WHERE   r.EntityID = i.ID
            AND     r.EntityName = 'Category'
        );

There is still potentially scope for improvement depending on what dbo.GenerarSlug actually does.


What is the meaning of the segment name of a domain index? I want to know the space used by one of my indexes. I query the view DBA_SEGMENTS to do it:

SELECT segment_name, sum(bytes) FROM dba_segments WHERE segment_name LIKE '%INDEX_NAME%' GROUP BY segment_name;

I got this response:

DR$INDEX_NAME$X 1369440256
DR$INDEX_NAME$I 1586495488
DR$INDEX_NAME$R 65536

What is the meaning of the prefix DR and the suffixes X, I and R in the name of my segments? What is the real size of my index : 1369440256 B, 1586495488 B or 2956001280 B?

The Oracle Text index consists of four basic tables, referred to as the $I, $K, $N and $R tables respectively. Other tables are used when particular indexing options are turned on. The tables exist within the schema of the text index owner, and have names concatenated from DR$, the name of the index, and the suffix (e.g. $I)

Oracle Text Whitepaper

According to the whitepaper, these are tables used by Oracle Text.

Use the following query to get the index size. For example, I have an index called I_ICOL1.

SQL> select segment_name,bytes/1024/1024 MB from dba_segments where SEGMENT_NAME='I_ICOL1';

SEGMENT_NAME        MB
------------        ----------
I_ICOL1             .4375


Mongodb deployment best practice for dual nodes? I have two nodes and I'd like to deploy mongodb on them. It seems there're two methods:

Deploy as master-slave. But it's marked in the official document as "legacy".
Deploy as a replica set. But a 2-node replica may not function well.

Any suggestions?

The first part is the easiest answer - don't use master-slave, it has been deprecated for a couple of years at this point and will be removed in the future (once a few long standing limitations on replica sets are removed).

The second part, how to run with 2 nodes in a replica set is a bit more complicated.  The simplest answer is don't, running one host with a data bearing node and an arbiter to get to 3 nodes is actually preferable.  Consider these scenarios for a 2 node set:

Host 1 down, Host 2 up. Result = no primary
Host 1 up, Host 2 down. Result = no primary
Host 1 up, Host 2 up. Result = healthy

Now, lets add an arbiter on Host 1 and see how that impacts things:

Host 1 down, Host 2 up. Result = no primary
Host 1 up, Host 2 down. Result = healthy
Host 1 up, Host 2 up. Result = healthy

In fact, if you make the second node non-voting it's actually better than having it as a regular replica set node and gives you the same list of scenarios as having an arbiter on Host 1.

Strictly speaking, the answer to your question is that best practices suggest not to run with two nodes at all, since it actually makes the set more fragile from an operational perspective (though at least you have a copy of your data on another host for disaster recovery).

If you have to stick with 2 data bearing nodes, the best practice recommendation is to run an arbiter on one of the hosts, with the non-voting option as your alternative.


Most efficient (or meaningful) way to index grid-based data I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have provided a table dump at the bottom for reference)

For some background, I am working with a 2-D grid, whose ranges vary from approximately -800000 to +800000 in each direction. Each row is identified  by its X/Z coordinate, for which each coordinate may have 1-30 associated bitstrings (type).

The concerns I have are:

1) I lack a meaningful way to Primary key this table. While I know I can create an id field with auto_increment, I know that in practice this key will never be used, as 100% of SELECTS will be in the form of:

SELECT `type`, `offset`, `bitstring` WHERE `x` = 0 AND `z` = 0;

2) I intend to index on multiple columns (the logical x/z), via:

CREATE INDEX coordinate ON bitstrings(x, z)

While I feel this appropriately addresses my real-world selects, I am constantly concerned it is insufficient indexing, based on the dozens of posts saying 'YES, YOU NEED A PK'.  Is this a case where a PK can be ignored or is the arbitrary id still ultimately going to provide some behind-the-scenes optimization well worth the additional table size and column?

As a side note, I have absolutely no limitations on completely restructuring this table, if there is are any more practical, proven ways to store this sort of data.

CREATE TABLE `bitstrings` (
  `x` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` int(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

--
-- Dumping data for table `bitstrings`
--

INSERT INTO `bitstrings` (`x`, `z`, `type`, `offset`, `bitstring`) VALUES
(0, 0, 1, 0, 0x52),
(0, 0, 2, 1878, 0x52);

Two things

Since  range of x and z are +/- 1000000, use meduimint (Range of meduimint is +/- 8388608)
Since ranges of offset and type is 1-65535, use smallint unsigned

Make these chnages

CREATE TABLE `bitstrings` (
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192'
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

When it comes to indexes, you may have a choice

CHOICE #1 : Use a primary key of ID

Setup the table like this

CREATE TABLE `bitstrings` (
  `id` int unsigned not null auto_incrmenet,
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (id),
  key xztype (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

This could allow you to query using x and z, while retrieving all ids to have a quick reference back to the row:

SELECT id FROM bitstrings WHERE x=0 and z=0;

If you ever have to retrieve the data for a given id, you could select it as

SELECT * FROM bitstrings WHERE id = 12;

This will retrieve the specific info for whatever x, z and type are there.

CHOICE #2 : Use x,z,type as the PRIMARY KEY

Run this query

SELECT COUNT(1) rcount,x,z,type FROM bitstrings GROUP BY x,z,type HAVING COUNT(1) > 1;

If this query comes back with no rows at all, this can be your primary key

CREATE TABLE `bitstrings` (
  `x` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `z` meduimint(11) NOT NULL COMMENT 'roughly +/- 10^6 range',
  `type` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-4096',
  `offset` smallint(6) UNSIGNED NOT NULL COMMENT 'range: 1-65535',
  `bitstring` blob NOT NULL COMMENT 'binary data len: 1-8192',
  primary key (x,z,type)
) ENGINE=MyISAM DEFAULT CHARSET=utf-8;

CONCLUSION

Since all queries are based on mainly on x and z, either choice would be OK

The first choice would yield a bigger .MYI file, but two ways to gather data (grouped by x,z or granularly by id).
The second choice only gathers by x and z but a smaller .MYI file.


SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014 I have 2 databases that contain nearly identical data.

The first has compatibility level 100 and is restored on a sql2012 instance.
The second has compatibility level 110 and is restored on a sql2014 instance.

I am running the same query on both databases.

On the first database the query finishes in approximately 20-30 seconds.

I tried running the same query on the second, and it did not finish after 50 hours. If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

I have tried rebuilding statistics for all tables affected by the query, and i also checked fragmentation (the first database actually looks to have far more fragmentation)
Why is there such a difference in performance, and what can I do to fix this?

This is the actual query plan for the first database:

https://www.brentozar.com/pastetheplan/?id=rJUN72gQB

This is the estimated query plan for the second database:

https://www.brentozar.com/pastetheplan/?id=SysNr2gQr

The full query looks like this:

https://justpaste.it/53b9c

Why is there such a difference in performance...

Check out this part of the estimated plan for the slow query (second server):

This estimates that 21 rows will come out of the join to RatesByCategory (which is after _Results323 has been joined to AttributesTable twice, and DataStreamDirection).  If we look at the equivalent part of the actual plan for the fast query:

There will actually be ~16,000,000 rows coming out of that join.  These is bad news, because those millions of rows flow right into a wall of Nested Loops joins, which is likely where that query slows to a crawl.

You were on the right track looking at estimates and trying to update stats, but this actually seems to be because of a row goal set by the semi-joins later in the execution plan.

Here's where the semi-join lives, and notice that everything "under it" has an estimate of 1 row:

  ...and what can I do to fix this?

Try adding this hint to the end of the query:

OPTION (QUERYTRACEON 4138)

This will disable the row goal and should you give you a different plan (possibly similar to the one on the lower compatibility level).

Since you said this query is generated, your best bet for solving this might be to create a plan guide so that you can add that hint without having to change the application that produces the query.

  If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

Changing compat level to 120 would have enabled the "new" cardinality estimator, resulting in a different query plan - likely one that was less affected by the row goal (and thus why you were able to see it finish in 40 minutes, where the compat 110 query ran for 50 hours without finishing).


Importing CSV's into MySQL(5.6) tables leaves date fields as 0000-00-00 During the import from a CSV file created from an MS Access table into a MySQL table it converts all date fields in the csv into  0000-00-00. I use the following Load Data command for the import.

LOAD DATA LOCAL INFILE 'C:\\..\\tableName.txt'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Here's an example line from the CSV file im trying to import.

1,1,5/30/1934 0:00:00,"H","Vancouver, B.C."....

I tried the solution from "Dates changed to 0000-00-00" but it didn't work.

You can import your .csv file and replace after:

Make your date column new_date_column VARCHAR(10).
Import the file.
UPDATE with REPLACE.

  UPDATE db1.table1
   SET  new_date_column = STR_TO_DATE(REPLACE(new_date_column,'/','.'),GET_FORMAT(DATE,'EUR'));

Change the datatype of new_date_column.

ALTER TABLE db1.table1 MODIFY new_date_column new_date_column DATE;

This will convert your new_date_column into XXXX-XX-XX date format.


Replicating data for ease of access I have a table, lets call table a, that I allow users to enter data into. They can enter data as many times as they want, and based on some constraints, I only use the most recent entry.

My question is this: is it okay to take the entered data from table a, simplify it (take the most recent data, apply some formulas), and store it in table b?

This improves access access for my application and convert makes most of my data access queries simple selection statements. The down side is that my data is replicated, and a script must be run regularly to keep data in sync.

So, is this an acceptable practice?

This sounds like the classic case of History versus CurrentState.  Sure, have two tables.  Insert a new row into History and overwrite the row keyed to the user in the other table.

That does lead to "redundant" data, which is a 'no-no' by DB purists.  So here is another approach:

Insert into History, as above.  Get back the AUTO_INCREMENT id.  Then Update the CurrentState with just the id (and user_id).  Then, for accessing the current info, use a JOIN between the tables.


MySQL To Show Month Over Month Data By Year How can I change this query to show data in this format

01-18  2000
01-19  4000
02-18  3000
02-19  1000

This is the syntax I have but since it's union I can't use an order by clause

SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName,    SUM(saleAmount)
FROM  prevyear
WHERE BDM = 'Jason Hernandez'
GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName
UNION ALL
SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName, SUM(saleAmount)
FROM  curryear
WHERE BDM = 'Jason Hernandez'
GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName

You could wrap a new query around this one to allow you to aggregate the results:

SELECT Month, SUM(salesAmount) FROM (
    SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName,    SUM(saleAmount)
    FROM  prevyear
    WHERE BDM = 'Jason Hernandez'
    GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName
    UNION ALL
    SELECT DATE_FORMAT(salesdate, "%m-%Y") AS Month, employeeName, SUM(saleAmount)
    FROM  curryear
    WHERE BDM = 'Jason Hernandez'
    GROUP BY DATE_FORMAT(salesdate, "%m-%Y"), employeeName
) previousQuery
GROUP BY Month

Ideally I'd build the query to do this in one go rather than turning your query into a subquery but it will work. Here's an example that shows it working.


Which user defined data type is memory optimized? How can i know which one of my uddts is memory optimized? and how can i know it's definition?

The query below will identify memory-optimized table types in the current database.

SELECT
      OBJECT_SCHEMA_NAME(tt.type_table_object_id) AS SchemaName
    , tt.name TableTypeName
FROM sys.table_types AS tt
JOIN sys.objects AS o ON
    o.object_id = tt.type_table_object_id
WHERE tt.is_memory_optimized = 1;

You can query other catalog views to get other meta-data. For example, columns
can be listed with this query:

SELECT
      OBJECT_SCHEMA_NAME(tt.type_table_object_id) AS SchemaName
    , tt.name AS TableTypeName
    , c.name AS ColumnName
    , ty.name AS ColumnTypeName
    , c.max_length AS ColumnLength
    , c.is_nullable AS IsNullable
FROM sys.table_types AS tt
JOIN sys.objects AS o ON
    o.object_id = tt.type_table_object_id
JOIN sys.columns AS c ON c.object_id = o.object_id
JOIN sys.types AS ty ON ty.system_type_id = c.system_type_id AND ty.user_type_id = c.user_type_id
WHERE tt.is_memory_optimized = 1
ORDER BY
      SchemaName
    , TableTypeName
    , c.column_id;

If you need the complete definition with indexes, constraints, etc. as DDL, script the type using SSMS Object Explorer or PowerShell.


Upgrade to SQL Server from 2008 to 2012 - Developer perspective We are in the process of migration from SQL Server 2008 to SQL Server 2012.

The entire process of migration will be taken care by dedicated DBA team.

From a developer prespective, I would like to know `what could be the impacts of upgrade.

We have tables, stored procedures, views, functions and few SSIS jobs.

I have searched over google for a while, and could not find out clear information on what could be impacts on SSIS and DB's

It would be great once I would come to know what could be possible impacts :

So, here are the questions :

Will there by any impact on any features of SQL Server that might not work in 2012 ?
What will be impact on SSIS jobs running on 2008 moved to 2012 ?

Yes there may be impact. To start with, you must read

List of SQL Server features deprecated in SQL Server 2012
List of deprecated Database engine features in SQL Server 2012
Breaking changes to DB engine feature in SQL server 2012

You must run SQL server 2012 upgrade advisor and generate report before doing migration. This report would list out all features deprecated and which would not work moving ahead in SQL Server 2012.

There is an option to allow few old features to work in SQL server 2012 and that would be by keeping compatibility level of database to 100 after migration. As a good practice you should change compatibility level of database to match that of Server. In case you want to change compatibiltity level please read Alter database Compatibility level. The good thing with compatibility level is it can be changed immediately if something goes wrong. About compatibility level please note that

  Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL Server applications are affected by behavioral differences in SQL Server 2012, convert the application to work properly.

For upgrading SSIS packages you should read BOL document I guess pretty much every thing is documented in Upgrade Integration services packages. If you want reference document please download 5 Tips on smooth SSIS upgrade to SQL Server 2012

Basically you have to read a lot and you have to look for features that has been deprecated. You might as well need to make changes in your code. As you can understand it wont be possible for me to tell you changes, you have to look at your code taking doc as refernce as do necessary changes.