Forwarded Records, effective way to fix them I'm dealing with an issue in SQL Server databases of an application. This application is used for Healthcare and I only maintain the databases, I do not create the application.

Clients are facing the problem of forwarded records on their tables after a period of time. I've created a stored procedure to fix these forwarded records by creating a clustered index and dropping it again, effectivly reorganizing the table and fixing the forwarded records.

The way my current script works is as follows:

Fill a table (#Forwarded_Records) with information from
sys.dm_db_index_physical_stats, sys.indexes and sys.columns. This will fill in the table with tables which contain forwarded records and information about their indexes including offline/online availability.
Once the table is filled, the stored procedure will check if there is still time in the window to do other jobs. If there is it will start with the first record from the forwarded_records table and start creating/dropping an clustered index.
Once all the records have been fixed, it will start to refill the forwarded_records table  and search for new tables containing forwarded records. This will start the cycle again.

I'm facing the following problems:

Healthcare only gives small maintenance windows per day and the database must be performing good/always available 24/7
The filling of the forwarded_records table can take up to 45 minutes(way too long). This is also the case if there are no forwarded records to be found.
The databases can consist out of 4000+ tables. Some having over millions rows. I prefer first looking for forwarded records instead of applying the job to all tables(including ones which don't contain any forwarded records)

Now what is my question?

How can I improve this process so it will be more effective. Currently it is very effective in its first cycle and fixes all the forwarded records. But starting it's second cycle it will just spend more time on "finding" forwarded records than fixing them(it takes 45 minutes to find them, and only 2 minutes to fix them).

Are there any better methods for my case? Should I take the 45 minutes per day to find them for granted or am I better off doing all tables instead of searching for tables which actually need fixing.

Thank you for your help.

(First post on StackExchange, tried to be as constructive as possible. Please help if this question needs editing)

SQL Server does not keep a "statistic" around for the number of forwarded records. To return the number it has to access the physical table pages. If the table is large, that can take a while.

That said, you can query sys.dm_db_index_physical_stats in SAMPLED mode. This will give you an estimate for the number of forwarded records based on a 1% sample of the base table.

Alternatively, you could correlate the number of updates (see sys.dm_db_index_usage_stats) with the number of forwarded records on each table. Say if every 10th update causes a forward, then you can check if the table has crossed your threshold by looking if the number of executed updates is 10 times higher then your threshold. Querying sys.dm_db_index_usage_stats is instant. However, it accumulates starting fresh with each server restart.

I would still regularly (once a month maybe) run the sys.dm_db_index_physical_stats, to make sure that the ratio is still accurate.

To actually get rid of the forwarded records you can use ALTER TABLE dbo.test REBUILD WITH(ONLINE=ON); though this will also rebuild all nonclustered indexes on a heap table. That should be a lot faster than creating and dropping a clustered index, which rebuilds all nonclustered indexes twice. (The WITH(ONLINE=ON) clause requires Enterprise Edition.)

Now, the bigger question you have to ask yourself is, if you really should have 4000 heaps in a database. In general a well chosen clustered index will improve performance of the system as a whole. The storage structure "heap" is usually only recommended for tables with a high number of inserts, and very few selects, updates or deletes.

In a typical OLTP environment that would be only true for log tables. Try to figure out if you could add clustered indexes to your tables, and your forwarding pain will go away. (Then however you need to deal with fragmentation, but there are some good (and free) solutions on the market already; e.g. Ola Hallengren's scripts).


Is it possible to convert .trn (transaction log) file to human readable form I have used below SQL statement to get the transaction information done on 2015-10-26, but the information not in human readable form.

**

SELECT * FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'D:\Inventrax369\RT\TransactionFilesForMani\FalconWMS_RT_Release_27102015.trn',
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT, DEFAULT)
where convert(date,[Begin Time]) = convert(date,'2015-10-26')

**

Looking forward to receive vital suggestions.

I've used 3rd party tools in the past when it got too difficult to track down the log file modifications, especially as the file is minimally logged to mostly record the page modifications.  Here is a tool I've used, you can search for others if you like.  It has a functional free trial.  Sometimes I install these on a VM then remove it as it does tend to embed itself sometimes.

http://www.apexsql.com/sql_tools_log.aspx


SQL Server database backup to store only the last 7 backups in a file I want to create a scheduled task that will run (every night) a SQL script to fully backup my database X to a file. I want that file to store up to 7 full backups. The way I tried doing this is using the following code:

SELECT @SQL = '
BACKUP DATABASE [X]
TO  DISK = N''\file.bak''
WITH NOFORMAT, NOINIT, NAME = N''name'', RETAINDAYS = 7
'

But this doesn't overwrite/delete full backups contained on that file that are older than seven days.

Any ideas? Suggestions for alternatives?

Thanks

I would recommend you to study and implement  Ola Hallengren's SQL Server Backup solution. Its free and is widely tested and recognized in the community.

e.g :

Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script will give you a good start.


Use transaction logs multiple times Every night a full backup is made of all the user databases. Every 15 minutes, transaction logs are created. Today i succesfully restored a database with the use of transaction logs. I set the time (08:30) and set it with the option WITH REPLACE, and pressed the Ok button.

It looked like this.

Afterwards (a hour later) i wanted to restore it again. But i saw no full backup and transaction logs. (transaction logs are still created on the server)

Any suggestions for restoring databases with transaction logs for multiple times? Or is this not an option?

Full backup is not showing up because it has already been restored and no new latest info is there in msdb to reflect the same.
Moreover, once msdb is updated with the backup info, it lets you avail with such info and in a manner of chain as followed in as LSN.

However, you can do this manually by running below script on a specific database to retrieve backup information for. The process will then find the latest Full backup and all transaction log backups that have occurred after this full backup.

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
DECLARE @max_family int

-- set database to be used
SET @databaseName = 'enterDatabaseNameHere'

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM  msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM  msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

select  @max_family=max(family_sequence_number)
FROM    msdb.dbo.backupset b,
    msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
AND     b.database_name = @databaseName
AND     b.backup_set_id = @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT  backup_set_id, family_sequence_number,
    case mf.family_sequence_number
        when 1 then 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''
        else '                         , DISK = '''
    end
        + mf.physical_device_name +
    case mf.family_sequence_number
        when @max_family then ''' WITH NORECOVERY'
        else ''''
    end
FROM    msdb.dbo.backupset b,
    msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
AND     b.database_name = @databaseName
AND     b.backup_set_id = @backup_set_id_start
UNION
SELECT  backup_set_id, 1,
    'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,
    msdb.dbo.backupmediafamily mf
WHERE   b.media_set_id = mf.media_set_id
AND     b.database_name = @databaseName
AND     b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
AND     b.type = 'L'
UNION
SELECT  999999999 AS backup_set_id, 1,
    'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id, 2

Also, you can go through the link here for more option to get the same.


How to specify not null contraints for the fields of composite types in postgres Let's say I would like to have a composite type for address, like:
create type address as (
  city text,
  address_line text,
  zip_code int
);

And to make data integrity better, I don't want to allow NULLs to be members of city, address_line, or zip_code. So I would like to have a not null constraint for those fields.
Creating domain checks isn't working for me. So this code produces error:
create domain address_domain as address
check (
  value.city is not null and
  value.address_line is not null and
  value.zip_code is not null
);

You might say: "Well, why won't you store address as three columns, and add contraints to the fields?". And I will answer with that I would like to have ability to make address itself nullable, but if address is present, all of it's fields should be present as well. Something like this:
create table companies (
  id serial primary key,
  name text not null,
  headquaters address -- this one can be null tho
)

The correct syntax for a check constraint for a composite type would look like this:
create domain address_domain as address
check (
  (value).city is not null and
  (value).address_line is not null and
  (value).zip_code is not null
);

Let's check:
melkij=> create table test_address_domain (a address_domain);
CREATE TABLE
melkij=> insert into test_address_domain values (('foo', 'bar', 11));
INSERT 0 1
melkij=> insert into test_address_domain values (('foo', 'bar', null)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (('foo', null, 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values ((null, 'bar', 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (null); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"

But pay attention to the last line. It seems that this is not what you want. Try allow explicitly:
melkij=*> create domain address_domain2 as address
check (
  value is null or (
  (value).city is not null and
  (value).address_line is not null and
  (value).zip_code is not null
));
CREATE DOMAIN
melkij=*> create table test_address_domain2 (a address_domain2);
CREATE TABLE
melkij=*> insert into test_address_domain2 values (null); -- works now
INSERT 0 1


SQL Server 2008 R2 Standard with SSIS Enterprise I currently have SQL server 2008 R2 Standard installed with SSIS. I need a SSIS feature that is only available with the enterprise edition. Is it possible to upgrade SSIS only to enterprise while keeping the database install as is (standard)?

No. The only way to license SSIS enterprise features is to get SQL Server Enterprise Edition.  Your other option is to implement the functionality in some other way such as a stored procedure, TVF, or .Net code embedded in the SSIS package (e.g. a script task).


Ready-to-Use Database models example Where can I find ready-to-use database models ?

I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex and real world.

David Hay's

Enterprise Model Patterns. This is a beast of a book, but has some great patterns.
Conventions of Thought. More stuff on MRP.
A Meta-Data Map . Haven't read this one.

Len Silverston's

Data Model Resource Book Vol. 1. Your main data model patterns.
Data Model Resource Book Vol. 2. Case studies by industry.
Data Model Resource Book Vol. 3. A deeper explanation of Vol 1.

Michael Blaha's Patterns of Data Modeling. This one has some interesting temporal, graph, and tree models.

Martin Fowler's Analysis Patterns. This one skims some of the other patterns, but gives accounting a solid treatment.

They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on safaribooksonline.

Also, OASIS's Universal Business Language, schemas


LogShipping and clock change I have a log shipping process running. Today I realized that the log files must have been overwritten during the clock change period yesterday, where the clock was set back from 3 AM to 2 AM. So I am concerned that something may be wrong.
I looked at the files that have been backed up and found out they are stored with a filename in UTC time. The period of 01 AM - 02 AM UTC is missing here.

The same output is in log_shipping_monitor_history_detail's output: Here 01AM-02AM are missing as well while the log_time colum is ongoing wihtout any duplicates.

I do backup, copy and restore in 15 minute periods.

The genreal status (master.dbo.sp_help_log_shipping_monitor) of the log shipping process shows 0 (OK).

Does logshipping consider the clock change or do I have to take any actions?

Log shipping restores (like all restores) are based on LSN. Created .trn files should not be affected.   Also, is this for Daylight savings in US, as that is next weekend, not yesterday.  If files have gone missing, there is a different reason for it.  Missing from Primary or Secondary?


SQL 2008 Cluster Install, "Cluster shared disk Availability Check" I am trying to setup a new sql server 2008 cluster, on windows 2012R2, and the installer is failing on the cluster shaed disk availability check.  I have verified that there are 5 disks assigned to "Available Storage" when viewed in the fail over cluster manager.

Some background, this is my second attempt to install sql server on this cluster.  The first time, the cluster object was unable to create the new computer object during the cluster installation.  This caused the installer to fail do to lack of permissions.  I have since resolved this, and have run "Remove node from cluster" to uninstall sql server from the node.  I am now trying again to run the installer.

About the environment,
OS: windows 2012R2
SQL Version/Edition: 2008/Enterprise
I am running the installer from the current cluster host(node1), and all storage is owned by node1.  This includes the quorum, as well as the 5 disks assigned to the available storage group.  Both cluster nodes are up and available, and accessible either through the node names, or through the windows cluster name.

There are no cluster validation warnings that I know of, but I have asked the windows admin to rerun the validation tool to confirm that, that is still the case.  SQL 2008 is required by the front end application (I pushed for at least 2012, but was told it was a no go)

Well, it looks like when the first sql server install ran, it also configured the file server services, which were still trying to hold the disk and the network name.  Odd thing is, the disks where all showing 0 dependencies.  This probably could have been fixed if we opened a case with microsoft, maybe cleaned up some registry entries, etc, but the guys from out windows team wanted to just rebuild from bare metal, as it would be quicker and easier.  I was on board with that, so that is the solution I am implementing


"Column check constraint cannot reference other columns" I get the error "Column check constraint cannot reference other columns" with the table below. How can I solve this?

CREATE TABLE SALES_ORDER
(
   S_ORDER_NO   VARCHAR2(6) PRIMARY KEY CHECK (S_ORDER_NO LIKE 'O'),
   S_ORDER_DATE DATE,
   DELY_ADDR    VARCHAR2(25),
   SALESMAN_NO  VARCHAR2(6) REFERENCES SALESMAN_MASTER(SALESMAN_NO),
   DELY_TYPE    CHAT(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
   BILLED_YN    CHAT(1),
   DELY_DATE    DATE CHECK (DELY_DATE > S_ORDER_DATE),
   ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('PROCESS', 'FULFILLED', 'BLOCKORDER', 'CANCELED'))
);

Your syntax is not correct for row level CHECK constraints.

  CHECK (DELY_DATE > S_ORDER_DATE) needs to be at the table level.

  -- from Martin Smith's comments

Within the CREATE TABLE statement:

CREATE TABLE SALES_ORDER
(
   S_ORDER_NO   VARCHAR2(6) PRIMARY KEY CHECK (S_ORDER_NO LIKE 'O'),
   S_ORDER_DATE DATE,
   DELY_ADDR    VARCHAR2(25),
   SALESMAN_NO  VARCHAR2(6) REFERENCES SALESMAN_MASTER(SALESMAN_NO),
   DELY_TYPE    CHAR(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
   BILLED_YN    CHAR(1),
   DELY_DATE    DATE,
   ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('PROCESS', 'FULFILLED', 'BLOCKORDER', 'CANCELED')),
   constraint <CNAME> CHECK (DELY_DATE > S_ORDER_DATE)
);

Outside of the CREATE TABLE statement:

CREATE TABLE SALES_ORDER
(
   S_ORDER_NO   VARCHAR2(6) PRIMARY KEY CHECK (S_ORDER_NO LIKE 'O'),
   S_ORDER_DATE DATE,
   DELY_ADDR    VARCHAR2(25),
   SALESMAN_NO  VARCHAR2(6) REFERENCES SALESMAN_MASTER(SALESMAN_NO),
   DELY_TYPE    CHAR(1) DEFAULT 'F' CHECK (DELY_TYPE IN ('P', 'F')),
   BILLED_YN    CHAR(1),
   DELY_DATE    DATE,
   ORDER_STATUS VARCHAR2(10) CHECK (ORDER_STATUS IN ('PROCESS', 'FULFILLED', 'BLOCKORDER', 'CANCELED'))
);

-- from Lennart's comments
ALTER TABLE SALES_ORDER ADD CONSTRAINT <CNAME> CHECK (DELY_DATE > S_ORDER_DATE);


Availability Group environment clone I've been assigned a project where we have an active secondary environment that I'm confident that Availability Groups would address but have some questions that I hope someone can answer for me.

The situation :

I'm trying to resolve isn't the standard business continuity or disaster recovery but to expedite the release of an upgraded (schema changed) environment.

Currently the databases/UI/cubes that support our customer facing systems get an new release every 8 weeks and on that release weekend it will take 24 hours to apply new schema, apply data backfills, and to do system maintenance like rebuilding index and restarting servers.

The hope is to have a duplicate environment that we could update prior to the release date and 'swap' over minimizing the downtime to our customers. I'm primarily concerned with having a form of replicated databases that stay current with regards to data but that the secondary could have schema modifications made to it and I'm not sure if Availability Groups are the best answer for that.

Besides re-architecting the environment is there another technology I should consider to address my need to have 'hot swap' secondary environment that can be upgraded?

Since you mentioned about Availability Groups, this means that you are using SQL Server Enterprise edition (and possibly SQL Server 2012 or 2014).

You can use database snapshots, but in our case there were lot of dependencies on different components and so using database snapshot was ruled out.

Below will require a bit of more planning and testing.

Blue-Green Concept :

The gist of Blue-Green Concept is to divide your production into 2 environments and they are identical all times (data synchronization) wherein

The Blue (Current) will have the current version of the schema/build or product and will be your "LIVE" environment.
At the same time Green will be your staging/testing environment wherein you will upgrade your schema/build or product to the NEXT release, do a full regression test and get signed off by your business users. Once happy, during a cut-over period, you will promote the Green to be your "LIVE" environment and demote the Blue to be a preprod/staging or testing for the next release.

This way, you have a very less downtime and the risk of deployment failure on a live system (which is in maintenance window, since you are doing upgrade) will be highly minimized. Also, following the Blue-Green approach, you will be oscillating between LIVE and PREVIOUS version which will be staging for the next version.

Again, this will require more hardware/licensing as well as planning and testing.

Most of the steps can be automated using DACPACs and PowerShell. Also, if you are installing multiple instances on one server, make sure to re-balance the Memory settings when switching between Blue and Green. The LIVE environment gets more memory than the Passive environment.

In my current environment, we have implemented Blue/Green Model for Agile Code Deployment that allows us to promote code every 2 weeks with ample amount of time for testing and business sign-off. Also, its a breeze to rollback in case something goes horribly wrong. We have automated majority of the deployment stuff using Dacpacs and PowerShell.

(Image source)

Also refer to Grant Fritchey's article on Rollback and Recovery Troubleshooting; Challenges and Strategies


Using the name of a table contained in a column of another table? I have a table called MyTable with varchar columns named SourceID and ReferenceID, where SourceID contains a table name and ReferenceID contains a key value for the source table.
Is it possible in a query to refer to the source table's row assuming I know the name of the key column that matches the value in RefrenceID?
Sample:
SELECT
    *
FROM
    MyTable as MT
INNER JOIN MT.SourceID as Src ON MT.ReferenceID = Src.InstanceID

This doesn't work, obviously.  But is there a way to do this as opposed to dynamically building the SQL statement using the values of SourceID and ReferenceID and then executing the query?  More specifically, is there a way to make MyTable.SourceID in the example above be usable within the JOIN?
Further considerations
I found this article, and it has a trick like follows:
select
  E.EmployeeName, coalesce(s.store,o.office) as Location
from
  Employees E
left outer join
  Stores S on ...
left outer join
  Offices O on ...
where
  O.Office is not null OR S.Store is not null

The WHERE clause simply helps the outer joins behave like INNER JOINs.  This would work well enough if there were a limited number of table options and they all had UNIQUE constraints.  This gets me part of the way there but but I really want is to have the table names in the JOINs to be based on a column in Employees.
I haven't given up yet. Any and all help is greatly appreciated.

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.


One table with many rows or many tables with a few rows I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at physical level? How many space will use many tables instead of one table?  I'm thinking in how Wordpress handling the multiblog feature because I'm starting a new project and I'm looking for the best approach.

Assuming that you are talking about tables containing the same kinds of entities, you typically want to have one table.

You would not have any performance differences and a whole lot of management differences between the two approaches, with the single table being easier to manage.  Typically large tables do not have performance overheads compared to smaller versions of the same when properly indexed, since the index seek time grows slowly compared to the row growth.

In a normalized design, you will have different tables for different entities or relations, but as far as partitioning (either supported with a DBMS feature or manually with separate tables), that usually is necessary when you have certain requirements, like granularity of backup or data loading/unloading on a partition basis.


Set user variables from tables being queried in MySQL without locking I have a data warehouse in AWS Aurora, with a pretty traditional Date Dimension (from a DateSK, gives your RelativeDays, Weeks, Months, etc).

First, I have a simple function to convert any date to the DateSK:

CREATE FUNCTION `toDateSK`(`myVarParam` DATE) RETURNS int(11)
BEGIN
    IF myVarParam IS NULL
    THEN SET myVarParam = NOW(); END IF;
    RETURN cast(date_format(myVarParam, '%Y%m%d') AS UNSIGNED);
END

I created a function to get the current date in RelativeDays for some date math.  Function is:

CREATE FUNCTION `thisDay`() RETURNS int(11)
BEGIN
    SET @now := toDateSK(NOW());
    SET @res := (SELECT RelativeDays
                 FROM dimDate
                 WHERE DateSK = @now);
    RETURN @res;
END

I wanted to create a view:

CREATE OR REPLACE VIEW viewFirstActivity AS
SELECT
    da.AccountSK     AS AccountSK,
    min(fa.DateSK)    AS DateSK,
    min(dd.FullDate)  AS FullDate,
    min(dd.RelativeDays) AS RelativeDays
FROM ((factActivity fa JOIN dimAccount da ON ((fa.AccountSK = da.AccountSK))) JOIN dimDate dd ON ((fa.DateSK = dd.DateSK)))
WHERE (fa.ActivatedFlag = TRUE) AND dd.RelativeDays >= thisDay()-7
GROUP BY fa.AccountSK;

However, this causes tons of table waits if used in a way like this (I'm pulling this from memory):

SELECT * FROM viewFirstActivity WHERE AccountSK="12345" AND RelativeDays > thisDay()-3;

(I know this logically doesn't make sense, but I don't have the original query at my fingertips.  The key point is the function was used both in the view, and in the select query.  This seemed to create lots of locking, even though the dimDate table (InnoDB) was only being read).

If I reworked the query as follows (not using a view):

SET @var := thisDay()-3;
SELECT * FROM (
    SELECT
        da.AccountSK     AS AccountSK,
        min(fa.DateSK)    AS DateSK,
        min(dd.FullDate)  AS FullDate,
        min(dd.RelativeDays) AS RelativeDays
    FROM ((factActivity fa JOIN dimAccount da ON ((fa.AccountSK = da.AccountSK))) JOIN dimDate dd ON ((fa.DateSK = dd.DateSK)))
    WHERE (fa.ActivatedFlag = TRUE) AND dd.RelativeDays >= thisDay()-7
    GROUP BY fa.AccountSK) AS tmp
WHERE AccountSK="12345" AND RelativeDays > @var;

This seems to remove the blocking, but now I can't use a convenient view.

I know I'm missing something basic here, but is it easy to explain what it is?

Your first function can be even simpler by noting that

SELECT 0 + DATE('2017-01-18 08:31:38'); -- 20170118

But that is not usable for arithmetic.  Note that this time next month, 20170218, would be "100 days" from now:

For the second one, look into DATEDIFF().  (No need to convert from DATETIME to DATE.)

See also the TO_DAYS() and FROM_DAYS() functions.  Today is 736712 and would work with aritmetic.

I suspect that all of this will eliminate the need for dimDate, thereby solving your stated problem??  If not sufficient, please provide SHOW CREATE TABLE for each table involved; we need to see what indexes you currently have.


Is it normal for tempdb to not give up disk space when it releases things? So my tempdb is getting huge. Like 200+ GB.

USE tempdb  GO  EXEC sp_spaceused

database_name   database_size   unallocated space
tempdb          211668.88 MB    206803.45 MB

It's using a SIMPLE recovery model, which according to Microsoft:

  Set the recovery model of tempdb to SIMPLE. This model automatically
  reclaims log space to keep space requirements small.

But does 'reclaim log space' mean that it still maintains the disk allocation?

Kind of like if I had an influx of patrons at my business so I borrowed a lot across the street to put the extra cars, but then once I no longer need all that space I still reserved that extra parking lot for later (didn't allow anyone else to use it) despite it remaining empty?

If this is normal behavior then I might expect that it's hording space that it thinks it might use again one day, and thus if it had 'infinite' space to expand it would eventually stop growing based on the determined usage patterns?

Or, is it suppose to be giving back disk space to the OS when it releases?

UPDATE

Also found this: http://technet.microsoft.com/en-us/library/ms176037(v=sql.105).aspx

  By default, the tempdb database automatically grows as space is
  required, because the MAXSIZE of the files is set to UNLIMITED.
  Therefore, tempdb can continue growing until space on the disk that
  contains tempdb is exhausted. You can prevent tempdb from growing
  without limits by setting a MAXSIZE for tempdb, however this is not
  recommended. Limiting the size of tempdb may cause the database to run
  out of disk space. This can cause significant disruptions in your
  production environment and can prohibit applications that are running
  from completing operations.

So maybe I've answered my own question here but hopefully someone can clarify: Does it then follow that I should have it on a drive/partition by itself? Then it can grow 'as big as it can' while at the same time not taking up disk space that I want to use for other things? But in that case, I wonder what the difference is between MAXSIZE and actually not having any more space to physically grow.

Tempdb will release space when you restart SQL Server or when you shrink the files, refer to kb307487.  As was mentioned, it is usually a bad practice to use autoshrink.  If you are under space constraints because tempdb wasn't provisioned separate storage, you can shrink the data files using dbcc shrinkfile.  If you don't have to do that, I wouldn't.  It seems you have a workload that needs the space and it having the space already allocated prevents it from having to grow again.  Microsoft has some guidance on how to improve performance of tempdb.


Hours in a month per date range I have a table in mysql with the following data:

| StartDateTime       | FinishDateTime |
| 2016-08-18 10:00:00 | 2016-08-18 11:00:00 |
| 2016-08-18 12:00:00 | 2016-08-18 14:00:00 |
| 2016-08-31 17:00:00 | 2016-09-01 09:00:00 |

What I would like to get as the output is

| Month               | Duration       |
| Aug                 | 10             |
| Sept                | 9              |

I can get the right output when each record is only within 1 month but I am not sure how to go about crossing month boundaries. Can anyone point me in the right direction?

Create a months table:

CREATE TABLE Months (
  MonthDate date
);

and populate it with dates like these:

2016-07-01
2016-08-01
2016-09-01
...

Then you can get your results with a query like this:

SELECT
  m.MonthDate,
  SUM(TIMESTAMPDIFF(HOUR,
                    GREATEST(m.MonthDate                    , t.StartDateTime ),
                    LEAST   (m.MonthDate + INTERVAL 1 MONTHS, t.FinishDateTime)
                   )) AS Duration
FROM
  YourTable AS t
  INNER JOIN Months AS m ON t.StartDateTime  < m.MonthDate + INTERVAL 1 MONTHS
                        AND t.FinishDateTime > m.MonthDate
GROUP BY
  m.MonthDate
;

The output would be in the form:

MonthDate   Duration
----------  --------
2016-08-01  10
2016-09-01  9

Changing the dates to month names would, in my view, constitute a presentation issue but, if necessary, MySQL offers built-in functions like MONTHNAME to do the transformation directly in SQL.

Additional information can be found in the MySQL manual:

TIMESTAMPDIFF()
MONTHNAME()
GREATEST()
LEAST()


How to store an array in a table column? I have multiple contacts for a user and I want to store them in a single column.

uID |  Contact
----| ---------
2   | 1221
    | 1223
----|----------
3   | 1123

How can I do that?

You can create separate tables, like user table and contact table:

user table

uID
---
2

contact table

Contact | uID
1221    | 2
1223    | 2


Mysql compatibility between client and server of different versions How to know what version of mysql client is compatible with what version of the server?

Is there backward or forward compatibility tables from the client and/or server point of view?

For a given version of the server, how to know which clients are compatible, and conversely?

I do not know of any tables or charts that document this. In general, MySQL Client is backwards compatible, and MySQL support  indicates that 5.x clients can communicate with 4.x and 3.x servers.


Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1.

The restore seems successful, but the output gave me 27 ignored errors. Here is the output: http://developers.mapseed.org/posts/restore-output.txt

Should I be concerned? All of my unit tests are passing, but this is a production database, so I want to be sure. They seem to be errors related to permissions, but both my pg_dump and pg_restore commands were run with the postgres user. Although perhaps I'm missing something.

step by step of the dump/restore:

My database is named shareabouts_v2, so on my RDS instance, I ran a create database shareabouts_v2 and create extension postgis.

Then I created a dump file from my current database with the following command:

pg_dump -f backup.dump -Fc -p 5432 -U postgres -h localhost shareabouts_v2

and I restored that dump file into my RDS instance with the following command:

pg_restore -v -U postgres -h <my-RDS-host> -p 5432 -d shareabouts_v2 --disable-triggers -e backup.dump

I have followed numerous posts and best practices around dumping and restoring PostGIS data

Update

These errors were also encountered on this SO answer: https://stackoverflow.com/a/22247401/1884158

UPDATE

I was able to "resolve" the errors with the function declarations by removing them from the dump file, as described here: https://stackoverflow.com/questions/9715434/how-to-exclude-pl-pgsql-functions-in-export/31043192#31043192

where I basically did this:

pg_restore -l -f out.txt backup.dump
# then edit out.txt to delete all of the function definitions and spatial_ref_system:
pg_restore -L out.txt -U my_user -h my_host -p 5432 -d my_db -e --single-transaction backup.dump

But I'm not sure if that will result in other issues. I'll post more updates as I continue...

I managed to resolve these errors by doing 3 things:

1. Removing all definitions that are already defined in the PostGIS extension from the database dump

All of the function, operator family, and operator class definitions are already included in the new database because I installed PostGIS on the new database. This is also true of the spatial_ref_sys table in the public schema. So I avoided restoring this in my database dump using the following approach:

Create a table of contents list from my dump file with the following command: pg_restore -l -f out.txt backup.dump

Then I edited that out.txt to remove the function definitions. After that file was edited, I imported my dump using the updated out.txt as follows:

pg_restore -v -L out.txt -x -U my-user -d my_db -e --single-transaction ms.pgis-dump

I found out about this approach here: https://stackoverflow.com/questions/9715434/how-to-exclude-pl-pgsql-functions-in-export/31043192#31043192

2. All of my database's tables were created with the postgres admin user, so I migrated them to a regular users.

First I created my new user, then update the owner of my database to the new user:

create role my_user with password 'testuser' login;
grant postgres to my_user;
ALTER DATABASE my_db OWNER TO my_user;

Then I had to migrate all of my tables, views, and sequences to have the new user.

# Migrate tables:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select tablename from pg_tables where schemaname = 'public';" my_db` ; do  psql -U my_user -h localhost -p 5432 -c "alter table \"$tbl\" owner to my_user" -d my_db ; done
# views:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';"` ; do  psql -U my_user -h localhost -p 5432 -d my_db -c "alter table \"$tbl\" owner to my_user"; done
# sequences:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select table_name from information_schema.views where table_schema = 'public';"` ; do  psql -U my_user -h localhost -p 5432 -d my_db -c "alter table \"$tbl\" owner to my_user" ; done

I found out about these scripts here:

https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql
https://stackoverflow.com/questions/16959764/reassign-owned-by-for-1-specified-database?noredirect=1&lq=1

3. I had to avoid restoring user privileges:

I was getting error calling REVOKE commands on the existing postgres user. So I added the -x flag to my pg_restore command to prevent restoring privileges to the admin user.

Details were outlined here:

https://stackoverflow.com/questions/37271402/pg-restore-error-role-xxx-does-not-exist


How to check if all elements of an array exists in a table? Lets take an array of ids of size n in the application level. for instance [132,3425,13,13,... 392] where n is bigger than 100k entries. Some of this entries are repeated.

I need to check if all of these registers are contained in a MySQL table containing more than 5M entries.

Now I'm checking one after another, but this process takes a very long time.

How can I make this verification in one single check, or maybe make this more efficient.

I would like to know if all of them exists on the table, and, if possible, know which don't already exists on that table.

I recommend to use the application code to insert the array into a Temporary table, including an identity field so you can store every record, even if it is repeated. (the syntaxis is the same as CREATE TABLE but using CREATE TEMPORARY TABLE):

CREATE TEMPORARY TABLE A (
     id BIGINT NOT NULL AUTO_INCREMENT,
     number BIGINT NOT NULL,
     PRIMARY KEY (id)
);

Then make a query to check/determine if the number exists in the large table. Something like this:

--supose the temporary Table is A and the big table is B and the fields you are comparing ara called NUMBER.

SELECT A.id, A.number as ARRAY_NUMBER,
CASE
    WHEN B.number IS NULL THEN 'not found'
    ELSE 'found'
END as FOUND_IN_TABLE
FROM A LETF OUTER JOIN B ON A.number = B.number

good luck!


Shared and IX locks causing deadlock (Sql server) In my environment, I have 2 tables - Issue and IssuePropertyValues (which has foreign key reference to the Issue table).
Now, we insert values into these 2 tables using the below 2 queries:

INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
INSERT INTO [IssuePropertyValues] SELECT TOP 1 AssetIssueId, ?, ? FROM [Issue] WHERE [AssetId] = ? AND [IssueId] = ? "

These 2 statements are invoked from the java layer using batch update as shown:

issueStmt.executeBatch();
issuePropertiesStmt.executeBatch();

Now when more than 10000 values are being inserted, SQL server is throwing deadlock errors:

  java.sql.BatchUpdateException: Transaction (Process ID 506) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When I studied the deadlock behavior using DBCC TRACEON(1204,-1), I learnt that
the deadlock is for a page lock resource. What I see is that one of the stmts is having a shared lock, and another one an IX(Intent exclusive) lock. But I am not able to understand why this should be causing a deadlock. I understand shared and IX locks are not compatible with each other, but if a deadlock has to happen , it should throw even when few records are inserted.Why is it throwing when 10,000 plus records are inserted?

The DBCC trace snippet is as below

owner-list
2011-12-16 04:02:09.40 spid32s          owner id=process1b1c8ce08 mode=IX spid=372
INSERT INTO [IssuePropertyValues] SELECT TOP 1 AssetIssueId,  @P0 ,  @P1  FROM [Issue] WHERE [AssetId] =  @P2  AND [IssueId] =  @P3

waiter id=process1b1c8c748 mode=S requestType=convert
pagelock fileid=5 pageid=3506256 dbid=7 objectname=Issue mode=IX

Thanks for the explanation. Yes, I shall implement the solution you suggested. I realised that fetching the id using select clause is the culprit.

But I m still not really clear on the cause of the deadlock. What I understand is, when the first insert statement to the issue table executes, it will have an IX lock on the table. Meanwhile, when the second insert statement to IssuePropertyValue table tries to execute, it will try acquiring a shared lock on the Issue table(because of the select clause). But it cannot get the lock as the table is already having an IX lock. But why this should be a deadlock? Once the IX lock is released, second statement should be able to get the Shared lock. Am i correct in my understanding?

It's only the second statement that is waiting on the first stmt to release the lock, but the first statement is not waiting on the second statement . So, ideally its not a deadlock. Does it have something to do with the executeBatch() called from java layer?

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues]
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table.  Another thread is then taking an IX on the issue table (which is blocked).  This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.


Dual table default value? This link clear some of the queries about dual table.

But now I am having following doubt is the execution of two queries on dual table:

Query 1

Select * from dual;

output:

D
-
X

Query2

Select NUll from dual;

output:

D
-

Does Dual contain default value as NULL? If yes why blank is not displayed, as in case of second query.

Phil has an interesting test case. But the dual table has one column and one row with the value x. When you select NULL from dual you are selecting a literal from a table. The literal is not actually in the table. That you can select sysdate from dual does not mean
that sysdate is in the dual table either.

SQL> select sysdate from dual;

SYSDATE
---------------
23-MAY-14


Join multiple table with unique data I have 4 Tables. i.e. crm_entry(id, prefix, firstname, lastname), crm_phone(id, crm_id, extension,value), sale_order(id, crm_id) and purchase_order(id, crm_id)

I want a table like this..

  | id | prefix | firstname | lastname | contact no | sales(count)
  |purchase (count)|

for getting data like this I tried inner join and left join to do so. but when Sales count is more than one.. the contact no gets duplicated in row as sales(count) times. same for purchase count. The query I used is written below:

select
crm_entry.id,
crm_entry.prefix,
crm_entry.firstname,
crm_entry.lastname,
group_concate(concat(crm_phone.extension,'-',crm_phone.value)),
count(sale_order.id),
count(pur_order.id)
from crm_entry
inner join crm_phone on crm_entry.id = crm_phone.crm_id
inner join sale_order on crm_entry.id = sale_order.crm_id
inner join pur_order on crm_entry.id = pur_order.crm_id
group by  crm_entry.id

I want contact number only one time (contact can be multiple but not duplicate).
How do I do?

Ok, finally I got the mid-way to solve this problem.

I created views for contact number, sales order and purchase order along with the crm_entry and then I used these views in my stored procedure with inner joins.
Here the query is :

SELECT
  view_crm_entry_phone.*,
  view_crm_entry_sale.sales,
  view_crm_entry_purchase.purchase
FROM
  view_crm_entry_phone
INNER JOIN
  view_crm_entry_sale ON view_crm_entry_sale.id = view_crm_entry_phone.id
INNER JOIN
  view_crm_entry_purchase ON view_crm_entry_purchase.id = view_crm_entry_phone.id


How do I solve the following error on attaching ldf-less database in SQL Server? Before you write about the dangers of not having backups, etc., please withhold judgement... perhaps I just inherited the problem?

I have an .mdf which which I am trying to attach to SQL Server 8.
I've tried a number of methods.

For example:

CREATE DATABASE [foo] ON ( FILENAME = N'D:\Microsoft SQL Server\YourDataPath\Data\foo.mdf' )
FOR ATTACH_REBUILD_LOG
GO

Gives me the following error:
"Server: Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'ATTACH REBUILD LOG'"

If I run the query with only ATTACH, it errors out because of the missing log file.

Furthermore, I have tried to attach it through the Management Console.

I'm reasonably competent (usually) and as I wrote, I inherited this problem.

The database was completely hosed, I ran the emergency procedures, the CBCC CHECKDB commands, etc, and it appeared to be working (zero errors), however only in Read Only.  I thought it might have been a problem with permission on the log file, and I read that you could detach it and reattach to start a new log file, and when I detached it - I found that I can't reattach it.

My head hurts.  Any help will result in serious karma points. Halp!??!

Update, I did a pretty hack-y thing: I renamed the mdf, created a database with the same name, deleted the new mdf and ldf and then started it back up.  It was found in 'suspect' mode.

So I did this:

USE [master]
GO

EXECUTE sp_configure 'allow updates',1
RECONFIGURE with OVERRIDE
GO

UPDATE sysdatabases
SET status = 32768
WHERE name = 'foo'
GO

Then this:

USE [master]
DBCC CHECKDB('foo')
GO

Finally this:

USE [master]
GO

ALTER DATABASE [dbname] SET MULTI_USER
GO

UPDATE sysdatabases
SET status = 0
WHERE name = 'foo'
GO

EXECUTE sp_configure 'allow updates',0
RECONFIGURE with OVERRIDE
GO

It appears to be back online, I can run SELECT statements and the database appears to be reconnected, however there is no ldf file and if I try to do an INSERT or an UPDATE the server bonks.

I tried to create a table and it says:

'STUFF' table
- Unable to create table.
Invalid transaction state: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not run BEGIN TRANSACTION in database 'foo' because the database is in bypass recovery mode.

I ran:

ALTER DATABASE foo SET MULTI_USER WITH ROLLBACK IMMEDIATE

Still no dice.

EDIT; Version 8, really? That is SQL 2000? I fear that is far too out of support.

I did get a call 10 years ago about a corrupt ldf file in SQL 2000 and fixed it with an undocumented & unsupported command.

I don't remember what command I used, but take a look here
https://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/

Possibly one of these will do it, you have to see if anything is left on the google.

  DBCC dbrecover (dbname [, IgnoreErrors])
  DBCC dbrepair (‘dbname’, DROPDB [, NOINIT])

For later versions:

    CREATE DATABASE TestDb ON
    (FILENAME = N'C:\Program Files\....\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH_REBUILD_LOG

or

    EXEC sp_attach_single_file_db @dbname='TestDb',
    @physname=N'C:\....\MSSQL\DATA\TestDb.mdf'

and a third approach

    CREATE DATABASE TestDb ON
    ( FILENAME = N'C:\Program Files\..R\MSSQL\DATA\TestDb.mdf')
    FOR ATTACH

and for future reference, if the is NOT cleanly shutdown
https://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/


Stored procedures and network congestion When I went to an interview, the interviewer asked me "what are the benefits of stored procedures?" and I started answering but when I said "It reduces network congestion", he suddenly said "no, it doesn't".

I made a sudden pause, I was afraid to ask him why. After the interview session, I searched a lot on the Internet but I found the point I said was there but no explanation for it. So I asked myself and made some assertions:
Since a stored procedure is precompiled, i.e. an execution plan has already been created for it, it increases speed of execution.
But for cases where we pass some variables to the SP, those need to go to the server for execution, and should retrieve different results for each variable.

So my point is that the interviewer is correct: network congestion is not reduced, each time the variables have to be passed to the database and the results should be returned, so network is busy with these. Right?

Is my assertion correct?

In SQL Server, the query optimizer goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

syntactically check the query
if it's okay - it checks the plan cache to see if it already has an execution plan for that query
if there is an execution plan - that plan is (re-)used and the query executed
if there is no plan yet, an execution plan is determined
that plan is stored into the plan cache for later reuse
the query is executed

The point is: ad-hoc SQL and stored procedures are treatly no differently.

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.


Creating Linked Server Object to same instance I'm trying to create a Linked Server Object back to the same SQL Express 2014 server instance (see below for the context), but I'm hitting the following error...

Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "NewServer".
OLE DB provider "SQLNCLI11" for linked server "NewServer" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7303)

I've used the following configuration...

Linked Server: NewServer
Server Type: Other data source
Provider: Microsoft OLE DB Provider for SQL Server
Product name: {empty}
Data source: LOCALHOST\SQLEXPRESS (have also tried actual server name)
Provider string: SQLNCLI
Security, Login: Using the login's current security context (have also tried using this security context and providing sa/password)

Why am I getting this error, and how do I fix it?
Is it because I'm trying to create a linked server object back to itself?

For context, the reason I'm doing this is due to a client who's UAT and Production environments have a linked server with which we need to interact.
As a result I need to create stored procedures on my local development machine that use the [NewServer].[Database].[dbo].[Table] name... so that the sproc can be stored in the repo and easily deployed via comparison tools.  But it won't let me create/update the sprocs without the valid data source.
I thought the simple solution would be to create the [Database].[dbo].[Table] on my local SQL Express 2014 instance and add a linked server back to itself using the [NewServer] name.
Is the only option to create a new SQL instance?

I've already worked it out...
The problem was incorrectly setting the provider string to SQLNCLI.
When I removed it (leaving it empty) it connected immediately...

Linked Server: NewServer
Server Type: Other data source
Provider: Microsoft OLE DB Provider for SQL Server
Product name: {empty}
Data source: LOCALHOST\SQLEXPRESS
Provider string: {empty} (UPDATED)
Security, Login: Using the login's current security context


How to update JSONB array in postgres I have a table with following definition:

create table json_test (
    my_data jsonb);

Inserted to it values like this:

{"details": [
    {
        "f1": "",
        "f2": "free text"
    },
    {
        "f1": "",
        "f2": "free text"
    }
]}

Now I want to update this jsonb column(f1 to null):

{"details": [
    {
        "f1": null,
        "f2": "free text"
    },
    {
        "f1": null,
        "f2": "free text"
    }
]}

I want to change the f1 from string to null.

How to achieve this ?

On 9.3 you will have to rewrite your jsonb value.  From 9.5 you could use jsonb_set() to simplify the task.


In SQL Server, what happens when a constraint is added to a table with data in it? Q&A for SQL Server migrated here from this question.

What happens if a "NOT NULL" constraint is added to a table to a column that already has NULL values?

The question:

  What happens if a "NOT NULL" constraint is added to a table to a
  column that already has NULL values?

A simple example in SQL Server is a constraint to disallow NULLs.

Create a table and insert two rows, one not null and one null.

USE tempdb

CREATE TABLE dbo.OneNull (c1 INT)

INSERT dbo.OneNull ( c1 )
    VALUES  (0), (NULL)

The ALTER TABLE syntax can direct what happens when you create the constraint. Using WITH NOCHECK will tell the engine (vroom vroom) not to check the column contents, but just to add the constraint for future rows.

ALTER TABLE dbo.OneNull WITH NOCHECK ADD CONSTRAINT ck_checkone CHECK (c1 IS NOT NULL)

Adding this constraint runs successfully, but it means that the constraint is untrusted, and you lose certain query optimizations. For instance, if this were a Foreign Key, you would lose join elimination. For a column constraint, the optimizer can skip doing any work period if constraint information tells it that a search will yield no results.

But now this insert fails:

INSERT dbo.OneNull ( c1 )
    VALUES  (NULL)

You can also direct the engine (beep beep) to check the column values while adding the constraint.

ALTER TABLE dbo.OneNull WITH CHECK ADD CONSTRAINT ck_checkonechecktwo CHECK (c1 IS NOT NULL)

Adding this constraint will fail.

If you add one with no formal direction (at least for column check constraints), the engine (screeeeech) will throw an error.

ALTER TABLE dbo.OneNull ADD CONSTRAINT ck_checkornocheck CHECK (c1 IS NOT NULL)

An example of the type of optimization you can get with a trusted constraint is like so:

ALTER TABLE dbo.OneNull WITH CHECK ADD CONSTRAINT ck_checkonechecktwo CHECK (c1 <= 1)

INSERT dbo.OneNull ( c1 )
    VALUES  (1)

SELECT *
FROM dbo.OneNull AS onu
WHERE onu.c1 > 2
AND 1 = 1 --this is added to prevent a trivial plan

Rather than look for values at all, the engine (honk honk) knows that such a value can't exist because of the trusted constraint. Rather than put together a 'real' query plan and read any data, the optimizer short circuits and uses a Constant Scan operator to return NADA!


Backup architecture for frequent backups of business critical DB We've got a database (mysql) driven application which contains business critical information, were looking at building a system that will allow us to backup the db frequently (every 15 minutes, ideally less) so that we mitigate the danger of any data loss.

Because the application is private and only being used by a handful of users (at most 12 at the same time) and because all the data is user inputted (not generate / feed / api driven) we dont really need to have too many considerations to scale of the DB or the backup architecture.

Is there an established architecture / workflow for running backups with these constraints ?

Do you use InnoDB tables or MyISAM? If the former, then use Percona's hot backup solution here for scheduled hot backups. If you're using MyISAM (why?), you can at best take warm backups - but you have to tell your users to stop updating every 15 mins - not the best idea.
HTH, Paul...


MySQL Performance Tuning for MyISAM I need to performance tune a mysql server and need help.

I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are about 100 other innodb tables on the same database. The MyISAM table is pretty
much read only and used by user for searches.

The server has a max_connection value of 800, under load test of about 300 concurrent users the query time starts to increase significantly. Without load the queries take about 500ms to run.

The goal is to significantly improve the performance of searches on the MyISAM table.

The size of the tables in MB:

data_size   index_size  total           engine
8991        6920        15911           InnoDB
1110        718         1829            MyISAM

The following are my settings:

connect_timeout 10
innodb_additional_mem_pool_size 24117248
innodb_buffer_pool_size 12G
innodb_commit_concurrency   0
innodb_flush_log_at_trx_commit  1
innodb_log_buffer_size  11534336
innodb_log_file_size    449839104
innodb_open_files   300
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
join_buffer_size    131072

key_buffer_size 2147483648
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100

max_allowed_packet  134217728
max_connections 800
max_delayed_threads 20
max_join_size   18446744073709551615
max_user_connections    0

myisam_max_sort_file_size   107374182400
myisam_mmap_size    18446744073709551615
myisam_sort_buffer_size 70254592

open_files_limit    2048

query_cache_limit   104857600
query_cache_min_res_unit    4096
query_cache_size    419430400
query_cache_type    ON

read_buffer_size    262144

socket  MySQL
sort_buffer_size    2097152

table_type  InnoDB
thread_cache_size   100
thread_handling one-thread-per-connection
thread_stack    262144

tmp_table_size  1073741824

You clearly stated you goal in the question
The goal is to significantly improve the performance of searches on the MyISAM table
OK let's tune the MyISAM side of things.
I have four(4) suggestions for you
SUGGESTION #1 : Reduce your RAM
First of all, look back 5 years ago and note what I said in my answer to the post What are the main differences between InnoDB and MyISAM?: The MyISAM storage engine caches only the index pages from the .MYI of a MyISAM table.
You set key_buffer_size to 2147483648, that 2G. Yet, your MyISAM indexes only total 718M. You need to reduce key_buffer_size to 768M just to free up 1.2G of RAM.
SUGGESTION #2 : Load All MyISAM indexes into RAM
Many are not aware of this, but you can load one or more indexes from a MyISAM table into RAM. It's called a dedicated keycache. Over 3.5 years ago, I wrote about this in the answer to the post Can I partially invalidate the MySQL table cache? (See SUGGESTION #1). It is also in the MySQL Documentation : See Cache Index Syntax
SUGGESTION #3 : Expand all VARCHARs into CHARs
If the MyISAM table has many VARCHAR fields, you could make VARCHAR fields behave like a CHAR with one DDL command
ALTER TABLE myisamtable ROW_FORMAT=FIXED;

This will expand the table is such a way that a the data type of a VARCHAR(32) is actually 32 bytes wide all the time, just like a CHAR(32). This can potentially double or triple the size of the MyISAM table but you can gain a 20-25% increase in read speed.
I wrote about this many times

Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
Aug 12, 2011: Which DBMS is good for super-fast reads and a simple data structure?
Sep 20, 2011 : Best of MyISAM and InnoDB
May 02, 2012 : Which mysql storage engine to choose?
May 03, 2012 : Which is faster, InnoDB or MyISAM?

If you do this, you will need to go back and measure how big the MyISAM index has grown. Then, redo SUGGESTION #2 and create the MyISAM dedicated keycache to that new size.
SUGGESTION #4
Although MyISAM does not have a global cache of MyISAM data, each DB Connection has a local cache size by read_buffer_size (for sequential reads) and read_rnd_buffer_size (for indexed reads).
You have read_buffer_size set at 256K (262144). You did not set read_rnd_buffer_size at all, so it is set to the default size of 256K (262144). You may want to experiment with caching data but be very careful because those two MyISAM data caches are for each DB Connection.
GIVE IT A TRY !!!


Poor SSRS Rendering performance in SharePoint 2010 Compared to running reports locally (in Visual Studio) or in a Native installation of Reporting Services (SQL Server 2012 SP3) we are experiencing very frustrating performance issues when rendering reports in a SharePoint 2010 web-part.

In SharePoint we are using the SQL Server 2012 SP3 Reporting Services Add-In. We have two load balanced web servers, and two load balanced App servers in the web farm, and one database server which holds both the SharePoint databases and our 'Reporting' database.

Fro mthe execution logs I can see that the Data Retrieval and Data Processing times match when the reports are executed both locally and in Native reporting services, however the rendering times can be wildly different. 1 second locally or native, to 30-40 seconds in SharePoint.

I'm aware that in the past there has been documented problems with Report rendering when combining SharePoint 2010 and SQL Server 2008R2 - however, there's nothing to suggest this is still a widely known issue.

Our servers are given a lot of resource, more than enough, 32GB each - so I don't think it's a resource issue. I also don't think we can accept the extra overhead, in terms of added HTTP calls, is the issue. Surely it cannot make the issue that bad?

Are there any gotchas to be aware of when running this configuration? Why is running Report Services in integrated mode so bad for performance?

This was related to two things:

The web servers of the SharePoint farm had no external web access, so the root certificate revocation was failing (adding a delay of 15-20 seconds to paging and activity) - This was resolved by giving them access to the external web via a proxy.

The issue with extra overheads when using SSRS integrated into Sharepoint is pretty well documented and still seems to be a problem - I guess that's part of why Microsoft are disabling the Add-In in the future.


postgres cluster: Crash of master crashes the replicas too Environment:
Postgres version: 9.6
Cluster of 3 servers, with Patroni and etcd
Scenario:
When index of tables were started with 16 parallel requests (on a 16CPU machine), postgres on the master crashed with Linux OOM killer. This was a 124GB machine. We understand that spawning so many parallel requests needs more memory and we have addressed that.
Problem:
However, the concern is that, when the master crashed due to OOM, all the replicas too crashed. This is something not expected and puts the high availability of the cluster in question. We can easily simulate this and every time the behaviour of the replicas is exactly the same.
The logs of the master when the crash happened:
2020-12-16 09:54:44 UTC [11619]: [9-1] user=,db=LOG:  checkpointer process (PID 30834) was terminated by signal 9: Killed
2020-12-16 09:54:44 UTC [11619]: [10-1] user=,db=LOG:  terminating any other active server processes
2020-12-16 09:54:44 UTC [30838]: [1-1] user=,db=FATAL:  archive command was terminated by signal 3: Quit
2020-12-16 09:54:44 UTC [16870]: [1-1] user=postgres,db=mydbWARNING:  terminating connection because of crash of another server process
2020-12-16 09:54:44 UTC [16870]: [2-1] user=postgres,db=mydbDETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-12-16 09:54:44 UTC [16870]: [3-1] user=postgres,d=mydbHINT:  In a moment you should be able to reconnect to the database and repeat your command.
...
2020-12-16 09:54:59 UTC [24609]: [1-1] user=postgres,db=mydbFATAL:  the database system is in recovery mode
...
2020-12-16 09:55:04 UTC [22780]: [4-1] user=,db=LOG:  redo done at 52712/BAFFD3C0
2020-12-16 09:55:07 UTC [11619]: [13-1] user=,db=LOG:  database system is ready to accept connections

The logs of the replica, when the crash happened:
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2020-12-16 09:54:44 UTC [13293]: [2-1] user=,db=FATAL:  could not receive data from WAL stream: server closed the connection unexpectedly
                This probably means the server terminated abnormally
                before or while processing the request.
...
2020-12-16 09:54:55 UTC [12843]: [19675-1] user=,db=LOG:  restored log file "00000007000526C70000005D" from archive
...
2020-12-16 11:19:18 UTC [12843]: [38972-1] user=,db=LOG:  restored log file "0000000700052712000000C1" from archive
2020-12-16 11:19:20 UTC [18104]: [1-1] user=,db=LOG:  started streaming WAL from primary at 52712/C2000000 on timeline 7

Question: Will the postgres crash on the master (due to OOM/corrupted shared memory), necessarily cause a similar crash on the replicas too? Is there a way to circumvent this?*

This is harmless.
That message is sent by the server to the client when a process has crashed and the backend is about to die (quickdie in postgres.c) so that crash recovery can begin.
You are only seeing the message sent by the primary server to the WAL receiver. Note the WARNING: this is not even an error. This only gets logged because log_min_messages on the standby is set to warning or lower.
The standby server keeps running – as you see, it catches up from the archive while the primary server recovers. As soon as it has read all archives and the primary is up again, it will reconnect and continue streaming.


Replace String within Serialized data I am moving my forum from one domain to another and need to replace all instances of the previous domain with the new one. In looking at the mysql data, many of the matches are going to be in the middle of a serialized string, is there a quick way to replace all matches?

You're probably not still looking for an answer, but in case anyone else stumbles across this question.

This is an issue in WordPress development all the time. There's a tool built specifically for it by Interconnectit - https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

I've never tried it for anything other than WordPress, but it connects directly to the database using the standard user/pw info, so you should be able to connect it to any database. I think the only thing WordPress specfic is it auto-populates the database info.

It's quick and easy to use, and it deals with serialized strings so they don't become a hassle.


Should I create many ID for a same Person/entity in my DB? In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, except 2 teachers, which have 2 classrooms and there is no way we can remove one classroom (we MUST keep the 2 classrooms)… do you know of a way to handle this exception ? Knowing that it might happen with other teachers in the future.

The only solution I found so far goes as follow:

Kid Table

Kid ID (PK)

Teacher ID

Teacher Table

Teacher ID (PK)

Teacher Name

Classroom ID

And to have 2 teacher ID (or more) for the very few teachers who have more than 1 classroom. I do need the teacher name in the teacher table to be able to produce stats based on the teachers (no matter the number of teacher ID related to any given teacher).

Does it this model make sense ? is there a better way to do it ?

Properly normalized it will have 3 tables as follows:

Kid

kid_id (pk)
classroom_id (fk to Classroom)

Teacher

teacher_id (pk)
name

Classroom

classroom_id (pk)
teacher_id (fk to teacher)

This way you have no duplication and no update anomalies.


What is the difference between a SQL Login and a Database User in terms of security? I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database.

However, there is no corresponding database user mapped to any of the logins, and all of the logins seem to have full access to any of the databases on the server.

In fact, each login has a check next to all the server roles.

Is this a common practice and is it safe in terms of security?

You can have a login to the SQL Server instance, but no access to a given database. When your login is granted access to a DB, you become a user in that DB.

As for granting max perms via roles to all logins, this is very risky, and not common, or default. Best practice is to use the very lowest perms possible that still allows users to complete their work.


What's the fastest way to find the first 2 purchases for every customer in Redshift? I'm looking to find the first 2 purchases for every customer.

I have a table that looks something like this:

orders

With columns:

order_id
customer_email
grand_total
purchase_date

There are many millions of rows in this table and I'm trying to look for an efficient way to grab the first two earliest records for each customer, so that I get a result set that looks something like this:

order_id
customer_email
purchase_date1
purchase_date2

I'm at a loss on how to do this efficiently.

Assuming a separate customers table, and a compound index on orders(customer_email, purchase_date), something like this will do it:

select
  customer_email,
  array(
    select order_id
    from orders
    where customer_email = c.customer_email
    order by purchase_date
    limit 2
  ) as order_ids
from customers c;


Change left outer join on SAP Universe Design Tools I need to create a report with SAP Universe Design Tool but when I try to construct a LEFT JOIN between tables, SAP automatically create a LEFT OUTER JOIN when it executes the query in Web Rich Client.

Is it possible to force SAP to build a query just with a LEFT JOIN so it does not create blank lines in the report?

The OUTER clause is optional, you'll find a good answer in this SO question:

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

CREATE TABLE T1 (id int, name varchar(10));
CREATE TABLE T2 (id int, name varchar(10));
INSERT INTO T1 VALUES (1, 'OneT1'),(2, 'TwoT1'),(3, 'ThreeT1');
INSERT INTO T2 VALUES (2, 'TwoT2'),(5, 'FiveT2'),(6, 'SixT2');

SELECT    *
FROM      T1
LEFT JOIN T2
ON        T1.id = T2.id;

SELECT     *
FROM       T1
LEFT OUTER JOIN T2
ON         T1.id = T2.id;
GO

id | name    |   id | name
-: | :------ | ---: | :----
 1 | OneT1   | null | null
 2 | TwoT1   |    2 | TwoT2
 3 | ThreeT1 | null | null

id | name    |   id | name
-: | :------ | ---: | :----
 1 | OneT1   | null | null
 2 | TwoT1   |    2 | TwoT2
 3 | ThreeT1 | null | null

dbfiddle here


UPDATE a column by COUNT of another table It might be a naive question, but what is the difference of these two queries and which is preferred?

UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = B.idcount WHERE table1.id=B.id

and

UPDATE table1 A INNER JOIN
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) B USING (id)
SET A.Freq = B.idcount

Same query. different join syntax.

The first is old style ANSI, the latter is later ANSI. If you must pick between them, pick the latter.

I wouldn't use either of them due to the limitations of USING (can't be aliased). Instead write as:

UPDATE table1 A
INNER JOIN (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) as B
  ON B.id = A.id
SET A.Freq = B.idcount

See this Oracle answer as to why USING is not to be preferred: https://stackoverflow.com/questions/456684/mixing-using-and-on-in-oracle-ansi-join

And here is a similar discussion for SQL Server: https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax

In general, get used to writing SQL syntax in a way that is portable. While you may never port your code, you will at least be able to port your skills around to multiple database (Which is generally a cool thing to do to avoid becoming a one-trick pony).


unable to add a range to a tag using sh.addTagRange command Have setup a 4 node test shard cluster:

1- mongos
1- config server
2- shard nodes hosting 1 shard each for a collection.

Cluster is working perfectly, but when trying to run the following command:

mongos> sh.addTagRange("HTMLDumps.HTMLRepository", { ShardMapId: "7" }, { ShardMapId: "11" }, "NRT")

 "errmsg" : "min: { ShardMapId: \"7\" } should be less than max: { ShardMapId: \"11\" }",
        "code" : 9,
        "codeName" : "FailedToParse",

Below is the shard status output:

databases:
        {  "_id" : "HTMLDumps",  "primary" : "shard0001",  "partitioned" : true }
                HTMLDumps.HTMLRepository
                        shard key: { "ShardMapId" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000       2
                                shard0001       2
                        { "ShardMapId" : { "$minKey" : 1 } } -->> { "ShardMapId" : "" } on : shard0001 Timestamp(3, 1)
                        { "ShardMapId" : "" } -->> { "ShardMapId" : "1" } on : shard0001 Timestamp(1, 2) jumbo
                        { "ShardMapId" : "1" } -->> { "ShardMapId" : "7" } on : shard0000 Timestamp(2, 0)
                        { "ShardMapId" : "7" } -->> { "ShardMapId" : { "$maxKey" : 1 } } on : shard0000 Timestamp(3, 0)

                             tag: NYC  { "ShardMapId" : "1" } -->> { "ShardMapId" : "7" }

What am I missing?

Complete sh.status output:

Shard key zone (tag) ranges require specifying minimum and maximum bounds for a range of shard key values.

Since your shard key values are currently strings, they will be compared using binary order rather than numerical order. The error message is correctly indicating that a minimum value of "7" (string) is not less than a value of "11" (string). For example, given the strings "1", "7", and "11" the expected ascending sort outcome using a binary string comparison is "1", "11", and "7". This is roughly alphabetical order, but more specifically strings in MongoDB are stored as UTF-8 bytes.

As you are intending on using zone sharding, for this test deployment it would be better to have numeric shard key values so you can use the range of 1-7 for your zone. You could remove and replace existing documents or just add more with the desired shard key values.

I assume you aren't concerned about the cardinality of your shard key for a test cluster, but for a real deployment you may want to choose a more appropriate shard key if ShardMapId will only have a small range of values.

Note: tag-aware sharding was succeeded by Zone sharding in MongoDB 3.4. The sh.addTagRange() shell helper has been aliased to the modern equivalent of sh.updateZoneKeyRange(), but I would recommend using the newer helpers to be safe. You should also ensure that your shell version() matches your server db.version().


User with dba privs can't login to APEX, but can from SQL*Plus I'm using Oracle 11gR2 XE. A user with dba privileges can't log in to the APEX interface, but can from the command line with sqlplus.

I just created user:

CREATE ROLE administrators;
GRANT dba TO administrators;
CREATE USER superuser IDENTIFIED BY "root";
GRANT administrators TO superuser;

If I run cmd, type sqlplus and log in with "superuser", I get access.

But the web interface (APEX) denies access with an "invalid credentials" error message.

What do I need to do to be able to log in on the APEX interface?

The APEX administrator accounts are not linked to a database schema. Reciprocally, creating a database schema won't create an APEX account.

You'll have to create the administrator account via the web interface.

Connect to the administration page:

  If your setup uses the embedded PL/SQL gateway, go to:

http://hostname:port/apex/apex_admin

  If your setup uses Apache and mod_plsql, go to:

http://hostname:port/pls/apex/apex_admin

Then On the Login page:

  In Username, enter admin.
  In Password, enter the Oracle Application Express administrator account password you specified when you installed Oracle Application Express.

There are three levels of administration, decide which type you want to create:

  Developers create and edit applications.
  Workspace administrators are developers who also perform administrator tasks specific to their workspace such as managing user accounts, monitoring workspace activity, and viewing log files. See "Workspace and Application Administration".
  Oracle Application Express administrators are superusers that manage the entire hosted instance using the Oracle Application Express Administration Services application.

I suppose you want to create an APEX administrator account. They are workspace administrator of the special workspace "Administration" that you can create following the standard APEX account creation.


Combine multiple row's records in one row with MS Access Query? I have a table (name- tbl2) with 21 column but I want to combine multiple rows records in a row by using a unique reference number as below:

Table Field and Records Type

Expected Result:

Please help me to resolve this issue.

Try the below query will solve your problem:

SELECT ID, STUFF(
     (SELECT ', ' + CAST(Item AS VARCHAR(20)) [text]
     FROM TBL12
     WHERE ID = t.ID
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') Items
FROM TBL12 t
GROUP BY ID

Since you add many tags in the post, I solved in MSSQL using STUFF.
Working fiddle: http://sqlfiddle.com/#!3/d805d/4


How to select distinct TOP but group by column I have a result set of 500 rows, but this are for 100 invoice.
I need only the top 10 of the last invoices.

I found something like similar: How to select top 10 records from each category

Sample data:

InvNr | DetailLine
111   |    1
111   |    2
112   |    1
112   |    2
112   |    3
113   |    1
113   |    2
114   |    1
115   |    1
...   |    ...

What I hope to get is for example:

SELECT DISTINCT TOP 2 InvNr, DetailLine FROM tbl_Invoice

With this result:

InvNr | DetailLine
111   |    1
111   |    2
112   |    1
112   |    2
112   |    3

Update:
So I need the last 10 (or first 2 in the example above) invoices they have created, but each invoice can have "x" amount of detail lines and I want all their detail lines (of these last 10) in the results.

SELECT InvNr, DetailLine
FROM tbl_Invoice
where InvNr in (select distinct  top (2) InvNr from tbl_Invoice order by InvNr);


How to collapse cell borders in ssrs I have been creating some reports in SSRS 2008 R2. When I need to make borders that show up for all rows, both on the top and bottom of the report, as well as all the rows in between I can tell that the inner borders of the report are thicker because it is actually 2 borders side by side.

Is there a way to collapse borders that are side by side?

The only way I know how to do this is to remove either the left-hand or right-hand border from the cells where it is double. A very laborious work-around and something that should be addressed as a bug.


postgres partition trigger and checking for child table I found some sample code for creating a partition table.

Basically, setup the master table and partition on date field concatenated with the type of entity it is then, inherit from the master.

The first call of the trigger is fine, but subsequent ones fail because the 'if' condition to check for an existing child table yields false but upon trying to create the table fails for an already existing table.

I have tried 3 ways to check for a child tables existence and none yield correct answers.

These 2 statement demonstrate the contradiction:

SELECT count(*) FROM pg_class WHERE relname='DRIVER_2016_12_28';
count(*)
0

select count(*) from DRIVER_2016_12_28;
count(*)
1

Master table:

CREATE TABLE partition_test
(
  pk SERIAL PRIMARY KEY NOT NULL,
  id VARCHAR(36) NOT NULL,
  organizationid VARCHAR(36) NOT NULL,
  lat REAL NOT NULL,
  lon REAL NOT NULL,
  basetype VARCHAR(16) NOT NULL,
  name VARCHAR(64) NOT NULL,
  updatetimestamp TIMESTAMP NOT NULL
) WITHOUT OIDS;

trigger code:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE ' || partition || ' (check (updatetimestamp = ''' || NEW.updatetimestamp || ''')) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %',partition;
  END IF;
  EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER telemetry_partition_insert_trigger
BEFORE INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE telemetry_insert_trigger();

insert example:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

first output

  [2016-12-28 12:15:12] [00000] A partition has been created
  DRIVER_2016_12_28 [2016-12-28 12:15:12] completed in 33ms

second:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

  [2016-12-28 12:16:15] [42P07] ERROR: relation "driver_2016_12_28"
  already exists [2016-12-28 12:16:15] Where: SQL statement "CREATE
  TABLE DRIVER_2016_12_28 (check (updatetimestamp = '2016-12-28
  12:16:15.467012')) INHERITS (partition_test);" [2016-12-28 12:16:15]
  PL/pgSQL function telemetry_insert_trigger() line 11 at EXECUTE
  statement

The question: How can I effectively check for the existing of the child table so that I don't get this error? If you'd like to point out existing problems with my trigger, that'd be great too

I would advise to use always quote_ident when generating dynamic sentences, to make sure your idents don't have upper/lowercase problems, or that you can use identifiers with spaces or special chars, and that you avoid SQL injection if your idents are parameters out of your control.

That is, your trigger function should look like:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
  start_ts TIMESTAMP ;
  end_ts TIMESTAMP ;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;
  start_ts := partition_date::timestamp ;
  end_ts := start_ts + interval '1 day' ;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE '    || quote_ident(partition)
        || ' (check (updatetimestamp >= ''' || start_ts || ''' '
        || '     AND updatetimestamp <  ''' || end_ts || ''')) '
        || ' INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || quote_ident(partition) || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %', partition;
  END IF;
  EXECUTE 'INSERT INTO ' || quote_ident(partition) || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Check a usage example for quote_literal on the PostgreSQL documentation.

As a matter of preference, I prefer to have the names of all tables lowercase and with no special chars (so that I don't have "Table Names with Double Quotes", but table_names_without_quotes), because I think the code is less noisy and more readable. But, again, this is a matter of taste.


SQL Vs MySQL vs MongolSQL I've recently been doing a SQL course and I’m finding it incredibly easy to read.

If I can read SQL and write in it, could it be possible that it’s almost the same to MySQL and Mongol SQL? Just some clarification would be great.

Yes and No. Most of the RDBMS  like MySQL Postgres SQL-Server ... relay on the basic SQL Syntax. All the different systems have their own dialect with their one extensions on SQL but it is rather easy to transit between the individual ones.

MongoDB is somewhat different as it is a so-called NoSQL ( Not Only SQL). Mongo ( like CouchDB or Cassandra) aren't based on the premises that your data is structured and store them in key-value pairs ( I have to admit I'm not an expert here, so correct me if I'm wrong) instead of tables.


How to view the current settings of Autovacuum in Postgres? I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.

Postgres 9.1 is the version I'm most interested in.

For the general settings use:

select *
from pg_settings
where name like '%autovacuum%'

for table specific settings, check out the column reloptions in pg_class:

select relname, reloptions
from pg_class

You will probably want to join that to pg_namespace to limit this to a specific schema. Where joining needs to happen on the hidden col pg_namespace.oid (added 9.3+).

select relname, reloptions, pg_namespace.nspname
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname like 'data%' and pg_namespace.nspname = 'public';


SQL Server 2012 is putting [brackets] around Table and Column Names When you drag the Columns folder to editor Window, the full list of Columns are added without Brackets.  If you drag columns individually they have Brackets.

Is there anyway to turn that off?  I'm unable to find anything in options, and they're really a distraction.

There isn't a way to prevent this. Microsoft feels that it is better to prevent you from shooting yourself in the foot, probably since the number of people out there who create databases, objects or columns with bad names (e.g. my table or date) or reserved keywords (e.g. log or timestamp) outnumber those who never do so.

There was a Connect item asking for this option, and it was very quickly dismissed and closed as "Won't Fix." And in fact many more users were asking that square brackets be added to places where they previously didn't work.


mysql ibd file in file system I am in searching of where mysql stored its indexes for innodb engine.I found it stores in ibd file but where I will found those files on system.Can anyone help please?

In InnoDB both data and indexes are stored together, there is no separate file for indexes. Within your MySQL install directory you will find directories for each database, and within each database directory will be the tablespace files. Depending on whether you have InnoDB File-Per-Table enabled or not, you will see individual files per table or not.


MS-SQL-Server 2019 is slower than SQL-Server 2008 Since we updated from SQL Server 2008 to 2019, the queries have become much slower.
The following query ran for ten minutes on version 2008. On version 2019 it runs for over 30 minutes.
Until yesterday, we always limited the WHERE-clause: where date > '20180101'
To make the script at least a bit faster, I narrowed it down from January 2019: where date > '20190101'
Now it's even slower than before. No joke! Because one year less has to be evaluated, the running time has increased by a few minutes.
I have checked all the indexes in detail and I think we have created the correct indexes.
But I don't know if this is really an index problem, because in version 2008 we had the same indexes.
In version 2019 I even deleted and re-created a few important indexes. But that didn't help at all.
MS-SQL Server 2019 got very slow and I don't know why.

From the query plan you provided it runs a minute:

But it compiles 33 minutes (1992254 ms ~= 1992 s ~= 60 * 33 = 33 minutes):

So basically you had a problem on 2008 already. It's just exaggerated in 2019. Never experienced such an extreme case in my life, but I would try to simplify the views you are using and drop not used indexes on underlying tables. Or maybe split the query: first select view into temp table and then use the temp table in your original query.


MySQL High Severity Error I am completely new to MySQL. I am not a programmer or anything.

I was installing the program months ago, and forgot my root password, tried to fix things and it failed.

I did a system restore and now I get two messages.

One is short:

"Log file path must be defined before calling the WriteToLog method"

The second is long:

  at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlTextReaderImpl.Read()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.Load(String filename)
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbenchConnectionCollection.LoadXmlFile(Boolean saving)
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbenchConnectionCollection.Load()
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbench.LoadExternalConnections()
   at MySQL.Utility.Classes.MySQLWorkbench.MySqlWorkbench.set_ExternalApplicationConnectionsFilePath(String value)
   at MySql.Notifier.Notifier.InitializeMySqlWorkbenchStaticSettings()
   at MySql.Notifier.Notifier..ctor()
   at MySql.Notifier.NotifierApplicationContext..ctor()
   at MySql.Notifier.Program.Main(String[] args)

Have I destroyed my MySQL, can I fix this myself? Or should I call someone?

Decided to just remove and reinstall it!


COUNT beginning of, mid of & end per month for many years In my last post PostgreSQL: How to get a weekly average over a year including 'gap' weeks per animal from years of data? with the fiddle http://sqlfiddle.com/#!17/0bff3/1
I got a weekly count. But it was difficult to graph.

I decided to go a different route. doing the month is 3 parts (beginning, middle and end)
My new objective: (and I won't graph this)

|animal|start             |peak      |end        |
|dog   |beginning of may  |mid of may|end of june|
|cat   |beginning of march|end of may|mid of june|

I was thinking of using a case like structure to assign a part of the month... something like the following...

SELECT animal, count(*),
min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as start,
max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as end
    FROM brachyopa_cleaned bc
    AND concat(from_month,'/',from_day) not in ('12/31','01/01','1/1') -- dates where month and day are unknown
GROUP BY animal
ORDER BY aminal

... and then find per species per month section the amount of occurrences to find the month section (there are in fact 12x3 'mm-dd' sections in a year) with the highest count.

Can someone give me some pointers how to do this?

[EDIT1: Changed the SELECT in what I am using so far, now I need somehow to find the peak, the 10 days period where there is the max count]
[EDIT2: rewrote the ...per specie per month... section]

One thing I can think of, is to generate a date range from the input date that splits the month into three parts. It's quite easy to keep the first two parts constant and have the third one vary:

create function get_first(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date, date_trunc('month', p_input)::date + 10, '[]')
$$
language sql
immutable;

create function get_second(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 11, date_trunc('month', p_input)::date + 20, '[]')
$$
language sql
immutable;

create function get_third(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 21, (date_trunc('month', p_input) + interval '1 month')::date, '[)')
$$
language sql
immutable;

With that you can do the following aggregation:

select animal,
       count(*) filter (where get_first(from_date) @> from_date) as start,
       count(*) filter (where get_second(from_date) @> from_date) as mid,
       count(*) filter (where get_third(from_date) @> from_date) as "end"
from occurrence
group by animal;

The three functions can be enhanced/changed to deal with the variable length of a month, but that would require much more complex calculations. It would boil down to making the +1 or +20 a percentage of the number of days in the month.

If you need to find the highest of those three parts, you can unpivot the values and then using distinct on() to get it:

select distinct on (x.animal) x.*
from (
  select animal,
         to_char(from_date, 'yyyy-mm') as month,
         count(*) filter (where get_first(from_date) @> from_date) as start,
         count(*) filter (where get_second(from_date) @> from_date) as mid,
         count(*) filter (where get_third(from_date) @> from_date) as "end"
  from occurrence
  group by animal, month
) t
  cross join lateral (
    values (animal, start, month, 'start'), (animal, mid, month, 'mid'), (animal, "end", month, 'end')
  ) x (animal, num, month, section) --on true
order by x.animal, num desc

The cross join lateral essentially turns the three columns into rows and then distinct on () is used to pick the highest value of the three for each animal

Online example


Query to get counts of values per column I have a big table of vendor-supplied data (that I can't change around much) with about 315 columns. I suspect that many of the columns are not being used (or at least not consistently).

I'd like a query that can give me the count per column of the values in the table.

For example

CREATE TABLE foo AS VALUES
    ( null   , 'xyz'  , 'pdq'  , null ),
    ( 'abc'  , 'def'  , 'ghj'  , null ),
    ( 'hsh'  , 'fff'  , 'oko'  , null );

So this would give results something like:

Col1 | 2
Col2 | 3
Col3 | 3
Col4 | 0

EDIT: to clarify, I know I can just use COUNT but I'm hoping for a way to loop over possibly a query to the system table first to avoid having to hand code 315 count statements. Thanks!

Something like

FOR column_names IN SELECT * FROM information_schema.columns WHERE
table_schema = 'public' AND table_name = 'vendor'
LOOP
 RAISE NOTICE 'doing %s', quote_ident(column_names.column_name);
 SELECT count(column_names.column_name) from vendor
END LOOP;

You can get the first part of that done easily like this,

SELECT FORMAT(
        E'SELECT %s\nFROM %I.%I.%I;' -- query template
        , string_agg(  -- generate the select list for query template
                FORMAT('count(DISTINCT %I) AS %I', column_name, column_name)
                , E',\n\t'
        ),
        table_catalog, -- not strictly required, but future safe
        table_schema,
        table_name
)
FROM information_schema.columns
WHERE table_name = 'foo'
GROUP BY table_catalog, table_schema, table_name;

This will return a query like this,

SELECT count(DISTINCT column1) AS column1,
        count(DISTINCT column2) AS column2,
        count(DISTINCT column3) AS column3,
        count(DISTINCT column4) AS column4
FROM ecarroll.public.foo;

Which is pretty much what you want, except you need to pivot it.

 column1 | column2 | column3 | column4
---------+---------+---------+---------
       2 |       3 |       3 |       0

To do that pivot, we can use unnest(ARRAY[cols]) AS col_name, so we essentially have to generate

dynamic SQL to do the count()
wrap that with more dynamic sql to do the pivot.

Like this,

SELECT FORMAT(
        $$
        SELECT ordinality AS column_number, distinct_values -- the col#, and count
        FROM (
                SELECT %s      -- This was the query we
                FROM %I.%I.%I  -- used previously
        ) AS t
        CROSS JOIN unnest(ARRAY[%s]) WITH ORDINALITY -- Here we use unnest(array)
                AS distinct_values;                  -- to pivot the table
        $$,
        string_agg(
                FORMAT('count(DISTINCT %I) AS %I', column_name, column_name)
                , E',\n\t'
        ),
        table_catalog,
        table_schema,
        table_name,
        string_agg(column_name, ', ')
)
FROM information_schema.columns
WHERE table_name = 'foo'
GROUP BY table_catalog, table_schema, table_name;

That returns a query like this..

    SELECT ordinality AS column_number, distinct_values
    FROM (
            SELECT count(DISTINCT column1) AS column1,
    count(DISTINCT column2) AS column2,
    count(DISTINCT column3) AS column3,
    count(DISTINCT column4) AS column4
            FROM ecarroll.public.foo
    ) AS t
    CROSS JOIN unnest(ARRAY[column1, column2, column3, column4]) WITH ORDINALITY
            AS distinct_values;

And you can just run \gexec and you'll get,

 column_number | distinct_values
---------------+-----------------
             1 |               2
             2 |               3
             3 |               3
             4 |               0