Does MySQL have a limitation of one index per query? MySQL supports spatial index. However, all the spatial index can do is find points in a box. It can't do fancy stuff like find the nearest 1000 points, etc.

I think, and I don't know where I went wrong, that simply finding points in a box is easy – just do constraints:

x < xmax and x > xmin and y < ymax and y > ymin

Yes those are 4 constraints that use at least 2 columns.
The result is that things get too slow when there are 1.6 million rows.

But why? The x and y are sorted. The complexity should be log(n). Yet queries can take 60 seconds.

Using a spatial index shortens it to 1 second with some subquery trick.

What am I missing? Why does using a bunch of constraints in a query take time?

Even without a fancy algorithm it should be easy to quickly get the points in the box. Yet, I feel that the complexity must be O(n) at least.

There are multiple questions here.

To answer the title: MySQL can utilize more than one index per query. This is called index-merge.

However, it does not always happen that index-merge optimization is used.

"Normal" indexes are B/B+ Trees. Such trees provide with linear sorting. You can index x, and the index sorts x in ascending order. You can do the same for y. You can even index both (x, y) but you still get linear sorting -- not two dimensional one.

To get back to your other question, regarding the query

x < xmax and x > xmin and y < ymax and y > ymin

Let's assume then that we have an index on x and an index on y. The problem begins with the fact that these indexes do not share resources, nor information, nor at any way point to one another.

It's very easy to figure out all rows where x < xmax and x > xmin, using the index on x. But once you have these, you can't just take those rows to the y index and ask of it to "return rows from withing these rows I'm giving you and for which y < ymax and y > ymin. The reason is that the index onycan quickly lookup usingy` values. It does not know how to quickly lookup row #3984987.

The way index-merge works is by getting row ids from the x index, getting row ids from the y index (meanwhile the x rows are stored in a temporary buffer), and then intersect these. Note that intersection has nothing to do with indexes. It is a brute force operation (hopefully, achieved in linear time thanks to the fact that the two lists of rows are already sorted).

Now given such a query as yours, MySQL asks itself: what would be cheaper? Use just one index on x, then row by row filter those rows where y < ymax and y > ymin applies? Or perhaps use an index-merge, create a temporary buffer, work out a brute-force intersection?

The fact is that MySQL rarely chooses the index-merge optimization. I've been looking at many queries and their execution plans. Very rare. Perhaps there's someone there for whom this works on regular basis.

But looking at the X:Y coordinates problem, it's easy to see why this wouldn't work: there's just too many matching values for the x-range alone, and just too many values for the y-range alone for this to be a good plan.


Create two columns from one, based on two different WHERE clauses SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_DataSets]
WHERE [VariableID] in ('1')
UNION ALL
SELECT TOP 1000 [Value]
FROM [OnlineQnres].[dbo].[tmp_oDataSets]
WHERE [VariableID] in ('4')

The above query produces 2000 rows; 1000 with Value for VariableID = 1, and 1000 with Value for VariableID = 2. What I want is 1000 rows, with one column (val1) containing the values for VariableID = 1, and the other column (val2) with the values for VariableID = 2.

;WITH x AS
(
  SELECT TOP (1000) Value, row_num = ROW_NUMBER() OVER (ORDER BY Value)
    FROM OnlineQnres.dbo.tmp_oDataSets
    WHERE VariableID = '1'
), y AS
(
  SELECT TOP (1000) Value, row_num = ROW_NUMBER() OVER (ORDER BY Value)
    FROM OnlineQnres.dbo.tmp_oDataSets
    WHERE VariableID = '4'
)
SELECT val1 = x.Value, val2 = y.Value
FROM x INNER JOIN y
ON x.row_num = y.row_num;

As I stated in a comment, for this correlation to be meaningful, you need to define how the ROW_NUMBER() is calculated (or indicate how these two sets of values are related). In this case it will correlate the smallest value for variableID = 1 with the smallest value for variableID = 2, the 2nd smallest value for variableID = 1 with the 2nd smallest value for variableID = 2, etc.

Also, as others noted, to get predictable results, you need to define TOP (1000) with a separate ORDER BY.

Essentially, if you have these rows in the table, and pretend you are only looking for two of each variableID:

VariableID    Value
----------    -------
1             foo
1             bar
4             mort
4             splunge

What does the result look like? Should it be:

val1    val2
------  -------
foo     mort
bar     splunge

If so, why? If not, why not? Or should it be:

val1    val2
------  -------
foo     splunge
bar     mort

If so, why? If not, why not? Or should it be:

val1    val2
------  -------
foo     NULL
bar     NULL
NULL    mort
NULL    splunge

Or something else entirely?

There are other query approaches too, but they depend on your requirements, and how the rows are linked other than the fact that they are "any" row that match the WHERE clauses.

Flimsy requirements lead to flimsy answers, sorry.


SQL Server 2000 query error When I attempt to run on the SQL Server 2000 to a query which was developed for SQL Server 2008, I receive the following the error.

  ambiguous column name SucNo

Here is my query:

 SELECT
   p.SiraNo, p.SucNo, p.Fail, p.SucAdi, p.SucTarihi, p.Musteki, p.Supheli,
   (select f.AdiSoyadi as Kayıp_Şahıs where f.SucYili = '2011') as Kayıp_Şahıs,
   p.EvrakEksigi, p.EvrakSorumlusu, p.SevkTarihi, p.evrakSevkDurumu,
   p.sucYili as Yıl
 FROM pStationTbl p
 LEFT JOIN kayipTbl f ON p.SucNo = f.SucNo
 WHERE p.SucYili = '2011'
 ORDER BY CASE evrakSevkDurumu
   When 'Beklemede' Then 1
   WHEN 'İkmal Edildi' Then 2
   Else 3
 End, SucNo DESC

You need to qualify SucNo DESC in the ORDER BY i.e. p.SucNo DESC.

Presumably the SQL2000 query processor can't deduce that f.SucNo and p.SucNo resolves to the same sort, whereas SQL2008 can.


Index rebuild at partition level on a table We have functionality in SQL Server to rebuild indexes at partition level using below command

ALTER INDEX IX_name ON table REBUILD PARTITION=68

In non-partitioned table we disable the indexes, load the data and rebuild the indexes after the data load complete. Here we disable the indexes prior to data load so that indexes will not be processed while data insertion to table is happening.

How can I do the same process for a partitioned table? If i disable indexes on a partitioned table before data load, and run the rebuild partition command after the data load is complete, it won't work, saying disabled indexes cannot be rebuilt.

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Enterprise Edition.

How can I do the same process for a partitioned table?

While you can rebuild a single partition, you can't disable just one partition.

So you would use a staging table on the same partition scheme.  Disable its indexes, load it, rebuild the indexes, and then switch one or more partitions into the main table.


What to check for when receiving intermittent error on Kill Command I have a C# scheduled task which runs every night and includes a kill command to drop an existing database so that it can be recreated. It randomly generates this error about once a week.

  Kill database failed. User does not have permission to alter database
  'Foo', the database does not exist, or the database is not in a state
  that allows access checks. Kill database failed.

The C# used is as follows.

srv.KillAllProcesses;
srv.KillDatabase;

So I am already trying to kill all of the processes. Are there any other checks or commands I could do before the kill command to ensure it happens successfully? It can't be a user permission problem as the operation works most of the time so what could be causing the "not in a state that allows access checks"?

When you execute srv.KillAllProcesses; SMO will just launch a KILL <spid> for every process connected to the database.

Once those kill commands are executed, some transactions may still be rolling back. I'm not sure what may be going on behind the scenes that puts your database in the state returning your error message when transactions are still rolling back but in any case that call isn't needed.

Calling only srv.KillDatabase; will execute a ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before attempting to drop the database using DROP DATABASE, effectively killing/rolling back any active transactions.

It's the ALTER DATABASE statement that is returning the error you receive.

This can be verified by looking at a profiler trace or extended event target.

Have a look at Set a Database to Single-user Mode

  If other users are connected to the database at the time that you set
  the database to single-user mode, their connections to the database
  will be closed without warning.

There is no reason to kill the processed first and you should probably remove that line of code


Change Oracle user home directory at OS level after Oracle Databse/Client Software Installation Can we change oracle user's home directory at OS level after Oracle Database/Client Software-Only installation?

No database has been created yet.
We want to change the home directory to /Oracle/profile/oracle from /home/oracle.

As per your comment:

  Yes, I meant the home directory of oracle user at OS level!

You can easily achieve that.

We have a user called ora

[root@myserver /]# su - ora
[ora@myserver ~]$ pwd
/home/ora

And it has its home directory as /home/ora.
Let's create another home directory for the user ora.

[root@myserver ora]# mkdir -p /home/profile

Let's move the files such as .bash_profile and directories like Desktop and Documents, from the old home directory to new one.

[root@myserver ora]# cp -a /home/ora /home/profile/

Finally, modify the user to change the home directory. Make sure that there are no active sessions exist for the user.

[root@myserver ora]# usermod -d /home/profile/ora/ ora

Let's check the result.

[root@myserver ora]# su - ora
[ora@myserver ~]$ pwd
/home/profile/ora/


select specified number of unique IDs where second column is unique Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have sec=1. We continue to work our way down like this, but when we get to id=7 we skip it because we already have sec=5 (from row with id=8). We continue in the same manner, and we finally stop at id=3 because we have accumulated 4 rows (our desired limit).

 id | sec
----+-----
  9 |   1  <- 1
  8 |   5  <- 2
  7 |   5  # skip, already have sec=5
  6 |   4  <- 3
  5 |   1  # skip, already have sec=1
  4 |   1  # skip, already have sec=1
  3 |   3  <- 4
  2 |   2
  1 |   1

