Inclusion of ORDER BY on query that returns no rows drastically affects performance Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant when ORDER BY not included. Why?

SELECT *
FROM tinytable t                          /* one narrow row */
JOIN smalltable s on t.id=s.tinyId        /* one narrow row */
JOIN bigtable b on b.smallGuidId=s.GuidId /* a million narrow rows */
WHERE t.foreignId=3                       /* doesn't match */
ORDER BY b.CreatedUtc          /* try with and without this ORDER BY */

I understand that I could have an index on bigtable.smallGuidId, but, I believe that would actually make it worse in this case.

Here's script to create/populate the tables for test. Curiously, it seems to matter that smalltable has an nvarchar(max) field. It also seems to matter that I'm joining on the bigtable with a guid (which I guess makes it want to use hash matching).

CREATE TABLE tinytable
  (
     id        INT PRIMARY KEY IDENTITY(1, 1),
     foreignId INT NOT NULL
  )

CREATE TABLE smalltable
  (
     id     INT PRIMARY KEY IDENTITY(1, 1),
     GuidId UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
     tinyId INT NOT NULL,
     Magic  NVARCHAR(max) NOT NULL DEFAULT ''
  )

CREATE TABLE bigtable
  (
     id          INT PRIMARY KEY IDENTITY(1, 1),
     CreatedUtc  DATETIME NOT NULL DEFAULT GETUTCDATE(),
     smallGuidId UNIQUEIDENTIFIER NOT NULL
  )

INSERT tinytable
       (foreignId)
VALUES(7)

INSERT smalltable
       (tinyId)
VALUES(1)

-- make a million rows
DECLARE @i INT;

SET @i=20;

INSERT bigtable
       (smallGuidId)
SELECT GuidId
FROM   smalltable;

WHILE @i > 0
  BEGIN
      INSERT bigtable
             (smallGuidId)
      SELECT smallGuidId
      FROM   bigtable;

      SET @i=@i - 1;
  END

I've tested on SQL 2005, 2008 and 2008R2 with same results.

I agree with Martin Smith's answer, but the problem is not simply one of statistics, exactly.  The statistics for the foreignId column (assuming automatic statistics are enabled) accurately show that no rows exist for a value of 3 (there's just one, with a value of 7):

DBCC SHOW_STATISTICS (tinytable, foreignId) WITH HISTOGRAM

SQL Server knows that things might have changed since the statistics were captured, so there might be a row for value 3 when the plan is executed.  In addition, any amount of time might elapse between plan compilation and execution (plans are cached for reuse, after all).  As Martin says, SQL Server contains logic to detect when sufficient modifications have been made to justify recompiling any cached plan for optimality reasons.

None of this ultimately matters, however.  With one edge-case exception, the optimizer will never estimate the number of rows produced by a table operation to be zero.  If it can statically determine that the output must always be zero rows, the operation is redundant and will be removed completely.

The optimizer's model instead estimates a minimum of one row.  Employing this heuristic tends to produce better plans on average than would be the case if a lower estimate was possible.  A plan that produces a zero-row estimate at some stage would be useless from that point on in the processing stream, since there would be no basis to make cost-based decisions (zero rows is zero rows no matter what).  If the estimate turns out to be wrong, the plan shape above the zero row estimate stands almost no chance of being reasonable.

The second factor is another modelling assumption called the Containment Assumption.  This essentially says that if a query joins a range of values with another range of values, it is because the ranges overlap.  Another way to put this is to say that the join is being specified because rows are expected to be returned.  Without this reasoning, costs would be generally underestimated, resulting in poor plans for a broad range of common queries.

Essentially, what you have here is a query that doesn't fit the optimizer's model.  There's nothing we can do to 'improve' estimates with multi-column or filtered indexes; there's no way to get an estimate lower than 1 row here.  A real database might have foreign keys to ensure that this situation could not arise, but assuming that is not applicable here, we are left with using hints to correct the out-of-model condition.  Any number of different hint approaches will work with this query.  OPTION (FORCE ORDER) is one that happens to work well with the query as written.


Preordering the table by a specified column I have a table and I am always getting the data by ordering a column. In my case, I  order by datenum. However, the selection is so slow because of the ordering.

Is it possible to order the table after datenum. This column is defined as unique. If  defined as an index, would the selection will be faster?

There is no natural, implied or intrinsic ordering to a table
Any order is only guaranteed by the outermost ORDER BY

If this is slow, then you can either

make the index covering (a UNIQUE constraint is an index already)
make it the clustered index (which is covering by its nature)


Issue with DECLARE @CutOffDate DATE = DATEADD(YEAR), @NumberOfYears, @StartDate); When executing the steps in this query. I am running into an issue with the

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

I get a message of Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable “”CutOffDate”.

Should I be adding something in this syntax? Meaning “year” should be defined? or something along those lines?

I notice a case-sensitive difference between your declaration @CutoffDate, and @CutOffDate from the error message.

Check to see if you're not using a case-sensitive collation.


Slower subquery performance on older MySQL Same query

SELECT z.userid, z.usermeno, z.userpriezvisko, z.userfirma, z.useriban
       (SELECT cis_faktury FROM hlavicka_faktury
        WHERE userid = z.userid ORDER BY cis DESC LIMIT 1) as cis_faktury
FROM zmluvy z;

But a lot slower executed on mysql  Ver 14.14 Distrib 5.5.44 (32 seconds)

The faster runs under mysql  Ver 15.1 Distrib 10.1.24-MariaDB (0.3 seconds)

