sql indexing and performance I am using mysql5.1, i have table which has about 15 lakh (1.5 million) records.This table has records for different entities i.e child records for all master entities.

There are 8 columns in this table , out of which 6 columns are clubbed to make a primary key. These columns could be individual foreign keys but due to performance we have made this change.

Below is the show create table output

 CREATE TABLE `hybrid_exp_trait_dtl` (
  `HYBRID_NUMBER` varchar(100) NOT NULL,
  `TRIAL_STATUS` char(4) NOT NULL,
  `LOCATION_CODE` char(5) NOT NULL,
  `EXPERIMENT_ID` char(20) NOT NULL,
  `REPLICATION_NUMBER` int(3) unsigned NOT NULL,
  `TRAIT_NAME` varchar(30) NOT NULL,
  `TRAIT_VALUE` varchar(50) DEFAULT NULL,
  `MACHINE_NO` char(2) NOT NULL,
  `USER_ID` char(8) NOT NULL,
  `MYTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `FK_HYBRID_EXP_TRAIT_DTL_2` (`LOCATION_CODE`),
  KEY `FK_hybrid_exp_trait_dtl_3` (`USER_ID`),
  KEY `Index_4` (`HYBRID_NUMBER`, `EXPERIMENT_ID`, `TRIAL_STATUS`, `LOCATION_CODE`, `REPLICATION_NUMBER`, `TRAIT_NAME`, `USER_ID`) USING BTREE,
  CONSTRAINT `FK_HYBRID_EXP_TRAIT_DTL_2` FOREIGN KEY (`LOCATION_CODE`) REFERENCES `location_mst` (`LOCATION_CODE`),
  CONSTRAINT `FK_hybrid_exp_trait_dtl_3` FOREIGN KEY (`USER_ID`) REFERENCES `user_data` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

As you  can see I have four indexes

KEY FK_HYBRID_EXP_TRAIT_DTL_2 (LOCATION_CODE)
KEY FK_hybrid_exp_trait_dtl_3 (USER_ID)
KEY Index_4 (HYBRID_NUMBER, EXPERIMENT_ID, TRIAL_STATUS, LOCATION_CODE, REPLICATION_NUMBER, TRAIT_NAME, USER_ID) USING BTREE

Below are the two queries which take long time 4.26 and 0.64 respectively.

select  distinct
        location_code,
        Max(replication_number) as replication
from hybrid_exp_trait_dtl  t
where t.experiment_id='NSKOK12K1102' and t.trial_status='MLT';

And this one

SELECT *
FROM  hybrid_exp_trait_dtl h
where h.experiment_id='NSKOK12K1102'
  and h.TRAIT_NAME='YLD' ;

In order to improve performance i modified 3rd index and added one more index , so below are the indexes for my tables

KEY FK_HYBRID_EXP_TRAIT_DTL_2 (LOCATION_CODE)
KEY FK_hybrid_exp_trait_dtl_3 (USER_ID)
KEY Index_4 (EXPERIMENT_ID,TRIAL_STATUS,LOCATION_CODE,REPLICATION_NUMBER)
KEY hbrid (HYBRID_NUMBER,EXPERIMENT_ID,TRAIT_NAME)

Now performance has increased significantly for both the queries and now performance time is .45 and .3 sec respectively.

Is this indexing correct , will it have effect on my update and insert statement as i will be doing bulk update.

I took the liberty of editing your SQL so the clauses are on separate lines. I violated CodeReview group discipline by doing that. Sorry!  My suggestion to you is to format your queries so the various clauses are clearly visible, as I did. This kind of formatting, in my experience, makes it easier to understand the logic of queries and to spot errors.

You've done a good job declaring your columns NOT NULL where possible. That helps performance a lot.

Your first query seems to be illogical, as it combines an aggregate function with a DISTINCT qualifier. MySQL allows a lot of sloppiness in aggregate queries unfortunately.

It looks like you're looking for a resultset with a row for each location that shows the the largest replication_number at that location. Do you want this query instead?

select  location_code,
        Max(replication_number) as replication
   from hybrid_exp_trait_dtl  t
  where t.experiment_id='NSKOK12K1102' and t.trial_status='MLT'
  group by location_code

Your Index_4 will allow this query to be satisfied using a so-called loose index scan, which is really very fast.

Your second query will most likely be a lot faster if you change your fourth compound key (the one you've named hbrid) to have the following order.

(EXPERIMENT_ID,TRAIT_NAME,HYBRID_NUMBER)

That is because your query looks for exact values of EXPERIMENT_ID and TRAIT_NAME. When those two columns come first in the index, the query can jump to the exact position in the index and then sequentially reel off your results.

Now, you may have some other query that needs to have HYBRID_NUMBER first, but you didn't mention it in your question.

You asked what effect these indexes will have on load performance. It's hard to answer that question exactly without knowing a lot about your load process.

You don't have any indexes that call for uniqueness. That is very good for loading performance.

But, there are a few things you can do to speed up loading. They are detailed here. The most important speed trick is probably to avoid autocommit.  Start your import process with this SQL command.

 SET autocommit=0;

Then do a few hundred INSERT or UPDATE statements in a row, then do

 COMMIT;

Don't try to do too many INSERT or UPDATE statements before COMMIT or you'll use up a lot of RAM with transaction buffers.

You can also turn of foreign key checks while loading. See the web page mentioned above. But be careful. If your loaded data violates foreign key constraints, when you try to turn checking back on you'll have a mess.

When you're done with a load operation that changes a large fraction of the rows, do

 OPTIMIZE LOCAL TABLE hybrid_exp_trait_dtl;

Your table will be unavailable while this OPTIMIZE operation runs: it rebuilds the table and indexes.


Execute a script with SQLPlus containing html entity mappings When executing a script with SQL PLus, I was prompted to enter value for lt, gt, and apos.

Here are some code examples in this script.

v_qry := replace(v_qry, '&lt;', '<');
v_qry := replace(v_qry, '&gt;', '>');
v_qry := replace(v_qry, '&apos;', '''') ;

How to execute this script without being prompted to enter values?

Thank you
Frank

set define off

More details can be found in the manual:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2698854


Most efficient way to aggregate data to new table Say I have a large table A around 151 million rows:

TableA
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[MetaDataProp1] [int] NULL,
[MetaDataProp2] [int] NULL,
[MetaDataProp3] [int] NULL,
[MetaDataProp4] [int] NULL,
[MetaDataProp5] [int] NULL,
[MetaDataProp6] [int] NULL,
[MetaDataProp7] [int] NULL,
[MetaDataProp8] [int] NULL,
[MetaDataProp9] [int] NULL,
[Date] [datetime] NOT NULL,
[Cost] [decimal](10,20) NOT NULL

I need to aggregate my large table in 'n' ways, depending on what is needed by our end users.

For instance group by Prop1-3, Day and SUM(Cost) for date range X and prop5 IN (X,Y,Z).

Now I also have to sometimes join to other tables for some data aggregations on top of that. Creating 20+ indexes on each view seems like a bad idea, and would create massive write overhead. So this makes indexed views out of the question.
That leaves me with one option as far as I know: to push the query results to a new table on my end. But this has some drawbacks:

A select insert is pretty intensive as it blocks other queries while it does it's work. This would be a disaster at just 20 aggregate results.

My idea is this:

Fetch the Max Id.
Do the select from table A in paginated batches off 100k using offset-fetch where the Id < the fetched max Id. This ensures we won't be endlessly re-fetching data and new data is pushed and it also ensures that data won't suddenly fluctuate as it selects.
Push aggregate batch to aggregate table.
Keep going until nothing is found in the paginated select.

Advantage to this:

Since it's now batched, it allows other queries to flow through in between the batch select and batch insert.

Disadvantages to this:

More read I/O as each paginated fetch must order the grouped results.
Longer overall aggregation time.

Another approach could be to fetch the whole data set from a machine, hold it all in memory, then bulk save in batches from the machine. This would reduce read I/O, but that seems like a bad idea as the query being returned can be 40GB+ in size. The cost of running a machine like that is not worth it just for aggregated data.

What do you think of this solution? What's the recommended solution for type of problem?

The data is updated about 3 times a day. In which we tear out old data and push in the new data. About a few million deletes and re-inserts each go.

I've heard whispers of ColumnStore in SQL Server and I am in a position where I can use it. But never read much on it.

This is what SSAS was made for, and there are a whole host of client tools that can connect to it including Excel.

Elaborating

SSAS - SQL Server analysis services is a free component of SQL server, which will build a multidimensional version of your data(cube), that will pre-build all the aggregations you require/define.

The design tool is free to download called SQL Server Data Tools.

Once you set up the data source and data view based on the table you have the easiest way to go is to create a Dimension and Fact based on your table the dimension will have all the Metadata descriptors and the fact will just have the numerics/measures.

You then join the two in the Cube, once published/deployed both SSRS and other tools can connect to it.

Some links

https://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv

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


Replication error: the principal "dbo" does not exist I have replication set up between two servers.  This morning I came in and it was failing.  I was getting these errors:

  Error messages:

  The process could not execute 'sp_replcmds' on 'PSQL3'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help:
  Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated,
  or you do not have permission. (Source: MSSQLServer, Error number:
  15517)
  The process could not execute 'sp_replcmds' on 'PSQL3'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

It turns out that someone had deleted an account on the publishing database that had been the database owner without fixing the database ownership before doing so.  This orphaned user, since it didn't exist, caused replication to break.  I assume it's because it was the database owner.


I do not understand SQL Server Transaction Logs I understand that a Microsoft SQL Server Database has two components:

A database file
A transaction log file

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? Or does it just go to the transaction log and then convert to the database file after a while?

Furthermore: when I do a backup of the transaction log file, does it clear ALL logs? Or does it just remove some?

Whenever I make a change to a table, does it get written to both the
  database file AND the transaction log file?

It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some point, though before the data file is written to disk to keep with the write ahead logging protocol.

  Furthermore: When I do a backup of the transaction log file, does it
  clear ALL logs? Or does it just remove some?

A log backups allows VLFs of the log to me marked for reuse if nothing is actively depending upon them. Nothing is physically cleared, just marked to allowed to be reused.


Reducing database size to restore to SQL Server Express I have a SQL Server 2008 R2 database, about 15 GB.

I want to copy it for a partner, who is using SQL Server 2008 R2 Express.

I deleted many tables and rows, and now I am sure that the data is smaller than 2 GB.

I make a backup, send to the partner, he tries to restore it but he receives an error:

  CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database

What did I do wrong?

The sum of the file size for all data files has to be < 10 GB, not the amount of data in the file. So, deleting data from some tables, or even dropping some tables, does not solve problem. You need to shrink the file, something like this:

ALTER DATABASE mydb MODIFY FILE (name = N'logical_name', size = 2048MB);

This will fail if the database size can't be reduced to 2 GB. You may need to first issue:

DBCC SHRINKFILE(logical_name, 2048);

If you use any form of SHRINKFILE, then you'll need to validate in File Explorer that the data file(s) are actually as small as you think (because shrink operations will shrink as much as they can, and stop silently when they can't reach your target size).

Then take a backup, then restore on SQL Server Express (with @@VERSION the same or higher than the source, of course).

You may come across other issues, for example if you have used any features that aren't supported on Express.


Pentaho Kettle - Unable to connect to virtual Oracle Database I'm attempting to connect to a virtualized Oracle database but I am receiving the following error:

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
Listener refused the connection with the following error:

  ORA-12505, TNS:listener does not currently know of SID given in
  connect descriptor The Connection descriptor used by the client was:
  foohost:1521:foodba

Since SIDs are not usable with virtualized databases I need to provide a fully qualified URL (from what I have heard), however I'm not certain how to configure Pentaho to accomplish this.

So the trick was to go into the Options tab in the Edit Connection dialogue and create a database parameter to define the connection string.


Understanding statistics, execution plans, and 'ascending key problem' I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution.

Am I correct in saying that statistics are only used when creating the execution plan for a stored procedure, and they are not used in the actual execution context? In other words, if this is true, once the plan is created (and assuming it's properly reused), how important are "up to date" statistics?

I was particularly motivated by an article I read (Statistics, row estimations and the ascending date column) which describes a scenario very similar to one I face daily with several of our client's databases.

We have an ascending date/time column in one of our largest tables that we query regularly using a specific stored procedure.

How do you prevent execution plans from growing stale when you have a hundred thousand rows being added a day?

If we're updating statistics frequently to combat this issue, would it make sense to use the OPTION (RECOMPILE) hint on this stored procedure's query?

Any advice or recommendations would be appreciated.

Update: I'm using SQL Server 2012 (SP1).

Am I correct in saying that statistics are only used when creating the execution plan

No, out-of-date statistics may cause an optimality-related recompilation of the affected statement.

  We have an ascending date/time column in one of our largest tables that we query regularly

Sub-optimal execution plans caused by predicate values being outside (specifically above) the range of values stored in the corresponding statistics histogram is known as the Ascending Key Problem. Rebuilding statistics is one possible solution, but it can be quite resource-intensive. Alternatives include:

Trace flags 2389 and 2390. This requires that an index exists with the problematic column as the leading key. It does not work with partitioned tables, and is only effective in SQL Server 2014 if the original cardinality estimator is used. Trace flag 4139 may also be required if the statistics object is branded stationary.
Upgrade to SQL Server 2014. The new cardinality estimator includes logic to estimate beyond the histogram using average density information. This can be less accurate than the 2389/2390 trace flags in some important circumstances.
Enable more frequent automatic statistics updates for large tables with trace flag 2371. With this trace flag, instead of updating after 20% + 500 changes, only SQRT(1000 * Table rows) modifications are required. This is not as complete a solution as those mentioned previously, as updates may still not be triggered often enough.

If the source of your problem is not so much frequent plan compilations based on beyond-histogram predicate values, but more about the effects of occasionally caching such a bad plan as a result of parameter sniffing, you could also consider:

Disabling parameter sniffing using trace flag 4136
Using OPTIMIZE FOR (@parameter = value) to compile a plan for a known representative value
Using OPTIMIZE FOR (@parameter UNKNOWN) to optimize using the average distribution
Using OPTIMIZE FOR UNKNOWN (same as 4136, but per-query)
Using OPTION (RECOMPILE) to compile every time, sniffing the particular value. If the vast majority of runtime values are within the histogram, this may be effective.

For more information about parameter sniffing, embedding and the recompile options, see my article on SQLperformance.com.


SAN block size for SQL Server on 4KB NTFS A potential customer wants to evaluate our storage system. They run Windows 2008R2 x64 with 4KB NTFS on a virtual test machine they've sent us. They did not seem to know this at hand, so I think it's reasonable to assume that environment was not tweaked. The tests are inserting, indexing, searching, deleting. Tool is not known to me.

Given that the windows NTFS block size is 4KB, and SQL write in 64KB chunks - is it safe to assume that a block size of 64k on the SAN is a good choice?

They run SQL Server 2008, perhaps standard.

Quoting from Brent Ozar's article SQL Server Virtualization Q&A:

  Question: “Regarding Virtualization and SAN… is there a recommendation
  for setting block size on SQL Server 2008?”

  There’s a few related settings here including NTFS allocation unit
  size, RAID stripe size, and partition offset.  Check with your SAN
  vendor’s documentation to see what’s right for you.  In most cases for
  OLTP databases, you’re in decent shape with 64K NTFS allocation unit
  size and RAID stripe size, and a 1mb partition offset.

So yes, by default you'll be safe with a 64k block size, but see also your storage documentation, maybe they specify some other preferable unit for a database server.

Some more great information from the same blog: SAN Storage Best Practices for SQL Server.


Postgresql Upgrade from 9.1.9 to 9.4.5 pg_stat_activity.procpid causes failure Attempting to upgrade from 9.1.9 to 9.4.5 Postgresql.

pg_upgrade process fails while trying to pg_restore pg_stat_activity. This fails because the column name differs from the old system to the new system.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 498; 1259 26457 VIEW blocking_procs postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column a.procpid does not exist
LINE 14: ...bl" JOIN "pg_stat_activity" "a" ON (("bl"."pid" = "a"."procp...

How does one solve this issue and use pg_upgrade to move from 9.1.9 to 9.4.5?

Apparently, you have a view, blocking_procs on top of pg_stat_activity.  You will need to drop that view and reinstall it after the upgrade.

DROP VIEW blocking_procs;


Listener is not starting in my Standby database I'm facing a serious issue in data guard installation. I'm setting oracle_sid and oracle_home and when I start the listener,error is showing as below:

C:\Windows\system32>lsnrctl start
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 31-MAY-2017 16: :38
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting tnslsnr: please wait...
Failed to start service, error 0. TNS-12560: TNS:protocol adapter error

Solutions which I have tried:

I've set the correct path in environment variables which is in System Properties.
I've given correct IP address in Hostname(listener.ora)

But still nothing could help me out....It's urgent

Can you try the below,

run the cmd as administartor and verify the ORACLE_HOME and PATH variable and try lsnrctl start
If it still fails to start, provide listener.ora file


UPDATE on big table in PostgreSQL randomly takes too long I'm trying to figure out why an UPDATE statement takes too long (>30 sec).
This is random, i.e. in most cases it finishes in under 100msec. However, sometimes (randomly) it takes > 30sec to complete.
Some specifics:

I'm using PostgreSQL 12 (actually, AWS Aurora)
I'm trying this in a database with no traffic, so it's not being affected by any other queries running in the same time. I'm also monitoring the logs to see if anything else is running, and I don't see anything.
I've tried REINDEX ing, VACUUMing (and VACUUM ANALYZE), with no improvement
I've checked for locks, (log_lock_waits) and I don't see anything.
The query is performed in a loop (from a Python app). It performs ~5000 queries, and at some point some of them, they don't seem to follow a pattern, take a huge time to complete.
I've tried running them in batches, but again, some batches randomly take too long.
The size of the table is kinda big, ~10000000 rows and ~25 indexes.

The query:
UPDATE "my_table" SET "match_request_id" = 'c607789f-4816-4a38-844b-173fa7bf64ed'::uuid WHERE "my_table"."id" = 129624354;

The output of EXPLAIN (ANALYZE VERBOSE BUFFERS COSTS)
 Update on public.my_table  (cost=0.56..8.58 rows=1 width=832) (actual time=34106.965..34106.966 rows=0 loops=1)
   Buffers: shared hit=431280 read=27724
   I/O Timings: read=32469.021
   ->  Index Scan using my_table_pkey on public.my_table  (cost=0.56..8.58 rows=1 width=832) (actual time=0.100..0.105 rows=1 loops=1)
         Output: (...)
         Index Cond: (my_table.id = 130561719)
         Buffers: shared hit=7
 Planning Time: 23.872 ms
 Execution Time: 34107.047 ms

Note that this is EXPLAIN ANALYZE. I'm baffled since while the cost is really low, the actual running time is huge!
I'm trying to understand if this is expected and if I can improve the situation somehow. Any ideas would be welcome, I'm kinda running out!
EDIT: Adding some more info requested by comments:
A query plan for a "normal" update
 Update on public.my_table  (cost=0.43..8.45 rows=1 width=837) (actual time=2.037..2.037 rows=0 loops=1)
   Buffers: shared hit=152 read=1
   I/O Timings: read=1.225
   ->  Index Scan using my_table_pkey on public.my_table  (cost=0.43..8.45 rows=1 width=837) (actual time=0.024..0.026 rows=1 loops=1)
         Output: (...)
         Index Cond: (my_table.id = 129624354)
         Buffers: shared hit=4
 Planning Time: 1.170 ms
 Execution Time: 2.133 ms
(9 rows)

The table has 23 indexes, 6 foreign-key constraints. 3 BRIN, 1 GIN. The rest B-Tree. I'm not sure how to check for fastupdate on the GIN index, the output of \d+ index_name is
 Column | Type | Key? | Definition | Storage  | Stats target
--------+------+------+------------+----------+--------------
 search | text | yes  | search     | extended |
gin, for table "public.my_table"

GIN indexes have a "fastupdate" mechanism where new data gets written to a specific section in a linear fashion.  Once that exceeds some set size (gin_pending_list_limit, which is set globally but can be overridden per index) the next process which happens along to write to the index gets assigned the task of consolidating those entries into the main part of the index, which can lead to long freezes for that process.  For the length of freezing you are seeing, you must have a pretty high setting for gin_pending_list_limit.
If consistent latency is more important to you than overall insert/update performance, you can disable fastupdate.  Or you could lower the value of gin_pending_list_limit to keep some of the benefit while lowering the freeze-up time.
If you need both consistency and overall efficiency, you could write a manual process/thread which will open its own connection and run select gin_clean_pending_list(...) on the index in a loop while the main update operation is ongoing.  That way you get the benefit of the pending list, while the latency of cleaning it up is all loaded onto a background process where it doesn't matter.  (Vacuum and autovacuum will also clean it up, but you can't really get them to run often enough to do a sufficient job of it, because of all the other work they also need to do.  Hence gin_clean_pending_list)


SQL server migration to new server from 2008 R2 to SQL 2014 I need to run the upgrade advisor before migration, so my question is where to Run the upgrade advisor ? In the old server or after restoring the DB in new server ?

As I am new to migration so need help and what all things I should take care for Migration and step by step process would be helpful.

You can run upgrade advisor 2014 on a backup of your current to-be-migrated database which in your case is sql server 2008R2.

I have written detailed steps on migration here.

PowerShell based start-sqlmigration is what I highly recommend (I am using it quiet often to migrate server instances to new server) when you have to migrate your entire server instance.

e.g.

  Start-SqlMigration -Source sql2008R2 -Destination sql2014 -BackupRestore -NetworkShare \nas\sql\migration

Make sure that the sql server service account has proper permissions on the NetworkShare


Name of a descriptive text field? I have seen descriptive text field named as DESCRIPTION, REMARK, and NOTE. They seem to mean the same thing. So what is more appropriate than the others?

A description describes an object like a product.

  Description: 5x10" widget used for squiggle placement.

A remark is often printed on invoices/statements/payments to show to the customer or supplier.

  Remark: This payment relates to invoices #1, #2, and #4.

A note calls out attention to something out of the ordinary or seperate to everything else. It may be internal-only.

  Note: Do not sell any more widgets to this customer because they have an outstanding invoice.

These aren't hard and fast rules but it's where I've seen them used.


Can sys.database_permissions contain REVOKE? The Microsoft documentation for sys.database_permissions mentions that the valid values for state are "D", "R", "G", "W". The corresponding values for state_desc are "DENY", "REVOKE", "GRANT", "GRANT_WITH_GRANT_OPTIONS".
The value of "R" / "REVOKE" is a surprise to me. Isn't "REVOKE" just part of the syntax for eliminating a previously GRANT'ed or DENY'ed permission?
Is it possible for the values or "R" / "REVOKE" to be present in this system view? If so, under what circumstances would it appear?
I know that if you REVOKE a DENY'd permission, the DENY'd permission goes away; and if you REVOKE a GRANTed permission the GRANTed permission gets removed. Under what circumstance would SQL Server register the REVOKE'ing in sys.database_permissions instead of simply removing the existing GRANT/REVOKE/GRANT_WITH_GRANT_OPTIONS row?

REVOKE doesn't just remove access, rather it undoes a specific permission that was either explicitly given by a GRANT or DENY. In other words, if a permission is explicitly denied on an object for a security principal (e.g. User, Login, etc), that explicitly denied permission can be removed by using REVOKE on it.
After some research it looks like the use-case for "R" as a potential value of the state column in the sys.database_permissions view is for column level permissions that contradict the parent table's or view's permissions as per this Stack Overflow answer by Mark Chesney:

For objects which can have column permissions, such as tables or views, the presence of DENY or GRANT object permissions requires REVOKE for column permissions to be persisted.


set date value based on other row values Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem:

I've got a table with the following structure and some example data:

CREATE TABLE [dbo].[set_dates](
[Split3_ID] [int] IDENTITY(1,1) NOT NULL,
[CU_ID] [int] NULL,
[order_id] [int] NULL,
[st_date] [datetime] NULL,
[sku] [int] NULL,
[Priority] [int] NULL,
[Delay] [int] NULL,
[CourseDate] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[set_dates]
       ([CU_ID]
       ,[order_id]
       ,[st_date]
       ,[sku]
       ,[Priority]
       ,[Delay])
 VALUES
       (25721,7907,GETDATE(),63,4,4),
       (25718,7910,GETDATE(),63,4,4),
       (25718,7910,GETDATE(),6,5,2),
       (25719,7908,GETDATE(),6,5,2),
       (25719,7908,GETDATE(),57,4,4),
       (25719,7908,GETDATE(),52,8,2)
  GO

So my Table should look like this:

Split3_ID CU_ID order_id st_date                    sku Priority Delay CourseDate

1         25721 7907     2014-07-08 18:04:30.973    63  4        4     NULL
2         25718 7910     2014-07-08 18:04:30.973    63  4        4     NULL
3         25718 7910     2014-07-08 18:04:30.973    6   5        2     NULL
4         25719 7908     2014-07-08 18:04:30.973    6   5        2     NULL
5         25719 7908     2014-07-08 18:04:30.973    57  4        4     NULL
6         25719 7908     2014-07-08 18:04:30.973    52  8        2     NULL

What I would like to accomplish now is to set the CourseDate depending on three fields, order_id, Priority and Delay ... more exact:

If there is only one row with the same order_id, then CourseDate = st_date, if there is multiple rows with the same order_id, then the Dates should be entered depending on the Priority. Lowest Priority would be the first CourseDate = st_date, next bigger Priority would be st_date + Delay from previous entry ... and so on ...

In the above example, the outcome should look like this:

Split3_ID CU_ID order_id st_date                    sku Priority Delay CourseDate

1         25721 7907     2014-07-08 18:04:30.973    63  4        4     2014-07-08

2         25718 7910     2014-07-08 18:04:30.973    63  4        4     2014-07-08
3         25718 7910     2014-07-08 18:04:30.973    6   5        2     2014-07-12

4         25719 7908     2014-07-08 18:04:30.973    6   5        2     2014-07-12
5         25719 7908     2014-07-08 18:04:30.973    57  4        4     2014-07-08
6         25719 7908     2014-07-08 18:04:30.973    52  8        2     2014-07-14

Is there a way to accomplish this? I've got up to ten orders with the same order_id with no presorting, so my first attempts with CASE Statements ended up in a lot of writing.

The second, but not so important problem is, that there may be orders with the same priority from time to time under the same order_id. In this case I would like those to be treated just like CourseDate = st_date

Any help is highly appreciated, as I got a major brainfart at the moment ... ;)

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.st_date = D2.st_date and
                    D1.Priority > D2.Priority
              ) as D2(Delay)

SQL Fiddle

Update:

I am not really sure how you want to deal with different st_date within a order_id. The above query groups on order_id and st_date but I guess an alternative would be to use the lowest st_date per order_id in the dateadd function.

select D1.Split3_ID,
       D1.CU_ID,
       D1.order_id,
       D1.st_date,
       D1.sku,
       D1.Priority,
       D1.Delay,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as CourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0),
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
order by D1.Split3_ID

Update 2:

The update statement for the second query could look like this.

with C as
(
select D1.CourseDate,
       dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as NewCourseDate
from dbo.set_dates as D1
  cross apply (
              select isnull(sum(D2.Delay), 0),
                     min(D2.st_date)
              from dbo.set_dates as D2
              where D1.order_id = D2.order_id and
                    D1.Priority > D2.Priority
              ) as D2(Delay, st_date)
)
update C
set CourseDate = NewCourseDate

Update 3:

Or even like this if st_date is guaranteed to be the same within one order_id.

update D1
set CourseDate = dateadd(day, (select isnull(sum(D2.Delay), 0)
                               from dbo.set_dates as D2
                               where D1.order_id = D2.order_id and
                                     D1.Priority > D2.Priority), D1.st_date)
from set_dates as D1


Different results returned when ignoring Primary Key index. Daylight Savings Time related bug? I'm hoping you can help explain this behavior or try to reproduce the issue so I can be confident filing a bug report.

Essentially, I get different results from these queries:

# returns 0
select count(*) as COUNT_WITH_INDEX
from a
where id = 1  and begin_time='2018-11-04 01:01:00.000';

# returns 1
select count(*) as COUNT_WITHOUT_INDEX
from a ignore index (PRIMARY)
where id = 1  and begin_time='2018-11-04 01:01:00.000';

The key difference is the use of ignore index (PRIMARY).

In case the date didn't stick out to you immediately, that date falls in the "Fall Behind" hour of Daylight Savings transition for 'US/Central' timezone.  1:01am occurred twice on 2018-11-04.  I only found issues with timestamps that fall in this window, so I suspect its a bug with how DST rules are applied.

Regardless of whether or not I need to use convert_tz() to correctly get the date I want, there's still the fact that I get different results with and without the PRIMARY key index.

Full test case:

create database if not exists test_dt;
use test_dt;

drop table if exists a;

CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `begin_time` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000',
  PRIMARY KEY (`id`,`begin_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

set TIME_ZONE='+0:00';

insert into a values(1, '2018-11-04 06:01:00.000');
insert into a values(1, '2018-11-04 07:01:00.000');

set TIME_ZONE='US/Eastern';

select * from a;

# returns 0
select count(*) as COUNT_WITH_INDEX
from a
where id = 1  and begin_time='2018-11-04 01:01:00.000';

# returns 1
select count(*) as COUNT_WITHOUT_INDEX
from a ignore index (PRIMARY)
where id = 1  and begin_time='2018-11-04 01:01:00.000';

set TIME_ZONE='US/Central';
# repeat w/ Central if you like

Side note: Install Time Zone data to use named time zones.

Tested on:: CentOS 7 w/ 5.6.36 and 5.6.43.  I don't have 5.7 or 8.0 installations handy.

Another side note:  The initial problem I encountered was that joins between parent-child tables were not returning data with composite PK (id, timestamp).  Since timestamps are stored in UTC format, I wouldn't think DST dates would be an issue, but here I am.

Do you have an explanation for the behavior?
Do you think it's a bug?

Thanks!

Edit
Additional info per comments

  What result do you get if you drop the p.k?

set TIME_ZONE='+0:00';

insert into a values(1, '2018-11-04 06:01:00.000');
insert into a values(1, '2018-11-04 07:01:00.000');

select * from a ;

select 'Set time_zone=US/Central' as msg;
set TIME_ZONE='US/Central';
+------------------+
| COUNT_WITH_INDEX |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

drop index `primary` on a;

select count(*) as COUNT_DROPPED_PK
from a
where id = 1  and begin_time='2018-11-04 01:01:00.000';

+------------------+
| COUNT_DROPPED_PK |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

set TIME_ZONE='+0:00';

select * from a;

+----+-------------------------+
| id | begin_time              |
+----+-------------------------+
|  1 | 2018-11-04 06:01:00.000 |
|  1 | 2018-11-04 06:01:00.000 |
+----+-------------------------+

Notice If I change the TZ to US/Central, drop the PK, then set the TZ back to '+0:00', I can see that the data is screwed up.  Both records are 06:01 time, when one was inserted 07:01.  That behavior is not logical to me since the timestamps should always be UTC in the backend.

  1) Show the output of set TIME_ZONE='US/Eastern'; select * from a; 2) What if set TIME_ZONE='-4:00';?