Of course the SQL algorithm can (will!) be different than I described.

Desired result:

 id
----
  9
  8
  6
  3
(4 rows)

If I wanted to increase the limit to 5 rows, then the row with id=2 would be included in the results. However, if I increased the limit to 6 rows, the row with id=1 would not be added because sec=1 has already been seen.

Note: Though it shouldn't matter, I am on PostgreSQL 9.3.1.

In case you want to quickly build the table to test this out:

CREATE TABLE my_table (id serial primary key, sec integer DEFAULT 0 NOT NULL);
INSERT INTO my_table (sec) VALUES
  (1)
, (2)
, (3)
, (1)
, (1)
, (4)
, (5)
, (5)
, (1);
CREATE INDEX index_my_table_on_sec ON my_table (sec);

SELECT id,
       sec
FROM (
  SELECT id,
         sec,
         row_number() OVER (PARTITION BY sec ORDER BY id DESC) AS rn
  FROM my_table
) t
WHERE rn = 1
ORDER BY id DESC
LIMIT 4;

SQLFiddle example: http://sqlfiddle.com/#!15/1ca01/1


Query with different time period in same query ( WHERE ) I compare the some total numbers between today and 1 months ago.

I created a query like this. Below you will see "commented" Where statements since I cannot get both results in same query

SELECT
  COUNT(DISTINCT a.order_id) AS total_orders,
  SUM(invoice_amount)        AS total_amount
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id
--WHERE CURRENT_DATE >= b.order_creation_date;
--WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date
AND b.seller_id IN(1,3)

I wrote this query that would solve my problem but I think it is too much complex. I wish there was a better one.

SELECT
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date AND b.seller_id IN (1, 3))                     AS total_amount_until_now,
  COUNT(DISTINCT a.order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (1, 3)) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date AND b.seller_id IN (1, 3)) AS total_amount_until_a_month_ago
FROM
  order_items a
  INNER JOIN orders b ON a.order_id = b.order_id

Well first factor our the seller_id IN (1,3) and use USING

SELECT
  COUNT(DISTINCT order_id)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date)                     AS total_orders_until_now,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE >= b.order_creation_date)                     AS total_amount_until_now,
  COUNT(DISTINCT order_id)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date) AS total_orders_until_a_month_ago,
  SUM(invoice_amount)
    FILTER (WHERE CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date) AS total_amount_until_a_month_ago
FROM order_items a
INNER JOIN orders b
  USING (order_id)
WHERE b.seller_id IN (1, 3)

You can use GROUPING to further clean this up (I guess) if you can accept a different format..

SELECT
  CASE
    WHEN CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date THEN 'until_a_month_ago'
    WHEN CURRENT_DATE >= b.order_creation_date THEN 'until_now'
  END,
  COUNT(DISTINCT order_id) AS total_orders,
  SUM(invoice_amount) AS total_amount
FROM order_items a
INNER JOIN orders b
  USING (order_id)
WHERE b.seller_id IN (1, 3)
GROUP BY ( CASE
  WHEN CURRENT_DATE - INTERVAL '1 month' > b.order_creation_date THEN 'until_a_month_ago'
  WHEN CURRENT_DATE >= b.order_creation_date THEN 'until_now'
END )


AVG using specific condition for each row I have a Quotation table with QuotationID, ClientID field.
A QuotationDetail table with QuotationID, ProductID
A Sales table with Year, ProductID, ClientID and Quantity.

I'm trying to create a view with the following columns:
QuotationID | Client Name | Product Name | Quantity2015 | Quantity2016

I am able to join the quotations table with clients and products but can't figure out how to SUM Quantity values for each view row filtering by YEAR (2015 and 2016).

PS: I did not specify but I have the Clients and Products tables with the fields I mentioned.

You could do something like this.

Quantity2015 = SUM(CASE WHEN Year = 2015 THEN QuantityTotal ELSE 0 END)
Quantity2016 = SUM(CASE WHEN Year = 2016 THEN QUantityTotal ELSE 0 END)

Not very flexible, but it does work.  You can use other aggregators than SUM as well.

Ok, so a full query example would look like this.  You might need to correct table names (especially for Client/Product).

SELECT Q.QuotationId
    , C.ClientName
    , P.ProductName
    , Quantity2015 = SUM(CASE WHEN S.[Year] = 2015 THEN S.Quantity ELSE 0 END)
    , Quantity2016 = SUM(CASE WHEN S.[Year] = 2016 THEN S.Quantity ELSE 0 END)
FROM Quotation AS Q
    INNER JOIN Client AS C ON C.ClientID = Q.ClientID
    INNER JOIN QuotationDetail AS QD ON QD.QuotationID = Q.QuotationID
    INNER JOIN Product AS P ON P.ProductID = QD.ProductID
    INNER JOIN Sales AS S ON S.ProductID = QD.ProductID AND S.ClientID = Q.ClientID
GROUP BY Q.QuotationId
    , C.ClientName
    , P.ProductName


Add article to transaction replication without generating a snapshot at all I have a transaction replication and the subscription was synced from snapshot years ago.  Now I have a new table that needs to be added to the replication.  Problem is the table is close to a TB and I do not want to generate a snapshot for that table instead just want the new data to be synced, kinda like when you sync from backup.  Its inserts only to the table and I can sync the old data manually later.

How do I make replication to start transferring the data without generating the snapshot.

There really isn't a supported way to do what you are asking. You can use a backup/restore but that would be to a new database. You could try a filtered article where you only grab data after a certain create date.

Or you could use a manual process such as an SSIS package or T-SQL Scripts to stage the schema and data. But the snapshot is more or less a required item.

I would probably do a single article snapshot or maybe look to filter if you can filter create_date > getdate() and then add in rows manually after if you have such a column, or an identity column, etc.

A few links:

About manually preparing.

About using backups or other methods.

About filtering.


How do I remove first line from text and only the first? I have a tables "Table1"  in this table I have a Text NOT null field - "value". the value field is populated like so:

value: 1.2.3.4 [cookie] : butter - 1 cup.
1.2.3.4 [cookie] : flour - 2 cup.
1.2.3.4 [cookie] : sugar - 1/2 cup.

I need to remove only the FIRST line and keep all others. This value can grown up to 500 lines. I am unsure how to delete only the
1.2.3.4 [cookie] : butter - 1 cup.
line.
I tried using replace and substring_index without success. This was my attempt:
SELECT name, SUBSTRING_INDEX(value, '\n', 1) FROM variables

which returns:
Cookies|  1.2.3.4 [cookie] : butter - 1 cup.
The values are put in with new lines as the "separator". I am looking to replace the values with everything after the first string of character up to the end of the first "line". This is more of of a block of text that I need to adjust.
Please assist in removing only the first line for the value.

You're almost there. I think what you need is:

SELECT substring(value, LOCATE('\n', value)+1)

This means:

find the first newline in the string
then add one (to go to the next character after the newline)
and return everything from that position to the end of the string


Postgresql small index for row contains query I want to store several hundreds of gigabytes of geodata in a postgresql database.
I want to query the data on position, time and/or a unique identifier for each object.

My table layout is similar to this:

CREATE TABLE objects(
id int not null,
at timestamp not null,
pos geometry(Point) not null
/* Other columns irrelevant to the question */
) PARTITION BY RANGE (at)

BRIN indexes on at and pos perfectly serve the needs, since they speed up the queries with relatively small indexes.

A B-TREE index on id quickly becomes several gigabytes, so the server memory cannot hold many of them. BRIN is not suitable, as the id's are very spread within a page, so minimum and maximum is not a useful statistic.

Is there an alternate index type which can speed up queries like SELECT ... FROM objects WHERE id = x with smaller index sizes?

The table is append-only, so only for the newest timestamps new rows will be added.

Since seems like each value of id has many occurences in the table.  Given that, you can use a GIN index with its compressed posting lists to save space.  Usually GIN indexes are used with array types or their equivalents, but you can use the btree_gin extension to get access to GIN indexes with a scalar type.

However, GIN indexes have several limitations.  They don't support ORDER BY, which doesn't seem to matter to you.  And they don't support index-only-scans, which may be important to you.  Even if the index shrinks to fit in RAM, each tuple pointer found in the index still has to go to the table for resolution.  If your Btree index doesn't fit in RAM, surely your table doesn't either.  So you will still have lots of random seeking into the table.

Indeed, that was almost certainly the problem in the first place--not random seeks against the btree index, but rather against the table.  When you are using the BRIN index to drive table access, you naturally are going to visit the table with high locality of reference.  It is not that the BRIN index is effective, it is that the same condition that allows the BRIN to be used also makes the table access for the particular set of rows being returned also be efficient.  If you were to replace the BRIN indexes with equivalent Btree indexes, you would probably find that while the storage requirement went up, the performance stays mostly the same.


"Main" folder is not found at "/var/lib/postgresql/9.3/" I am new to Ubuntu and PostgreSQL. I am trying to find a "data directory" for PostgreSQL on my Ubuntu.
When I Googled, it appears that it should be /var/lib/postgresql/9.3/main. But on my Ubuntu, I do not see that folder.
 I only see "bin" and "lib" folders. Can anyone guide where I could locate a data directory for PostgreSQL?

I was informed that the variable is normally called datadir.

pg_conftool

Debian and Ubuntu provide their own utilities. To get this information, use pg_conftool. Here is what it looks like,

pg_conftool show -s data_directory;
/var/lib/postgresql/9.5/main

For reference,