CREATE TABLE `hlavicka_faktury` (
  `cis` int(11) NOT NULL AUTO_INCREMENT,
  `cis_fakturacii` int(2) NOT NULL,
  `cis_faktury` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `datum_vyst` int(11) NOT NULL,
  `datum_spl` int(11) NOT NULL,
  `datum_dod` int(11) NOT NULL,
  `zaklad` double(14,4) NOT NULL,
  `dph` double(12,4) NOT NULL,
  `zaokruhlenie` double(10,4) NOT NULL,
  `celkom` double(14,4) NOT NULL,
  `sposob_uhrady` int(2) NOT NULL,
  `uhradene` tinyint(1) NOT NULL,
  `stornovane` int(10) NOT NULL,
  `stornofa` tinyint(1) NOT NULL,
  `opakovanie` int(11) NOT NULL,
  `odoslane` tinyint(1) NOT NULL,
  `fo` tinyint(1) NOT NULL,
  `mena` varchar(3) COLLATE utf8_bin NOT NULL DEFAULT 'EUR',
  PRIMARY KEY (`cis`),
  KEY `cis_fakturacii` (`cis_fakturacii`),
  KEY `cis_faktury` (`cis_faktury`),
  KEY `userid` (`userid`),
  KEY `datum_spl` (`datum_spl`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `zmluvy` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `userpass` varchar(15) COLLATE utf8_bin NOT NULL,
  `useraktiv` int(1) NOT NULL,
  `usermeno` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userpriezvisko` varchar(50) COLLATE utf8_bin NOT NULL,
  `useremail1` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userfirma` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `userico` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `userdic` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `usericdph` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `userbanka` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `usercu` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `useriban` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `old_userid` int(11) NOT NULL,
  `user_platdisciplina` double(10,2) NOT NULL,
  `user_efaktura` enum('0','1') COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Any suggestion how to improve without MySQL upgrade?

For further performance, replace

KEY `userid` (`userid`),

With

KEY `ucf` (`userid`, `cis`, `cis_faktury`),

Your Answer has a problem:  It is "wrong" to GROUP BY only some of the non-aggregate columns in the SELECT.  (Newer versions spit at users for doing so.)  And, I can't see how the query would return the same results.

MySQL 5.6 and MariaDB 10.0 added a number of different optimizations.  It is hard to say what exactly lead to the timing difference you see.

Moving to InnoDB is also advised.


The process could not connect to Subscriber '' I'm trying to set up replication between of a database between two servers. The servers are both Server 2012 R2 running SQL SQL Server 2014 SP2 CU5 (12.0.5546.0).

I'm following the tutorial from Microsoft and am stuck on this section (Lesson 2: Creating a Subscription to the Transactional Publication)

I have completed each of the steps as described, but when I view the synchronization status, I get the error

  *The process could not connect to Subscriber ''.

I have gone through and confirmed that I have created all the required local users and re-set their passwords to ensure they are correct. Both machines are on the same domain connected on the same physical LAN.

Any suggestions on how to go about diagnosing? I'm fairly certain it's security related, but just diagnosing what has me stumped.

I recently started to document Replication issues since they are usually unique, and can be troublesome.

if the Subscriber is NULL

Likely this is a problem where the subscription was setup on one side yet not on the other or became corrupt so that an empty value returns. In these cases, recreating Subscription Agent is often the best solution to clean out the errors in the Distribution database.

Generate the publication scripts via SSMS for creating and dropping the subscriptions onto their own query window.
ONLY run the drop subscription to the problem Subscription.
Manually ensure this subscription no longer exists as a job and/or subscription on the Subscriber side.
Run the create subscription statement. Check that the @sync_type is also set properly. Sometimes this can be incorrect.
Generate the snapshot by either starting the Snapshot Agent for the publication or right click on the publication in SSMS and select the Reinitialize Subscription... and select generate new snapshot.

If the Subscriber is not NULL but still has problems

The SQL Agent or some AD account used during the replication process need to be checked.

Check the Type of Replication and start from the side the replication Agent is executed from normally.
Check if the replication is supported.

It is considered supported if the Distributor and the Subscriber are at least 2 Major editions from each other. For example, SQL Server 2008 R2 to 2014 is supported but not 2008 R2 to 2016 and vice versa. Deprecated Features in SQL Server Replication | Microsoft Docs
If unsupported...well, you can guarantee odd issues come up and this will require lots of patience.

Check the Agents Involved using the following query on each Instance (Distributor and Subscriber)

SELECT servicename, service_account
FROM   sys.dm_server_services

Ensure the Instance Has a valid Login and that the database has a valid User Login Mapped to that database as db_owner

Check the Replication Agents from The source of the Subscription Agent (Pull on the subscriber and Push on the Distributor)

The Distribution Agent is db_owner of the database for Replication.
The subscription Agent is also db_owner of the replicated database and a member of the PAL or Publication Access List | Microsoft Docs.

Check the AD account is valid on the server box.

the Agent must be able to Login into the server if using an AD account or directly to the Instance via a valid SQL Server Login. Please note that a SQL Server Login is not sent using very robust encryption methods and is considered a weak form of security. Utilize Active Directory Objects instead to hide the credentials from unwanted intruders.
For both servers, verify the AD account is also valid for the Snapshot Folder used by replication. If the Agent is the Distributor, it needs at least write access. If the Agent is the subscriber, it needs at least read access.

Most likely, one of these steps will reveal the mystery...though, for unsupported replications I have seen a few odd situations.


Select arbitrary single value for GROUP BY: What's the fastest option? I have a query that I use to indicate locations in a map where there are overlapping points:
select
    min(objectid) as min_objectid,
    longitudex,
    latitudey,
    count(1) as count,
    min(shape) as min_shape
from
    workorders
group by
    longitudex,
    latitudey
having
    count(1) > 1

In the mapping software that I use, I need to include columns like objectid and shape. For those columns, it doesn't matter which of the grouped-by rows the values come from, just as long as there is a value.
Presently, I'm using min() to get an arbitrary value for those columns. However, I don't know if that's the fastest option since finding the minimum value would require calculation — and I wonder if that time spent is unnecessary.
What is the fastest option for getting an arbitrary/single value for GROUP BY in an Oracle query?

Your query does return the duplicate locations, but does not return the individual points (work orders) with the same location.
This returns those locations again (same as your query, just reformatted in a more compact notation):
select longitudex, latitudey, count(*)
from workorders
group by longitudex, latitudey
having count(*) > 1;

Then this returns the individual work orders that share the same location:
select objectid, longitudex, latitudey, shape
from workorders
where (longitudex, latitudey) in (
  select longitudex, latitudey
  from workorders
  group by longitudex, latitudey
  having count(*) > 1
);

That obviously only works if the geographical coordinates are exactly the same for work orders at the same location, down to all decimals. If not then you need to use spatial operators to compare the locations. Those are available with databases like Oracle (out of the box) or PostgreSQL (with the PostGis extension).


Using MATCH, AGAINST multiple times in MySQL fulltext search I'm using MySQL database for my e-commerce project.
And here is my query for search:

SELECT DISTINCT p.id, p.name, p.price, p.created_at,
    MATCH(p.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Prv,
    MATCH(b.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Brv,
    MATCH(bm.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as BMrv,
    MATCH(o.name)   AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as Orv,
    MATCH(ov.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE) as OVrv
FROM products p
    LEFT JOIN brands b          ON  b.id = p.brand_id                     AND MATCH(b.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN brand_models bm   ON  bm.id = p.brand_model_id              AND MATCH(bm.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN options o         ON  o.product_type_id = p.product_type_id AND MATCH(o.name)  AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
    LEFT JOIN product_option_values pov ON  pov.product_id = p.id
    LEFT JOIN option_values ov  ON  ov.id = pov.option_value_id           AND MATCH(ov.name) AGAINST('Sony* xperia* FHD* ' IN BOOLEAN MODE)
WHERE
    (MATCH(p.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(b.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(bm.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(o.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE)
    OR MATCH(ov.name) AGAINST('Sony* xperia* FHD*' IN BOOLEAN MODE))
    AND
    COALESCE(b.id, bm.id, o.id, ov.id, pov.id) IS NOT NULL
GROUP BY
    p.id
ORDER BY
    ((Prv*100) + Brv + BMrv + Orv + OVrv) DESC, p.updated_at DESC;

Here I used MATCH, AGAINST 3 times. First in SELECT for relevance. Second in JOIN and third time in WHERE.

1) Can usage MATCH, AGAINST multiple times affect to speed?

2) If yes, how can I change my code to get same result and reduce speed?

The main performance problem comes from OR between multiple tables, then from the need to check for rows from each table.

Plan A:

One common improvement is to turn OR into UNION, but it may be only a partial solution.  See how far you can get with this query (which will become a subquery in the real query):

SELECT p.id, MATCH(b...) AS Brv
      FROM brands b        JOIN products p ON b.id = p.brand_id
      WHERE MATCH(b...)
UNION ALL
SELECT p.id, MATCH(bm...) AS BMrv
      FROM brand_models bm JOIN products p ON bm.id = p.brand_model_id
      WHERE MATCH(bm...)
...

That query should be reasonably fast because each subquery can efficiently use the FULLTEXT index that (I assume) exists in brands, etc.  Also, you must have INDEX(brand_id), INDEX(brand_model_id), etc.

Once that looks good (and fast), the rest will be even messier, but fast.

Plan B:

This is possibly better in the long run, but it requires setup.

Build a single table with all the text columns into 5 columns (with a single FULLTEXT index on all 5).  Or, you could build a single text column with all the text combined from the other columns.  By having a single column (with FULLTEXT), the test will be faster, and avoid the need for OR or Coalesce.

That table would have a copy of p.id, thereby allowing you to quickly reach into the 5 tables to get the 5 individual MATCHes (if not already gotten) and do the ORDER BY computation.

Plan C:

This might be best.  Noticing the format of

((Prv*100) + Brv + BMrv + Orv + OVrv)

Do like Plan B, but with 2 text columns -- one for p.name, one for the combination of the other 4 tables.  You get Prv from the MATCH on one column, and you get (approximately) Brv + BMrv + Orv + OVrv from the other text column.

Plan D:

Hmmm... This may be even simpler?  Have an extra table with p.id, name, source (products, brands, etc) and a "weight" (100 or 1).  Then a simple query against this table gets virtually all the data needed (except for p.name, p.price, p.created_at).  Then JOIN to products to get those 3 columns.


Can PostgreSQL index calculations be multithreaded? During a single query write of approximately 300,000 rows with 30 columns all indexed, I noticed that only one core was maxed at a time.

Is there any way to calculate indexes multithreaded for single inserts?

Is there any way to calculate indexes multithreaded for single inserts?

Unfortunately, no.

PostgreSQL backends are single-threaded, and a single connection can use only one backend.

There's work to enhance this, but it's a seriously difficult and big job because of PostgreSQL's process-based architecture.

The only real option is to split the insert its self into multiple concurrent connections, each doing part of the work.


Advantages of a hierarchical schema for private chat This post claims that

  All modern chatting interfaces, without exception, implement a
  hierarchical and a not-chronological schema for chat.

It also links another post which gives an example schema for such an interface. Basically, each message contains a reference to the message it replies to (the parent). In order to display a conversation, those references are traversed recursively, starting from messages without a parent.

But I don't see how this is any better than simply sorting the messages by the time they were sent. Since one person may send multiple messages in a row, this doesn't save me from identifying the author (even if it's just a boolean). Can somebody explain the advantages?

One advantage is that the system can distinguish each thread of conversation. That makes it easier for the computers and for the people. For example, machine analytics will benefit by knowing who replies to whom, and hence who the cliques, influencers and outliers are. People will benefit from being able to segregate each topic of conversation. Responses to direct questions do not get lost in the general traffic. Long-ago posts can be brought back into play without having to explicitly pull context. You can get notification on responses to your messages, rather than just general traffic in the chat room, which is more helpful.

Say I log onto a chat. There are some messages to which I want to reply. One says "Fancy a movie on Tuesday?" the other "Fancy skating on Tuesday?" I reply "yes" and "no." What will happen on Tuesday? I could write "I want to go skating but not see a movie." From my point of view clicking the "reply to" button and typing two single words is much easier and conveys the same information.

As an aside, I'd take statements of an absolute nature about how third-party systems are written with a pinch of salt until they are substantiated by knowledgeable sources.


Table Partitioning in SP1 for SQL Server 2016 Can anyone validate\conclude for sure if SP1 for SQL Server 2016 now makes Table Partitioning available in the Standard Edition version? It has always been an Enterprise Edition feature in the past. I have seen this on a few blogs, but is this true?

Thank You,
Max

I can do one better than Standard Edition, here is proof that it is available even in Express Edition (click to enlarge):

As are all of the other features "they say" are enabled. Everything in this post is true, for example.


Apache Ignite as linked Server in SQLServer? I tried to set up a linked server via ODBC connection in SQLServer. It seem to connect but showed all the caches as empty names in SSMS. Also I couldnt get a select query to work.

Has anybody had any luck with doing this?
If so.. How did you write the select statement?

Maybe you don't have tables for your caches. By default, Apache Ignite will not create tables in its caches - i.e. they are not mapped to SQL but work in key-value mode.

There are basically three options to make tables in Ignite - CREATE TABLE, Query Entities, and Indexed Types + annotations.

You can validate it by connecting to cluster with built-in sqlline utility and then issuing !tables command.


How to use a table as a reference for another table in MySQL I have code from generic SQL that I need to find out how to run in MySQL:

CREATE TABLE firstTable
(
freq int NULL,
w1 int NULL,
w2 int NULL,
w3 int NULL
)

CREATE TABLE secondTable
(
[wID] [int] NULL,
[w1] [varchar](50) NULL,
[w1cs] [varchar](50) NULL,
[L1] [varchar](50) NULL,
[c1] [varchar](25) NULL
)

The creation of the first table is simple enough, but MySQL runs into syntax errors with trying to create the second table. I know this code works in another version of SQL, but I only have access to MySQL. The data I need is in [firstTable], and I need to use [secondTable] to look up the identity of the items (coded as integers) in [firstTable].

Also, the data in [firstTable] is really large (though I do have the requisite amount of RAM)... Is there a better way of importing the data than the standard MySQL command that's designed for larger files?

Cheers, and thanks so much!

Change the syntax as per mysql:

CREATE TABLE secondTable
    (
    wID int NULL,
    w1 varchar(50) NULL,
    w1cs varchar(50) NULL,
    L1 varchar(50) NULL,
    c1 varchar(25) NULL
    )


Avoiding "Row by row" fetch method when dealing with source LOB columns I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying:

  'Row by Row' fetch method is enforced because the table has LOB column(s). Column content is LOB

The thing is, none of the column really need to be LOBs. There's a few that are TEXT types, but could fit easily within a varchar(max). Even stranger, though, most already are varchars, but it seems anything over varchar(128) is being treated as if it was a LOB (in advance properties, the data type is DT_NTEXT).

I event tried doing a manual SQL command where I explicitly casted every string type to a varchar of an appropriate length in the select statement, and they're still being set as DT_NTEXT in the ODBC source.

I'm not a DBA, so it's entirely possible I'm doing something really stupid. I would just like to know the best way to ensure that the types end up as varchars so I can batch fetch. Any ideas?

In case it matters, I'm using SSIS-BI 2014 inside Visual Studio 2013.

I used Data Conversion for the varchar larger than 128 as NTEXT but what removed the error for me eventually is the set Validate External Data to False.


Trigger to create symmetrical rows I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. I've tried creating after trigger, but it exceed stack depth, because it called itself on insert it had within itself. What is the proper way to do it?

Table would look somewhat like this:

CREATE TABLE MY_RELATION(
ID SERIAL,
THING1 INT NOT NULL,
THING2 INT NOT NULL,
PRIMARY KEY (ID)
);

CREATE TABLE MY_THING(
ID SERIAL,
VAL INT NOT NULL
PRIMARY KEY(ID)
);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING1) REFERENCES MY_THING(ID);
ALTER TABLE MY_RELATION ADD FOREIGN KEY (THING2) REFERENCES MY_THING(ID);

The right way to do this will probably be to have your application INSERT via a stored procedure (or in Postgres a function).

If stored procedures are not an option, create a view and rewrite inserts against that view so that they affect some other table.

 CREATE TABLE t (
     a integer,
     b integer
 );
 CREATE VIEW v AS SELECT t.a, t.b FROM t;
 CREATE RULE mirror AS
    ON INSERT TO v DO INSTEAD
    INSERT INTO t (a, b) VALUES (new.a,new.b), (new.b,new.a);

Incidentally, using a view will allow you to enforce your A-B, B-A pairing by also rewriting UPDATEs and DELETEs using other rules.


How to Get Median Value From Row in SQL Server 2008 R2 Without Using Pivot and Unpivot Table Create Table Script

create table temp
(
    id int identity(1,1),
    a decimal(6,2),
    b decimal(6,2),
    c decimal(6,2),
    d decimal(6,2),
    e decimal(6,2),
    f decimal(6,2),
    g decimal(6,2),
    h decimal(6,2),
    i decimal(6,2),
    j decimal(6,2),
    k decimal(6,2),
    l decimal(6,2),
    m decimal(6,2),
    n decimal(6,2),
    o decimal(6,2),
    p decimal(6,2),
    q decimal(6,2),
    r decimal(6,2),
    s decimal(6,2),
    t decimal(6,2),
    u decimal(6,2)
)

Insert Script

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8)

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8)

Expected Result

Median
======
first row  - 5.00
second row - 4.00

Non-working Solutions

I tried the below query which is working fine in SQL Server 2014, but has issues in SQL Server 2008 R2.

select id, avg(val)
from (
    select id, val
         , count(*) over (partition by id) as c
         , row_number() over (partition by id order by val) as rn
    from temp unpivot (
             val for col in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
         ) as x
) as y
where rn IN ((c + 1)/2, (c + 2)/2)
group by id;

I ran the above query in 2014 version and it's working properly, but it's not working in 2008 R2. I get this error in SQL Server 2008 R2:

  Incorrect syntax near the keyword 'for'

The reason must be because my database's compatibility level is 80. But if I change the compatibility level, it will affect my application, so I can't do that.

I've also tried this query:

select id,
(select cast(Avg(TotAvg)as decimal(6,2)) as Median from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b)),
(convert(decimal(6,2), c)),
(convert(decimal(6,2), d)),(convert(decimal(6,2), e)),
(convert(decimal(6,2), f)),(convert(decimal(6,2), g)),(convert(decimal(6,2), h)),(convert(decimal(6,2), i)),
(convert(decimal(6,2), j)),(convert(decimal(6,2), k)),(convert(decimal(6,2), l)),(convert(decimal(6,2), m)),
(convert(decimal(6,2), n)),(convert(decimal(6,2), o)),(convert(decimal(6,2), p)),(convert(decimal(6,2), q)),
(convert(decimal(6,2), r)),(convert(decimal(6,2), s)),(convert(decimal(6,2), t)),(convert(decimal(6,2), u))) as Totalavg(TotAvg))
Median
from tempone

Obviously it calculates the average, but I need the median.

PIVOT and UNPIVOT are indeed not supported under compatibility level 80.

However, you can unpivot rows using a nested VALUES constructor. The resulting query in my case looks slightly unwieldy because of the double nesting, but it works in SQL Server 2008 with any supported compatibility level:

SELECT
  id,
  median =
  (
    SELECT
      AVG(val)
    FROM
      (
        SELECT
          c   = COUNT(*) OVER (),
          rn  = ROW_NUMBER() OVER (ORDER BY v.val ASC),
          val = v.val
        FROM
          (
            VALUES
            (t.a), (t.b), (t.c), (t.d), (t.e), (t.f), (t.g),
            (t.h), (t.i), (t.j), (t.k), (t.l), (t.m), (t.n),
            (t.o), (t.p), (t.q), (t.r), (t.s), (t.t), (t.u)
          ) AS v (val)
        WHERE
          v.val IS NOT NULL
      ) AS derived
    WHERE
      rn IN ((c + 1) / 2, (c + 2) / 2)
  )
FROM
  temp AS t
;

The v.val IS NOT NULL filtering is there to imitate the UNPIVOT behaviour more closely, because UNPIVOT automatically filters out NULL values.

An extra nesting was necessary because there was no other way to produce the count and row numbers and use them at the same nesting level.

So the innermost SELECT (SELECT ... FROM (VALUES ...)) unpivots the row and provides the row count and row numbers, while the middle-tier level calculates the median.

It is possible to reduce nesting with the help of CROSS APPLY and grouping in the main query, like this:

SELECT
  id,
  median = AVG(x.val)
FROM
  temp AS t
  CROSS APPLY
  (
    SELECT
      c   = COUNT(*) OVER (),
      rn  = ROW_NUMBER() OVER (ORDER BY v.val ASC),
      val = v.val
    FROM
      (
        VALUES
        (t.a), (t.b), (t.c), (t.d), (t.e), (t.f), (t.g),
        (t.h), (t.i), (t.j), (t.k), (t.l), (t.m), (t.n),
        (t.o), (t.p), (t.q), (t.r), (t.s), (t.t), (t.u)
      ) AS v (val)
    WHERE
      v.val IS NOT NULL
  ) AS x
WHERE
  x.rn IN ((x.c + 1) / 2, (x.c + 2) / 2)
GROUP BY
  t.id
;

For a live demonstration of both methods, please follow this dbfiddle.uk link.


Postgresql: Aggregate objects into an (json) array (subquery issues) I'm sorry for the vague title, but I simply don't know the right words to describe this.

I have this query that transforms a bunch of columns into an object that works just fine:

SELECT row_to_json(t)
FROM (
  SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t

However, I want to group the objects that fall within a certain category into an array. This category is defined by a fourth column inside my table named "cargoProductId". The array should have the value of "cargoProductId" as the key. So:

"961":[
{"type":"Feature",....
{"type":"Feature",....
{"type":"Feature",....
],
"962":[
.....
]

So I've been struggling with this for the last 1 1/2 hours or so. I really have no clue how to do this. This is what I have right now:

SELECT array_agg(row_to_json(t))
FROM (
  SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t) FROM "bgbCargoMinardJSON" GROUP BY "carProductId"

If you are on 9.4 something like this might be what you are after:

select json_object(array_agg(id)::text[],array_agg(rw)::text[])
from( select id
           , ( select to_json(array_agg(row_to_json(t)))
               from (select typ,prop from bgb where id=b.id) t ) rw
      from bgb b
      group by id ) z;


Possible Causes of a Slow Sql View When Filtered I have a view in Sql Server 2008 that when performing just a:

SELECT * FROM [Table]

Will return roughly 40000 rows by 130 columns. Here are 6 runs with client statistics on (roughly 7 seconds average):

Let's just assume those times are fine, the problem comes when a filter is applied. Here are 6 runs with client statistics on when I filter (roughly 6 seconds average):

There are a few things that are annoying / frustrating about this:

The time difference is only about 1300ms on average.
Only 16 rows are returned in the filtered version.
The column that I am filtering is an int column, that has proper indexes.
The filtered SQL literally looks like:

SELECT * FROM [Table] Where [IntColumn] = 15 -- returns 16 rows

I am coming to you guys because I am lost as to why the filtered version could possibly take so long. It should be extremely simple for the SQL engine to find those 16 rows right from the start of the execution and then calculate / look-up the values for the 130 columns. My questions really are:

What can cause this?
What should I be looking for to try and fix this?

Here are the anonymous execution plans (.sqlplan in .zip):

Anonymous Sql Plans (Zippyshare)
Anonymous Sql Plans (Dropbox)

It seems like a small portion of the plan is different depending on whether the filter is present. I'm trying to piece together what is happening, but that is some serious obfuscation trying to match up everything. Without knowing the definitions of things like Table1.Index9 and Table2.Index38 it's going to be very difficult to get specific help with the most expensive operators you have here.

In the meantime I have a couple of generic performance suggestions, other than reducing the complexity of the view and maybe considering having simpler views that don't output 130 columns (try not to go cross-eyed looking at this join diagram):

(1) make sure your statistics are up to date on all the underlying tables. Almost every single operation is showing a massive gap between estimated and actual rows, which could help to explain suboptimal optimizer choices.

(2) get rid of two lookups by adding the following columns to the following indexes (probably as INCLUDE columns):

Table1.Column8  => Index3
Table1.Column43 => Index1

(3) some of these expressions are maddening, e.g.

CASE WHEN (
 Database0.Schema0.Table3.Column42 IS NOT NULL OR
 Database0.Schema0.Table3.Column42 IS NULL
)
AND Column56 IS NOT NULL
AND Database0.Schema0.Table3.Column55 IS NULL
AND Database0.Schema0.Table3.Column43 IS NULL
THEN (1) ELSE (0) END

Seriously? Look at that clause closely (and the view seems to be littered with dozens of these). No human could have done this on purpose. So what brain dead code generator produced this mess?


DBaaS concept for SQL Server I need to create a Database as a Service (DBaaS) concept for our Datacenter. Currently we have everything of VMware – but this is not really a DaaS concept I think. Corporate policy does not allow public cloud solutions. Is it possible to implement something like Azure SQL Database in your own datacenter?

Yes, of course it's possible. Microsoft's done it in their datacentres, you can do it in yours. It may take some time and effort however. :-)

There is not yet an Azure on-prem product. I've read rumours of one due in the next year or so. Either way, it will take a while to be fully functional and licensing not going to be cheap.

Best bet would be to roll out the DevOps toolkit. Infrastructure as code; desired state configuration; pre-speced VM images produced from a pick list; automated on-demand app deployment. All the bits are around and open source. Linking them will take a few dedicated months. Getting management approval, and educating users to take responsibility for their usage may take longer.

In July 2017 Microsoft announced Azure Stack, which makes their cloud management software available for on-premises deployment. Included in this is SQL databases.


How to programmatically remove a push subscription? I have 4 databases, a publisher and 3 subscribers (transactional replication).

In case of an error in the publisher I want to:

delete all subscriptions
create a new publisher
make the two remaining databases subscribe to the new publisher.

(so that the program can continue normally and the 3 databases continue up to date)

I have the code to create a new publication and to create a new subscriber, but I don't know how to remove the old subscription.

I've read this Here:

  At the Publisher on the publication database, execute
  sp_dropsubscription (Transact-SQL). Specify @publication and
  @subscriber. Specify a value of all for @article. (Optional) If the
  Distributor cannot be accessed, specify a value of 1 for
  @ignore_distributor to delete the subscription without removing
  related objects at the Distributor.

  At the Subscriber on the subscription database, execute sp_subscription_cleanup (Transact-SQL) to remove replication metadata
  in the subscription database.

but im my case, publisher and distributor are on the same machine. (and so I can't execute sp_dropsubsription right away, because the publisher machine is down)

edit:
I've done sp_subscription_cleanup @publisher='PCONE', @publisher_db='TEST' (in one of the subscribers) but the subscription still is in the local subscriptions folder

and so this does not prevent the old publisher to send data when he comes back to life.

How can I prevent that? (how can I programmatically do the equivalent to right click a subscription and select delete)

Since its the publisher that pushes the data, removing the subscription is done at the publisher.  Here's a script I use (Taken from Wrox).  I first find the subscription I want to remove in part 1, and then use that information to drop the subscription.  This script isn't meant to be run all at once.

**/* Part I on the publisher/distribution server */**
use distribution
go

/* Declare a table variable */
declare @subscription_push table
(
publisher_id smallint,
publisher_db sysname,
subscriber_db sysname,
subscription_int int,
sync_type tinyint,
status tinyint
);

insert into @subscription_push
select publisher_id, publisher_db, subscriber_db, subscription_type, sync_type, status
from distribution..MSsubscriptions
where subscription_type = 1 and status = 2

/* check the data */
select * from @subscription_push

/* Declare table variable that will store the publisher, the publication database, the type of publication and the name of the publication using
sp_helpsubscription */

declare @subscriberinfo table
(publisher sysname,
subscriber sysname);

insert into @subscriberinfo
select publisher, subscriber from distribution..MSsubscriber_info

/* check the data */
select * from @subscriberinfo

**/* Part II */**

/* Then remove the subscription from the publisher based on the information in the table @subscriberinfo*/
exec sp_dropsubscription @publication='publication_name', @article='article or all for all articles', @subscriber='subscribername', @destination_db='TEST'

go

Hope this helps.  I have used in a test environment several times.  Perhaps someone has a better way to remove push subscriptions?


Maximize production of log records I need to fill in transactional log file in possibly shortest amount of time.
I have tried a few variants of a loop of inserts and deletes on the same record. I fired it on separate tables in several sessions at the same time. The result was not bad but it is still too slow - about 1 GB per minute. And my log file is 500 GB (Despite regular backups. It is a very busy system, and put in Availability Group).
Could you suggest a clever and effective way to fill in the file?
And in case you are wondering why I am doing such a strange thing. I need to temporarily create a secondary log file and switch the log writer header to it. Then I will be able to truncate and recreate the primary log file. Because of too small autogrowth, it  consists now of some 15,000 VLFs which I want to fix.

This is an XY problem. You want to fix the VLFs in your log file, not fill a transaction log so it overflows into a second log file, freeing up the first log file.
There are definitely ways to stabilize VLFs, even in an AG, even in full recovery, and without disrupting your log chain, creating a second log file, or sending a bunch of bogus transactions to your secondaries. Here is a long-winded but easily reproducible example.

First, let's make a database with less than optimal filegrowth for the log. This will generate some pretty bad VLF layout. Let's also take a backup so it isn't operating in pseudo-simple mode:
CREATE DATABASE abc
      ON (name = N'abc_data', filename   = N'c:\temp\abc.mdf')
  LOG ON (name = N'abc_log',  filename   = N'c:\temp\abc.ldf',
          size = 1024KB,      filegrowth = 1024KB);
GO

ALTER DATABASE abc SET RECOVERY FULL;
GO

BACKUP DATABASE abc TO DISK = 'c:\temp\abc.bak' WITH INIT, COMPRESSION;
GO

Now, let's look at the VLF and file info (note that your metrics may differ slightly). Before adding any data:
SELECT current_vlf_size_mb, total_vlf_count, active_vlf_count,
  avg_size_mb = total_log_size_mb/total_vlf_count , [used_log_%]
    = (SELECT used_log_space_in_percent FROM abc.sys.dm_db_log_space_usage)
FROM sys.dm_db_log_stats(DB_ID(N'abc'));

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.242187             4                1                 0.24804675   49.6063

Let's create a table and generate a bunch of log activity to encourage some sub-optimal log growth (this is to simulate the problem in a new database, not to solve the problem you already have):
SET NOCOUNT ON;
GO

CREATE TABLE dbo.flarb(a int, x char(4020) not null default 'x');
GO

BEGIN TRANSACTION;
INSERT dbo.flarb(a) SELECT object_id FROM sys.all_objects;
COMMIT TRANSACTION;

BEGIN TRANSACTION;
INSERT dbo.flarb(a) SELECT object_id FROM sys.all_objects;
COMMIT TRANSACTION;
GO 500

After we add that data, let's run query 1 again (and also EXEC abc.sys.sp_helpfile;):
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
1                    11035            11032             0.997552532  99.97239

name     size
-------  -----------
abc_log  11272192 KB

Your data may vary depending on SQL Server version and how many objects are in your database, but order of magnitude should be similar. Let's see what happens when we try to shrink our log file:
DBCC SHRINKFILE(abc_log, 4000);

Running query 1 + sp_helpfile again:
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
1                    11033            11032              0.99755208  99.9913

name     size
-------  -----------
abc_log  11270144 KB

I mean, it shrank a little wee bit, but didn't have a meaningful impact on VLFs. Think about it - it can't shrink the log because the log hasn't been backed up, so all that data is still active and can't be removed. So let's try backing up the log, then running another shrink, and run the query again:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.1.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.242187             11033            6                 0.997552088  3.1719937

name     size
-------  -----------
abc_log  11270144 KB

No good. While we got the log usage down to 3%, we still couldn't reduce the size of the log. Let's try again:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.2.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             11033            5                 0.997552088  3.1494207

name     size
-------  -----------
abc_log  4096000 KB

Getting closer! This time, the log file agreed to shrink, but the VLF count is still out of proportion. So, we'll try one more time:
BACKUP LOG abc TO DISK = 'C:\temp\abc.bak.3.trn' WITH INIT, COMPRESSION;
DBCC SHRINKFILE(abc_log, 4000);

-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             4027             1                 0.993293317  3.153424

name     size
-------  -----------
abc_log  4096000 KB

This time, it worked a little better. We got the size down, and the log % down, and VLFs are reduced, but still too high (and average size is too small).
So let's try halving the log file, then growing it in even, 8GB chunks. Using 64GB as an example; you may need more, or that may be ridiculous. But the point is to pick a size it's never exceeded before, because if you want to control VLFs, you're going to want to prevent it from auto-growing, and if it grew to 120GB before, it will grow to 120GB again (Lots of background here):
DBCC SHRINKFILE(abc_log, 1);
GO
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size =  8000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 16000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 24000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 32000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 40000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 48000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 56000MB);
ALTER DATABASE abc MODIFY FILE (name = 'abc_log', size = 64000MB);
-- should probably fix auto-growth here, too

Now, query 1 + sp_helpfile returns:
-------------------  ---------------  ----------------  -----------  ----------
current_vlf_size_mb  total_vlf_count  active_vlf_count  avg_size_mb  used_log_%
-------------------  ---------------  ----------------  -----------  ----------
0.265625             132              1                 484.8484257  0.00710449

name     size
-------  -----------
abc_log  65536000 KB

Your results may vary - you may have 1000 VLFs, you may have 700, it can vary depending on how the shrink and growth operations work through the log. But you should be in a better place. You can keep experimenting with sizes that get you toward an optimal VLF count/size (say, 0.5 GB per VLF).
Remember these log backups were introduced into your log chain. So you should put them with your other log backups, just in case you need to recover, or do this right before your next full backup.
Also, you may wonder, why wasn't one log backup enough? Some background here.


How do I copy SQL Server Data Discovery and Classification rules and suppressed recommendations to another database? We have hundreds of SQL Server databases, all of which share a common schema. We are implementing Data Discovery and Classification policies. We wish to create one rule set and copy it to all like databases.
Using either SSMS or the Azure portal, it's possible to view recommendations for data classification, accept or suppress recommendations and create custom classifications.
It is well-documented that classifications are stored in sys.sensitivity_classifications and can be managed with ADD SENSITIVITY CLASSIFICATION and DROP SENSITIVITY CLASSIFICATION commands.
What is not documented as of this writing is where the suppressed recommendations are stored.
We wish to replicate the suppressed recommendations to all databases in our fleet, so that Azure Security Center will stop nagging us to finish classifying all our data.
Where are the suppressed recommendations stored? How can I copy the rules to other like databases?

I found the Data Discovery and Classification recommendation suppressions in sys.extended_properties with the property name sys_data_classification_recommendation_disabled.
Copying these extended properties to another database successfully suppresses the recommendations.
I wrote the following SQL to be run in a database in which classification rules and suppressions have already been set. It code-generates the SQL commands needed to migrate suppression rules from one database to another. The script it generates is idempotent, checking for the existence of the properties before setting them.
Do be mindful that you might have permission to read the properties, but not write them.
;with Suppress (SchemaName, TableName, ColumnName)
AS
(
select QUOTENAME(schema_name(t.schema_id)) as SchemaName, QUOTENAME(t.Name) as TableName, QUOTENAME(c.Name) as ColumnName
from sys.extended_properties p
join sys.tables t on p.major_id = t.object_id
join sys.all_columns c on c.object_id = t.object_id and p.minor_id = c.column_id
where p.name = 'sys_data_classification_recommendation_disabled'
)
select Script =
N'IF NOT EXISTS (
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID(''' + s.SchemaName + N'.' + s.TableName + ''')
        AND [name] = N''sys_data_classification_recommendation_disabled''
        AND [minor_id] = (
            SELECT [column_id]
            FROM SYS.COLUMNS
            WHERE [name] = ''' + s.ColumnName + '''
                AND [object_id] = OBJECT_ID(''' + s.SchemaName + N'.' + s.Tablename + ''')
            )
    )
EXEC sys.sp_addextendedproperty @name=N''sys_data_classification_recommendation_disabled'', @value=N''1'' , @level0type=N''SCHEMA'',@level0name=N''' + s.SchemaName +
''', @level1type=N''TABLE'',@level1name=N''' + s.TableName + ''', @level2type=N''COLUMN'',@level2name=N''' + s.ColumnName + ''';'
from Suppress s

This next script code-generates the SQL commands needed to migrate the actual classification rules (not recommendation suppressions) from one database to another.
The ADD SENSITIVITY CLASSIFICATION command is blissfully idempotent by nature. You do not have to check for existence before running it a second time.
select 'ADD SENSITIVITY CLASSIFICATION TO ' + s.schema_name + '.' + s.table_name + '.' + s.column_name + ' WITH (LABEL=''' + s.label + ''', INFORMATION_TYPE=''' + s.information_type + ''', RANK=' + s.rank_desc + ');'
from
(
SELECT
    cast(schema_name(O.schema_id) as nvarchar(200)) AS schema_name,
    cast(O.NAME as nvarchar(200)) AS table_name,
    cast(C.NAME as nvarchar(200)) AS column_name,
    cast(information_type as nvarchar(200)) as information_type,
    cast(label as nvarchar(200)) as label,
    rank,
    cast(rank_desc as nvarchar(200)) as rank_desc
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
    JOIN sys.columns C
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
) as s
order by s.schema_name, s.table_name, s.column_name


Can I connect two mysql database located in different server by php? I want to connect two mysql database from different server. Is it possible..

Please give me a example if yes. I have bluehost and godaddy server.

You can actually use as many connections as needed (within system resources limitations).

Using mySQLi:

<?php
$servername_1 = "server_1";
$username_1 = "username_1";
$password_1 = "password:1";
$db_1 = "db_1" ;

$servername_2 = "server_2";
$username_2 = "username_2";
$password_2 = "password_2";
$db_2 = "db_2" ;

// Create connections, and check
$conn_1 = new mysqli($servername_1, $username_1, $password_1, $db_1);
if ($conn_1->connect_error) {
    die("Connection 1 failed: " . $conn_1->connect_error);
}

$conn_2 = new mysqli($servername_2, $username_2, $password_2, $db_2);
if ($conn_2->connect_error) {
    die("Connection 2 failed: " . $conn_2->connect_error);
}

echo "Connected successfully";
?>

Using PDO

<?php
$servername_1 = "server_1";
$username_1 = "username_1";
$password_1 = "password:1";
$db_1 = "db_1" ;

$servername_2 = "server_2";
$username_2 = "username_2";
$password_2 = "password_2";
$db_2 = "db_2 ;

try
{
    $conn_1 = new PDO("mysql:host=$servername_1;dbname=$db_1", $username_1, $password_1);
    // set the PDO error mode to exception
    $conn_1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully 1";
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

try
{
    $conn_2 = new PDO("mysql:host=$servername_2;dbname=$db_2", $username_2, $password_2);
    // set the PDO error mode to exception
    $conn_2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully 2";
}
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}
?>

More parameters can be passed both to the mysqli function (such as port number) or the PDO connection strings.

For more info:

PHP Connect to MySQL  (the above examples are basically a variation (x 2) of theirs)
MySQL Improved Extension
MySQL Functions (PDO_MYSQL)


Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008? Hopefully the question is fairly self-explanatory.

I've been ferreting through the, what appears to be quite minimal/poor microsoft documentation regarding both views and geography datatypes in the sql server 2008, and I think the answer is "no, geography datatypes are either not supported or minimally supported within a view"; but I'm not entirely sure, and I'd love some corroboration.

I've been attempting to add geography data to an existing view. And my end goal is to retrieve longitude/latitude data from the field in question.

Here's the select statement (simplified) that works when applied to the table but doesn't work when defined within the view:

SELECT
    [dbo.vendor.location].Lat AS latitude,
FROM
    dbo.vendor

Note that [dbo.vendor.location] is the field, and Lat is the latitude property of the geography datatype.

And when that sql is run within the view, the following error is returned:

The multi-part identifier "dbo.vendor.location.Lat" could not be bound

Edit:

Note that I've also tried the following query, with similar results:

SELECT
    [dbo].[vendor].[location].Lat AS latitude,
FROM
    dbo.vendor

In this case, SQL Management Studio removes the quotes and returns the same "multi-part ... could not be bound" error.

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list.

use simply vendor.location.Lat or location.Lat or define a table alias and use that.

CREATE TABLE dbo.Vendor([location] geography)

GO

/*Works fine*/
CREATE VIEW V1
AS
SELECT
   v.location.Lat AS latitude
FROM
    dbo.vendor v

GO

/*Fails*/
CREATE VIEW V2
AS
SELECT
   dbo.vendor.location.Lat AS latitude
FROM
    dbo.vendor

The use of the column dbo.vendor.location on its own (without the CLR property call) does in fact still work in the column list but the use of "More than two-part column name" is on the list of deprecated TSQL features anyway.


Combined column data display only unique value This is my source table. I want to combined column data and display unique data only.

SQL> SELECT * FROM DUMMY;

PLAN_CD              PLAN_NM              CLASS
-------------------- -------------------- ------
D01501               DENTAL               AA
D01501               DENTAL               AB
D01501               DENTAL               AC
V01501               VISION               AA
V01501               VISION               AB

And i want to ouput like this.

PLAN_CD              PLAN_NM
-------              --------
D01501               DENTAL,AA,AB,AC
V01501               VISION,AA,AB

I am using oracle 10g.Please give me query for oracle 10g.

Thanks in advance.

There is no easy and supported built-in method for string aggregation in 10g. (Do not use WM_CONCAT, as it is not supported, and it is not available anymore in 12c.)

You need to write your own string aggregation function, for example:

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

CREATE FUNCTION to_string (
                nt_in        IN varchar2_ntt,
                delimiter_in IN VARCHAR2 DEFAULT ','
                ) RETURN VARCHAR2 IS

   v_idx PLS_INTEGER;
   v_str VARCHAR2(32767);
   v_dlm VARCHAR2(10);

BEGIN

   v_idx := nt_in.FIRST;
   WHILE v_idx IS NOT NULL LOOP
      v_str := v_str || v_dlm || nt_in(v_idx);
      v_dlm := delimiter_in;
      v_idx := nt_in.NEXT(v_idx);
   END LOOP;

   RETURN v_str;

END to_string;
/

Source is: http://www.oracle-developer.net/display.php?id=306

Your sample data:

create table dummy
(
  plan_cd varchar2(6 char),
  plan_nm varchar2(6 char),
  class varchar2(2 char)
);

insert into dummy values ('D01501', 'DENTAL', 'AA');
insert into dummy values ('D01501', 'DENTAL', 'AB');
insert into dummy values ('D01501', 'DENTAL', 'AC');
insert into dummy values ('V01501', 'VISION', 'AA');
insert into dummy values ('V01501', 'VISION', 'AB');
commit;

And finally the query:

select
  plan_cd,
  plan_nm || ','
  || to_string(cast(collect(class order by class) as varchar2_ntt)) as plan_nm
from
  dummy
group by
  plan_cd, plan_nm
;

PLAN_CD PLAN_NM
------- ----------------------------------------
D01501  DENTAL,AA,AB,AC
V01501  VISION,AA,AB


Calculating percentile using counts of occurrences Example table:

Speed       Count
102         3
201         2
205         9
208         4
301         1
303         2
307         6

Count is the number a specific speed is measured.

I need to calculate the percentile for the column Speed but not on the Speed values in the table but for the values times the count of those values.

Written out the data looks like this:

102,102,102,201,201,205,205,205,205,205,205,205,205,205,208,208,208,208,301,303,303,307,307,307,307,307,307

I know how to calculate the percentile on this set of data. But is there a way to use the table data without first transforming it in one huge set of individual numbers? The sum of Count in the real data runs in the billions so transforming the data in individual values would result in a (temporary) dataset of billions of rows.

If transformation is nevertheless necessary, what is the easiest way to do this?

I'm using SQL Server 2016

I'll walk you through one solution to calculate PERCENTILE_DISC for percentiles 1, 2, ... 100. Apparently there is not an agreed upon definition for percentile. It appears that PERCENTILE_DISC mostly implements the nearest rank method, except that low percentiles are not undefined (see the last bullet point in the wiki).

  One definition of percentile, often given in texts, is that the P-th percentile of a list of N ordered values (sorted from least to greatest) is the smallest value in the list such that P percent of the data is less than or equal to that value.

Per wikipedia, the percentile P (0 < P <= 100) is the value located at ordinal rank n = CEILING (N * P / 100), where N is the number of ordered values. For your problem, N = SUM(COUNT) from the table. For a given percentile P you need to find the nth row in the table. You can find that without exploding all of the values into separate rows by calculating a running total. If you take the row with the largest speed with a running total less than or equal to n then you have the percentile value. One implementation of that is below.

First we'll need a numbers table. These are invaluable for many SQL problems. In this example, I'll only put 100 integers in my numbers table because that's all I need to solve this one, but if you create one on your database you should add more than that.

CREATE TABLE #X_NUMBERS (NUM INTEGER NOT NULL, PRIMARY KEY (NUM));

-- put 100 integers into #X_NUMBERS table
WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b)
INSERT INTO #X_NUMBERS
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e2;

Here is the sample data from the question:

CREATE TABLE #X_SPEED_TABLE (SPEED INT, CNT INT, PRIMARY KEY (SPEED));

-- this is your test data
INSERT INTO #X_SPEED_TABLE
VALUES
(102, 3),
(201, 2),
(205, 9),
(208, 4),
(301, 1),
(303, 2),
(307, 6);

To make the algorithm easier to present let's just start with percentiles 10, 20, ... 100. Suppose I take the sum of the CNT column from my table and multiply it by 0.1, 0.2, ... 1.0. I need to take the CEILING of the values (this is RANK_COL):

╔══════════╦══════════╦═══════╗
║ PERC_COL ║ RANK_COL ║ SPEED ║
╠══════════╬══════════╬═══════╣
║       10 ║        3 ║ NULL  ║
║       20 ║        6 ║ NULL  ║
║       30 ║        9 ║ NULL  ║
║       40 ║       11 ║ NULL  ║
║       50 ║       14 ║ NULL  ║
║       60 ║       17 ║ NULL  ║
║       70 ║       19 ║ NULL  ║
║       80 ║       22 ║ NULL  ║
║       90 ║       25 ║ NULL  ║
║      100 ║       27 ║ NULL  ║
╚══════════╩══════════╩═══════╝

Now I'll look at my data and calculate a running total based on speed. For your sample data:

╔══════════╦══════════╦═══════╗
║ PERC_COL ║ RANK_COL ║ SPEED ║
╠══════════╬══════════╬═══════╣
║       -1 ║        3 ║   102 ║
║       -1 ║        5 ║   201 ║
║       -1 ║       14 ║   205 ║
║       -1 ║       18 ║   208 ║
║       -1 ║       19 ║   301 ║
║       -1 ║       21 ║   303 ║
║       -1 ║       27 ║   307 ║
╚══════════╩══════════╩═══════╝

Let's combine that data and order it by RANK_COL descending and PERC_COL ascending:

╔══════════╦══════════╦═══════╗
║ PERC_COL ║ RANK_COL ║ SPEED ║
╠══════════╬══════════╬═══════╣
║       -1 ║       27 ║ 307   ║
║      100 ║       27 ║ NULL  ║
║       90 ║       25 ║ NULL  ║
║       80 ║       22 ║ NULL  ║
║       -1 ║       21 ║ 303   ║
║       -1 ║       19 ║ 301   ║
║       70 ║       19 ║ NULL  ║
║       -1 ║       18 ║ 208   ║
║       60 ║       17 ║ NULL  ║
║       -1 ║       14 ║ 205   ║
║       50 ║       14 ║ NULL  ║
║       40 ║       11 ║ NULL  ║
║       30 ║        9 ║ NULL  ║
║       20 ║        6 ║ NULL  ║
║       -1 ║        5 ║ 201   ║
║       -1 ║        3 ║ 102   ║
║       10 ║        3 ║ NULL  ║
╚══════════╩══════════╩═══════╝

Now let's find the minimum SPEED value as I loop through the rows:

╔══════════╦═══════╗
║ PERC_COL ║ SPEED ║
╠══════════╬═══════╣
║       -1 ║   307 ║
║      100 ║   307 ║
║       90 ║   307 ║
║       80 ║   307 ║
║       -1 ║   303 ║
║       -1 ║   301 ║
║       70 ║   301 ║
║       -1 ║   208 ║
║       60 ║   208 ║
║       -1 ║   205 ║
║       50 ║   205 ║
║       40 ║   205 ║
║       30 ║   205 ║
║       20 ║   205 ║
║       -1 ║   201 ║
║       -1 ║   102 ║
║       10 ║   102 ║
╚══════════╩═══════╝

Finally, filter out the rows with a -1 value for PERC_COL. You are left with the percentile calculations for percentiles 10, 20, ... 100:

╔══════════╦═══════╗
║ PERC_COL ║ SPEED ║
╠══════════╬═══════╣
║      100 ║   307 ║
║       90 ║   307 ║
║       80 ║   307 ║
║       70 ║   301 ║
║       60 ║   208 ║
║       50 ║   205 ║
║       40 ║   205 ║
║       30 ║   205 ║
║       20 ║   205 ║
║       10 ║   102 ║
╚══════════╩═══════╝

The above algorithm can be implemented in SQL using window functions. Here is one such implementation for N = 1, 2, ... 100:

SELECT
  PERC_COL
, SPEED
FROM
(
    SELECT
    PERC_COL
    , MIN(SPEED) OVER (ORDER BY RANK_COL DESC, PERC_COL ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SPEED
    FROM
    (
        SELECT
          NUM PERC_COL
        , CEILING(s.SUM_SPEED * 0.01 * NUM) RANK_COL
        , NULL SPEED
        FROM #X_NUMBERS
        CROSS JOIN (SELECT SUM(CNT) SUM_SPEED FROM #X_SPEED_TABLE) s
        WHERE NUM <= 100

        UNION ALL

        SELECT
          -1 PERC_COL
        , SUM(CNT) OVER (ORDER BY SPEED ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RANK_COL
        , SPEED
        FROM #X_SPEED_TABLE
    ) t
) t2
WHERE t2.PERC_COL <> -1;

To check my results, I'm going to calculate all of the percentiles using the built-in PERCENTILE_DISC. They match for your sample data and a few other random data sets that I created.

-- this is code to test solutions against PERCENTILE_DISC for percentiles 1, 2, ... 100
-- it's not meant to be a well performing solution
DECLARE
@perc INT,
@speed INT;
BEGIN
    DECLARE @results_table TABLE (PERC INT, SPEED INT);

    SET @perc = 1;
    WHILE @perc <= 100
    BEGIN
        INSERT INTO @results_table (PERC, SPEED)
        SELECT TOP 1 @perc, PERCENTILE_DISC(0.01 * @perc) WITHIN GROUP (ORDER BY SPEED) OVER ()
        FROM
        (
            SELECT SPEED
            FROM #X_SPEED_TABLE xst
            INNER JOIN #X_NUMBERS n ON xst.CNT >= n.NUM
        ) t;

        SET @perc = @perc + 1;
    END;

    SELECT * FROM @results_table;
END;


Where do I find the tables that a role has access to in Vertica? I'm trying to use the System Tables to fetch some metadata about the whole Vertica cluster at a table level (including permissions).

Where can I find the information about permissions required to SELECT on a table?

I've poked around in the GRANTS and ROLES system tables, but I can't find any role->table mapping.

I considered using HAS_TABLE_PRIVILEGE() but that does it on user level. I'd rather do it at the role level.

SELECT * FROM grants ORDER BY grantor, grantee;

The privileges_description column shows the privilege types, such as (in your case) SELECT.  (If you only want the SELECTs, you can refine that query.)

Source: Viewing Privileges Granted on Objects in the Admin Guide.  (That's from the documentation for version 7.1.x.)


Benefit of having time dimension in a star schema? What would be the benefits of having a time dimension in a star schema over having the time attributes in the fact table itself?

For example:

I have a transaction data with user information for each transaction, country where the transaction took place and dates of when it occured.

Option 1
Correct me if I am wrong, but this is probably the widely used approached and most recommended by many:

A transaction fact table containing transaction_ID (PK), user_id (FK) and country_id (FK), and date_id (FK)
User dimension containing user_id (PK) and the other user attributes, let's say name & phone_number.
Dates dimension that consists of date_id (PK), date, day, month, year, quarter.

Option 2
Something that I just thought about instead of choosing Option 1, but unsure about:

A transaction fact table containing transaction_ID (PK), user_id
(FK) and country_id (FK), date, day, month, year,
quarter.
User dimension containing user_id (PK) and the other user attributes, let's say name & phone_number.

What would be the benefits of having Option 1 over Option 2? I am not aware of the reasons why joining with another Date dimension would be a better option even though it is most widely used approach. Thanks a lot!

Let me answer this question with a scenario starting with a simple Transaction table.  When our business started, management wanted to know the 'name' of the month, so I've included that information in the table.

DECLARE @Transactions TABLE (
    TransactionId INT
    ,UserId VARCHAR(10)
    ,CountryId INT
    ,TransactionDate DATE
    ,[MonthName] VARCHAR(20)
    ,SalesAmount DECIMAL(18, 2)
    )

Business has been good and we already have 1 million rows in our Transactions table.  In fact, business is so good that management is now asking more in depth questions about our sales.  They wanted to know what 'quarter' the sale was made.

ALTER TABLE Transactions ADD [QuarterName] VARCHAR(10)
UPDATE Transactions SET QuarterName = ...

We just updated 1 million rows.

As time goes by, management starts asking more and more questions about our sales.

  What DayOfTheWeek was that sale made?
  Was that a holiday?
  Was the moon full on that day?

ALTER TABLE Transaction ADD ...

UPDATE TABLE SET ...

Hopefully you can see where this is going.  Additionally, all of that redundant data on each and every Transaction row can contribute to reduced performance and increase resource utilization (memory, disk space, etc.).  Our databases are bigger and take longer to back up.  All of the redundant data takes up memory.

With a Date Dimension table, all of that information is stored in one place.  A Date Dimension table with dates from 2000-01-01 to 2100-01-01 contains just 36525 rows.  Anytime we want to track a new attribute of a date, we only have to alter that table by adding the additional attribute and update 36525 rows.

When we want specific information about the 'Date' attributes of a sale, we simply join up against the Date Dimension table

Additionally, the data in a Date Dimension is consistent.  January is spelled correctly, Saturday is spelled correctly, etc.  Storing this kind of data in the Transaction table can lead to all kinds of discrepancies with incorrect spellings, etc.

For more information on the creation of a Date Dimension table, check out
Creating a date dimension or calendar table in SQL Server


Convert SQL Server query to MS Access syntax I have this query in SQL Server:

SELECT Dated, AccountID, AccountName, Rate,
  case (when (Credit-Debit) > 0 then Credit-Debit END) as Credit,
  case (when (Credit-Debit) < 0 then Credit-Debit END) as Debit,
  Balance
FROM TempTrallBalance

How can I convert it to Access syntax?

The only issue here is that MS Access has no idea what a CASE expression is. Instead, you need to use IIF, e.g.:

IIF(Credit-Debit > 0, Credit-Debit, NULL) AS Credit,
IIF(Credit-Debit < 0, Credit-Debit, NULL) AS Debit,

BTW IIF is now supported in SQL Server 2012.


How to know SQL Server installation language while it is down? SQL Server is down after a security update.

I need to install a cumulative service pack which is language discriminated.

I'm downloading Spanish and English versions, I'm sure it will be either one, but it could be better to determine which it is while the server is down.

You can check the system registry to determine the installed language for SQL Server.  To get the name of the SQL Server Instance Key, first look at this registry key:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL

That key shows a list of installed instances; the Data column shows the registry key name to use when looking for the Language key in this key:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<name-from-previous-step>\MSSQLServer\CurrentVersion\Language

This Microsoft site lists the various language IDs and the corresponding language name.

I wrote a quick blog post showing this process in greater detail at SQLServerScience.com.


Select data divided in groups evenly distributed by value I would like to select into 4 groups the data from a table having the sum of a values in the groups as evenly distributed possible. I am sure that I am not explaining it clear enough so I will try to give an example.

Here I use NTILE(4) to create the 4 groups:

SELECT Time, NTILE(4) OVER (ORDER BY Time DESC) AS N FROM TableX

Time -  N
-------------
10  -   1
 9  -   2
 8  -   3
 7  -   4
 6  -   1
 5  -   2
 4  -   3
 3  -   4
 2  -   1
 1  -   2

In the above query and result, the other columns have been omitted for brevity.

So you can see the groups also as follows:

  1    2    3    4
---  ---  ---  ---
 10    9    8    7
  6    5    4    3
  2    1
---  ---  ---  ---
 18   15   12   10  Sum Totals of Time

Notice that the Sum Totals of Time using NTile is not really balanced between the groups. A better distribution of the Time values would be for example:

  1    2    3    4
---  ---  ---  ---
 10    9    8    7
  3    5    4    6
  1         2
---  ---  ---  ---
 14   14   14   13  Sum Totals of Time

Here the Sum Totals of Time is more evenly distributed over the 4 groups.

How can I perform this through a TSQL statements?

Furthermore I have to say that I am using SQL Server 2012.
If you have something that can help me out, let me know.

I wish you a nice day.

Stan

Here's a stab at an algorithm. It's not perfect, and depending on how much time you want to spend refining it, there are probably some further small gains to be made.

Let's assume you have a table of tasks to be performed by four queues. You know the amount of work associated with performing each task, and you want all four queues to get an almost equal amount of work to do, so all queues will complete at about the same time.

First off, I'd partition the tasks using a modulous, ordered by their size, from small to large.

SELECT [time], ROW_NUMBER() OVER (ORDER BY [time])%4 AS grp, 0

The ROW_NUMBER() orders every row by size, then assigns a row number, starting at 1. This row number is assigned a "group" (the grp column) on a round-robin basis. First row is group 1, second row is group 2, then 3, the fourth gets group 0, and so on.

time ROW_NUMBER() grp
---- ------------ ---
   1            1   1
  10            2   2
  12            3   3
  15            4   0
  19            5   1
  22            6   2
...

For ease of use, I'm storing the time and grp columns in a table variable called @work.

Now, we can perform a few calculations on this data:

WITH cte AS (
    SELECT *, SUM([time]) OVER (PARTITION BY grp)
             -SUM([time]) OVER (PARTITION BY (SELECT NULL))/4 AS _grpoffset
    FROM @work)
...

The column _grpoffset is how much the total time per grp differs from the "ideal" average. If the total time of the all tasks is 1000 and there are four groups, there should ideally be a total of 250 in each group. If a group contains a total of 268, that group's _grpoffset=18.

The idea is to identify the two best rows, one in a "positive" group (with too much work) and one in a "negative" group (with too little work). If we can swap groups on those two rows, we could reduce the absolute _grpoffset of both groups.

Example:

time grp total _grpoffset
---- --- ----- ----------
   3   1   222         40
  46   1   222         40
  73   1   222         40
 100   1   222         40
   6   2   134        -48
  52   2   134        -48
  76   2   134        -48
  11   3   163        -21
  66   3   163        -21
  86   3   163        -21
  45   0   208         24
  71   0   208         24
  92   0   208         24
----
=727

With a grand total of 727, each group should have a score of about 182 for the distribution to be perfect. The difference between the group's score and 182 is what we're putting in the _grpoffset column.

As you can see now, in the best of worlds, we should move about 40 points worth of rows from group 1 to group 2 and about 24 points from group 3 to group 0.

Here's the code to identify those candidate rows:

    SELECT TOP 1 pos._row AS _pos_row, pos.grp AS _pos_grp,
                 neg._row AS _neg_row, neg.grp AS _neg_grp
    FROM cte AS pos
    INNER JOIN cte AS neg ON
        pos._grpoffset>0 AND
        neg._grpoffset<0 AND
        --- To prevent infinite recursion:
        pos.moved<4 AND
        neg.moved<4
    WHERE --- must improve positive side's offset:
          ABS(pos._grpoffset-pos.[time]+neg.[time])<=pos._grpoffset AND
          --- must improve negative side's offset:
          ABS(neg._grpoffset-neg.[time]+pos.[time])<=ABS(neg._grpoffset)
    --- Largest changes first:
    ORDER BY ABS(pos.[time]-neg.[time]) DESC
    ) AS x ON w._row IN (x._pos_row, x._neg_row);

I'm self-joining the common table expression that we created before, cte: On one side, groups with a positive _grpoffset, on the other side groups with negative ones. To further filter out which rows are supposed to match each other, the swap of the positive and negative sides' rows must improve _grpoffset, i.e. get it closer to 0.

The TOP 1 and ORDER BY selects the "best" match to swap first.

Now, all we need to to is add an UPDATE, and loop it until there's no more optimization to be found.

TL;DR - here's the query

Here's the complete code:

DECLARE @work TABLE (
    _row    int IDENTITY(1, 1) NOT NULL,
    [time]  int NOT NULL,
    grp     int NOT NULL,
    moved   tinyint NOT NULL,
    PRIMARY KEY CLUSTERED ([time], _row)
);

WITH cte AS (
    SELECT 0 AS n, CAST(1+100*RAND(CHECKSUM(NEWID())) AS int) AS [time]
    UNION ALL
    SELECT n+1,    CAST(1+100*RAND(CHECKSUM(NEWID())) AS int) AS [time]
    FROM cte WHERE n<100)

INSERT INTO @work ([time], grp, moved)
SELECT [time], ROW_NUMBER() OVER (ORDER BY [time])%4 AS grp, 0
FROM cte;

WHILE (@@ROWCOUNT!=0)
    WITH cte AS (
        SELECT *, SUM([time]) OVER (PARTITION BY grp)
                 -SUM([time]) OVER (PARTITION BY (SELECT NULL))/4 AS _grpoffset
        FROM @work)

    UPDATE w
    SET w.grp=(CASE w._row
               WHEN x._pos_row THEN x._neg_grp
               ELSE x._pos_grp END),
        w.moved=w.moved+1
    FROM @work AS w
    INNER JOIN (
        SELECT TOP 1 pos._row AS _pos_row, pos.grp AS _pos_grp,
                     neg._row AS _neg_row, neg.grp AS _neg_grp
        FROM cte AS pos
        INNER JOIN cte AS neg ON
            pos._grpoffset>0 AND
            neg._grpoffset<0 AND
            --- To prevent infinite recursion:
            pos.moved<4 AND
            neg.moved<4
        WHERE --- must improve positive side's offset:
              ABS(pos._grpoffset-pos.[time]+neg.[time])<=pos._grpoffset AND
              --- must improve negative side's offset:
              ABS(neg._grpoffset-neg.[time]+pos.[time])<=ABS(neg._grpoffset)
        --- Largest changes first:
        ORDER BY ABS(pos.[time]-neg.[time]) DESC
        ) AS x ON w._row IN (x._pos_row, x._neg_row);


Error During IMPDP in oracle database 12c I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7

EXPDP Command:

expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log COMPRESSION=ALL FULL=Y

Now when I run IMPDP command in oracle database 12c with operating system Oracle Linux 7.7 then I am getting errors. I have CDB and PDB database in 12c

IMPDP Command:

impdp c##app/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log FULL=Y

Error:

ORA-39083: Object type TABLE:"HMIS"."TEMP_WE_TEST" failed to create with error:
ORA-01918: user 'HMIS' does not exist

ORA-39083: Object type TABLE:"APEX_030200"."WWV_FLOW_WORKSHEET_STICK" failed to create with error:
ORA-01918: user 'APEX_030200' does not exist

ORA-39083: Object type TABLE:"SYSMAN"."MGMT_GENSVC_AVAIL_BEACONS" failed to create with error:
ORA-01918: user 'SYSMAN' does not exist

How to solve this problem
Thanks

The error message is self-explanatory:

ORA-01918: user 'XXXXXX' does not exist

Look at the log output for your expdp execution. If it does not contain the following:

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

... then the users & grants were not exported in the first place, and so were not created when importing.

FULL=Y does not solve the problem when the users were not exported in the first  place.

Check that the exporting user, hmis has the CREATE USER grant, then re-export the data.


changing server roles in streaming replication I want to implement streaming replication using postgresql-9.2.4 in my reporting application.  I came stuck on the below scenario.

create master and slave with synchronous replication which is working fine.
then I brought down master and brought up slave as master using trigger file on slave side manually.
fired log in new master which is working fine.

Now my question is that how can I bring up the old master as slave so that it points to new master without taking base backup or using rsync?

My reporting application will contain large data (approx 50 TB) and more. If I take base backup or rsync feature it will surely take more time to sync old master to new master .

Is there any other way or third party tool which help me to avoid base backup?

Unforutnatley, you cannot do this without taking a new base backup. This is something being worked on, but at this point repointing the slave is not supported.

There exists a tool called pg_rewind, but there are definite cornercases where that one does not work properly, so it's quite a risky way.

For now, doing a basebackup/rsync is the supported method for this.


Unable to login to SQL Server using SSMS and SQL agent failing I have SQL Server 2008 R2 ENT insalled on Windows server 2008 R2 ENT. I am unable to login to SQL Server locally after the installation. Getting below error:

  Connection handshake failed. An OS call failed: (80090331)
  0x80090331(The client and server cannot communicate, because they do
  not possess a common algorithm.). State 56.

The error led me to this article and I updated my DB engine to 10.50.6542.0. But still unable to login. What should I do in order to allow my SSMS to connect locally. Also the second issue is SQL agent service is not starting. It fails with below error:

  Agent could not be started ( unable to connect to server (local),
  SQLserveragent cannot start).

How to fix this as well? Below is my environment details:

Windows server 2008 R2 Ent. SP1
SQL Server 2008 R2 Ent (10.50.6542.0)
.Net version 4.6.01055

SCHANNEL Status:

  SSL 2.0:  Client: DisabledByDefault = 1 Enabled = 0  Server:
  DisabledByDefault = 1 Enabled = 0

  SSL 3.0:  Client: DisabledByDefault = 1 Enabled = 0  Server:
  DisabledByDefault = 1 Enabled = 0

  TLS 1.0:  Client: DisabledByDefault = 0 Enabled = 1  Server:
  DisabledByDefault = 0 Enabled = 1

  TLS 1.1:  Client: DisabledByDefault = 0 Enabled = 1  Server:
  DisabledByDefault = 0 Enabled = 1

  TLS 1.2:  Client: DisabledByDefault = 0 Enabled = 1  Server:
  DisabledByDefault = 0 Enabled = 1

Please let me know if you need any further details.

Update:

I made some progress on it. I installed ADO.NET - SqlClient (.NET Framework 3.5/.NET Framework 2.0 SP2) referring link https://support.microsoft.com/en-us/kb/3135244. I am now able to connect SQL server using SSMS locally and also able to start SQL agent service. Yipee.

But now the new issue is appearing. I can't connect the server remotely using windows authentication. I get below errors in windows eventviewer logs when I try to connect it. Also, no errors in SQL server error logs.

  Log Name:         System Source:         Schannel Event ID:      36888
  Task Category: None  Level:          Error Keywords:        User:
  SYSTEM Description: The following fatal alert was generated: 40. The
  internal error state is 1205.   Log Name:       System Source:
  Schannel Event ID:      36874 Task Category: None  Level:
  Error Keywords:        User:         SYSTEM Description: An TLS 1.0
  connection request was received from a remote client application, but
  none of the cipher suites supported by the client application are
  supported by the server. The SSL connection request has failed.

Fix for my this question was to apply

.NET framework 4.6.1
ADO.NET - SqlClient (.NET Framework 3.5/.NET
Framework 2.0 SP2) from this link
https://support.microsoft.com/en-us/kb/3135244.

These fixed the local login and SQL agent service issues. Then the new issue surfaced i.e. unable to SSMS this server from other servers.

To fix this I had to enable ‘KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA’ in registry. After enabling it remote connection started working fine.


How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY I wrote the following SQL query to group results and then sort results in each group by date:

SELECT
origin,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
            ORDER BY departure_date
            SEPARATOR ','),
        ',',
        10)
FROM
    `flights`
WHERE
    origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
        AND cabin_type = 'ECONOMY'
GROUP BY origin , provider;

Second query:

SELECT
destination,
provider,
SUBSTRING_INDEX(GROUP_CONCAT(price
            ORDER BY departure_date
            SEPARATOR ','),
        ',',
        10)
FROM
    `flights`
WHERE
    origin = 'LHR' AND destination IN ('FCO', 'CDG', 'AMS')
        AND cabin_type = 'ECONOMY'
GROUP BY destination, provider;

InnoDB table flights is quite simple:

+----------------+---------------+------+-----+-----------+----------------+
| Field          | Type          | Null | Key | Default   | Extra          |
+----------------+---------------+------+-----+-----------+----------------+
| id             | int(11)       | NO   | PRI | NULL      | auto_increment |
| origin         | varchar(255)  | NO   | MUL | NULL      |                |
| destination    | varchar(255)  | NO   | MUL | NULL      |                |
| departure_date | date          | NO   | MUL | NULL      |                |
| price          | decimal(14,2) | NO   |     | NULL      |                |
| provider       | varchar(255)  | NO   |     | airnet    |                |
| cabin_type     | varchar(255)  | YES  |     | ECONOMY   |                |
| created_at     | datetime      | YES  |     | NULL      |                |
| updated_at     | datetime      | YES  |     | NULL      |                |
+----------------+---------------+------+-----+-----------+----------------+

I have indexes on columns: origin, destination, departure_date. And approx. 10 million records in the table.

Above-mentioned query takes about 200 to 600 milliseconds. I tried to use profiler and found out that most of the time is spent on Creating sort index (approx. 98% of whole query time).

Is there any way to optimize this query. I tried compund index on origin, provider, departure_date, but that did not seem to help. Maybe configuration should be tweaked more and if so which configuration variables must be considered?

Machine has 4 GB of RAM, 2 vCPUs and dedicated for DB only (10.3.13-Maria DB).

Test

WITH cte AS ( SELECT origin,
                     provider,
                     price,
                     departure_date,
                     ROW_NUMBER() OVER (PARTITION BY /* origin, */ provider
                                        ORDER BY departure_date) rn
              FROM flights
              WHERE origin = 'LHR'
                AND destination IN ('FCO', 'CDG', 'AMS')
                AND cabin_type = 'ECONOMY' )
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte
WHERE rn <= 10
GROUP BY origin,
         provider;

UPDATE: (draft code, see comments)

WITH
cte1 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn
          FROM flights
          WHERE origin = 'LHR'
            AND destination ='FCO'
            AND cabin_type = 'ECONOMY' ),
cte2 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte1
          WHERE rn <= 10),
cte3 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn
          FROM flights
          WHERE origin = 'LHR'
            AND destination = 'CDG'
            AND cabin_type = 'ECONOMY' ),
cte4 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte3
          WHERE rn <= 10),
cte5 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn
          FROM flights
          WHERE origin = 'LHR'
            AND destination = 'AMS'
            AND cabin_type = 'ECONOMY' ),
cte6 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte5
          WHERE rn <= 10),
cte7 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte2
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte4
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte6 )
cte8 as ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                        ORDER BY departure_date) rn
           FROM cte7)
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte8
WHERE rn <= 10
GROUP BY origin,
         provider;


Why is restoration from binary logs so slow? We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump.  In testing database restoration from the dump & binlogs, we found that the latter are an order of magnitude slower.

Specifically:

Restoring from a 92.8 MB database dump with mysql dbname < dbdump.sql took 3m 26.526s.
Restoring all of the binary logs from after the dump with mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname took 38m 10.090s.  The total size of the binlog files was 56.8 MB, and the size of the output from mysqlbinlog was 66.4 MB.
Combining the above two steps by just restoring from a (93.2 MB) dump taken after the events the binlogs cover took 2m 15.406s.

Is this time disparity normal?  Can anything be done to reduce it?

You must understand what the binary logs are: they store all the queries (in STATEMENT format) or row changes (in ROW format) that happened since they were recorded. So, your command:

mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname

basically executes all insert, updates & deletes that occurred to the server during those almost 30 binlogs, serially. That is why recovering from the binary logs is only useful for point in time recovery (recovering the latest changes since the last backup).

There are some things that you could do to reduce the application time, like reducing temporarily the durability and consistency constraints of the database (as you can always replay the steps) -innodb_flush_log_at_trx_commit, disable the binary log, the double write, etc.- or try using ROW binary logs only, which may increase the size on disk but make them faster to apply.

Alternatively, you could reduce the number of binlogs that you have to apply by creating more frequent full backups (the size of your full backups is not precisely big), creating real incremental or differential backups with tools like xtrabackup- or using lagged slaves for disaster recovery. This is because both mysqldump and mysqlbinlog recover data in a logical way- physical backups may be faster in some cases, specially for full recovery.

Obviouslly, there could be specific issues as well, for example, I have sometimes found that replaying LOAD DATA statements is slower than I would have thought.


How to see last actions executed (and time) of a specific database in MongoDB? There are multiple databases but some of them look stale. I need to understand if the database is stale or not.

How can I get the last actions executed of a specific database in MongoDB? And can I get the date and time information of the last actions?

As per MongoDB documentation here through getLog is an administrative command that returns the most recent 1024 logged mongod events.

The getLog does not read log data from the mongod log file. It instead reads data from a RAM cache of logged mongod events. To run getLog, use the db.runCommand( { command } ) method on the admin database.

The getLog command has the following syntax:

{ getLog: <value> }

The possible values for getLog are:

global - returns the combined output of all recent log entries.
rs - if the mongod is part of a replica set, getLog returns recent
notices related to replica set activity.
startupWarnings - returns logs that may contain errors or warnings
from MongoDB’s log from when the current process started. If mongod
started without warnings, this filter may return an empty array.

Retrieve Recent Events from Log

The following operation retrieves the most recent global events for the mongod

db.adminCommand( { getLog : "global" } )

For your further ref here


Moving primary key constraint from one index to another I have a SQL Server database, and have run sp_blitz against it. This pulled out that there are a couple of heaps on moderate size tables (a few hundred thousand rows in one case).

Most of these tables have a primary key (one does not, but I'll deal with that later). The primary key itself has an unusual name - i.e. not the default one, when most of the tables have a default primary key name (PK_tablename).

There is an index with a name matching the primary key, which is unique and non-clustered.

I can rename the primary key, but I then think I should be creating a clustered index. If I do this, then I'll have duplicate indexes, so it would make sense to remove the non-clustered index. However, it's being used for the primary key.

If I move the primary key constraint from the old non-clustered index to the new clustered index:

Does this make sense?
Are there any things I should be aware of?
What is the best method to do this?

The following script illustrates an efficient way to convert the existing nonclustered primary key to clustered, and to rename it:

-- How the table looks now
CREATE TABLE dbo.Example
(
    pk integer NOT NULL,
    some_data integer NOT NULL,

    CONSTRAINT PK_UnusualName
        PRIMARY KEY NONCLUSTERED (pk)
);

-- Some data
INSERT dbo.Example (pk, some_data)
VALUES (1, 100), (2, 200), (3, 300);

-- Change the nonclustered PK to clustered
CREATE UNIQUE CLUSTERED INDEX PK_UnusualName
ON dbo.Example (pk)
WITH (DROP_EXISTING = ON);

-- Rename
EXECUTE sys.sp_rename
    @objname = N'dbo.Example.PK_UnusualName',
    @newname = N'PK__dbo_Example_pk',
    @objtype = 'INDEX';

-- Tidy up
DROP TABLE dbo.Example;


Loading External Table from dumpfile I have created a dumpfile using EXPDP process.

I am trying to create an external table using the above said dumpfile.

I am using the following statement to create the external table:

CREATE TABLE HR.DATA_LOAD
(
"EMP_NO" NUMBER(10,0) NOT NULL ENABLE,
    "EMP_NAME" VARCHAR2(20 BYTE),
    "DEPT_ID" NUMBER(10,0),
    "DEPT_NM" VARCHAR2(20 BYTE),
    "DOJ" VARCHAR2(20 BYTE),
    "LOCATION" VARCHAR2(20 BYTE),
    "PHN_NUM" NUMBER(20,0),
    "EMAIL" VARCHAR2(20 BYTE),
    "COL1" VARCHAR2(20 BYTE),
    "COL2" VARCHAR2(20 BYTE)
)
ORGANIZATIONAL EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY DATA_PUMP_DIR
  LOCATION ('exp_dumpfile.dmp')
);

After execution of the above statement the table is created but when I issue SELECT statement to see data I get the following error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39324: cannot load data from Data Pump dump file

Am I doing something wrong or is it the case that I can use a dumpfile generated by Datapump method to create an external table?

If you look up the description of error ORA-39324 in the Oracle documentation you will see:

Cause: A dump file was specified for a load operation that had been created using the Data Pump. This type of dump file cannot be processed by external tables using the ORACLE_DATAPUMP access driver.
Action: Access the data in this dump file using the Data Pump.

A dump created by EXPDP can only be read by IMPDP (or dbms_datapump).  Whereas a dump created using writable external tables (using datapump driver) can be re-read only using external table interface (using datapump driver). Expdp does more than dump data (which is the only thing done by writable external tables using datapump driver).

Community Wiki answer originally based on comments by BriteSponge and Raj

Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


Number of ISA relationship triangles in an Entity-Relationship Diagram The following two diagrams are nearly identical. However, in one, there are two ISA triangles from the Entity "Movie" and in the other there is just one.

What is the difference in meaning?

ISA in First diagram has no point. ISA is used as "either".

Movie CAN BE a Cartoon or Natural or movie MUST BE a cartoon or Natural.

There is no point to append an ISA while there is no either condition