set TIME_ZONE='US/Eastern';
select * from a;
+----+-------------------------+
| id | begin_time              |
+----+-------------------------+
|  1 | 2018-11-04 01:01:00.000 |
|  1 | 2018-11-04 02:01:00.000 |
+----+-------------------------+

set TIME_ZONE='-4:00';
select * from a;
+----+-------------------------+
| id | begin_time              |
+----+-------------------------+
|  1 | 2018-11-04 02:01:00.000 |
|  1 | 2018-11-04 03:01:00.000 |
+----+-------------------------+

I've got an answer that I don't like :

  Indexed searches convert the "value or range to be matched " into a
  UTC time to be compared against the indexed value.

  Non-indexed searches convert the "stored value" from UTC to the session time zone
  for comparison.

That explains why I get different results with and without an index.  To me it feels like the implementation is broken, but I don't know what I would do for an easy fix. /shrug

Fortunately we can work around the issue in this case, but we are reconsidering how to handle this in the future.


Create Materialized View from foreign table I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server.

What do I have to add to make the query access the foreign database and the table there?

CREATE MATERIALIZED VIEW mv_table_1 AS
  SELECT *
  FROM public.mv_table_1 --The schema & table from the different DB
WITH DATA;

I tried using the fully qualified table name (db name before the schema name) but this results in an error:

  References to other dbs are not implemented