Usage:
  /usr/bin/pg_conftool [options] [<version> <cluster name>] [<configfile>] <command>

Options:
  -b --boolean  Format output as boolean
  -s --short    Print only value
  -v --verbose  Verbose output
  --help        This help

Commands:
  show <parameter>|all
  set <parameter> <value>
  remove <parameter>
  edit

pg_lsclusters

If you're running multiple versions of PostgreSQL on the same machine, you may find pg_lsclusters useful. It will show you all of the versions running.

pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

You can parse that pretty easy, here we use awk to get out the Ver and Data directory,

pg_lsclusters | awk '{print $1,$6}'
Ver Data
9.5 /var/lib/postgresql/9.5/main

If you have JSON.pm installed you can use pg_lsclusters -j to output JSON,

Error: Please install JSON.pm for JSON output (Debian: libjson-perl)


How to Loadbalance a Postgres BDR Cluster I'm planning to use BDR (by 2ndQuadrant) to setup a multi master cluster for my PostgreSQL Databases. how can i achieve real loadbalancing for read and write Operations? I dont want to do it on the application side.

Is it a good idea to combine pgpool and a BDR Cluster, given that pgpool seems to be able to Balance only Select-Queries or is there a better way to distribute load over the whole cluster?

No it's bad idea to combine BDR with pgpool.

Instead this you can use HAProxy load balancer which have various ACL rules which can be configured for your BDR cluster.


SQL Server: How does Uniquifier work with Replication? In SQL Server, if I have a non-unique clustered index on a table, how does replication work for that table?

For example:

CREATE TABLE MyData (
    MyDataID uniqueidentifier not null,
    MyData varchar(max) not null,
    RecordDateTime smalldatetime not null,
    PRIMARY KEY NONCLUSTERED (MyDataID ASC)
)

CREATE CLUSTERED INDEX IX_MyData_RecordDateTime ON MyData (
    RecordDateTime ASC
)

(For purposes of the question, the table structure is more illustrative than exact)

I know that SQL Server will add a hidden 4-byte "uniquifier" to rows with duplicate RecordDateTime values, but in this case, how are the rows replicated?

Specifically, if there are two rows R1 and R2 on two publishers P1 and P2, if R1.RecordDateTime is unique on P1 and R2.RecordDateTime is unique on P2, but R1.RecordDateTime is equal to R2.RecordDateTime, how are the rows merged onto a shared subscriber S?

Replication requires a primary key constraint on the replicated tables, not a clustered index. Primary key constraints enforce uniqueness. If you have more than one publisher to the same subscriber, you'll need to ensure that there are no collisions. This is often done with identity columns. For instance, one server would use negative numbers, the other server would use positive numbers.

If you also have a non-unique clustered index, the uniqifier doesn't get transferred as it's not part of the INSERT/UPDATE/DELETE command. The uniquifier value will be added on the subscriber and would be different than the value on the publisher.


How to make a transition from SQL Server DBA to Oracle? As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do?

I would assume the concepts are the same and the difference is merely the programming language but I have not seen the other side of the door.

Oracle and SQL Server have a number of architectural and idiomatic differences, and several key bits of terminology are used differently in the documentation.  It's quite a few years since I did this, but some of the major idiomatic differences are:

Oracle has no direct equivalent to tempdb.  Global temp tables are persistent entities and you do not create them on the fly as you would with a temp table in SQL Server.
Idiomatic PL/SQL is much more procedural than idiomatic T-SQL.  On the flip side, PL/SQL has features to support fast cursor ops including parallel loop constructs.  Often, intermediate cursor results are used in ways similar to the sort things one would use temp tables for in SQL Server.  Look up how to use '%' to get some polymorphism in cursor variable types.
SQL*Plus is a command line tool similar to SQLCMD.  On a vanilla installation it may be all you have available.  If you want a GUI tool like SSMS you can get SQL Developer or third party tools such as TOAD.  TOAD is quite expensive.
Learn the role of rollback segments (used in Oracle's MVCC) and the joys of the 'snapshot too old' error.
Get a metalink account if possible.
Read the concepts guide.  It has a lot of useful starter information.
Tablespaces are roughly equivalent to filegroups.
SQL and the SQL dialect embedded within PL/SQL are not quite the same thing in Oracle.
Oracle didn't support recursive CTEs until quite recently.  You can do recursive queries with CONNECT BY.
Read the docs for RMAN to see how to do backups.  You can do a few neat tricks like merge differential backups into a single restorable backup.
The system data dictionary has 'ALL', 'USER' and 'DBA' versions of the tables.  'USER_' shows objects owned by the login.  'ALL_' shows all objects the login can read.  'DBA_*' is
a global view, but you have to have appropriate permissions to see that.  You will probably use the data dictionary more with Oracle than with SQL Server, so get to know it.
Clustered tables are called 'index ordered tables' in Oracle.  'Clustereed tables' in
Oracle refer to a very different feature.  In Oracle, a clustered table is a facility that allows physical storage of master/detail tables to be comingled based on a common key.  The feature is not widely used.
Indexed views are called 'materialized views' in Oracle.  They work somewhat differently, with enough nuances to merit their own question.
Oracle does not have a concept equivalent to a 'database' in SQL Server.  In Oracle, a schema is just the owner of some database objects.
PL/SQL has an explicit module structure called 'packages'.
Look at using sequences instead of identity columns.
Getting a query plan is a bit fiddlier in older versions of Oracle (prior to 10g).  You have to create a table (of the right format) to dump the plan to, then 'EXPLAIN PLAN' the query into the table to get the query plan.  You can then get the query plan back out of the table.  Everybody and their dog have favourite query plan queries.  Do a google search and find one, then frig it to taste.  10g added a package called dbms_xplan, which simplifies working with query plans.
If you're running Oracle on a unix or linux host, having a working knowledge of shell scripting wouldn't hurt.
Oracle has support for autonomous transactions - you can commit an inner autonomous transaction and expect it to stay committed if the parent rolls back.  SQL Server nested transactions are not true nested transactions and have different semantics.  You could emulate this behaviour with a CLR stored procedure in SQL Server by opening an independent connection and performing the autonomous operation through that.

Oracle comes with less B.I. tooling out of the box, although Oracle do sell OLAP servers, reporting tools and ETL tooling.

Oracle bundle a bulk load tool called SQL*Loader that is similar in function to bcp.
The closest equivalent to SSIS is Oracle Warehouse Builder (OWB), but this is a separate item and not bundled with the base system.
Oracle make a reporting tool called Oracle Reports, but this is also not bundled with the base system.  This is the closest equivalent to Reporting Services.
Oracle also make a ROLAP tool called Discoverer.  This is the closest product to Report Builder that Oracle produce.  More recent versions ('Drake') have support for Oracle's OLAP engine.
Oracle used to have an OLAP product called Express.  At one point they built it into the core Oracle DBMS engine, storing its native data structures in opaque blobs.  It is not totally compatible with Express, and has a propritary interface with very little support from third-party OLAP front-end tools.
Oracle B.I. Enterprise Edition is what used to be Siebel Analytics.  This has some OLAP functionality but I'm not familiar with it.  Maybe someone who has used it might like to comment.  Without directly admitting that Siebel was a completely separate product from its own B.I. tooling, Oracle stated that there was 'no upgrade path' from Oracle B.I. Standard Edition to Enterprise Edition.  Note that both the Standard and Enterprise B.I. suites are separate products and not bundled with the base system.
In practice, the closest thing to SSAS sold by Oracle is Essbase (formerly Hyperion).  This is a separate product and not bundled with the base system.

Not surprisingly, Oracle's B.I. strategy is viewed by industry pundits as something of a dog's breakfast.

There are probably other things that I can't think of OTOH; I'll add them as I think of them.


Deleting Record from Linked Table Slow in MS Access I have a Microsoft Access database that is linked to a SQL Server 2000 backend. It has one form with several subforms, all using information from linked tables. When I try to delete a record from one of the subforms, Access sometimes hangs for 60 seconds before prompting me to confirm that I want to delete the record. After the 60 seconds, it proceeds normally without delays. I haven’t been able to find any pattern as to when it hangs; it seems to happen randomly about half the time. The only pattern is that the delay is always the same length.

The delay occurs somewhere between the Delete and Before Delete Confirm events. We do not have any macros or VBA code behind either event. There is a trigger on the table that records a timestamp of when a record was deleted and some other information about the values of the fields. When I delete a record, the timestamp matches when I originally hit delete (before confirming the delete in the dialog box 60 seconds later), so that suggests that transferring the data TO the server is fine, but perhaps there is a delay in either transferring information BACK to Access, or else some other delay on Access’s end after it sends the delete command. The problem still occurs when the trigger is disabled, so the trigger itself isn’t causing the delay.

I have seen similar questions about slow deletes for thousands of records, but this delay occurs when I am just trying to delete one record.

Any ideas about what's causing the delay or suggestions for how to track it down would be greatly appreciated!

It could be a mechanism within MS Access when it comes to the linked table. How ???

MSAccess sees a linked table and you issue this:

DELETE FROM [mytable];

This will not run over to SQL Server and do a deletion of all records in a single transaction. What MSAccess does is pick up every PRIMARY KEY and issues an individual DELETE FROM [mytable] WHERE pk = ???, probably as individual transactions (explains the delay before the confirm). I remembered this wonky behavior going back 15 years because I saw this happen when having linked tables to SQL Server, MySQL, and Oracle.

If this is still what MS Access does to a linked table, you are better off connecting to SQL Server via isql and issuing the DELETE on the server side, especially if there are a lot of rows to delete. At the very least, you should minimize executing bulk operations against SQL Server from MSAccess.

Although I have never worked with it, MSAccess can allow "pass through" queries where you issue a SQL Command and the ODBC Driver simply passes the query to the Server Side for parsing and execution rather that trying to parse and execute the SQL the "ODBC" way.

If things have changed over the years with linked tables, I'll leave it to the SQL Server community to address this if I am way off base.


Accuracy of mysqldbcompare vs other tools like pt-table-checksum I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases.

I have heard that pt-table-checksum is pretty dead-on accurate in figuring out if two DBs are different, but it looks like mysqldbcompare is able to show the actual row differences better and more clearly in its output.

However, how do we know whether mysqldbcompare is as accurate as pt-table-checksum or is it more/less ?

Thanks
IS

It is a non goal of pt-table-checksum to show you the actual differences.  However, there is a companion tool pt-table-synch  which can read the checksum table generated by pt-table-checksum.  It will take the bounds of chunks where master/slave checksums differ and then scan with in those to identify rows that don't match.

It will even generate replace statements ready to execute on the slave to get it in synch with the master.


Restoring plain-text pg_dump with psql and --disable-triggers I had to perform some tests with a short script to update some "legacy" data in one of my tables.

Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. Simplest way to do that was:

pg_dump -a --file table.sql -t table database

Now I did what I had to do, checked the results and found them rather unsatisfactory. I thought to myself: how lucky I am to have a backup of that table.

I had already been warned when I backed up the table that:

pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump:   table
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

I didn't think much of it, but now we have a problem. Indeed the table in question has multiple triggers attached to it, but I cannot restore the table.sql with option --disable-triggers  of the pg_restore command.

If I try following command I get an error message:

pg_restore -a -d database -t table -h localhost --disable-triggers table.sql

namely:

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

Is there a flag for the psql-command that exhibits the same behavior as --disable-triggers?

I have already checked the psql "manpage", searching for trigger and similar keywords but didn't find anything.

Or is the only option I have to drop the triggers on the table before restoring the data?

Sidenote: I am using postgres v. 9.3 on a Ubuntu 14.10 System

It was suggested to edit the generated sql-file, to include the statement:

ALTER TABLE table DISABLE TRIGGER ALL

When I now executed: psql -d database -f table.sql I got an error message about violating the "Unique" constraint of the primary key.

To fix this I tried to wrap the copy into:

BEGIN TRANSACTION READ WRITE;
TRUNCATE TABLE table;

-- copy here

COMMIT;

Now the error message is:

psql:project_backup.sql:18: ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "another" references "table".
HINT:  Truncate table "another" at the same time, or use TRUNCATE ... CASCADE.
psql:project_backup.sql:20: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:project_backup.sql:21: invalid command \N
psql:project_backup.sql:22: invalid command \N

The latter warning repeats for each \N (symbolizing the null value) in the dump.

@dezso had the completely right idea:

  All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

UPDATE table SET
    (column1, column2, ...) =
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

So I am finally back with my original data, ready for the next testrun ;)


Does an index with multiple columns make a similar index redundant? Let's say I have a table that looks like this:

CREATE TABLE Activity (
    ActivityID int primary key identity(1,1) ,
    ActivityName nvarchar(10),
    InactiveFlag bit
)

with an index that looks like this:

CREATE INDEX Activity_Index on Activity
(
    ActivityName
)

then for some reason, someone has created a second index:

CREATE INDEX Another_Activity_Index on Activity
(
    ActivityName, InactiveFlag
)

is it definitely safe to delete the first index? Is it just taking up unneccesary disk space? Will the second index cover all cases of the first? The column ordering is definitely "ActivityName" first.

In this case I consider the first index redundant (and less useful). Note that dropping the index will invalidate any plan that references it, so the next time you run any of those queries, you may see a blip in performance due to the required recompile to access the second index instead. While both indexes existed, any query that only needed to act on ActivityName would probably have chosen the first index since it is skinnier.

If the second column in the second index was something wider, then I might be a little less cavalier about my advice to drop the first index. If that column were a wide varchar, for example, then the additional I/O required to use the index when necessary (and when the varchar column were not needed for output or filtering) would actually make the first index (even though it is technically redundant) more desirable to satisfy that type of query.

So, just a long-winded way to say: it depends. :-)


If I need to restrict access to certain databases? So I've just created a MySQL server. I also created some users only having specific access to certain databases, but do I need to restrict access to default databases to the users I've created?
If so, how? Should I just create a role to try to restrict this access?

I have been using this site as a reference:
https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch04s02.html

Generally you should start by running mysql_secure_installation on any mysql production database servers to ensure the minimum security measures are in place. You shouldn't need additional restrictions on general users after this.


Asking SQL schema by FOIA I've sent FOIA requests to various public institutions in my area.

I'm only asking for the schema of the SQL databases their financial services might be using (column name for every table for every database).

I'm getting answers along the line that what I'm asking for is a security risk, therefore the organization will refuse my request.

I'm not a DBA. I use MySQL to process data in the public interest, but am only beginner level. I'd like to understand exactly how knowing only the schema of an SQL database could be a security risk. The general guidelines detailed in the answer to this question don't mention it.

Thank you very much in advance for your help!

Let's suppose the institution does send me a listing of all the tables and their columns.  Even though I don't have the actual data, I can now attempt social engineering attacks to convince insiders into giving me whatever data I want.

The request would sound much more legitimate, because I could ask for very specific pieces of information using the actual table names. If I were to find someone with access to the database and convince them that I'm a higher-ranking employee (e.g. their manager or the CEO), I could make a legit-sounding request for sensitive information.

You would be surprised how often this happens in a corporate environment. Just having the metadata for a database exposed can be a big security risk.


Insertion Rule on a view I m using postgres 9.3 and i m trying to insert data  to the view objectifsBoutique and and PostgreSQL returns following error(« objectifsboutique » relation is not found).
I have to mention that the view was successfully created

CREATE OR REPLACE VIEW "objectifsBoutique" AS
SELECT vendeur.boutique_id, objectifs.produit_id, cast(NULL as bigint) AS objectif
FROM boutique, vendeur, objectifs
WHERE boutique.id = vendeur.boutique_id AND vendeur.id = objectifs.vendeur_id
GROUP BY vendeur.boutique_id, objectifs.produit_id;
ALTER TABLE "objectifsBoutique"
OWNER TO postgres;

create table objectifsBoutiques(boutique_id bigint, produit_id bigint, objectif bigint);

CREATE RULE objectifsBoutiques_insert AS ON INSERT TO objectifsBoutique
DO INSTEAD INSERT INTO objectifsBoutiques VALUES (NEW.boutique_id, NEW.produit_id, NEW.objectif);

That's a typical case of case misuse.

When creating the view, notice that "objectifsBoutique" is written with a uppercase B plus double quotes. This implies that any future reference to this object must include the uppercase B.

Yet in the error message « objectifsboutique » relation is not found it's a lowercase b so that name does not refer to the view, it refers to nothing, hence the error.

The solution is to either not use double quotes at all, or use them consistently.


SQL Merge Replication Issue - problem where backup and maintenance job which is running till morning and most of the server jobs are stuck We have a merge replication environment which includes
Publisher: Server1 where only one database of 125 GB in replication and also Distributor is also configured on same server hosted in a HyperV environment.
Subscribers: We have approx 280 servers with Distributor(push subscription) configuration.
NOTE: we have a environment with lots of DML changes in day so we perform many checks on a daily basis on every server through maintenance and backup jobs which includes step1 : check integrity for complete database step2: Rebuild/Reorganise Index step3: Backup/Verify step4: Old backup jobs clean up step5: Old job history clean up
Also this job completes in 4-5 hrs on a daily basis but it varies sometimes on server to server basis.
Snapshot Agent runs at daily at 00:05 AM
PROBLEM:

Since sunday night we are having this problem where backup and
maintenance job which is running till morning and most of the server
jobs are stuck on step1 dbcc checkdb ('my database') (and Clients are
also complaining that application is crashing , can't login , very
slow)

Upon checking, my backup spid is waiting on OLEDB wait type and we do not have configured linked server connections on Publisher and other subscribers except few which are our head office servers from where we do our major imports / small updates / if we need to push out any database changes.
While checking the below query i can see the percentage is moving for my dbcc spid id but doesn't really tell me why it is stuck on this step?
select session_id
, percent_complete
from sys.dm_exec_requests
where percent_complete > 0

And it doesn't really matter which time I run this job or single step i.e. dbcc checkdb, it did not complete.
Also Check with our Windows team there was no update rolled out on weekend and no changes have been made on application side.
Any suggestions of what could be the problem ?

I came to know the Anti virus was updated last week and past weekend all servers were rebooted. To test on one server we disabled some of the install anti virus features, and my job ran successfully in a reasonable time (4-5 hrs). Hope this work globally too. Fingers crossed!


Reset postgres password v9.1 I'm trying to reset my lost password for Postgres v9.1 (windows 7) like written at https://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F  :
1. edit pg_hba.conf to allow trust authorization temporarily

In pg_hba.conf I've put :

local all all trust
host all all 127.0.0.1/32 trust

2. Reload the config file (pg_ctl reload)

Done. I have also restarted the Postgres service (via services.msc)
3. Connect and issue ALTER ROLE / PASSWORD to set the new password

Then I execute 'psql.exe' but it asks me for a password !
Why ?
Thanks for your help.

Try to run psql -h 127.0.0.1 (...)

When you use the -h that will tell psql to use a TCP/IP connection
('h' is from host), and the parameter after -h is the name of the host.

In that case 127.0.0.1 is a default address, meaning localhost.