PostgreSQL databases cannot "see" each other by default. This is a good thing.

You can make one pg database see another by using the postgres_fdw Foreign Data Wrapper: https://www.postgresql.org/docs/current/static/postgres-fdw.html

Steps:

Add the extension
Create the foreign server
Create the user mapping
Create the foreign table. See https://www.postgresql.org/docs/current/static/sql-importforeignschema.html if you have 9.5+
Create the materialized view pointing to the foreign table


Out-of-order results in the READ UNCOMMITTED isolation level? On the Wikipedia page for Isolation (database systems), when explaining dirty reads, it says:

The only thing that may be prevented in the READ UNCOMMITTED isolation level is updates appearing out of order in the results; that is, earlier updates will always appear in a result set before later updates.

I am confused about in which case updates would appear out of order in the results. I think it is impossible and not what READ UNCOMMITTED isolation level avoids?

That entry is poorly-worded, so I don't blame you for getting confused by it.
The author(s) appear to be trying to make the point that even when dirty reads are allowed, it is impossible to encounter changes made by a later update before seeing changes made by an earlier update.
In other words, a 'dirty read' will always include all changes (committed or not) up that point in time.
It seems to be a way to stress a perceived difference compared with non-repeatable reads, as the preceding sentence mentions:

Dirty reads work similarly to non-repeatable reads; however, the second transaction would not need to be committed for the first query to return a different result.

No such distinction appears in the original definitions as I understand them, so it would be better if the part you refer to were removed.
Note that an implementation of read uncommitted isolation is not required to experience dirty reads, just that it may. Several popular products provide a read uncommitted isolation level that delivers a much higher degree of isolation and protection from so-called read phenomena. This is explicitly allowed by the SQL Standard.


Update different oracle database from different oracle database I want to update a CLOB column in B Oracle 11g database using a CLOB column in A Oracle 11g database located in a different server.

What are the different ways to do that? and which is the best and easiest way?

In B:

create database link dblink_to_a connect to myuser identified by mypassword using 'tns_a';

merge into destination_table d using source_table@dblink_to_a s on (d.id = s.id) when matched then update set d.myclob = s.myclob;
commit;


How can I manage an exception generated by Postgis? I am running the following query on Postgres 10.3/Postgis 2.4:

create table table_c as (
    select A.*, B.area_name, B.id
    from table_a A
    left join table_b B
    on ST_Contains(A.geom, B.geom)
);

that fails with a cryptic:

ERROR:  GEOSContains: TopologyException: side location conflict at -2.0889721121124643 53.528652265475735

due to Postgis (and relative used libraries).

Is there a way to skip exceptional cases and keeping the query running until finished?

You can wrap the PostGIS function ST_Contains() in a plpgsql (or other PL) function of your own and trap the exception there. Like:

CREATE OR REPLACE FUNCTION f_ST_Contains(geometry, geometry)
  RETURNS boolean AS
$func$
BEGIN
   RETURN ST_Contains($1,$2);

EXCEPTION WHEN OTHERS THEN
   RETURN FALSE;  -- Return FALSE instead of raising an exception
END
$func$  LANGUAGE plpgsql IMMUTABLE;

This returns FALSE instead of raising an error. You might want return NULL instead. Same effect on your query.

And use that instead of ST_Contains() in your query:

...
ON f_ST_Contains(A.geom, B.geom)

Schema-qualify the function name in the function definition (and in the call) to remove dependency on the search path:

...
RETURN public.ST_Contains($1,$2);  -- actual schema of extension here
...

Not sure about implications on performance (especially index usage); didn't test.


Is there a standard implementation of a Users Database? I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a single table with each user having his own row, or should this info be split among different tables and linked together (maybe for efficiency?) I'm not tremendously concerned with security at this point, but I'll obviously want to have password encryption before too long.

I tried to find what I was looking for on google, but to no avail. Just let me know if the question needs further clarification or anything.

I suggest that you use typical normalization standards. One user per row.

User ID (incrementing bigint)
User Common Name (to be displayed on
the site)
User Email Address
Password Salt (Unique for every user, inserted when the account is created)
Password (Hashed with the salt - MD5 or SHA1, your preference)
Date Account Was Created

The rest is up to you given your business rules.


Why isn't partition-level lock escalation the default? In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.

However, this isn't automatically enabled for partitioned tables - by default, the table is set to skip partition locking and go right from row or page -> table. Why would this be the case? Is there a reason why I wouldn't want to switch all my tables from TABLE to AUTO so that they escalate locks to partition level instead of to table level?

Since the default is still TABLE, I figure there must be something I'm missing about the downside of AUTO.

BOL has answer -- It is not ON by default as it may increases the potential for deadlock.

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition.

HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better.


SQL Server 2008 R2 Database - Why is the database not using free space before growing again? I have a vendor application database with 700 tables.  The data hasn't ever been archived and dates back to 2006.   The business just wants all that data.

My question is as I am monitoring the size/growth of the database, I am noticing a trend where the database is growing but it has free space.

The database is 187GB, has 40GB free and the backup size is 146GB.
The database is growing by 1GB autogrow increment which is should not need to do if it has free space it can use, right?

I have looked at fill_factor as I wasn't sure what the vendor was doing, and at the database level it remains at the 0 default indicating the pages will be filled to 100%.  There are some objects, indexes mostly, that have a 80% or 85% fill factor.

I am stumped.

I am re-indexing and the disk fragmentation reports are coming back with little or no fragmentation.

Got any ideas?

A couple of things worth noting here.

First, the database will grow whenever it needs the space to complete a transaction. I wrote about this here. If you performing maintenance on your database (say, rebuilding indexes) and you hit a large enough table then you could see a growth event happen as a result, even though it appears you have a large amount of free space.

Second, you can examine you default trace files in order to find out when your autogrowth events are happening. Often times you will find that the timing of the growth events coincides with maintenance, or perhaps a batch load of some kind.

HTH


How to pass a table of values to a stored procedure from SSIS? I am really new to SSIS, so I apologize for the "dumb" question. I know open ended "Best Practices" questions are seriously frowned upon, so I will try to be as specific as I can.

I have a basic SSIS package, which moves data from one SQL Database to another SQL Database. There are few data transformations and one filter going on, in the Data Flow task section. I've been trying to come up with a good way to write my data to it's destination, but it can't go straight into the "Final" table (as I would with the Data Flow Destination tasks). I would prefer to pass it as a variable to a stored procedure. I see two options, both of which I don't like:

I could use the OLE DB Command and loop through my data line by line, running the stored procedure for each one.
I could write the data to a temp table and call the stored procedure from the Control Flow section.

For option 1, it seems like really bad form to have to loop through each data row. Is there no way to call the stored procedure and pass it a table?

I have someone here at work telling me not to use a temp table, so Option 2 seems off the table. I think if I had justification for using Option 2, as a "best practice", I might be able to convince them to let me do the data load that way.

Am I missing something? Is there another way to tackle this problem? If you could include a reason WHY with your answer, it would greatly be appreciated, since I'm trying to understand the rhyme and reason.

EDIT: To clarify, I'm trying to find a good way to pass a table-valued parameter.

Is there no way to call the stored procedure and pass it a table?

There is, if you're using SQL Server 2008 or higher: table-valued parameters (TVPs).

I've already blogged about how to use TVPs in SSIS. Essentially, you have to use ADO.NET inside a Script Component. While this isn't the prettiest solution, it gets the job done relatively cleanly with all the code in one spot.