If you don't use the -h (with host next to this parameters) it will
use a "local way" of connection called unix domain sockets.


Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them? If I do a vanilla SELECT * FROM sys.tables or SELECT * FROM sys.dm_exec_query_stats are those tables getting locked since I'm reading data from them?

If so, what happens when SQL Server ties to write to them while I'm locking them e.g. when a new table is created?...does the table creation actually hang until the read lock is released?

To start with, sys.tables is a system catalog view, not a table. And DMVs / DMFs (especially those that report stats) often query memory, which is why they get cleared out when you restart or execute DBCC FREESYSTEMCACHE(N'ALL');.

Still, since sys.tables does eventually lead to actual system tables, I figured I should test this, especially because I have noticed in the past that sometimes adding WITH (NOLOCK) does help when querying some of the system catalog views.

In order to test this, I used a SQLCLR function to pause for a few seconds after returning each row. This keeps the auto-commit transaction open long enough to be able to see what locking is actually happening during the operation (without coming up with several cross joins in an attempt to return tens of millions of rows which also takes a while but takes up a lot of memory as well). The SQLCLR function, DB_WaitForDelay, is available in the free version of SQL#, a library of SQLCLR procs and functions that I wrote.

USE [tempdb];

SELECT *, SQL#_Full.SQL#.DB_WaitForDelay(5000, NEWID())
FROM   sys.tables  -- WITH (NOLOCK)

While that was executing, the following resources were being locked by that session:

LockResource                             Mode   Type  Status
[sys].[sysschobjs] (SYSTEM_TABLE)        Sch-S  LOCK  GRANT
[sys].[sysidxstats] (SYSTEM_TABLE)       Sch-S  LOCK  GRANT
[sys].[sysobjvalues] (SYSTEM_TABLE)      Sch-S  LOCK  GRANT
[sys].[syssingleobjrefs] (SYSTEM_TABLE)  Sch-S  LOCK  GRANT
[sys].[sysmultiobjrefs] (SYSTEM_TABLE)   Sch-S  LOCK  GRANT

[sys].[sysschobjs]                       S      LOCK  GRANT {not if WITH (NOLOCK) is used}

That last item, sysschobjs does not show up if I uncomment the WITH (NOLOCK) when selecting from sys.tables. Interesting.

While that was running (without the WITH (NOLOCK)), I was able to execute the following in [tempdb]:

CREATE TABLE dbo.IsLocked (Col2 INT);
GO
DROP TABLE dbo.IsLocked;
GO

So, even with some locks being taken, it seems like you should be fine. In fact, I even tested again, but this time I first executed the following before the SELECT:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;

And again there was no interference with the CREATE TABLE / DROP TABLE operations.

When selecting from the sys.dm_exec_query_stats DMV and using DB_WaitForDelay I did not see any locks being taken by that session.

Just to have this caveat stated: I only tested the two objects mentioned in the question. It is possible that other system catalog views and/or DMFs/DMVs do some additional locking that might have a more noticeable impact.


Bulk insert from a complex text file into a table I want to use bulk insert to load a text file into a table, but my coding is only able to load half of the text file because my text file has two parts:

(ID, CompanyName and Date) as one part; and
(Name Dept, Status, Age and Gender)

The (ID, CompanyName and Date) section appears only once in the file. I want it repeated in the rows when inserted to the table.

My coding is only able to load with one format text file.

Below is my text file example:

  A1242
  CompanyName
  Date 2019-11-28 18:58:04
  Name Department Status Age Gender
  John R&D Active 23 Male
  James R&D Active 21 Male

How can I use bulk insert to load the above text file into a table looking like the below example?

  A1242 CompanyName 2019-11-28 18:58:04 John R&D Active 23 Male
  A1242 CompanyName 2019-11-28 18:58:04 James R&D Active 21 Male

Below is my coding:

-- Procedure Load Text File
ALTER procedure [dbo].[ImportFiles]
@FilePath       varchar(1000) = 'c:\Transfer\' ,
@ArchivePath        varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask       varchar(1000) = 'bcp*.txt' ,
@MergeProc      varchar(128) = 'MergeBCPData'

AS

set nocount on

declare @ImportDate datetime

select @ImportDate = getdate()

declare @FileName       varchar(1000) ,
@File           varchar(1000),
declare @cmd varchar(2000)

create table ##Import (s varchar(8000), sd varchar(8000))
create table #Dir (s varchar(8000), sd varchar(8000))

/*****************************************************************/
-- Import file
/*****************************************************************/
select  @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'