MySQL Union not showing 2nd query result set I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this.

select  *
    From
      ( SELECT  c1.ia_device_home_carrier, avg(rf1.ia_signal_strengh_rssi) as ss
            from  rf_voice_data_verizon_sec2 rf1, device_market_config d1,
                ia_device_carrier c1
            where  rf1.ia_device_id = d1.ia_device_id
              and  rf1.ia_device_id = c1.ia_device_id
              and  rf1.ia_signal_strengh_rssi BETWEEN -150.0 AND -40.0
              and  d1.ia_project_name = 'AA'
            group by  ia_device_home_carrier
         union
         select  c2.ia_device_home_carrier, avg(rf2.httpul) as http
            from  rf_voice_data_verizon_sec2 rf2, device_market_config d2,
                ia_device_carrier c2
            where  rf2.ia_device_id = d2.ia_device_id
              and  rf2.ia_device_id = c2.ia_device_id
              and  rf2.httpul > 0
              and  d2.ia_project_name = 'AA'
            group by  ia_device_home_carrier
      ) x
    group by  ia_device_home_carrier

                            +------------------------+----------+
                            | ia_device_home_carrier | ss       |
                            +------------------------+----------+
                            | xyz                    | -77.8559 |
                            | abcggg                 | -69.8158 |
                            | sfg                    | -95.8884 |
                            | VZW                    | -94.3992 |
                            +------------------------+----------+

But i need a result set like this

                            +------------------------+----------+------------+
                            | ia_device_home_carrier | ss       |   httpul   |
                            +------------------------+----------+------------+
                            | xyz                    | -77.8559 |  7848.5175 |
                            | abcggg                 | -69.8158 |  5623.5231 |
                            | sfg                    | -95.8884 |  7228.5677 |
                            | VZW                    | -94.3992 |  7118.3175 |
                            +------------------------+----------+------------+

Since UNION lines up the columns, not the column names, you cannot directly go from what you have.  However, here is one technique:

Instead of

SELECT * FROM (
    SELECT x, ss     FROM...
    UNION
    SELECT x, httpul FROM...
)

do

SELECT  x,
        MAX(ss) AS ss,  -- MAX() will ignore the NULL from the 'other' table
        MAX(httpul) AS httpul
FROM (
    SELECT x,         ss, NULL AS httpul  FROM ...
    UNION
    SELECT x, NULL AS ss,         httpul  FROM ...
)


Locate a specific word in a database Goal
Locate a specific word among tables in a database and display the name of the table and column in a list.
Problem
There are TOO many tables to locate a specific word in the database.
Info

Using SQL Server 2012
Every table use a 'schemaname.tablename'

This will not be fast (this took over a minute to produce 0 results in my local copy of AdventureWorks).

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (
   SELECT 1 FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
     + N' WHERE ' + QUOTENAME(c.name) + N' LIKE @s
 )'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;

If you need to support sql_variant for some reason (I recommend against it), only a few changes are needed:

DECLARE @SpecificWord NVARCHAR(32);
SET @SpecificWord = N'donut';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT NULL,NULL WHERE 1 = 0';
SET @SpecificWord = N'%' + @SpecificWord + N'%';

SELECT @sql += N' UNION ALL SELECT '''
 + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
 + ''',''' + QUOTENAME(c.name) + ''' WHERE EXISTS
 (SELECT 1 FROM ' + QUOTENAME(s.name) + N'.'
 + QUOTENAME(t.name) + N' WHERE CONVERT(
   NVARCHAR(MAX), ' + QUOTENAME(c.name)
 + N') LIKE @s)'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE c.system_type_id IN (35,98,99,167,175,231,239);

EXEC sys.sp_executesql @sql, N'@s NVARCHAR(32)', @SpecificWord;


Query improvements without UNION I have the following scenario. A users table, a records table and a memberships table.

A user can be associated to zero or more records either directly with the records table

records.user_id = users.id

or via a membership (the membership table contains user_id and record_id)

memberships.user_id = users.id

Let's assume I want to load all the records managed by the user. The query directly translates into

SELECT records.* FROM records WHERE records.user_id = ?
UNION
SELECT records.* FROM records
INNER JOIN memberships ON memberships.record_id = records.id
WHERE memberships.user_id = ?

Is there a way to combine these two queries into a single one without UNION?

I tried starting from:

SELECT records.*
FROM records
LEFT OUTER JOIN memberships ON memberships.record_id = records.id
WHERE records.user_id = ? OR memberships.user_id = ?

But it also returns the records where the JOIN matches for other user memberships. Any hint?

Use OR with EXISTS or IN:
(the IN version only if the memberships.record_id column is not nullable)

SELECT r.*
FROM records AS r
WHERE r.user_id = ?
   OR EXISTS
      ( SELECT 1
        FROM memberships AS m
        WHERE m.record_id = r.id
          AND m.user_id = ?
      ) ;

SELECT r.*
FROM records AS r
WHERE r.user_id = ?
   OR r.id IN
      ( SELECT m.record_id
        FROM memberships AS m
        WHERE m.user_id = ?
      ) ;

This way you also do not get the duplicate rows of records that the join introduces (and then are get rid in the UNION DISTINCT part of your query.)

You could use the LEFT join query but you would have to either add DISTINCT or GROUP BY the primary key of the records table to avoid the duplicate rows:

SELECT r.*
FROM records  AS r
  LEFT OUTER JOIN memberships AS m
    ON m.record_id = r.id
WHERE r.user_id = ?
   OR m.user_id = ?
GROUP BY r.id ;


File share Witness or Disk Witness I have 2 SQL Servers at production site Windows Server Failover Cluster and 1 SQL Server at DR site. I will add the DR node to existing Windows cluster and configure AOAG.

Should I go for File share witness or Disk Witness for Quorum Configuration for this AOAG set up .Please let me know where to place the file server share in which site.

Generally when there are geographically distributed clusters, having a disk witness does not make sense. This is due to the need to have block level synchronous disk replication between all sites while preserving things like write ordering and scsi reservations.

In almost all instances of having a geo-cluster it's best to go with a fileshare witness. The two different types of witnesses do the same thing (there are subtle differences like how the arbitration happens, etc.)

Where should you place the witness? The best place would be a neutral 3rd site, however most won't have this available. My recommendation is to place this at the site you want to keep primary in the event of a communications loss between the nodes at each site. In your case I'd likely put it on the primary site. It should be somewhere that is as highly available as possible.


Could too many idle connections affect PostgreSQL 9.2 performance? Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high.  When running ps aux, I see ~250 "idle" connections (which I believe to be too many).  I haven't started doing a full diagnosis, but I wanted to know if this is a good place to start looking.

I am also using PgBouncer with transaction-level pooling.  I am suspecting that I can easily reduce the number of idle connections by tuning the pool size.  However, I don't want to start doing too many changes unless there is a good reason to do it.

Can many idle connections in PostgreSQL 9.2 affect performance?

Thanks very much!

I seriously doubt idle connections are your issue.  PostgreSQL is very capable of handling idle sessions since they take almost no resources.  Active sessions on the other hand are much more likely the cause of slowness.

See https://wiki.postgresql.org/wiki/Number_Of_Database_Connections for reference on the number of database connections.


Order by column should have index or not? I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How performance will affect by making or not making index to that column?

Yes, MySQL can use an index on the columns in the ORDER BY (under certain conditions).  However, MySQL cannot use an index for mixed ASC,DESC order by (SELECT * FROM foo ORDER BY bar ASC, pants DESC).  Sharing your query and CREATE TABLE statement would help us answer your question more specifically.

For hints on how to optimize ORDER BY clauses:
http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html

Edit 2012-01-21 8:53AM

There were questions about the source of my statement about using an index with mixed ASC/DESC
in the ORDER BY.  From the ORDER BY Optimization documentation:

  In some cases, MySQL cannot use indexes to resolve the ORDER BY,
  although it still uses indexes to find the rows that match the WHERE
  clause. These cases include the following:

  ...

  You mix ASC and DESC:

  SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  ...

Also, the DESC keyword is meaningless in CREATE INDEX

  An index_col_name specification can end with ASC or DESC. These
  keywords are permitted for future extensions for specifying ascending
  or descending index value storage. Currently, they are parsed but
  ignored; index values are always stored in ascending order.


Pivoting 4 by 44 result set I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each store to get the 4 values as columns for each store as shown below:
Query: Query1

but when I try to use PIVOT function with dynamic SQL it returns multiple nulls as shown below:
Query:Query2

the table ##tbl1 includes the data set that I want to pivot:

I used the following query: (the three dots represent the rest of the columns to be pivoted)
select * from ##tbl1

pivot (sum(total_sales)     for s in ([50001 Sales],[50002 Sales],...)) as pv_tb
pivot (sum(Margin)          for m in ([50001 margin],[50002 margin],...)) as pv_tb1
pivot (sum(total_profit)    for p in ([50001 profit],[50002 profit],...)) as pv_tb2
pivot (sum(total_cost)      for c in ([50001 cost],[50002 cost],...)) as pv_tb3

I also use dynamic SQL to pass the column names inside the pivot without having to type each one of them individually, but for the sake of troubleshooting I just showed you how the query would look.
I can't get around using CROSS APPLY and UNPIVOT with this 132 column result, it's very hard to maintain.
Can anyone help me find a way is there anyway easier than this?

WITH Sales_CTE ([50001 Sales], [50002 Sales], [50001 margin],[50002 margin],[50001 profit],[50002 profit],[50001 cost],[50002 cost])
AS
(
select * from
    (
    select rtrim(cast(csstor as char))+' Sales' as s
            ,rtrim(cast(csstor as char))+' margin' as m
            ,rtrim(cast(csstor as char))+' profit' as p
            ,rtrim(cast(csstor as char))+' cost' as c
            ,[TOTAL_PRICE]
            ,[Margin]
            ,[TOTAL_Profit]
            ,[Total_COST]
    from sales

    ) as result_1
pivot (sum(total_price)     for s in ([50001 Sales],[50002 Sales])) as pv_tb
pivot (sum(Margin)          for m in ([50001 margin],[50002 margin])) as pv_tb1
pivot (sum(total_profit)    for p in ([50001 profit],[50002 profit])) as pv_tb2
pivot (sum(total_cost)      for c in ([50001 cost],[50002 cost])) as pv_tb3)
SELECT
MAX([50001 Sales]) as [50001 Sales]
, MAX([50002 Sales]) AS [50002 Sales]
, MAX([50001 margin]) AS [50001 margin]
,MAX([50002 margin]) AS [50002 margin]
,MAX([50001 profit]) AS [50002 margin]
,MAX([50002 profit]) AS [50002 profit]
,MAX([50001 cost]) AS [50001 cost]
,MAX([50002 cost]) AS [50002 cost]
FROM Sales_CTE

Result is
    50001 Sales     50002 Sales 50001 margin    50002 margin    50002 margin    50002 profit    50001 cost  50002 cost
1   39498,300       31647,150   12,562300       11,159300      4961,932         3531,624        34536,3675  28115,5264


How to properly change "innodb_buffer_pool_size" value for mysql? I have following environment:

# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
# rpm -qa | grep ^mysql-community-
mysql-community-common-5.5.48-2.el7.x86_64
mysql-community-libs-5.5.48-2.el7.x86_64
mysql-community-client-5.5.48-2.el7.x86_64
mysql-community-release-el7-5.noarch
mysql-community-server-5.5.48-2.el7.x86_64
#

I need to adjust innodb_buffer_pool_size value for mysql, however last time when I try to do that, something went south and I end up restoring database from backup( database is ~100G, so ideally I wouldn't want to go that route again.

steps that I took last time:

stop mysqld
adjust value of innodb_buffer_pool_size inside of my.cnf
killed ib_logfile*
start mysqld

What did I do wrong? What should I do different this time?

Changing innodb_buffer_pool_size doesn't need you to nuke ib_logfiles. This could easily be the reason for you had to restore the database (*).

Just stop mysql -> change the settings in my.cnf -> start mysql.

  (*) ib_logfiles are holding recovery data, pages which were not yet
  checkpoint to the tablespace. If you remove them without making sure
  you have a clean shutdown you risk losing data and corrupting your
  database.


Identify cause of unusual transaction log growth I have an application running on SQL Server 2008 (it's a cluster but it hasn't failed over.)

Previously, the transaction log used to grow to 10-20GB every 2-3 days. We would shrink it back (after backup) to let it fill up again. Over the last few weeks, the log has been growing at 20GB a day; we don't believe we have changed the application code, so are wondering what process is causing the increase in growth rate.

What is a good strategy to identify transactions causing the largest increase in transaction log size?

Backing up or shrinking the file will release the disk space. I just think that is just a band-aid over the actual problem.  The application is doing more of something; thereby using more resource than before.

This SQL shows queries with most "logical_writes":

SELECT TOP 100
    st.text,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
ORDER BY total_logical_writes desc
OPTION (RECOMPILE);

This T-SQL could help to find direction, but it is not really what you asked for, as logical write != write. and logical write != log write.

It could be very very interesting to find more complete answer to your question.


How to select from pdbs with a cdb user? I have several store procedures, which analyze oracle logs using log miner. These procedures run on CDB user C##ADMIN which created for this manner.
Now I want to compare the logged values to the live values, so I need to be able to access each pdb from the CDB.
After several searches, I came across database link which looks like a possible answer. but when I'm trying to create a database link it says ORA-02011: duplicate database link name. and when I'm trying to drop it I'm getting ORA-65230: internal database link cannot be altered or dropped.
I'm tried to run the following queries, but it failed: (even when connected as: / as sysdba)
SELECT * FROM ALL_TABLES@testpdb;

ORA-12541: TNS:no listener
*Cause: The connection request could not be completed because the listener
      is not running.
*Action: Ensure that the supplied destination address matches one of
      the addresses used by the listener - compare the TNSNAMES.ORA entry with
      the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
      go by way of an Interchange). Start the listener on the remote machine.

SELECT * FROM test.TABLE1@testpdb;

ORA-12541: TNS:no listener
*Cause: The connection request could not be completed because the listener
      is not running.
*Action: Ensure that the supplied destination address matches one of
      the addresses used by the listener - compare the TNSNAMES.ORA entry with
      the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
      go by way of an Interchange). Start the listener on the remote machine.

So my questions are:

Is using database links is it the correct way?
If it is, how to configure it?
How can I use it?
Do I have any alternative to achieve my goal?

That error means your listener is not running. Start your listener.
This just works normally without doing anything:
SQL> create user c##myadmin identified by password;

User created.

SQL> grant dba to c##myadmin container=all;

Grant succeeded.

SQL> alter user c##myadmin set container_data=all container=current;

User altered.

SQL> conn c##myadmin/password
Connected.
SQL> select con_id, name from v$containers;

    CON_ID NAME
---------- ----------
         1 CDB$ROOT
         2 PDB$SEED
         3 PDB1
         4 PDB2

SQL> select count(*) from dba_users@pdb1;

  COUNT(*)
----------
        37

SQL> select count(*) from dba_users@pdb2;

  COUNT(*)
----------
        37

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------
SYS_HUB

An alternative is the CONTAINERS clause, but that has some serious limitations (queried object must exist in all containers).


Slow query log on production? What are the best practices regarding the slow query log? Should it be active in production environment? If so, what would be a reasonable long query time? I know its different for every application, but on general, what would you set it in a data warehouse application with a mysql database of 15gb? Thank you very much

"I know its different for every application"

15G is not a large data-set unless you tell me it's a single table! Your data can be contained in innodb-buffer-pool and performance should be better.

Importantly, you should make sure your queries are well written and tables are indexed correctly. (log-queries-not-using-indexes)

I'd start with 1 Sec and review the amount of queries getting logged and work on improvements. long-queyr0time is dynamic and you should be  able to play with it depending on load it generates.


select seg.seg_text,
       ifnull(nights.Night_Nb,0) as Night_Nb,
       ifnull(nights.Percentage,0) as Percentage,
       ifnull(bookings.Booking_Nb,0) as Booking_Nb,
       ifnull(bookings.Percentage,0) as Percentage
  from __Segmentations seg
 left join ( select seg.seg_id,
                    count(*) as Night_Nb,
                    round(100*count(*)/(select count(*) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as nights  on (nights.seg_id=seg.seg_id)
 left join ( select seg.seg_id,
                    count(distinct bil.bil_bookingid) as Booking_Nb,
                    round(100*count(distinct bil.bil_bookingid)/(select count(distinct bil_bookingid) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as bookings  on (bookings.seg_id=seg.seg_id)
 group by seg.seg_id;
+----------+----------+------------+------------+------------+
| seg_text | Night_Nb | Percentage | Booking_Nb | Percentage |
+----------+----------+------------+------------+------------+
| Direct   |        6 |      85.71 |          2 |      66.67 |
| Website  |        1 |      14.29 |          1 |      33.33 |
| Other    |        0 |       0.00 |          0 |       0.00 |
+----------+----------+------------+------------+------------+
3 rows in set (0.02 sec)


Can we downgrade oracle database form 12cR2 to 12cR1 I have upgraded the Oracle database to 12cR2, now I want to downgrade it to 12cR1.Is this possible?What are the conditions?

You can downgrade both major releases and patchset releases, based on the original Oracle Database release from which the database was upgraded. Major release downgrades are supported back to Oracle Database 11g Release 2 (11.2.0.2) and subsequent 11.2 releases, and Oracle Database 11g Release 1 (11.1.0.7). Patchset downgrades are supported back to Oracle Database release 11.2 patchsets, except release 11.2.0.1.

This link provides a summary of releases supported for downgrading.

Also, check this link for compatibility.


Is there any way in Postgres to parameterise a procedure for sort order column, when the columns are of different types? I am trying to write a function which does (some complex stuff) and returns the results in different orders based on a parameter.
A simplified version would look something like this:
CREATE OR REPLACE FUNCTION test(order_column text)
    RETURNS TABLE(thing1 bigint,thing2 text, thing3 timestamp without time zone)
    LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
RETURN QUERY
    SELECT thing1, thing2::text, thing3 FROM some_table
    ORDER BY
        CASE WHEN order_column='id' THEN thing1
        ELSE thing3
        END
    DESC;

END;
$BODY$;

Unfortunately, thing1 is a bigint and thing3 is a timestamp, and when I try to run the function I get an error saying bigint and timestamp types can't be matched, which I interpret as saying that the types returned from the case need to be the same (or at least compatible). I can't cast them both to text, because the range of values don't sort correctly then.
I've tried returning the column numbers instead of the column names - this at least executes, but it ignores the column order (in the function or just executing as a simple statement). For example,
SELECT * FROM some_table ORDER BY 1;

works correctly but
SELECT * FROM some_table ORDER BY CASE WHEN TRUE THEN 1 ELSE 2 END;

does not order by column 1
My work-around would be to do
if column_order='first' then
  (masses of complex stuff)
  SELECT ... ORDER BY thing1
else
  (masses of complex stuff, duplicated)
  SELECT ... ORDER BY thing3
end if;

but that's horrible, and I'm really hoping there's some other way around this, and that I'm currently missing something.
Is there any way to do what I'm trying to do?

Be careful with conditional ordering, it can create bad query plans sometimes forcing table scanning. If the filtering and joining clauses, or just the size of the actual data, mean that you have a small number of rows to sort at the end then this is not an issue and something like this will work:
ORDER BY CASE WHEN ordering_column = 'id'        THEN id        ELSE NULL END
       , CASE WHEN ordering_column = 'timestamp' THEN timestamp ELSE NULL END

In fact it will work anyway, it might just be inefficient for a large amount of data.
For larger outputs your workaround may be more efficient as it may be able to make better use of indexes for the sorting. Another alternative is to have two procedures, one for each sort, and either call each as needed or have your main procedure call the others depending on the sort order it is passed in the parameter. Depending on how postgres handles cached query plans for procedures this may[†] avoid issues of a cached plan for one case being used for another where it is vastly less efficient.
[†] I'm no expert at all on pg's internals, but single "kitchen sink" procedures and queries with conditional sorts etc. can be a performance killer in SQL Server for this sort of reason.


Returning Multiple Result-Sets or Single XML Blods I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with result-sets that should be avoided.

It would appear that my options are to use result-sets or construct an xml representation of the complex objects and return them using SQL-XML channels (this is to avoid truncation of the blogs, by ADO clients). Does anyone have any experience with the performance and/or relative efficiencies of these choices and maybe provide some insights of the bad and good of each?

For additional context...

Each result-set would have no more than 30 columns, with most around 10
Column data is mostly integers (1 to 8 bytes) and some strings between 50 and 2000 characters
During peak load we expect between 5-9 queries per second for the SPROCs in question although there is one Use Case that could be 10’s per second
Denormalizing to a flatter view is not really a reasonable option insofar as we can see

I prefer multiple resultsets because its faster and uses less memory and much easier to deal with.

But everything depends on consumer of the data - for example if it can receive only XML - you have no choice 8-)


Why is my select statement so slow? SELECT name FROM sys.databases
WHERE
name
not in
('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC')
AND
CASE WHEN state_desc = 'ONLINE'
THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U')
END IS NOT NULL

I'm using the above to populate a combobox in a c# winforms project. -The reason I'm filtering out databases that don't include the table is for security purposes.

I added the WHERE clause as before I did that it was taking up to 21 seconds!?
(This is on a local dev machine with 4 databases(not including system DB's), only one of which has the table I was looking for.)

Now that I have added the where clause it only takes nine seconds.

I can't have it running this slowly, as the program I am writing is going to be in use on machines that can have as many as 20 databases on them. -All with around 35 tables in.
(These aren't servers, these are Support Monkey Machines!)

From the Actual Execution Plan I can see that there are three Clustered Index Seek's taking up 22%, 24% and 48%. -Because this is a 'system' query is there anything I can actually do about that?!

And finally, the bombshell to make this a tad bit harder:
I have no control over the indexes of the databases and I can't change them in anyway, shape or form.

The query is pretty simple and ought to run fast enough.

My guess is that you have a long running transaction performing DDL in one of the databases and your query is getting blocked waiting on a lock.

Unfortunately the metadata functions such as OBJECT_ID don't take account of the transaction isolation level of the outer transaction so even setting the isolation level to allow dirty reads wouldn't help.

Can you try this alternative method of doing the same operation and report back? (If still slow try adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to the beginning)

DECLARE @Databases TABLE(name SYSNAME PRIMARY KEY);

DECLARE @dbname    SYSNAME,
        @tablename SYSNAME = '_LOOK_UP_TABLE'

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT name
    FROM   sys.databases
    WHERE  state_desc = 'ONLINE'
           AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                             'ReportServer', 'ReportServerTempDB', '1303VISBOC' )

OPEN @C1;

FETCH NEXT FROM @C1 INTO @dbname;

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @nsql NVARCHAR(max) = 'USE ' + QUOTENAME(@dbname) + ';
                                     SELECT @dbname
                                     FROM sys.tables WITH (READPAST)
                                     WHERE schema_id=1
                                           AND Name = @tablename';

      INSERT INTO @Databases
      EXEC sp_executesql
        @nsql,
        N'@dbname SYSNAME, @tablename SYSNAME',
        @dbname,
        @tablename

      FETCH NEXT FROM @C1 INTO @dbname;
  END

SELECT name
FROM @Databases


Where to download Sql Server 2008 R2? I need to do a fresh install in a lab environment. Where's a good place to download it?

If you want it for a lab environment you can get a preconfigured VHD with an evaluation edition of 2008 R2 installed. It is valid for 180 days:

SQL Server 2008 R2 Standard on Windows Server 2008 R2 SP1 Standard (x64)

Also, you can get it if you or one of your colleagues have a MSDN subscription. If the subscription is not yours you can download it from there and use it for the 180 day evaluation period.


CREATE DATABASE permission denied in database 'master'. unable to get the permission I am making a database in sql server and it shows an error that "CREATE DATABASE permission denied in database 'master'"
I am using the administrator log in itself.
What i need to do?
Please suggest a fix!

The user must be a member of dbcreator server role for user to have enough permissions to create a database.

You can execute the following statement to make a user member of dbcreator server role.

EXEC master..sp_addsrvrolemember @loginame = N'Shubhankar', @rolename = N'dbcreator'
GO

Members of sysadmin role are by default members of dbcreator server role therefore they can create databases too.


Oracle out-of-place upgrade on same host: impdp issues I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another instance on the same host, it's trying to recreate the same directory structures. The beginning of the import log looks like this:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@NEWINSTANCE full=Y directory=bak_dir

dumpfile=dbexp.dmp
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-31684: Object type TABLESPACE:"USERS" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-01119: error in creating database file 'D:\ORACLE\ORADATA\DB1\TBLSP_73_DATA.DBF'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
Failing sql is:
CREATE TABLESPACE "TBLSP_73_DATA" DATAFILE 'D:\ORACLE\ORADATA\DB1\TBLSP_73_DATA.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT

It appears that it's attempting to use the same paths as the source database. How can I get the datapump import to point to a new path like D:\ORACLE\ORADATA\DB2...

Check the Oracle Utilities documentation.  When using Datapump tools, you can reset paths using the REMAP_DATAFILE parameter for import jobs.

Also, insure your OS session variables reflect the new DB.  This is often over-looked when working with a new DB on a server that already has a DB working on it.


Distinct Children rows ordered by a column of Parent I've got a query where I want to retrieve distinct Child rows, but ordered by a column of Parent. If I do the following, I get an error because the column specified in the ORDER BY is not included in the DISTINCT list:

SELECT
    DISTINCT c.foo, c.bar
    FROM Parent p
    JOIN Child c on c.parentId = p.id
    ORDER BY p.createdDate

However, if I add p.createdDate to the select list, I will lose distinctness of Child rows, as p.createdDate makes them all distinct.

If I use a CTE or subquery to first do the ordering, and then select distinct rows from that, the outer query doesn't guarantee that it will maintain the order of the inner/cte query.

Is there a way to achieve this?

You need to GROUP BY instead of DISTINCT (the effect is the same) and you need to aggregate the column you want to use for sort order. In this case I used MIN, but you can use whatever makes sense here.

SELECT c.foo, c.bar
FROM Parent p
JOIN Child c on c.parentId = p.id
GROUP BY c.foo, c.bar
ORDER BY MIN(p.createdDate);

Please note that, since you're ordering by a column of the parent table, you may have multiple rows from the child table that have the same value for Parent.createdDate, so the sort order within the same createdDate will be non predictable. You need to add at least one column from the child table to make the sort order predictable.


Selecting top 10 from indexed field of a big table takes too long I have a table with 165M records like this:

Performance
   id        integer
   installs  integer
   hour      timestamp without time zone

I also have an index on hour:

CREATE INDEX hour_idx
  ON performance
  USING btree
  (hour DESC NULLS LAST);

However, select top 10 records ordered by hour takes 6 minutes!

EXPLAIN ANALYZE  select hour from performance order by hour desc limit 10

Returns

Limit  (cost=7952135.23..7952135.25 rows=10 width=8) (actual time=376313.958..376313.964 rows=10 loops=1)
  ->  Sort  (cost=7952135.23..8368461.00 rows=166530310 width=8) (actual time=376313.957..376313.960 rows=10 loops=1)
        Sort Key: hour
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Seq Scan on performance  (cost=0.00..4353475.10 rows=166530310 width=8) (actual time=0.006..327149.828 rows=192330557 loops=1)
Planning time: 0.070 ms
Execution time: 376330.573 ms

Why is it taking so long? If there is an index on date field desc - shouldn't it be super quick to retrieve data?

In your sample code above, the index is explicitly created as NULLS LAST and the query is implicitly running NULLS FIRST (which is the default for ORDER BY .. DESC)  so PostgreSQL would need to re-sort the data if it used the index. As a result the index would actually make the query many times slower than even the (already-slow) table scan.

rds-9.6.5 root@db1=> create table performance (id integer, installs integer, hour timestamp without time zone);
CREATE TABLE
Time: 28.100 ms

rds-9.6.5 root@db1=> with generator as (select generate_series(1,166530) i)
[more] - > insert into performance (
[more] ( >   select
[more] ( >     i id,
[more] ( >     (random()*1000)::integer installs,
[more] ( >     (now() - make_interval(secs => i))::timestamp installs
[more] ( >   from generator
[more] ( > );
INSERT 0 166530
Time: 244.872 ms

rds-9.6.5 root@db1=> create index hour_idx
[more] - > on performance
[more] - > using btree
[more] - > (hour desc nulls last);
CREATE INDEX
Time: 67.089 ms

rds-9.6.5 root@db1=> vacuum analyze performance;
VACUUM
Time: 43.552 ms

We can add a WHERE clause on the hour column so that using the index becomes a good idea - but notice how we still need to re-sort the data from the index.

rds-9.6.5 root@db1=> explain select hour from performance where hour>now() order by hour desc limit 10;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit  (cost=4.45..4.46 rows=1 width=8)
   ->  Sort  (cost=4.45..4.46 rows=1 width=8)
         Sort Key: hour DESC
         ->  Index Only Scan using hour_idx on performance  (cost=0.42..4.44 rows=1 width=8)
               Index Cond: (hour > now())
(5 rows)

Time: 0.789 ms

If we add an explicit NULLS LAST to your query then it will use the index as expected.

rds-9.6.5 root@db1=> explain select hour from performance order by hour desc NULLS LAST limit 10;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.68 rows=10 width=8)
   ->  Index Only Scan using hour_idx on performance  (cost=0.42..4334.37 rows=166530 width=8)
(2 rows)

Time: 0.526 ms

Alternatively, if we drop the (non-default) NULLS LAST from your index then the query will use it as expected without modification.

rds-9.6.5 root@db1=> drop index hour_idx;
DROP INDEX
Time: 4.124 ms

rds-9.6.5 root@db1=> create index hour_idx
[more] - > on performance
[more] - > using btree
[more] - > (hour desc);
CREATE INDEX
Time: 69.220 ms

rds-9.6.5 root@db1=> explain select hour from performance order by hour desc limit 10;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.68 rows=10 width=8)
   ->  Index Only Scan using hour_idx on performance  (cost=0.42..4334.37 rows=166530 width=8)
(2 rows)

Time: 0.725 ms

Note that you can also drop the DESC from your index; PostgreSQL can scan indexes both forwards and backwards and on single-column indexes it's generally unnecessary to reverse them.  You only need to be careful about having the right combination of order and nulls first/last.

rds-9.6.5 root@db1=> drop index hour_idx;
DROP INDEX
Time: 3.837 ms

rds-9.6.5 root@db1=> create index hour_idx
[more] - > on performance
[more] - > using btree
[more] - > (hour);
CREATE INDEX
Time: 94.815 ms

rds-9.6.5 root@db1=> explain select hour from performance order by hour desc limit 10;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..0.68 rows=10 width=8)
   ->  Index Only Scan Backward using hour_idx on performance  (cost=0.42..4334.37 rows=166530 width=8)
(2 rows)

Time: 0.740 ms


Inconsistent answers to simple queries I receive results that I consider inconsistent to simple queries.

-- This returns 3 rows
select * from persons
where person_id in (12,13,14)

-- This returns nothing
select *
from documents
where person_id in (12,13,14)

-- This returns nothing !?!?
select *
  from persons
 where persons.person_id not in ( select documents.person_id from documents )

There is a FK on documents.person_id pointing to persons.person_id

Am I missing something?
This is my @@version:

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)   Jan  6 2017 14:24:37   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

As @Scott Hodgin has pointed out on his comments there is some NULL value for person_id in the documents table.

You can easily avoid it by adding where person_id is not null

create table persons (personID int);
create table documents (documentID int, personID int);

insert into persons values(10),(11),(12),(13),(14),(15);
insert into documents values (1,10),(1,11),(1,null),(2,null),(3,11),(3,15);
GO

12 rows affected

select * from persons where personID in (12,13,14);
GO

| personID |
| -------: |
|       12 |
|       13 |
|       14 |

select * from documents where personID in (12,13,14);
GO

documentID | personID
---------: | -------:

select personID from documents
GO

| personID |
| -------: |
|       10 |
|       11 |
|     null |
|     null |
|       11 |
|       15 |

select *
from   persons
where  personID not in (select personID from documents);
GO

| personID |
| -------: |

select *
from   persons
where  personID not in (select personID from documents where personID is not null);
GO

| personID |
| -------: |
|       12 |
|       13 |
|       14 |

dbfiddle here


Scripting Creation Of Database Diagrams Is it possible to script the creation of a database diagram using T-SQL?

If not, what is the Database Diagram Designer doing when you use it to save / edit a diagram (is it using PowerShell or some other language to save and load these type of objects)?

I ask because I'm looking to automate the creation of database diagrams.
I have a tool one of my developers wrote (in C#) which automatically creates tables and their foreign key relationships based on the responses of some web service calls it makes to our vendors' data.

The Database Diagram objects are indeed created using T-SQL.  You can see what SSMS does when creating, editing, and saving diagrams by running a T-SQL trace against your development SQL Server while using the Database Diagram tools.

A database that has the Database Diagram objects enabled will contain a dbo.sysdiagrams table.

Many properties use by the Database Diagram system are stored in Extended Properties.  These can be seen looking at sys.extended_properties in the diagram-enabled database.

The following objects are also engaged in the Database Diagram system:

dbo.sp_upgraddiagrams
dbo.sysdiagrams
dbo.sp_helpdiagrams
dbo.sp_helpdiagramdefinition
dbo.sp_creatediagram
dbo.sp_renamediagram
dbo.sp_alterdiagram
dbo.sp_dropdiagram

Hopefully the above details will get you on the road to reverse-engineering the Database Diagram system, however be aware the actual diagrams are stored in a binary format that is, as far as I can tell, not documented.


Can SQL Server (2012-2014) be configured to run "on-demand?" I am very, very new to SQL, and am having difficulty configuring the server appropriately. All I have to work with for now is a small copy of the database schema of the company I work for, with randomized data in it. Running a small server with just a few tables that I only occasionally query to test applications, can I configure SQL to use resources "on-demand" (perhaps an agent that listens for queries, and does only this, not caching data in memory) or to use minimal system resources?

I find that, even with only 15 tables totaling 250MB in storage, SQL is always using at least 2GB of memory on a machine with only 8GB to spare.

I did look for similar questions here on StackExchange, but only found debates about how much memory to devote to the OS as opposed to the database on a server machine.

The best fit for your requirements is probably SQL Server Express LocalDB (2012 and later).

It has a very small footprint, does not create a service, starts on demand, aggressively releases resources, and automatically shuts down a few minutes after the last connection to it is closed. LocalDB is specifically targeted to developers testing on a local machine. Things like external connectivity and SQL Agent are not available.

More information:

Introducing LocalDB, an improved SQL Express
SQL Express v LocalDB v SQL Compact Edition
Getting Started with SQL Server 2012 Express LocalDB


How to adjust a hierarchical query in Oracle so then the parent and the id can come in sequence? I have a view with a hierarchical query like the print screen below. I'm trying to find a way of getting the ids and parent ids (columns cfg_info_id and cfg_parent) as sequential numbers. Is there a way of doing this just adjusting my sql? Or do I need to create a pipelined function to manipulate these values?

Here is the sql (simplified):

select
       ci.CONFIG_ID, rownum, ci.CONFIG_INFO_ID as cfg_info_id,
       lpad(' ',(level-1)*3,' ')||ci.key as CFG_KEY,
       ci.value AS CFG_VALUE, ci.parent AS CFG_PARENT,
       level AS CFG_LEVEL
from CONFIGS_INFO ci
where config_id = 16
start with ci.parent is null
connect by prior ci.CONFIG_INFO_ID = ci.PARENT;

I can use rownum to get the cfg_info_id as a sequence. But do I have some way of changing the cfg_parent accordingly?

Create sample data:

create table t1
(
  config_id number,
  config_info_id number,
  parent number
);

insert into t1 values (1, 1, null);
insert into t1 values (16, 3983, null);
insert into t1 values (16, 3984, null);
insert into t1 values (16, 3985, null);
insert into t1 values (16, 3986, 3985);
insert into t1 values (16, 3987, 3985);
insert into t1 values (16, 3988, null);
insert into t1 values (16, 3989, 3988);
insert into t1 values (16, 3990, 3988);
commit;

Generate new id values for CONFIG_INFO_ID where config_id = 16:

with new_ids as
(
  select rownum as new_id, config_info_id from
  (
    select config_info_id from t1
    where config_id = 16
    order by config_info_id
  )
)
select * from new_ids
;

    NEW_ID  CONFIG_INFO_ID
---------- ---------------
         1            3983
         2            3984
         3            3985
         4            3986
         5            3987
         6            3988
         7            3989
         8            3990

Replace the original CONFIG_INFO_ID and parent values with the generated values:

with new_ids as
(
  select rownum as new_id, config_info_id from
  (
    select config_info_id from t1
    where config_id = 16
    order by config_info_id
  )
)
select
  ci.CONFIG_ID,
  n1.new_id as cfg_info_id,
  n2.new_id  AS CFG_PARENT,
  level AS CFG_LEVEL
from
  t1 ci
  join new_ids n1 on (ci.config_info_id = n1.config_info_id)
  left join new_ids n2 on (ci.parent = n2.config_info_id)
where config_id = 16
start with ci.parent is null
connect by prior ci.config_info_id = ci.parent
;

 CONFIG_ID  CFG_INFO_ID CFG_PARENT  CFG_LEVEL
---------- ------------ ---------- ----------
        16            1     (null)          1
        16            2     (null)          1
        16            3     (null)          1
        16            4          3          2
        16            5          3          2
        16            6     (null)          1
        16            7          6          2
        16            8          6          2


Creating Clustered Index uses all my tempdb space. Why? Table size = 44GB  (no clustered index)

Row Count  = 122016576

Disk space allocated to tempdb = 200MB

I wanted to create a clustered index for the table due to slow query times so I ran the following query:

USE [myDatabase]
GO

CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[myTable]
(
    [myColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After 50 minutes of executing, my server's tempdb files (4 total) ran out of space and the database was sent into "In Recovery" status for about 10 minutes.  It's up and running now but I'm confused as to why tempdb was utilized so much when I specifically said SORT_IN_TEMPDB = OFF.

Does anyone know why this operation utilized so much tempdb space?

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb.  You also had set ONLINE = ON.

Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data.  This is described here:

http://technet.microsoft.com/en-us/library/ms179542.aspx

In part it says: "Online index operations use row versioning to isolate the index operation from the effects of modifications made by other transactions. ... Concurrent user update and delete operations during online index operations require space for version records in tempdb."

EDIT: Actually a 200 MB tempdb seems quite small for a database with a 44 GB table.


How do I count the number of columns in each table? I want to write a script to listing the tables in my database with the number of columns in that table.

Like this:

table name      number
---------       --------
table1            1
table2            13
table3            2
table4            6

You can look at columns in sys.columns:

  Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:

Table-valued assembly functions (FT)
Inline table-valued SQL functions (IF)
Internal tables (IT)
System tables (S)
Table-valued SQL functions (TF)
User tables (U)
Views (V)

For columns count in tables, this query can be used:

SELECT [Schema] = s.name
    , [Table] = t.name
    , number = COUNT(*)
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.name, s.name

sys.tables can be replaced by sys.views for count in views
sys.objects can also be used with a WHERE clause on required type(s):

SELECT [Schema] = s.name
    , [Table] = o.name
    , number = COUNT(*)
    , o.type_desc
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE type IN (
    'U'     --> U = Table (user-defined)
    , 'V'   --> V = View
    , ...)
GROUP BY o.name, s.name, o.type_desc;

This would work too although it is preferable (read The case against INFORMATION_SCHEMA views from Aaron Bertrand) to use the first query:

SELECT TABLE_SCHEMA
    , TABLE_NAME
    , number = COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_SCHEMA, TABLE_NAME;