while exists (select * from #Dir)
begin
select  @FileName = min(s) from #Dir
select  @File = @FilePath + @FileName

select  @cmd =      'bulk insert'
select  @cmd = @cmd +   ' ##Import'
select  @cmd = @cmd +   ' from'
select  @cmd = @cmd +   ' ''' + replace(@File,'"','') + ''''
select  @cmd = @cmd +   ' with (FIELDTERMINATOR=''\t'''
select  @cmd = @cmd +   ',FIRSTROW = 5 '
select  @cmd = @cmd +   ',ROWTERMINATOR = ''\n'')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete  #Dir where s = @FileName

exec @MergeProc

-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath +
@FileName
exec master..xp_cmdshell @cmd
end

drop table ##Import
drop table #Dir

--Procedure Insert
/****** Object:  StoredProcedure [dbo].[MergeBCPData]    Script Date:
11/28/2019
8:32:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[MergeBCPData]

AS
set nocount on

-- insert data to production table
insert  BCPData
(
fld1 ,
fld2 ,
fld3 ,
fld5
--fld4
)
select
fld1    = substring(s,1,12) , --testing
fld2    = substring(s,15,4) , --testing
fld3    = s ,
fld5    = s
--fld4  = convert(datetime,substring(s,26,19))
from    ##Import

--Exetuce the stored procedur
exec ImportFiles 'D:\Piccolo\' , 'D:\Piccolo\Archive\' , 'bcp*.txt',
'MergeBCPData'

I cannot change the file format and external application is my backup plan.

SQL Server BULK INSERT is very limited in the types of files it can import and your type of file is way outside of what it can do.

The best approach is to have the file format changed.

Second best is to have an external application do the parsing of the file and upload data to table.

If none of the above is possible then you should use SQL Server solutions below:

Assuming the "header" section appears only once in a file, load this file in two parts.
Load the first part into a separate one column staging table (use LASTROW parameter). The second part would be loaded using your current approach. You will then join the data using tSQL.
Create a staging table that would have an ID (will act as row number) and a single VARCHAR( MAX) column. Load the file into this table as is, without column splitting.
Then, using SQL queries, manipulate the data into the format you need. The code will be rather ugly.

Some useful info: Splitting delimited strings

Update

SQL Server implementation option 1 would follow the steps below. Note: this is pseudo-code to avoid overly long code, that I have no way to test.

create table ##ImportHeader (RowID INT IDENTITY, RowData VARCHAR( 3000 ))
-- Load the first 3 rows into a separate table.
bulk insert ##ImportHeader from [YourFile] with( LASTROW = 3, FIELDTERMINATOR='\t' ROWTERMINATOR = '\n' )

-- Load the rest of the data
-- Your import table needs to have the same number of columns as the data you are loading
create table ##Import (RowID INT IDENTITY, Name VACRHAR( 200 ), Department VARCHAR( 200 ), Status VARCHAR( 10 ), Age INT, Gender VARCHAR( 10 ))
bulk insert ##Import from [YourFile] with( FIRSTROW = 5, FIELDTERMINATOR='\t' ROWTERMINATOR = '\n' )

-- Join data
SELECT CompanyID.RowData, CompanyName.RowData, CAST( SUBSTRING( CompanyDate.RowDate, 5, 50 ) AS DATETIME ),
   D.*
FROM ##Import AS D
    INNER JOIN ##ImportHeader AS CompanyID ON CompanyID.RowID = 1
    INNER JOIN ##ImportHeader AS CompanyName ON CompanyName.RowID = 2
    INNER JOIN ##ImportHeader AS CompanyDate ON CompanyDate.RowID = 3

What you are doing is loading the same file twice. First time you are loading first 3 rows and second time you are loading the rest of the structured data.


Grant deny permission stacking For the role db_denycustomer, I want only the column code of the customer table to be SELECTable, and none of the others. So I did this:

DENY SELECT ON dbo.customer TO db_denycustomer
GRANT SELECT ON dbo.customer (code) TO db_denycustomer

...and it works fine. Cool! But, why?

What I've read in related articles is that permissions stack, but DENY takes precedence. In contrast, in my case, it seems that the last permission "query" took precedence. Sure enough, if I execute them in reverse order, the latter DENY hides the code column too.

Could you please elaborate on this?

I have also included the default db_datawriter and db_datareader roles to the user that I tested with.

This is documented behavior provided for backwards compatibility. Documentation excerpt:

  Caution A table-level DENY does not take precedence over a
  column-level GRANT. This inconsistency in the permissions hierarchy
  has been preserved for backward compatibility.


What is the fastest way to export a table to a text file I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell command) to export it to a text file, one line for each row of data? The .net process should know when the export have been finished or if there was any error. The datatype will be all int or nvarchar.

I am assuming that a pure C# code using ado.net to execute a select * command and looping over the datareader and writing to a file for each record will be slow and there is no way I can parallelize this.

Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine. The SQL Server will be a HA cluster. Is SSIS better suited for this, no data transformation required?

The .Net process would run on Machine A, SQL Server on Machine B and ultimate file destination is a network share. One option is SQL server writes the file directly to the network share. The other option is SQL Server writes to machine A and then when the file is written the .net process copies it to network share. I don't have formal SLA but expecting 30 mins - 1 hr for the file write.

Looping through all those articles is an option if you want to get old watching it execute.

Some different options that you should try are:

the bcp utility (mentioned above, too, it's a command line tool to import and export data to/from an SQL Server);
BULK INSERT statement - the T-SQL language sugar to work with data imports from scripts;
SqlBulkCopy class from .NET - if you want to work from inside the app. A nice example can be seen here: Linchi Shea's Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy .

And you can try all these options while looping in another session, just for fun :-).


MySQL: logging queries which would execute without using indexes I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, queries are logged whenever the optimizer really decided not to use an index as a criterion in a WHERE clause. And not if they truly have no indexes matching the filtered columns.

So given a table with the following structure

CREATE TABLE `test` (
    `id_test`   int(11) NOT NULL AUTO_INCREMENT,
    `some_text`  varchar(255) DEFAULT NULL,
    `some_more_text` text,
 PRIMARY KEY (`id_test`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

a query SELECT id_test from test where id_test != 69 would be logged to the slow log because of not using indexes (the optimizer has decided that a table scan is more efficient as not much could be won by using an index) but SELECT id_test from test where id_test = 69 would not.

I would expect the behavior of the latter query in the first case as well since the index is present. As is, it makes troubleshooting missing indexes rather tiresome. Ideas on how to approach this greatly appreciated.

I'm not really sure why you expected log_queries_not_using_indexes=1 to not log queries that didn't use an index. The idea of the setting is to see which queries are getting run on your server that could be ideal candidates for optimization. For your example:

SELECT id_test from test where id_test != 69

Unless you change the requirements of the query, there's not much you can do. Of course it's faster to do a full scan and return all rows that don't match that id_test value.

As for ideas on how to approach analysis:

You can set min_examined_row_limit to a higher value. If, for example, you want to exclude queries that examine less than 5,000 rows, set this variable to 5000. This is useful to reduce the 'noise' in the slow query log.
You can use a tool called pt-query-digest with the --review option to review your slow queries. The tool allows you to store your review history so you don't repeat work on the same query during analysis:

  It won’t show you queries you’ve already reviewed. A query is considered to be already reviewed if you’ve set a value for the reviewed_by column. (If you want to see queries you’ve already reviewed, use the --report-all option.)

It also has the added benefit of storing a history of the queries that get logged in your slow query. Who wouldn't love historical evidence of improving your queries?


remove almost duplicates based on insert time Have a MySql table that records magnetic stripe swipe times from identity cards:

CREATE TABLE `swipes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `personalid` varchar(20) NOT NULL,
 `swipe_time` datetime NOT NULL,
 `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
 `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
 `source` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The unique key is there to prevent exact duplicate inserts.
However there is another source of almost duplicates where the card gets swiped twice in quick succession or even for some odd reason within a minute or so when there may be other cards swiped in between. In some ways this is like bounce on electronic switch contacts.

Here's a snippet of rows to illustrate:

    id      personalid      swipe_time      status   ride_taken source
    1661    C08877547   2012-10-21 01:12:08     1        1        3
    1662    C09364782   2012-10-21 01:23:38     1        1        3
    1663    C09364782   2012-10-21 01:23:48     1        1        3
    1664    D09490557   2012-10-21 01:24:39     1        1        3

What I want is a query that will remove entries like id 1663, that are within some specified time of rows with the same personalid, status=1, ride_taken=1 and source

Yes I have searched and inspected both here and on stackoverflow
such as https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows
for a similar situation but have not found an answer yet. If there is one already, please point me at it.

I'm at a loss how to apply a time range filter for the swipe_time to delete only similar rows within a specified time range.

Here is something very quick and dirty

Get the minimum datetime for each personalid, status, ride_taken

SELECT B.* FROM
(SELECT personalid,status,ride_taken,MIN(swipe_time) swipe_time
FROM swipes GROUP BY personalid,status,ride_taken) A
INNER JOIN swipes B USING (personalid,status,ride_taken,swipe_time);

First let's load your sample data:

mysql> drop database if exists sdujan;
Query OK, 1 row affected (0.00 sec)

mysql> create database sdujan;
Query OK, 1 row affected (0.00 sec)

mysql> use sdujan;
Database changed
mysql> CREATE TABLE `swipes` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `personalid` varchar(20) NOT NULL,
    ->  `swipe_time` datetime NOT NULL,
    ->  `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
    ->  `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
    ->  `source` tinyint(4) NOT NULL DEFAULT '0',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into swipes values
    -> (1661,'C08877547','2012-10-21 01:12:08',1,1,3),
    -> (1662,'C09364782','2012-10-21 01:23:38',1,1,3),
    -> (1663,'C09364782','2012-10-21 01:23:48',1,1,3),
    -> (1664,'D09490557','2012-10-21 01:24:39',1,1,3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from swipes;
+------+------------+---------------------+--------+------------+--------+
| id   | personalid | swipe_time          | status | ride_taken | source |
+------+------------+---------------------+--------+------------+--------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 |
| 1663 | C09364782  | 2012-10-21 01:23:48 |      1 |          1 |      3 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 |
+------+------------+---------------------+--------+------------+--------+
4 rows in set (0.00 sec)

mysql>

Here is the output of my proposed query

mysql> SELECT B.* FROM
    -> (SELECT personalid,status,ride_taken,MIN(swipe_time) swipe_time
    -> FROM swipes GROUP BY personalid,status,ride_taken) A
    -> INNER JOIN swipes B USING (personalid,status,ride_taken,swipe_time);
+------+------------+---------------------+--------+------------+--------+
| id   | personalid | swipe_time          | status | ride_taken | source |
+------+------------+---------------------+--------+------------+--------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 |
+------+------------+---------------------+--------+------------+--------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

SUGGESTION #1

To accommodate the query better, add this index

ALTER TABLE swipes ADD UNIQUE KEY `my_key2`
(`personalid`,`status`,`ride_taken`,`swipe_time`);

SUGGESTION #2 (Optional)

To accommodate more than 255 swipe stations, perhaps source should be smallint unsigned

SUGGESTION #3 (Optional)

Some Transit Systems allow multiple swipes (up to 4 for PATH TransHudson) in one station. You could give some additional thought on this should you have to allow multiple swipes.

UPDATE 2012-10-24 17:30 EDT

If you are trying to limit within a time range, I have another viewpoint for you

Here is the subquery within my answer:

mysql> SELECT
    ->     personalid,status,ride_taken,MIN(swipe_time) swipe_time
    -> FROM
    ->     swipes GROUP BY personalid,status,ride_taken
    -> ;
+------------+--------+------------+---------------------+
| personalid | status | ride_taken | swipe_time          |
+------------+--------+------------+---------------------+
| C08877547  |      1 |          1 | 2012-10-21 01:12:08 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:38 |
| D09490557  |      1 |          1 | 2012-10-21 01:24:39 |
+------------+--------+------------+---------------------+
3 rows in set (0.00 sec)

mysql>

Let's say you want to limit to the Latest Entry Per 10 seconds. You will first have to generate a ten-second interval boundary like this:

mysql> SELECT personalid,status,ride_taken,swipe_time,
    -> (swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND) ten_sec_in
    -> FROM swipes;
+------------+--------+------------+---------------------+---------------------+
| personalid | status | ride_taken | swipe_time          | ten_sec_int         |
+------------+--------+------------+---------------------+---------------------+
| C08877547  |      1 |          1 | 2012-10-21 01:12:08 | 2012-10-21 01:12:00 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:38 | 2012-10-21 01:23:30 |
| C09364782  |      1 |          1 | 2012-10-21 01:23:48 | 2012-10-21 01:23:40 |
| D09490557  |      1 |          1 | 2012-10-21 01:24:39 | 2012-10-21 01:24:30 |
+------------+--------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Perhaps the thing to do is introduce the ten_second boundary into the table

ALTER TABLE swipes ADD swipe_int DATETIME;
UPDATE swipes SET
swipe_int = swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND;
ALTER TABLE swipes ADD UNIQUE (personalid,swipe_int);

I ran this

mysql> ALTER TABLE swipes ADD swipe_int DATETIME;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> UPDATE swipes SET
    -> swipe_int = swipe_time - INTERVAL MOD(UNIX_TIMESTAMP(swipe_time),10) SECOND;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> ALTER TABLE swipes ADD UNIQUE personalid_swipe_int_ndx (personalid,swipe_int);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from swipes;
+------+------------+---------------------+--------+------------+--------+---------------------+
| id   | personalid | swipe_time          | status | ride_taken | source | swipe_int           |
+------+------------+---------------------+--------+------------+--------+---------------------+
| 1661 | C08877547  | 2012-10-21 01:12:08 |      1 |          1 |      3 | 2012-10-21 01:12:00 |
| 1662 | C09364782  | 2012-10-21 01:23:38 |      1 |          1 |      3 | 2012-10-21 01:23:30 |
| 1663 | C09364782  | 2012-10-21 01:23:48 |      1 |          1 |      3 | 2012-10-21 01:23:40 |
| 1664 | D09490557  | 2012-10-21 01:24:39 |      1 |          1 |      3 | 2012-10-21 01:24:30 |
+------+------------+---------------------+--------+------------+--------+---------------------+
4 rows in set (0.00 sec)

mysql> show create table swipes\G
*************************** 1. row ***************************
       Table: swipes
Create Table: CREATE TABLE `swipes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `personalid` varchar(20) NOT NULL,
  `swipe_time` datetime NOT NULL,
  `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
  `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
  `source` tinyint(4) NOT NULL DEFAULT '0',
  `swipe_int` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`),
  UNIQUE KEY `personalid_swipe_int_ndx` (`personalid`,`swipe_int`)
) ENGINE=MyISAM AUTO_INCREMENT=1665 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql>

Once you do that, going forward, any INSERT within the same 10-second interval gets immediately rejected. That way, you do not have to code it. It will be part of the table design.

Naturally, you will need a trigger to populate swipe_int. Here is the trigger:

mysql> DELIMITER $$
mysql> CREATE TRIGGER swipes_bi BEFORE INSERT ON swipes
    -> FOR EACH ROW
    -> BEGIN
    ->     SET NEW.swipe_int = (NEW.swipe_time -
    ->     INTERVAL MOD(UNIX_TIMESTAMP(NEW.swipe_time),10) SECOND);
    -> END $$
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
mysql>


Does a standby slow down Oracle imp? I have Oracle 10gR2 installed on Redhat Linux 5.4 on two servers (primary and physical standby).

When I import data into the primary server while the standby is started it takes too long to complete (didn't wait for it to finish). When I shutdown Oracle on the standby server it completes in 15 minutes.

Is this really due to standby?  How can I speed up this slow imp?  Here is the command line used:

imp userid=/ file=base.dmp ignore=yes log=log1.log

It depends on the configuration you have in place.

Oracle uses log shipping (as default) to feed the transactions into the standby database. In order to make sure that ALL transactions are recovered on the standby database, we should make sure that the primary database is running in FORCE LOGGING mode. Switching FORCE LOGGING may cause a difference in performance on the primary database, where FORCE LOGGING mode would be a little slower than without force logging mode.

Just shutting down could - depending on the configuration - bring the primary database to a halt (protected standby database). When the primary and standby databases are on different hardware they should hardly influence each other, other than that the transactions need to be saved and transported to the standby site. If you run in sync mode the primary could be slowed more than when running a-sync which is the default.

The configuration that makes sure that the performance impact on the primary is the least, is the max performance mode, in which the transactions are only shipped when the redo logfile is full. It also gives the least data protection.


Bulk DELETEs in publisher breaking SQL Server Replication in subscriber We have SQL Server replication set up between 2 databases running SQL Server 2019.  Every night, a SQL Agent job runs on the publisher that deletes all the data from a table and then rebuilds the table with new data. However, for some reason, this seems to cause our replication to break on the subscriber with errors such as this:
The row was not found at the Subscriber when applying the replicated DELETE command for Table '[dbo].TABLENAME' with Primary Key(s): [Id] = 240843 (Source: MSSQLServer, Error number: 20598)

As a result, I need to fix this by manually 'filling in' the missing rows, so they can be deleted by the subscriber.  Is there any way to fix this, or are bulk deletes like this something that isn't supported, or best practice with SQL Replication?

It sounds like the root cause of your error is that someone/thing is deleting the rows on the subscriber prior to the replication trying to delete the same rows. I would try and address this by tracking down who/what is removing the rows and then restricting their permissions.
Doing a bingle for the error number brings up this automation with Powershell to try and "repair" these errors automatically - https://www.mssqltips.com/sqlservertip/3351/auto-fix-sql-server-transactional-replication-error-20598-for-updates/
but better to address the root cause IMO


Which SQL Server system database is more important: master or resource? Which SQL Server system database is more important: master or resource?

If the resource database is damaged, can the database engine work?

Can the system work with only the master database?  If not, which system databases are necessary?

Both the master and resource DB is important.

If the master DB becomes corrupt, you can still restart your server by creating a new master DB. You will loose your server principals and other data that are stored in the master, but at least you will be up and running. Once you recreate your logins (hopefully these were scripted) then you have access to your data.

The resource DB contains the sys schema. I am not sure if you can just copy these from another instance if there are not much changes done to these. Will have to see if there is anybody else who had this issue.


Referencing a SELECT subquery value in the WHERE clause Complete query
SET @vSelect =
    '
    SELECT
        T1.BranchShortName AS BranchShortName,
        (
            SELECT TT1.EmployeeName
            FROM [dbo].[Tb_OPL_Employee] AS TT1
            JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
            JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
            WHERE
                TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                (
                    SELECT MAX(TTT3.IdTb_OPL_JobTitles)
                    FROM [dbo].[Tb_SYS_Approval] AS TTT3
                    WHERE
                        TTT3.IsPassed = 1
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId =TT2.TaskId
                )
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
        ) AS LastApproval,
        (
            SELECT TT1.EmployeeName
            FROM [dbo].[Tb_OPL_Employee] AS TT1
            JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
            JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
            WHERE
                TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                (
                    SELECT MIN(TTT3.IdTb_OPL_JobTitles)
                    FROM [dbo].[Tb_SYS_Approval] AS TTT3
                    WHERE
                        TTT3.IsPassed = 0
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId =TT2.TaskId
                )
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
        ) AS NextApproval,
        T6.StatusDescription AS Status
    '
SET @vWhere = ' WHERE 1=1 '

IF (@ddlSearchCriteria = '1')
BEGIN
    -- The condition of search textbox is not empty
    IF (@txtSearch <> '')
    BEGIN
        -- The Condition of StartDate is not empty and EndDate is empty
        IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'

        -- The Condition of StartDate is empty and EndDate is not empty
        IF (@txtStartDate = '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- The Condition of StartDate is not empty and EndDate is not empty
        IF (@txtStartDate <> '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        IF (@txtStartDate = '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''

        PRINT @vSelect+@vWhere
    END

How can I get the subquery value for LastApproval to compare like I do with T1.BranchShortName?
I tried changing it like:
... IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND **T1.BranchShortName --> LastApproval** LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''  ...

but this gave me a message saying there was an error in my where clause.

The problem arises because the aliases defined in the SELECT list are not visible by the same level WHERE clause - and the reason behind this is the order of logical execution of queries (which is roughly FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY, omitting some other clauses).

You can bypass this problem with several methods:

duplicating the expression code for the alias (the subqueries code here), so the code is both in the SELECT and in the WHERE clause (or in the SELECT and in the HAVING clause, if there is a GROUP BY and the expression uses aggregated values). This might result in a query which looks more complicated that it needs to be - and it may result in errors/inconsistencies later (if a developer changes the subquery/expression only in 1 of the 2 places).
move the expression code (subquery in our case) to a CTE. This may not be possible or easy to do in all cases - eg. if the expression is a correlated subquery, like in the specific issue.
wrap the whole query into a derived table (or CTE) and move the conditions for those (calculated, aliased) columns to the external WHERE clause. This should work in all cases. It may not be the most efficient though in every case. If the optimizer can "push down" the condition, the plan should be identical to solution 1 (having the code duplicated) as the optimizer can then apply further transformations and choose between different execution paths (in regard with checking the various WHERE conditions and the order of joining tables). If the condition is/can not pushed down, it will have to be evaluated after the derived table which may not be the most efficient in some cases.
use OUTER APPLY instead of duplicating code. This is possible only if the expression does not use aggregated values. (If it uses aggregation, it will need more complex rewriting, using a derived table and OUTER APPLY.)
This in my opinion the best option, as the condition is already "pushed down" to the same level as the rest joins and conditions and we are sure the optimizer has more options to plan the execution. Plus the code is only in one place.

For your case, solutions 1 (duplicate code), 3 (wrap the whole query in a derived table) and 4 (the OUTER APPLY) seem to be doable. The APPLY would look like:

-- the SELECT, with the subqueries removed
SET @vSelect = '
        SELECT
             T1.BranchShortName AS BranchShortName,
             LA.LastApproval,
             NA.NextApproval,
             T6.StatusDescription AS Status
'
-- the FROM as it was (whatever you have there, doesn't change)
SET @vFrom = '
        FROM dbo.table_1 AS T1
          JOIN dbo.table_2 AS T2
          ON  T1.x = T2.y
'

Then we add the APPLY correlated subqueries to FROM clause:

-- OUTER APPLY subqueries added
SET @vFrom = @vFrom + '
          OUTER APPLY
            ( SELECT TT1.EmployeeName AS LastApproval
              FROM [dbo].[Tb_OPL_Employee] AS TT1
                JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
              WHERE TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                    ( SELECT MAX(TTT3.IdTb_OPL_JobTitles)
                      FROM [dbo].[Tb_SYS_Approval] AS TTT3
                      WHERE TTT3.IsPassed = 1
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId = TT2.TaskId)
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
            ) AS LA (LastApproval)
          OUTER APPLY
            ( SELECT TT1.EmployeeName AS NextApproval
              FROM [dbo].[Tb_OPL_Employee] AS TT1
                JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
              WHERE TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                    ( SELECT MIN(TTT3.IdTb_OPL_JobTitles)
                      FROM [dbo].[Tb_SYS_Approval] AS TTT3
                      WHERE TTT3.IsPassed = 0
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId = TT2.TaskId)
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
            ) AS NA (NextApproval)
'

Now, we can use the subqueries results in the WHERE clause:

SET @vWhere = ' WHERE 1=1 '

IF (@ddlSearchCriteria = '1')
BEGIN
    -- The condition of search textbox is not empty
    IF (@txtSearch <> '')
    BEGIN
        -- The Condition of StartDate is not empty and EndDate is empty
        IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'

        -- The Condition of StartDate is empty and EndDate is not empty
        IF (@txtStartDate = '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- The Condition of StartDate is not empty and EndDate is not empty
        IF (@txtStartDate <> '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- we can use LA.LastApproval as any other column
        -- and the OUTER APPLY becomes CROSS APPLY
        IF (@txtStartDate = '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND LA.LastApproval LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''

        PRINT @vSelect + @vFrom + @vWhere + ' ;'
    END


When creating a database using an ERD, what do the entities and relationships become (in database terms)? MIS student, here. Learning about ERDs (entity relationship diagrams). ERDs are supposedly used for the purpose of later constructing a database. My question is this:

What will an entity (and, likewise, an entity's attributes) become upon implementing the database? For instance, will entities turn into rows and attributes into columns?
In fact, now that I consider it, I'd like to know how one implements associative entities from ERD to database form as well. Is there a difference between what a regular entity and an associative entity represent in terms of database terminology?

When implementing an ERD as physical database the following mappings apply:

An entity becomes a table (set of rows)
A relationship becomes a foreign key.
Associative (many to many) relationships become a table with a foreign key to each associated table.

Generally the ERD shoud be in at least third normal form.

A few notes about relationships:

The (zero or) one side of the relationship is implemented as the  primary key of the table.
The many side of the relationship is usually implemented with a foreign key index.
If the site opposite the many side range is zero to one, the foreign keys column(s) are nullable.
If the side opposite the many side range is one to one, the foreign key columns are not nullable.
If the side opposite the many side range is also zero or one to many, then a relationship table must be implemented.

Many ERD tools can be used to create the tables and indexes; or the DDL (Data Definition Language) to do so.  They may also be able to create an ERD from a database.  The best tools can update an existing database to match a modified ERD, or generate the DDL to modify the database.


In Sql Server, is there a way to check if a selected group of rows are locked or not? We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large update/delete activity is being blocked for extended periods of time (as it is waiting to get locks on all the rows or page lock or table lock) resulting in timeouts or taking multiple days to complete the task.

So, we are changing the approach to delete small batch of rows at at time. But we want to check if the selected (let's say 100 or 1000 or 2000 rows) are currently locked by a different process or not.

If not, then proceed with delete/update.
If they are locked, then move on to the next group of records.
At end, come back to the begining and attempt to update/delete the left out ones.

Is this doable?

Thanks,
ToC

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then return to any batches that were not previously deleted and retry our original delete logic. We must repeat this cycle until all required batches of rows are deleted.

As has been mentioned, it is reasonable to use a READPAST hint and the READ COMMITTED (default) isolation level, in order to skip past ranges that may contain blocked rows. I will go a step further and recommend using the SERIALIZABLE isolation level and nibbling deletes.

SQL Server uses Key-Range locks to protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level...find more here:
https://technet.microsoft.com/en-US/library/ms191272(v=SQL.105).aspx

With nibbling deletes, our goal is to isolate a range of rows and ensure that no changes will occur to those rows while we are deleting them, that is to say, we do not want phantom reads or insertions. The serializable isolation level is meant to solve this problem.

Before I demonstrate my solution, I would like to add that neither am I recommending switching your database's default isolation level to SERIALIZABLE nor am I recommending that my solution is the best. I merely wish to present it and see where we can go from here.

A few house-keeping notes:

The SQL Server version that I am using is Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
My test database is using the FULL recovery model

To begin my experiment, I will set up a test database, a sample table, and I will fill the table with 2,000,000 rows.

USE [master];
GO

SET NOCOUNT ON;

IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
    ALTER DATABASE [test] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [test];
END
GO

-- Create the test database
CREATE DATABASE [test];
GO

-- Set the recovery model to FULL
ALTER DATABASE [test] SET RECOVERY FULL;

-- Create a FULL database backup
-- in order to ensure we are in fact using
-- the FULL recovery model
-- I pipe it to dev null for simplicity
BACKUP DATABASE [test]
TO DISK = N'nul';
GO

USE [test];
GO

-- Create our table
IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
      c1 BIGINT IDENTITY (1,1) NOT NULL
    , c2 INT NOT NULL
) ON [PRIMARY];
GO

-- Insert 2,000,000 rows
INSERT INTO dbo.tbl
    SELECT TOP 2000
        number
    FROM
        master..spt_values
    ORDER BY
        number
GO 1000

At this point, we will need one or more indexes upon which the locking mechanisms of the SERIALIZABLE isolation level can act.

-- Add a clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_tbl_c1
    ON dbo.tbl (c1);
GO

-- Add a non-clustered index
CREATE NONCLUSTERED INDEX IX_tbl_c2
    ON dbo.tbl (c2);
GO

Now, let us check to see that our 2,000,000 rows were created

SELECT
    COUNT(*)
FROM
    tbl;

So, we have our database, table, indexes, and rows. So, let us set up the experiment for nibbling deletes. First, we must decide how best to create a typical nibbling delete mechanism.

DECLARE
      @BatchSize        INT    = 100
    , @LowestValue      BIGINT = 20000
    , @HighestValue     BIGINT = 20010
    , @DeletedRowsCount BIGINT = 0
    , @RowCount         BIGINT = 1;

SET NOCOUNT ON;
GO

WHILE  @DeletedRowsCount &lt  ( @HighestValue - @LowestValue )
BEGIN

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION

        DELETE
        FROM
            dbo.tbl
        WHERE
            c1 IN (
                    SELECT TOP (@BatchSize)
                        c1
                    FROM
                        dbo.tbl
                    WHERE
                        c1 BETWEEN @LowestValue AND @HighestValue
                    ORDER BY
                        c1
                  );

        SET @RowCount = ROWCOUNT_BIG();

    COMMIT TRANSACTION;

    SET @DeletedRowsCount += @RowCount;
    WAITFOR DELAY '000:00:00.025';
    CHECKPOINT;

END;

As you can see, I placed the explicit transaction inside the while loop. If you would like to limit log flushes, then feel free to place it outside the loop. Furthermore, since we are in the FULL recovery model, you may wish to create transaction log backups more often while running your nibbling delete operations, in order to ensure that your transaction log can be prevented from growing outrageously.

So, I have a couple goals with this setup. First, I want my key-range locks; so, I try to keep the batches as small as possible. I also do not want to impact negatively the concurrency on my "gigantic" table; so, I want to take my locks and leave them as fast as I can. So, I recommend that you make your batch sizes small.

Now, I want to provide a very short example of this deletion routine in action. We must open a new window within SSMS and delete one row from our table. I will do this within an implicit transaction using the default READ COMMITTED isolation level.

DELETE FROM
    dbo.tbl
WHERE
    c1 = 20005;

Was this row actually deleted?

SELECT
    c1
FROM
    dbo.tbl
WHERE
    c1 BETWEEN 20000 AND 20010;

Yes, it was deleted.

Now, in order to see our locks, let us open a new window within SSMS and add a code snippet or two. I am using Adam Mechanic's sp_whoisactive, which can be found here: sp_whoisactive

SELECT
    DB_NAME(resource_database_id) AS DatabaseName
  , resource_type
  , request_mode
FROM
    sys.dm_tran_locks
WHERE
    DB_NAME(resource_database_id) = 'test'
    AND resource_type = 'KEY'
ORDER BY
    request_mode;

-- Our insert
sp_lock 55;

-- Our deletions
sp_lock 52;

-- Our active sessions
sp_whoisactive;

Now, we are ready to begin. In a new SSMS window, let us begin an explicit transaction that will attempt to re-insert the one row that we deleted. At the same time, we will fire off our nibbling delete operation.

The insert code:

BEGIN TRANSACTION

    SET IDENTITY_INSERT dbo.tbl ON;

    INSERT  INTO dbo.tbl
            ( c1 , c2 )
    VALUES
            ( 20005 , 1 );

    SET IDENTITY_INSERT dbo.tbl OFF;

--COMMIT TRANSACTION;

Let us kick off both operations beginning with the insert and followed by our deletes. We can see the key-range locks and exclusive locks.

The insert generated these locks:

The nibbling delete/select is holding these locks:

Our insert is blocking our delete as expected:

Now, let us commit the insert transaction and see what is up.

And as expected, all transactions complete. Now, we must check to see whether the insert was a phantom or whether the delete operation removed it as well.

SELECT
    c1
FROM
    dbo.tbl
WHERE
    c1 BETWEEN 20000 AND 20015;

In fact, the insert was deleted; so, no phantom insert was allowed.

So, in conclusion, I think the true intention of this exercise is not to try and track every single row, page, or table-level lock and try to determine whether an element of a batch is locked and would therefore require our delete operation to wait. That may have been the intent of the questioners; however, that task is herculean and basically impractical if not impossible. The real goal is to ensure that no unwanted phenomena arise once we have isolated the range of our batch with locks of our own and then precede to delete the batch. The SERIALIZABLE isolation level achieves this objective. The key is to keep your nibbles small, your transaction log under control, and eliminate unwanted phenomena.

If you want speed, then don't build gigantically deep tables that cannot be partitioned and therefore be unable to use partition switching for the fastest results. The key to speed is partitioning and parallelism; the key to suffering is nibbles and live-locking.

Please let me know what you think.

I created some further examples of the SERIALIZABLE isolation level in action. They should be available at the links below.

Delete Operation

Insert Operation

Equality Operations - Key-Range Locks on Next Key Values

Equality Operations - Singleton Fetch of Existent Data

Equality Operations - Singleton Fetch of Nonexistent Data

Inequality Operations - Key-Range Locks on Range and Next Key Values


How to group a resultset by day? I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day going back 3 months?

I am using datediff but it would be great if I can have a field with the actual date and a count in the resultset.

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())

For starters, why are you reversing your fields in the datediff?

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())

Number two, why does this not work for you?

SELECT crDate, COUNT(*)
FROM tracexml
GROUP BY crDate