Oracle export not importing correctly (missing more than half of the tables) I have an export of a 75gb database broken up into 2gb files. When I run imp utility after creating the tablespaces and users to match the original database, I don't think all tables are created. I think this because the table names (alphabetically) go from A-D. Each of the existing table names have a matching sequence. However, the sequences go from A-W. So there "should" be about 2x more tables that start with letters C-W.

So my first question is, is the entire schema for the database stored in the first dmp file (when it is split into multiple files with exp)? Or can it be split up into multiple. Looking through the first dmp file, I can not see any indication of the missing tables.

Alternatively, is this just a bad export?

No, the schema definition is not stored entirely in the first file:

SQL> select segment_name, bytes/1024/1024 as size_mb from user_segments;

SEGMENT_NAME    SIZE_MB
------------ ----------
T1                   80
T2                   80

$ exp bp/bp tables=T1,T2 file=expdat1.dmp,expdat2.dmp,expdat3.dmp,expdate4.dmp filesize=50M

Export: Release 12.1.0.2.0 - Production on Wed Sep 21 16:52:04 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditi
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             T1
continuing export into file expdat2.dmp
     675872 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                             T2
continuing export into file expdat3.dmp

continuing export into file expdate4.dmp
     675872 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

First CREATE TABLE is in the first file, second CREATE TABLE is in the second file:

$ grep -a "CREATE TABLE" *dmp
expdat1.dmp:CREATE TABLE "T1" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS
expdat2.dmp:CREATE TABLE "T2" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 83886080 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS

When you run the import, you should provide the name of all dumpfiles, in the correct order.


Is it possible to store Always Encrypted master key an on-premises key vault? I want to store the master key in a centralize key vault and have IIS access the key from there. I know I can use Azure Key Vault or local Windows key store, but I was looking for another option to have it outside the server.

Are there any on-premises centralized key vault solutions that can store Always Encrypted master key?

It's always safe to store the Keys in Centralize key vault rather than any on-premises.

For your case, you can try to create your own Custom Keystore provider


Invalid data types in restore headeronly resultset I'm trying to write a SQL Server script to iterate through .bak files in a directory and then restore them to our server. In doing so, I've created three temp tables: #Files to keep track of the file-list returned by running xp_dirtree, #HeaderInfo to hold data returned when querying restore headeronly to get the database names and #FileListInfo to hold data returned from querying restore filelistonly to get the logical file names.

My question is regarding the #HeaderInfo table. Consulting the MSDN definition of the resultset returned from restore header only, I find that the fifth column (Compressed) and the last column (CompressedBackupSize) have 'invalid data types' (BYTE(1) and uint64, respectively). This, obviously, gives me an error when I try to execute the query. To get around this, I have used tinyint and bigint, respectively, and the code now runs fine.

My question(s) is/are this/these:

Is using tinyint/bigint the 'correct' work around for this? Or is there a better way to do it?
Is using them likely to cause any undesired behaviour?
If SQL is expecting BYTE(1) and uint64s, why does using different data types not cause an error?
And why does MSDN specify BYTE(1) and uint64 if they're not what gets returned? What are these used for and where?
Bonus question, for anyone who's interested, is there a more elegant/efficient way of automating a restore script?

Many thanks

EDIT: SQL Server 2008

First your first questions

I would use tinyint for the BYTE(1) in this case they told us the possible values are 1 or 0. BIT may also work. You could also try BIT. But uint64 is an unsigned 64 Byte integer. BIGINT is signed, so the max value is lower. So technically speaking a DECIMAL(20,0) or greater precision would be used here. But in later versions of that same article this is a BIGINT (For SQL Server 2008 R2 and SQL Server 2012) so I am sure you are fine with BIGINT here. If you get enough disk space and time to create a database big enough to compress to a value that blows BIGINT you can test this theory out someday ;-)
No undesired behavior if you go with SMALLINT/BIGINT/DECIMAL(20,0)
I am not sure I understand your question, but I believe the answer is conversion if you are asking what I think you are asking but this is potentially just an oops
I'm not sure why those datatypes are in the documentation but you've chosen good logical approximations.

Then the last question

I hate to shove off on this one, but I'm kind of going to do that. There are a lot of great restore scripts out there on the internet for different scenarios. You haven't fully described yours so not sure I can comment on the efficiency/elegance but you are right to read the headers to determine what you do next. Some questions to ask yourself:

Are you looking at things like the date to ensure you restore the latest? Are you looking at things like full/diff/log backups and accounting for them in the restore? What purpose is this for? Restoring a dev environment? Or for a production restore? If a dev restore, I like to go more automated. If a prod restore I like to have a script that eliminates some "oops" factor from a critical production restore but not automate so much of it that it makes it easy to forget to do a critical step or do something like backup the tail of the log. I'd search for restore scripts and see what others have done, ask yourself these questions and incorporate what you like.

I also am not sure you need to know if the file is compressed or what the compressed size is. Those facts shouldn't be terribly necessary for a restore script since SQL just handles the restore of a compressed backup for you. You don't have to tell SQL it is compressed. So you may just drop those columns altogether and only take what you require from the header to perform you restore.


convert multiple columns as single row with appreciated data See the image below.

The first table is the actual table.

How to get the expected result in the second table using a procedure when I pass formid as parameter?

example:
exec procedure 123

Hope following expected result script help in your case, but you have prepare it for maximum number of fields.

Test environment (1 time execution only) as per your scenario:

Create table #Temp
(formid int, FieldName varchar(50), value_ varchar (50) )
go

Insert into #Temp
values
(101, 'FirstName', 'Name 1'),
(101, 'LastName', 'x'),
(101, 'Email', 'Name@s.com'),
(102, 'FirstName', 'Name 1'),
(102, 'Gender', 'M'),
(102, 'Mobile', '9xxxxxx'),
(103, 'DOB', '19900101')
go

Expected Result

Declare @FormID int = 103

Select  max(Case when F_Rn = 1 Then formid end) as formid,
        max(Case when F_Rn = 1 Then FieldName end) as Field1,
        max(Case when F_Rn = 2 Then FieldName end) as Field2,
        max(Case when F_Rn = 3 Then FieldName end) as Field3,
        max(Case when F_Rn = 4 Then FieldName end) as Field4,
        max(Case when F_Rn = 5 Then FieldName end) as Field5,
        max(Case when F_Rn = 6 Then FieldName end) as Field6
from            (
                select formid,
                       ROW_NUMBER() OVER (Order by FieldName) as F_Rn,
                       FieldName,
                       value_
                from #Temp
                where formid = @FormID
                ) as t
go

Drop Test environment

Drop Table #Temp
go


Find a closest match in the same table I have a table with timestamped data. For all the rows, I want to find the row that is the closest to 20 minutes after and between 15 and 25 minutes after.

e.g. if the table is like

CREATE TABLE foo(id,ts)
AS VALUES
  ( 1::int, '2017.10.27T10:12:15'::timestamp with time zone ),
  ( 2,      '2017.10.27T10:24:17' ),
  ( 3,      '2017.10.27T10:30:22' ),
  ( 4,      '2017.10.27T10:40:12' ),
  ( 5,      '2017.10.27T10:52:16' ),
  ( 6,      '2017.10.27T10:53:11' );

I then run a query

 select t1.id as base t2.id as after
 from table t1, table t2 where ??

I would like to get the answer:

 base after
 1    3
 2    4
 3    5

For 4,5 and 6 as base I get no result as there are no rows matching with my criteria

It would be easy enough to get this for a given timestamp:

select id from table where timestamp > $mytimestamp+'00:15:00'
  and  timestamp < $mytimestamp + '00:25:00'
order by
   abs(extract ( epoch from (timestamp -($mytimestamp + '00:20:00')))
limit 1;

But how to do this for all the rows in a table?

You can use a CROSS JOIN LATERAL ( ... LIMIT 1 ) to accomplish this, along with btree_gist and <->

CREATE EXTENSION IF NOT EXISTS btree_gist;

SELECT f1.id AS base, f2.id AS after
FROM foo AS f1
CROSS JOIN LATERAL (
  SELECT id, ts
  FROM foo AS f2
  WHERE f2.ts BETWEEN f1.ts+'15 minutes' AND f1.ts+'25 minutes'
  ORDER BY f1.ts+'20 minutes' <-> f2.ts ASC
  LIMIT 1
) AS f2(id,ts);

 base | after
------+-------
    1 |     3
    2 |     4
    3 |     5
(3 rows)

There are a lot of tricks going on here. We use <-> as a distance operator for your "closest to". Essentially, it can use a GiST index to determine this with KNN. We also use a CROSS JOIN LATERAL ... LIMIT 1 to get the list of all potential roles, and then limit it to just the one that is "closest to" as determined by our knn operator above.

This will work fastest with a gist index on ts.

CREATE INDEX ON foo USING gist ( ts );

See also another question using <-> on a time stamp


SQL Server Agent Alerts using severity 16 My work is currently deleting lot of stored procedures, views, tables and so on.

My worry is that if we miss judge a object and it goes into production that we'll only catch the errors when an user reports it.

I've created an alert using type "SQL Server event alert" and severity 16 and checked the e-mail for operator.

So, time to test the task:

EXEC SomeObjectThatDoesNotExist

I've verified that it's a severity 16 by checking message table.

USE MASTER

SELECT *
FROM SYS.MESSAGES m
WHERE m.language_id = 1033 AND
        m.message_id = 2812

But this does not raise an alert. Is it possible to raise an alert on object not been found?

Finally found the answer to my question.

You need to enable the is_event_logged in the sys.messages table.

Example code:

EXEC sp_altermessage @message_id = 2812, @parameter = 'WITH_LOG', @parameter_value = 'true'

Here is the blog post I found to answer my question
Reference


How do I configure SQL Server Management Studio Intelisense to ignore case The way we have our db configured, case does not matter, but in the query editor intelisense  is using case much to my frustration. I like that autocomplete feature of intelisense but I have had to disable it until I can find a way to get it to ignore case.

In case your using Sql Server 2008, Intellisense in SSMS 2008 R2 is case sensitive but T-SQL itself may not.

It was marked as fixed for later versions.

https://connect.microsoft.com/SQLServer/feedback/details/672196/t-sql-is-not-case-sensitive-but-t-sql-intellisense-is

It have been fixed on SQL 2012. I know because I am using it. You don’t need to upgrade in order to use a new version of the SQMS but you need to have the license for the new version.  SSMS is compatible with older SS Database Engine Instances but the license requires purchasing the new version.
If you have a MSDN subscription or a SA agreement maybe you already have it.

https://social.msdn.microsoft.com/Forums/en-US/871cd19f-227a-44af-a702-4dabc00cb74c/sql-server-management-studio-licensing

http://www.microsoft.com/licensing/products/products.aspx

Can I use SQL Server Management Studio 2012 on a 2008 server?

P.D.

Workaround suggested by OP : In SSMS, click on Tools->options, then navigate to Text Editor->Transact-SQL->General and uncheck Auto list members and OK. This will prevent SSMS from replacing something that you typed perfectly with something else that is very wrong. You can still manually list members, when you want to by typing Ctrl-Space, choosing what you want from the list and pressing enter to complete what you typed.


azure sql - create index, does DB have to be offline? New to Azure SQL. Simple example to ask question.
Table Cars.
Want to add an non clustered index to Cars table.
Takes a long time, say 30 min during staging test deploy.

When upgrading production Azure SQL, can we leave the database online while building a table index? During this time while the table is building, can applications send inserts/updates to this table?

Or do we need to take the production DB offline for 30 to build this index?

You should absolutely be able to access the table while the index builds. Now, depending on your service tier in Azure, you might be using a lot of your resources to build this index, and that might impact user access, but otherwise, no. The index will build, but access to the table will continue.


Backup and Restore across versions I did a backup of a database using pg_basebackup. I need to restore it to another server.
The database version in the source server is 9.2.23. The destination server postgresql version is 12.
Will there be any problems if I restore from the 9.2.23 to 12?

Yes, there will be problems. That won't work.
You have to restore the backup with PostgreSQL 9.2, only then you can upgrade.


List all used data types of a table or whole database in Oracle DB I am preparing a migration from Oracle DB to a SQLite DB.
I would like to know all used datatypes in the Oracle DB so I can check out the equal datatypes in SQLite.

I am sure using Information_Schema seems to be the right way but I don't know how to start.

The used version of the Oracle DB is 10g (10.2).

I am thanking in advance.

Here is an example to find the number and type of columns in a schema:

select data_type, count(*) from dba_tab_cols
where owner = '...' group by data_type order by data_type

DATA_TYPE              COUNT(*)
-------------------- ----------
ANYDATA                       6
BLOB                         32
CHAR                      12335
CLOB                        259
DATE                      16923
LONG                          2
NUMBER                    39618
NVARCHAR2                     9
RAW                          53
ROWID                       270
TIMESTAMP(3)                 37
TIMESTAMP(6)                109
VARCHAR2                 132291
XMLTYPE                       2


ADDING PARTITIONING MYSQL I'm using MySQL 5.7.17 and I have this table :

CREATE TABLE `posicaorastreadores` (
`Id` CHAR(36) COLLATE utf8_bin NOT NULL DEFAULT '',
`ComandoInicialString` VARCHAR(1024) COLLATE utf8_general_ci NOT NULL,
`Serial` BIGINT(20) NOT NULL,
`DataEnvio` DATETIME NOT NULL,
`DataGps` DATETIME NOT NULL,
`Latitude` DOUBLE NOT NULL,
`Longitude` DOUBLE NOT NULL,
`Velocidade` DOUBLE NOT NULL,
`TensaoBateriaPrincipal` DOUBLE NOT NULL,
`TensaoBateriaBackup` DOUBLE NOT NULL,
`DataInclusao` DATETIME NOT NULL,
`DataAlteracao` DATETIME NOT NULL,
`Ativado` TINYINT(1) NOT NULL,
`EnderecoArmazenado` TINYINT(4) NOT NULL DEFAULT 0,
`ModeloRastreador` INTEGER(11) NOT NULL,
PRIMARY KEY USING BTREE (`Id`),
KEY `enderecoArmazenado_idx` USING BTREE (`EnderecoArmazenado`),
KEY `serial_idx` USING BTREE (`Serial`),
KEY `datagps_idx` USING BTREE (`DataGps`),
KEY `ativado_idx` USING BTREE (`Ativado`),
KEY `ModeloRastreador_idx` USING BTREE (`ModeloRastreador`),
KEY `posicaorastreadores_idx1` USING BTREE (`ComandoInicialString`),
KEY `posicaorastreadores_idx2` USING BTREE (`DataEnvio`),
KEY `posicaorastreadores_idx3` USING BTREE (`Ativado`, `Serial`, `DataGps`)
) ENGINE=InnoDB
ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

I need to use partitioning.

I'm try to use with this command:

ALTER TABLE posicaorastreadores PARTITION BY HASH( DAYOFMONTH(DataGps) )
PARTITIONS 31;

I'm receiving this error:

  A PRIMARY KEY must include all columns in the table's partitioning function

Inorder to get rid of the error you have got, you may follow below command.

    CREATE TABLE posicaorastreadores (  Id CHAR(36) COLLATE utf8_bin NOT
NULL DEFAULT '',  ComandoInicialString VARCHAR(1024) COLLATE
utf8_general_ci NOT NULL,  Serial BIGINT(20) NOT NULL,  DataEnvio DATETIME
NOT NULL,  DataGps DATETIME NOT NULL,  Latitude DOUBLE NOT NULL,  Longitude
DOUBLE NOT NULL,  Velocidade DOUBLE NOT NULL,  TensaoBateriaPrincipal
DOUBLE NOT NULL,  TensaoBateriaBackup DOUBLE NOT NULL,  DataInclusao
DATETIME NOT NULL,  DataAlteracao DATETIME NOT NULL,  Ativado TINYINT(1)
NOT NULL,  EnderecoArmazenado TINYINT(4) NOT NULL DEFAULT 0,
ModeloRastreador INTEGER(11) NOT NULL,  PRIMARY KEY USING BTREE
(Id,DataGps),  KEY enderecoArmazenado_idx USING BTREE (EnderecoArmazenado),
KEY serial_idx USING BTREE (Serial),  KEY datagps_idx USING BTREE
(DataGps),  KEY ativado_idx USING BTREE (Ativado),  KEY
ModeloRastreador_idx USING BTREE (ModeloRastreador),  KEY
posicaorastreadores_idx1 USING BTREE (ComandoInicialString),  KEY
posicaorastreadores_idx2 USING BTREE (DataEnvio),  KEY
posicaorastreadores_idx3 USING BTREE (Ativado, Serial, DataGps)  )
ENGINE=InnoDB  ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE
'utf8_general_ci';

And then issue

  ALTER TABLE posicaorastreadores PARTITION BY HASH(
  DAYOFMONTH(DataGps) ) PARTITIONS 31;

You should add pk and partition key to be PK for the table.

PRIMARY KEY USING BTREE (Id,DataGps)

But looking at your table definition you have secondary indexes almost on all. You might want to verify on performance of all your selects. Or tune them inorder to add the partition name or partition key as a mandate clause in where clause. So its better you take perf against selects and updates you use and then think whether is it feasible to go for partitioning or archiving the table.

Also if you are anyway going for partition, you need to define the partition in such a way older partition can be removed. So I would suggest to go for

  PARTITION BY RANGE ( DAYOFMONTH(DataGps) ) ;

And define range each partitions.


Interesting problem. I'd use a calendar table:

calendar

calendar_date
--------------
2014-01-01
2014-01-02
...
2014-12-01
2014-12-02
..
2014-12-31
2015-01-01
...

and restructure the pricelist to use closed-open intervals, instead of closed ones:

pricelist

startHour | endHour | price
----------------------------
00:00     | 08:00   | 10
08:00     | 16:00   | 20
16:00     | 24:00   | 5

It would not work exactly as it is above because TIME datatype cannot hold the value of 24:00:00 hours, so you probably have to use datetime. (In the linked example, I used datetime2(0) but I think it would work with datetime as well.

Then you could use this query to check for overlapping intervals between your pricelist (statDate, enddate) intervals and the ones calculated from the calendar and the pricelist values:

SELECT
    startDate = CASE WHEN a.startDate < x.startD THEN x.startD ELSE a.startDate END,  -- max
    endDate = CASE WHEN a.endDate < x.endD THEN a.endDate ELSE x.endD END,            -- min
    a.jobID,
    p.price
FROM actualUse AS a
  CROSS JOIN calendar AS c
  CROSS JOIN pricelist AS p
  CROSS APPLY
    ( SELECT
          startD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.startHour),
          endD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.endHour)
     ) AS x
WHERE a.startDate < x.endD
  AND x.startD < a.endDate ;

The complicated DATEADD(day, DATEDIFF(day, ...) expression is there for adding a DATE and a DATETIME value:

SQL-Fiddle is down today, but (thank you SO!) we can test at dba.se Data Explorer


Delete using id's not found in loop - MySQL I have a stored procedure where I loop through an array passed as an argument, update the values provided, then delete the missing values using an id passed in the given array.

    /* edit the purchase items */
        WHILE items_index<items_length DO
            UPDATE PurchaseOrderItems SET quantity=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].quantity')) ,products_productid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].productid')) WHERE itemid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid'));
            IF items_index+1=items_length THEN
                /* condition will only be true in last iteration*/
                SET item_separator='';
            END IF;
            SET save_list = CONCAT(save_list,JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid')),item_separator);
            SET items_index=items_index+1;
        END WHILE;
        /* delete all other items that were not in the list */
        DELETE FROM PurchaseOrderItems WHERE purchaseorders_purchaseorderid=p_purchaseorderid AND itemid not in (save_list);

The delete command however deletes all the records except for the first item in the save_list variable. I have confirmed that the variable save_list has the id's to be skipped so where are things going wrong?

Maybe im wrong but the  SET item_separator='' looks empty. I could imagine that your ID´s you build there are not right and therefore he does not ignore them.
I'm going to rebuild a scenario like this and try to help you. But first see if the output of save_list is really a string of ID´s.


DBCC CHECKDB fails after restore to SQL Server 2017 I have a DB on SQL Server 2014 and after I restore a backup on SQL Server 2017 I get integrity check errors when running CHECKDB. When I restore it on another machine with SQL Server 2014 it works without CHECKDB reporting errors. I need to migrate the database to newer servers, so I can still update things as I see fit.

CHECKDB errors:

Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1737) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1738) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1739) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1740) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1741) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1742) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
Msg 8948, Level 16, State 6, Line 5
Database error: Page (1:1743) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60.
CHECKDB found 7 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 7 allocation errors and 0 consistency errors in database 'DBNAME'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBNAME).

You can find out what object(s) those pages belong to by running:

SELECT s.name, o.name, a.index_id
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.[schema_id] = o.[schema_id]
CROSS APPLY sys.dm_db_database_page_allocations
  (DB_ID(N'DBNAME'),o.[object_id],NULL,NULL,N'LIMITED') AS a
WHERE allocated_page_page_id BETWEEN 1737 AND 1743;

You can try rebuilding that index (or table if index_id is 0 or null) to see if that fixes the allocation problem. If it remains, you could try dropping the index and re-creating it or, again, if it is a table or heap, selecting everything into a new table, creating the new indexes there, dropping the old table, and renaming the new one.

If that doesn't help, you can inspect the contents of those pages, and determine if it is acceptable to lose the data as a part of your upgrade (or, if not, to not upgrade).

DBCC TRACEON(3604,-1);
DBCC PAGE(N'DBNAME', 1, 1737, 2);
DBCC PAGE(N'DBNAME', 1, 1738, 2);
DBCC PAGE(N'DBNAME', 1, 1739, 2);
...

If it is an acceptable loss then you can run the repair with allow data loss as suggested (it might be smart to keep a copy of the backup before repairing in case you ever need to pull the data from those pages in some way).


DBeaver Error 1146 (42S02) on SQL database restore- Table 'acumatica.AUNotification' doesn't exist I am trying to restore a database dump from a file for an online SQL course I'm taking into DBeaver. When I restore it, however, it runs for a little bit and then I get the error message

ERROR 1146 (42S02) at line 4037: Table 'acumatica.AUNotification' doesn't exist

I've tried:

Manually dropping the table via the mysql command line, and then copying the CREATE TABLE statement from the .sql dump file to manually create it.
Reinstalling SQL
Using sqldump except I think it just dumped the existing file.
Doing mysql -u root -p --database=acumatica < dump-acumaticadb-201903141403.sql

All of my attempts to restore it work for a little bit- when I refresh the database in DBeaver, it shows the table aunotification and all preceding tables. There are no subsequent tables. Also, I'm using the utf8mb4 charset, so I don't think that's the issue.

I would really appreciate any help someone could give- the other threads I've found online have not helped me.

Here's the full error message in DBeaver after restoring the database:

/usr/bin/mysql --host=localhost --port=3306 -u root acumatica
Task 'MySQL script' started at Sun Mar 22 11:43:56 PDT 2020
ERROR 1146 (42S02) at line 4037: Table 'acumatica.AUNotification' doesn't exist
Task 'MySQL script' finished at Sun Mar 22 11:44:01 PDT 2020
2020-03-22 11:44:01.556 - IO error: Process failed (exit code = 1). See error log.
2020-03-22 11:44:01.557 - java.io.IOException: Process failed (exit code = 1). See error log.
    at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.executeProcess(AbstractNativeToolHandler.java:182)
    at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.doExecute(AbstractNativeToolHandler.java:237)
    at org.jkiss.dbeaver.tasks.nativetool.AbstractNativeToolHandler.lambda$0(AbstractNativeToolHandler.java:52)
    at org.jkiss.dbeaver.runtime.RunnableContextDelegate.lambda$0(RunnableContextDelegate.java:39)
    at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)

https://imgur.com/a/Tvcc9k3 error log screenshot

https://pastebin.com/KfCRW2G2 create statements and surrounding file

The create table may not have used quotes around the table name, while a reference to it does.

Unless lower_case_table_names=1 is set upon database creation, the case of the tablenames has to be respected when quoted.


Can database backup and restore using pgdump resolve transaction wraparound in postgresql? I know the solution advocated is VACUUM. However, I was interested in knowing whether old versions of XIDs are marked as frozen when restoring a database dump  using pg_dump.

Also, this blogpost mentions that the transaction wraparound issue was resolved after truncating one of the large tables that had not been auto-vacuumed. How did the truncation help?

When restoring database from pg_dump, it will re initialize transaction id on the database. So, just like the beginning You create a database. It needs time for another transaction wraparound.

Transaction id wraparound is count on a per table level. If a table is truncated, but still another table is possible to be wraparound.

Hope it helps


Does tempDB disk perf matter if you have plenty of unused memory? We have a SQL Server 2012 Enterprise setup with a large amount of RAM (256 GB), and at this time fairly small databases (about 17GB). Right now we are experiencing performance issues due to various coding issues which will hopefully be resolved by some code reviews and optimization.  At the same time there is a movement to try to improve the performance by getting faster disk for the tempDB LUNs, like using a FusionIO PCIe flash card.

My concern is that given that SQL Server still has plenty of RAM to take advantage of, that we won't see any real performance improvement from adding faster disk.  I figure that most of the tempDB operations would take place in RAM, or take place on disk but end up cached in RAM (either on the host, HBA, or SAN).

Does anyone know if SQL Server 2012 will still end up waiting for IO blocking on tempDB disk operations when it has plenty of RAM to use, or is the tempDB disk activity we are seeing just there to provide persistence or durability?

Even if you have plenty of DRAM, tempdb may still be used.

This happens in a few situations:

Snapshot isolation: Using this feature can create a lot of tempdb activity.

Hash and sort Spills: When the optimiser creates a query plan, it will try to estimate the total amount of memory it needs to run the query. Before the query runs, the estimated memory is allocated. If it turns out (for example, due to bad statistics) that the allocated memory is too small, then SQL Server will spill operators like hash tables and sort buffers to tempdb, EVEN if there is enough memory after the query has started to satisfy the requirement.

Or course, if your queries are perfectly tuned and you run without snapshot isolation, you can avoid these effects. But in most situations, some amount of tempdb activity is to be expected, even on a large DRAM system.


Error Logs various questions I'm slowly collecting and trying to analyze lot of information on all of our instances (2008R2 SP1). I'm now going through the Error Logs and I've couple of question. There is a lot about error log size and found different recommendations on using sp_cycle_errorlog to recycle the logs on say, weekly basis, to avoid having very big error logs and be able to handle them adequately. So...

When an error log is considered to be "big"? Some of our logs take long to load, close to a million of rows. edit: one has 2 034 546 rows
I'm trying to find some tsql to get error log size, but can't find it, it is possible to do it?
I see the recommendation to recycle the logs and to configure more than just the default number of 6. Some authors recommend 50, others 10. I guess it depends on the environment, but is there a recommended number?

When an error log is considered to be "big"? Some of our logs take long to load, close to a million of rows.

This is going to be different for certain scenarios, but with your description ("Some of our logs take long to load"), that's my definition of big. When file operations (copying, moving, etc.) start to take a little longer than instant, or parsing an individual error log becomes an actual time consumer, then that's when I'd start to work on cycling my error log regularly to keep that size down. I know I'm not giving you a "X MB is too large of an error log file" statement, but hopefully this gives you insight. Outside of extremely abnormal and corner cases, the error log size shouldn't be a pain point in troubleshooting.

  I'm trying to find some tsql to get error log size, but can't find it, it is possible to do it?

As far as T-SQL is concerned, outside of a call to xp_cmdshell, I can't think of a way to get this. And because I wouldn't open up that security vulnerability for this type of operation, my recommendation would be to use PowerShell, consuming SMO:

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YOUR_SQL_SERVER_NAME")

$SqlServer.EnumErrorLogs() |
    Select-Object Name, @{Name = "SizeMB"; Expression = {[System.Math]::Round($_.Size/1024/1024,2)}}

  I see the recommendation to recycle the logs and to configure more than just the default number of 6. Some authors recommend 50, others 10. I guess it depends on the environment, but is there a recommended number?

There's nothing worse than not having historical data due to rollover, no matter what the source of troubleshooting is (SQL Server error logs, XEvents logs, etc. etc. etc.).  You need to really see what your cycling schedule is, and then determine how many days you want to retain. That may be different from server to server. It's hard to give a default retention value here, as there are very different factors such as available hard disk space, average error log generation size (normal conditions) during a 24-hour period, how far you care to go back with logs, and a few others. If you have the disk space with a comfortable buffer (always needs to be a buffer when planning out storage consumption), I personally like to err on the side of too many archived error logs. A conservative bet would be a retention duration of 4 weeks, but again there's too many factors to just making a hard and fast rule based off of. That's really a determination that you need to make, though.  As a DBA and a troubleshooter, how far back do you want your error logs to go?

Side Note: I see in your original question that you referenced sp_cycle_agent_errorlog.  It seemed to me like your question may have been more geared towards the instance's error log cycling, in which case it would be the sp_cycle_errorlog stored procedure.


Can I convert date format for this value '19920000'? I have a value 19920000. This is date format yyyyMMdd. I want to save by date field in the table. You may see it is invalid data because day and month value is zero. Can I convert date format to this value in SQL Server? Can or not?

How to save just only year in date field?

I get the data from excel sheet. These data are customer birth date. Some people want to hide day and month of birth date. They don’t gave to right date format. My application is read data from excel sheet and show on datagridview table and then save to database. The data is big amount which round about 30000 records above at least for one time read. I can’t separate column for this data field because performance may be down.

I am using SQL Server 2008.

.

The date type can't store partial dates, like only year, or only day and month.

In one project I had a need to store incomplete dates and I used three separate nullable columns for day (tinyint), month (tinyint) and year (smallint). I don't see in what cases you'd need to use varchar instead of integer types.

In my case it was information about customer birth dates and quite often salespeople didn't know the year, but knew month or day. With this approach it is very flexible and allows the user to enter any bit of information he has. It was also important to know that certain part of the date is missing, such as "we don't know the exact birth date, but we know that it is in June".

Obviously, there were a bunch of custom checks in the application (not at the database level) trying to prevent entering things like 31st of June. In that particular project it was OK to perform the checks in the application. Most likely it would have been possible to implement them in T-SQL as well. In general, it is better to have constraints implemented at the database level, because applications come and go, but data remains.

It was more difficult to use these incomplete dates in queries as well. Different queries/reports required different approaches to dealing with the missing bits.

30K rows is not much at all and performance should not be a problem. So, I'd focus on smart parsing and validation of your Excel data and storing all information that you can extract.


MongoDB durability and consistency with j= true and w:majority Assume there is a 7 member replica set, where each node is a voting member and no arbiter node is present. For a write w1, Using j=true and w:majority would ensure that a write is written to the journal and let's assume to the disk also after the default time interval.

If 3 nodes fail (all having the write w1 mentioned above) at the same time including the primary, only one of the remaining 4 nodes has the write w1. If this node does not get enough votes (say it's overloaded and will not be suitable for role of a primary), and some other node gets all enough votes to become primary, we have basically lost the write w1.

I know this is a rare case, but it still can happen. So isn't it true that using j= true and w:majority also will not assure that the writes are durable?

This is not how it works. With j:true and w: majority, w1 would only be acknowledged when the majority of nodes actually had reported to the primary that they have put the operation to the journal. Since the primary went down, this acknowledgement would never happen. So in this edge case, you would only have to do what you'd need to do what you always need to do if a failover happens before a write operation is persisted: redo it.

Let us expand the example a bit: let us assume that the write was successful, and then the nodes which already received the writes minus one go down. Now, an election takes place. The new primary is elected basically by each remaining node announcing the latest timestamp they have in the oplog. So, the node which received w1 would win (since the oplog is processed consecutively for each entry) and the remaining nodes would start to process the oplog from their latest position to the new primaries latest position - w1 will be replicated and hence it will be eventually consistent on all nodes.


Excel spreadsheet into a MySQL database tables I am relatively new at database design and would like to get this right from the start. I have had differing advice on how to do this and believe that the common suggestion of importing all the data is just wrong as the data won't be normalised and will have a lot of redundant repeated data. I come from an MS Access background rather than MySQL.

This question is about the design of the database tables not on the PHP processing to get it into the tables.

I need to add a lot of custom data to a Wordpress website. This data is currently in multiple Excel spreadsheets and has a lot of duplicated data in both rows and columns and appears to me to be ideal for splitting into 4 tables. The main table will contain initially around a million rows and will grow regularly. I intend to have a front end upload facility for certain users to upload their files and would like to automate this.

Each spreadsheet contains 10,000 to 15,000 rows. A lot of the rows have duplicated data with just two fields transposed with another two fields so these rows can be deleted in the database.

I have attached an image of part of one of the spreadsheets.

My current thinking is to have one table for Column E as every row contains exactly the same data. A second table for Column N as there is lots of repeated data  here. A third table for column's A B F G H with linked data to the first two tables and a fourth table for columns C D I J K L M with linked data in the other 3 tables. As you can see rows 2 and 3 are the same except for transposed data between columns I and J with columns C and D but this is not always the case See rows 8 and 9 for instance).

Is this the best way to break up the data or should I just be looking at one large table. The data will be queried in different ways (4 result sets). It is unlikely that once the data is entered it will need to be updated.

I would welcome any thoughts or advice before I actually start building this.

I did not look in detail at the data since I belief you can normalize it yourself, but from what I've grasped out of your posting I would say you are doing everything right.

One warning though: Likely you will encounter a lot of existing inconsistencies in the excel sheets. You must fix those in order to import and normalize the data correctly. This constitutes a high initial effort.

The alternative (keep the data in the form it is) instead will rise the maintenance costs of the application.

In the long run I claim that the additional initial costs will be less than the regular addition in maintenance cost.


Read performance when indexing one column vs indexing multiple columns (MySQL, InnoDB)

I have a table that has columns A, B and C. That table will have a lot of rows, let's say a million rows. I have a query that will be executed very often and will search the table by the A column, so I added an index on the A column.

I have another query that will not be executed as often as the first one but still often enough to qualify for a speed increase. That query will search by all the three rows.

My question is, will the second query have any speed increase from the index on column A or would I have to add indexes on all three columns in order to increase read speed? In other words, in a query where I have three WHERE conditions, will MySQL first search by the indexed column? Because if it does, out of those million rows, the first column would filter out maybe 100 or so, and querying those by the other two columns would be somewhat trivial and wouldn't require an index.

The index is a guide to the main table.  So your first query will use the Index on column A to identify which rows in the table have the data it wants, then does a key lookup to get the rest of the data and do further filtering if necessary.

So your second query will also use Index A and then do a key lookup and then do further filtering as required.

But you can have just a single index on A,B and C and your first query will still use that index (providing that A is the first column indexed).

So you can have your cake and eat it too.  I wouldn't create both indexes, I would just create the one on A,B and C.  However, If your table is narrow and only has four or five columns then I would only index on A.


MySQL case sensitivity for table names Why is it that different platforms (linux vs windows) does not support the same naming conventions? I'm stunned by the fact that the documentation uses file names as an excuse for this.

Yes, windows is not case sensitive for file names but one would think the developers would accommodate for this. I (personal preference) despise the under_score_convention and prefer camelCaseConvention.

Is there a way to enable the camel case sensitivity when connecting to the database, or is this something that has to be done in the default configuration?

Are there alternatives to MySQL that support the camelCaseConvention as per default on all platforms?

Edit

Ok, I feel a bit stupid now, I updated to mysql 5.5 and the camel-case convention is now supported, by lowering the entire name. It does not however preserve the camel-case, which is part of what I'm looking for.

The SQL standard defines that identifiers are not case sensitive (unless double-quoted: "CamelCase"). Your desire for CamelCase (mixed-case) names conflicts with the standard and you'd better learn to live with it. Or you start double-quoting your identifiers and never forget to do so.
Most RDBMS actually implement standard(-like) behavior. Even MySQL tries, lately, but it's very hard to break with established syntax rules.
Resources

MySQL manual on this topic.
Related article running a test case against the most popular RDBMS.
A list of how the most prevalent SQL dialects deal with delimited identifiers in wikibooks.


Separate license needed for Visual Studio 2012 SQL Server database projects? We have SQL Server 2014 Standard Edition and would like to use Visual Studio 2012 Database Projects for version control.

Do we need to purchase a license to use VS database projects? Or is it included with our standard edition license; we just have to download it separately?

You don't need a special SQL Server license to use Visual Studio database projects. As mentioned in srutzky's answer the database projects are driven by SSDT which is a free download.

If you are willing to use a newer version of Visual Studio than 2012, and you want to use a database project within Visual Studio then you should consider Visual Studio 2015 Community Edition. As you can see in the "Muliple languages" section you can choose a "SQL Server" project type/template which is the database project.

As far as I recall none of the free versions of Visual Studio 2012 allowed you to create database projects. That means if you're committed to using Visual Studio 2012 to create/manage/etc. your database project then you'll need to buy an appropriate license for the version of Visual Studio 2012 you want to use.

Note the licensing restrictions are at the bottom of the Visual Studio 2015 Community Edition that I linked to above. The cliff notes version at the time of writing this answer is:

Individuals

  Any individual developer can use Visual Studio Community to create their own free or paid apps.

Companies

  An unlimited number of users within an organization can use Visual Studio Community for the following scenarios: in a classroom learning environment, for academic research, or for contributing to open source projects.

  In non-enterprise organizations, up to five users can use Visual Studio Community. In enterprise organizations (meaning those with >250 PCs or >$1 Million US Dollars in annual revenue), no use is permitted beyond the open source, academic research, and classroom learning environment scenarios described above.

The full license terms are found here.


Best way to store currency values on MySql database I'm working on an application where we have some services that requires a monetary value to calculate budgets for services. Currently we are using columns DECIMAL(10,2).

While analysing some of the generated budgets I've noticed some of them returns values like 180.0000000024.

Reading on other questions, I've only found the same type of answers, to go with decimals. In a few cases I saw a recomendation to use INT (or similar) instead. The reason is because the calculations will be more precise.

Example: $64.58 + $348.99 would be calculated like 6458 + 34899 and stored the result 41357 and later on converted to 413.57.

So it left mw with this questions, on which is the best way to store currency values on the database. Is DECIMAL really the best way? How much of a problem those extra decimals values can be a problem? Is INT (or similar) type going to improve something?

Do not use FLOAT or DOUBLE.  If it is a single currency, then use DECIMAL(m,n) with m and n suitably chosen.  If different currencies are stored in the same column, values for m and n become messy.  And you can run into rounding issues -- because of different laws.

DECIMAL goes through a package that does INT-like stuff transparently.  DECIMAL(19,4) will be stored in

6 digits in (effectively) a `MEDIUMINT` (high-order digits)
9 digits in (effectively) a `INT`
4 digits in (effectively) a `SHORTINT`  (after the decimal point)


Restoring database to UNC path on local drive When I try to restore a database using a restore command with a local UNC path:

RESTORE DATABASE [dbname]
FROM DISK = N'\\PC91\D\backup.BAK' WITH  FILE = 1,
MOVE N'test' TO N'\\PC91\D\dbname.MDF',
MOVE N'test_log' TO N'\\PC91\D\dbname_log.LDF',
NOUNLOAD, STATS = 10

I get an error:

  Msg 3634, Level 16, State 1, Line 1
      The operating system returned the error '5(Access is denied.)' while attempting 'CreateFileW' on '\PC91\D\dbname.MDF'.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

If I use a local drive letter instead, then it works:

RESTORE DATABASE [dbname]
FROM DISK = N'D:\backup.BAK' WITH FILE = 1,
MOVE N'test' TO N'D:\dbname.MDF',
MOVE N'test_log' TO N'D:\dbname_log.LDF',
NOUNLOAD, STATS = 10

This command also restores the database to same folder. So why is there an error when I specify the network path?

Sounds like "access is denied" -  make sure that the SQL Server service account can access the share with both read and write (whether you can access the share is irrelevant). If you did not create a share named D that points to the root of D:, then your syntax is wrong - you need to use D$. However I recommend creating a share that is NOT in the root of a drive, and making sure that the SQL Server service account (again, not you unless you manually set the SQL Server service account to run as your Windows account) has read and write access. Then it would be:

MOVE N'test'     TO N'\\PC91\ShareName\Data\test.mdf',
MOVE N'test_log' TO N'\\PC91\ShareName\Log\test.ldf'

However I have to wonder why you would want to use a UNC path for this in the first place. It means your database files are more at risk because the share can be removed or become temporarily unavailable while SQL Server is running.


How to connect to 5gbfree hosting? Well, I have no exact information to remotely set up my connection to 5gb free. I have already added my IP address to remote_connection settings.
They have provided this info only.

database name = username_dbname
database user = username_dbuser
host = localhost
port = default
database password = dbpassword

I am unfamiliar with 5gbfree hosting, but the host = localhost for me is a clear indication that the host may not be accesible from the public internet, only the application server will have access.

Check if you can change the database server and network configuration/firewall, but I would strongly suggest to, asuming you have ssh remote access or similar to tunnelize the mysql connection (e.g. https://stackoverflow.com/questions/22639188/how-can-i-use-ssh-tunneling-to-connect-to-a-remote-mysql-server ) so you don't expose the mysql service to the outside, but still can access from your development machine.

If tunnelizing or opening ports are not options, consider setting up a local test database on your development machine instead rather than connecting directly to the one on your hosted environment or installing web administration tools such as phpmyadmin. Free hosting services are, for a reason, not very flexible. If you want to test mysql but cannot afford to pay for it I would suggest taking advantage of the 1 year free of limited hosting on AWS and the free accounts of RDS and EC2.


Why can you only drop a SQL Server view in the current database? Just noticed this and was having a hard time coming up with a technical reason for it.

Tables can be dropped using three part names, but views are restricted to two part names.

What is the reasoning behind this?

The documentation is skimpy on this, merely stating (and providing a syntax diagram) that only current database is relevant.

  Removes one or more views from the current database.

DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]

Someone fluent in the debugger could determine if this is because of a code path that is only hit when dropping certain types of objects, or you could ask someone at Microsoft. Regardless of the reason, and whether or not you actually know the reason, you still need the same workarounds... you can do

USE your_database;
GO
DROP VIEW dbo.viewname;

Or:

EXEC your_database.sys.sp_executesql N'DROP VIEW dbo.viewname;';


Disable auditing of select statements Somehow the auditing of select statements was enabled in our oracle database. We have several data processing apps that are generating upwards of 15 GB per day in the audit trail. Now we cannot turn it off because it seems that the noaudit command needs to match the original audit command, which of course no one knows. Does anyone know how to undo this? Can we turn off all auditing and then turn on the usual assortment of audit actions (alter, create, drop, etc.)? We have ran all kinds of noaduit permutations: “noaudit select table by USER;” and “noaudit select any table;” and yet it still posts select statements to the audit trail.

All enabled audit rules can be found in the below views:
Standard audit + FGA:
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts;
select * from dba_obj_audit_opts;
select * from dba_audit_policies;

Unified audit (12c and above):
select * from audit_unified_enabled_policies;

Unified audit policy details:
select * from audit_unified_policies;

With the above, you can find whatever audit was enabled, and disable/modify as needed.


Oracle 11g connect overhead Currently one of our new applications is connecting to an Oracle 11g instance over 30,000 daily (at every user request) via their application server, using the same account. (it will be significantly more when its in production) We are trying to convincing the developers to use a connection pool instead of connect/disconnect. However, I want to make sure, I first understand all the overheads associated with creating a connection.

I know the large volume of connections create a lot of overhead, and a lot of process will have to be spawned. Which results in connect being a fairly expensive operation.

However, what steps are exactly happening with a connect that creates the overhead?

The problem is the locking in the SGA that effectively cause the the concurrency is going down. This kind of apps make the database a close to single user platform instead of a highly scalable and concurrent accessible database. The cause of this is that the connections have to be maintained in a shared memory table where inserting and deleting means locking and causes serialization. There is a nice video series about this kind of real world performance problems connection pooling Implementing connection pooling in the demo app in the video causes a 10 fold performance upgrade ...

The spawning of the processes does cause a little overhead but that is handled well by the OS.

Next to that, the listener log wil grow quickly, causing every next connection to be slower than the previous one.

If session auditing is enabled - a default - the sys.aud$ table will explode and make your system tablespace grow very rapidly. I see many databases where this is the cause of an enormous system tablespace and sometimes even bigger than the rest of the database, because of this connection problem.


What is the recommended method to move databases? I want to know the recommended method to move a database and data from dev to pre-prod to production in SQL Server. The databases are originally created in MS Access so some upsizing to SQL Server will be needed

The 3 options I've read about: (I'm sure there are others)

Use Detach and Attach - I've been led to believe this doesn't give a clean build and may cause issues but is easy to do as just moving/copying the .mdf, .ldf files
Script the database and objects into each environment - this provides a clean build but is more time consuming
Do a back-up/ restore - takes longer but appears to be a favored method.

Any advice most welcome.

Do a back-up/ restore - takes longer but appears to be a favoured
  method.

This is what we prefer. -- Backup and Restore. Safer Method.

Option to detach the database using sp_detach_db and then attach it using CREATE DATABASE ... FOR ATTACH; or detach and attach using the GUI will actually detach the database from the source server.

Many things can go wrong here, which is why we prefer backup and restore. The backup process is online and since the source database was never made un-available in the first place, you don't risk destroying anything in case of mistakes, data corruption, or whatever other things that can happen.

Also for data move, depending upon how frequent you're data needs to be moved from Dev to pre-prod to prod, RedGate's SQL Compare tool can be used to keep the schema of 2 databases in sync.(Includes cost but depends upon you're needs and wallet)

http://www.red-gate.com/products/sql-development/sql-compare/

Also, something similar to Redgate as free open source tool you can find something :https://code.google.com/p/sql-dbdiff/


Database size not reduced after compressing tables Using Data Compression, my table size was reduced from 1770 MB to 250 MB, but the database size was not reduced. Why?

You can also shrink a database and shrink database files. See MS:

http://msdn.microsoft.com/en-us/library/ms189035(v=sql.105).aspx

Erik


How to monitor the performance of a mysql database during development? Is there a standard approach to monitor the performance of an application and its database, which is still in design?

What I'm doing so far: I develop queries and tests directly in mysql workbench and monitor the msec used per query. On top of that I'm using mocha tests in node.js to test the database api and also try here to measure the typical impact on the database.

The standard approach I know is: do your design and one day, as the amount of data has grown, there is a bad surprise, that the database needs to be optimized. I'm asking for good practices to monitor performance from the beginning.

My favorite monitoring tool is the slowlog, with long_query_time=1.  And use pt-query-digest to summarize the results.  Then work on improving the first few queries in the list.  They will, by default, be ordered with the "worst" queries first -- either slow but infrequent or fast but frequent (or both).

Digging into why a particular query is slow (or frequent) may lead to a missing index, the need for a 'composite' index, reformulation of a query, redesign of the code, tuning, more RAM, or any of a dozen other things.

More here


List user-defined schemata If I create schemata using...

CREATE SCHEMA [schema_name] AUTHORIZATION dbo

...is it possible to obtain a list of these user-defined schemata?

I can see that there's an INFORMATION_SCHEMA.SCHEMATA view but this doesn't offer any indication that I defined the schema.

There's also a sys.schemas view which lists the principal_id as 'dbo' - is it safe to assume that all schemata which reference 'dbo' as the principal_id are user-defined, or is there a safer means?

This query will show you any schema which includes objects shipped by Microsoft:

SELECT s.name
FROM sys.schemas s
WHERE EXISTS (
    SELECT 1
    FROM sys.objects o
    WHERE o.is_ms_shipped = 1
        AND o.schema_id = s.schema_id
    )
ORDER BY s.name;

If you run that, you'll see dbo and sys listed.

This query shows the owner for each schema present in the current database:

SELECT [Schema] = s.name
    , [Owner] = dp.name
FROM sys.schemas s
    INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
ORDER BY s.name;

When a schema is created, the "owner" of the schema is either the users default schema or dbo, unless a schema owner is declared in the schema definition.  Take for example:

USE MaxTest;
GO
CREATE USER SchemaOwner WITHOUT LOGIN;
GO
CREATE SCHEMA MaxSchema AUTHORIZATION SchemaOwner;
GO

SELECT [Schema] = s.name
    , [Owner] = dp.name
FROM sys.schemas s
    INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE s.name = N'MaxSchema';
GO
DROP SCHEMA MaxSchema;
DROP USER SchemaOwner;

The output shows:

╔═══════════╦═════════════╗
║  Schema   ║    Owner    ║
╠═══════════╬═════════════╣
║ MaxSchema ║ SchemaOwner ║
╚═══════════╩═════════════╝

For schemas that have been created recently, you can use the default trace to see who created that schema.

First step is to gather the default trace events into a temp table:

DECLARE @trcfilename nvarchar(260);

SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;

IF OBJECT_ID(N'tempdb..#trctemp', N'U') IS NOT NULL
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

Once you've got the details in the #trctemp table, use the following query to show schema create events, along with the login that created the schema:

SELECT [Login] = sp.name
    , ObjectName
    , DatabaseName
    , tt.StartTime
    , EventSubClass = CASE tt.EventSubClass
        WHEN 0 THEN 'Begin'
        WHEN 1 THEN 'Commit'
        ELSE 'Unknown'
      END
FROM #trctemp tt
    LEFT JOIN sys.trace_subclass_values tsv ON tt.EventClass = tsv.trace_event_id
        AND tt.EventSubClass = tsv.subclass_value
    LEFT JOIN sys.server_principals sp ON CONVERT(varbinary, tt.LoginSid, 0) = sp.sid
WHERE tt.EventClass = 46 --Object Created
    AND tt.ObjectType = 17235 --Schema
    AND tt.DatabaseID = DB_ID()
ORDER BY tt.EventSequence;

sys.trace_subclass_values is deprecated, and may go away in a future version of SQL Server.  However, it is unlikely that it will go away anytime soon.

Output looks like:

╔═════════════╦════════════╦══════════════╦═════════════════════════╦═══════════════╗
║    Login    ║ ObjectName ║ DatabaseName ║        StartTime        ║ EventSubClass ║
╠═════════════╬════════════╬══════════════╬═════════════════════════╬═══════════════╣
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:01.223 ║ Begin         ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:01.223 ║ Commit        ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:26.747 ║ Begin         ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:26.747 ║ Commit        ║
╚═════════════╩════════════╩══════════════╩═════════════════════════╩═══════════════╝


Why database size is smaller in RDS I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer.

I use this query:

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "schema_name";

But the size I see in HeidiSQL for RDS is much smaller.

Before = 253MB
RDS = 100MB

An example of a table with similar rows, but very different size:

Before

RDS

Is there any possible reason, or I am missing something?

Please note this Pictorial Representation of ibdata1 (from Vadim Tkachenko)

You have many moving parts inside ibdata1

Data Dictionary
Double Write Buffer
Insert Buffer
Rollback Segments
Undo Space

Almost 3 years ago, I wrote a post addressing why ibdata1 can grow too fast : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
If the database experienced lots of INSERTs, UPDATEs, DELETEs, you will likely have all kinds of fragmentation in ibdata1. This would be due to lots of transactions of varying sizes.
Another aspect would be variable length columns in a table. If the rows in your tables have TEXT fields, data could be bloat due to fragmentation from UPDATEs of such columns. I wrote about this 4.5 years ago : Different MySQL Datafile Sizes After Restoration
What you are seeing does not surprise me.
SUGGESTION
You may want to run CHECKSUM TABLE against source and target tables to verify the content is the same. If checksums do not match after reloading a mysqldump and you have TEXT or any variable columns, then you will have to mysqldump source and target and compare the data from that aspect.


How can I configure PostgreSQL 9.0 to start automatically under Windows XP? Yesterday I installed PostgreSQL 9.0 on Windows XP, and after the installation the DB was running. After booting the system this morning, the data base server did not start automatically.

How do I configure Windows XP to start the postgresql demon after each boot/reboot? (I'm a Unix guy, and guess that's easy for someone knowing Windows.)

Here's a couple things to check:

I believe the Windows PostgreSQL installer has an option you have to check saying you want to run PostgreSQL as a service.  Make sure you used this option when you did your install.
Make sure the service is set to start automatically.

Start->Control Panel->Administrative Tools->Services
Find the service (should be named something like postgresql-9.0)
Make sure the Startup Type is set to Automatic


Is query executed when mysql server has gone away? How can I be sure that a query was not executed?

For example, I try to execute

update `table` set `a`='b'

and got the error 2006 server has gone away

Don't run the SQL statement :-)

If you want the mysql client to stop you running it, there is a --safe-updates aka --i-am-a-dummy option.

If you want the server to rollback, use innodb tables that provide transactional support.

For the error see gone away.


How do I run a query only asking for a max date of attendance? I'm new to SQL and the school I work for wants to run a query on students' transcripts. We want to find out the most recent school the student attended prior to coming to ours. I think I need to use a Max date clause, but I'm not sure. Can someone help me? Thank you in advance.

I have to assume some things here because I'm not sure what your table looks like or what your columns are, but let's assume you have a table called StudentHistory.

Let's say StudentHistory contains the columns StudentID, StudentName, SchoolName, LeaveDate.

Each student may have multiple entries depending on how many schools they have frequented. These entries can be ordered by the LeaveDate column to get a chronological history of attended schools.

What you want as an output can be articulated as:

  For each student, return the StudentID, StudentName, SchoolName for
  the last School attended based on the latest LeaveDate.

You can achieve this using the RANK function:

WITH StudentData AS (
    SELECT StudentID,
        StudentName,
        SchoolName,
        RANK() OVER(PARTITION BY StudentID ORDER BY LeaveDate DESC) AS TheRank
)
SELECT *
FROM StudentData
WHERE TheRank = 1 ;


Key Lookup and Full-text index I have the following query:

SELECT T2.Title
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
    AND T2.FKID2 = @ID_PARAM2
    AND CONTAINS(T1.Title, '"' + @SINGLE_WORD_PARAM +'"')

Title is of type NVARCHAR(100). All the IDs are INT.

My Execution Plan is such that it is using the Key Lookup (Clustered) operation 80+% of the time:

Predicate: TitleTable.FKID2 AS T2.FKID2 = @ID_PARAM2
Object: TitleTable T2
Output list: TitleTable.Title

Each and every mentioned column is in its own non-clustered index, Title is additionally in the full-text index and there's a clustered index on the primary key as well. Is there a way to include the full-text index in the covering index or something?

EDIT:

Key lookup operations can be avoided by the use of a covering index.  Full-text-indexes cannot be "included" in a covering index, however, including the TitleTable in the covering index is still useful since SQL Server can find all the details it needs for the query, aside from the full-text-query results, by seeking the covering index.

I've created a simple test-bed to show this in action.

First, we'll create an empty database for our test, since we cannot create full-text catalogs in tempdb:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'FullTextTest')
BEGIN
    ALTER DATABASE FullTextTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE FullTextTest;
END
CREATE DATABASE FullTextTest;
ALTER DATABASE FullTextTest SET RECOVERY SIMPLE;
BACKUP DATABASE FullTextTest TO DISK = 'NUL:';
GO

Here, we'll create a mock-up of your table.  You say in the question that all mentioned columns have an associated non-clustered index, so we'll define those too:

USE FullTextTest;
GO

CREATE FULLTEXT CATALOG ftc
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION dbo;

CREATE TABLE dbo.TitleTable
(
    PK_ID int NOT NULL
        CONSTRAINT PK_TitleTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FKID1 int NOT NULL
    , FKID2 int NOT NULL
    , Title nvarchar(100) NOT NULL
);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID1
ON dbo.TitleTable (FKID1);

CREATE NONCLUSTERED INDEX IX_TitleTable_FKID2
ON dbo.TitleTable (FKID2);

CREATE NONCLUSTERED INDEX IX_TitleTable_Title
ON dbo.TitleTable (Title);

Here's the full-text index:

CREATE FULLTEXT INDEX ON dbo.TitleTable(Title)
KEY INDEX PK_TitleTable
ON ftc
WITH (CHANGE_TRACKING = AUTO, STOPLIST SYSTEM);

I've got a database with around 47,000 words in it which I'll use to fill the dbo.TitleTable table:

INSERT INTO dbo.TitleTable (FKID1, FKID2, Title)
SELECT wl.WordRow, wl.WordRow, wl.Word
FROM WordsDB.dbo.WordList wl;

Here's the query from your question:

DECLARE @ID_PARAM1 int;
DECLARE @ID_PARAM2 int;
DECLARE @SINGLE_WORD_PARAM nvarchar(100);
SET @ID_PARAM1 = 46777;
SET @ID_PARAM2 = 46777
SET @SINGLE_WORD_PARAM = N'"' + 'it' + N'"';
SELECT T2.Title
FROM TitleTable T1
    INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
    AND T2.FKID2 = @ID_PARAM2
    AND CONTAINS(T1.Title, @SINGLE_WORD_PARAM);

At this point, if we run the query, we see the following plan:

As expected, there is an index seek on the IX_TitleTable_FKID2 non-clustered index, with an associated key-lookup against the table itself for the Title column.

If we add a compound index on both FKID2 and FKID1, we'd expect a different plan, which is what we get:

CREATE INDEX IX_TitleTable_FTS_Cover_NoInclude
ON dbo.TitleTable (FKID2, FKID1);

However, the key lookup for the Title column is still there.  What if we add an INCLUDE clause to our index above?

CREATE INDEX IX_TitleTable_FTS_Cover
ON dbo.TitleTable (FKID2, FKID1)
INCLUDE (Title);

Success!  No key-lookup operation required.  The cost of the query has also dropped from 0.016 to 0.013, so that's a win.


This query is choking the server. How can I improve it? Following query is hogging a lot of resources, as much as that the queries are getting locked down & CPU usage goes through the roof!

The MySQL Processes list shows that as the number of requests for the queries increases, the time to completion also increases... It reaches to a point where queries just lock down.

As per the web host, there is some problem in the query design, but I am unable to figure it out.

SELECT DISTINCT pet.*,ownrs.id AS ownrunique, ownrs.super,
(SELECT count(*) from comments WHERE pet.id=comments.petid)
    AS noOfCom,
(SELECT count(*) from petfb WHERE pet.id=petfb.petid AND (feedback=1 OR feedback=100))
    AS newLikes,
(SELECT count(*) from petfb WHERE pet.id=petfb.petid AND (feedback=5 OR feedback=6))
    AS newDisLikes,
ownrs.name
FROM pet, ownrs
WHERE 1=1 AND pet.ownerID=ownrs.uniqueID AND catid=21
ORDER BY  date   DESC  LIMIT 0,10

EXPLAIN:

id  select_type         table       type    possible_keys   key         key_len ref                 rows    Extra
1   PRIMARY             pet         ref     catid           catid       4       const               4758    Using where; Using temporary; Using filesort
1   PRIMARY             ownrs       eq_ref  uniqueID        uniqueID    52      petdb.pet.ownerID   1
4   DEPENDENT SUBQUERY  petfb       ref     petid,feedback  petid       4       petdb.pet.id        11      Using where
3   DEPENDENT SUBQUERY  petfb       ref     petid,feedback  petid       4       petdb.pet.id        11      Using where
2   DEPENDENT SUBQUERY  comments    ref     petid           petid       4       petdb.pet.id        1       Using index

DESCRIBES of the 4 tables (linked to avoid lengthy post).

EDIT 1

Based on Bohemian & a1ex07 replies, I am now using the following query. Everything is working OK but the noOfCom is being calculated wrongly for the pets that have >0 comments - it just equals to the total number of feedbacks from the petfb table for that particular pet, not the actual no of comments. For the pets that have no comments, it shows correct as zero. What could be wrong here?

SELECT pet.*, ownrs.uniqueID AS ownrunique, ownrs.diamond, COUNT( comments.id ) AS noOfCom,
SUM( feedback =1 OR feedback =100 ) AS newLikes,
SUM( feedback =5 OR feedback =6 ) AS newDisLikes
FROM pet
JOIN ownrs ON pet.ownerID = ownrs.uniqueID
LEFT JOIN comments ON pet.id = comments.petid
LEFT JOIN petfb ON pet.id = petfb.petid
WHERE catid =13
GROUP BY pet.id
ORDER BY DATE DESC
LIMIT 0 , 10

EDIT 2

pet indexes:

Keyname     Type        Unique  Packed  Column          Cardinality     Collation   Null    Comment
PRIMARY     BTREE       Yes     No      id              40499           A
petindex    BTREE       Yes     No      detail (200)    40499           A
catid       BTREE       No      No      catid           62              A
date        BTREE       No      No      date            40499           A
detail      FULLTEXT    No      No      detail          1

petfb indexes:

Keyname     Type    Unique  Packed  Column      Cardinality Collation   Null    Comment
PRIMARY     BTREE   Yes     No      id          1222897     A
petid       BTREE   No      No      petid       71935       A
feedback    BTREE   No      No      feedback    18          A
deviceid    BTREE   No      No      deviceid    55586       A
ownrid      BTREE   No      No      ownrid      671         A

EDIT 3 - SHOW CREATE TABLE petfb

CREATE TABLE `petfb` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `petid` int(11) NOT NULL,
 `feedback` int(11) NOT NULL COMMENT '1=Like 100 = Hidden Like  5=Inappropriate  6=Dislike  7=Duplicate  8=Wrong Type',
 `fbdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `ownrid` varchar(50) NOT NULL,
 `deviceid` varchar(50) NOT NULL,
 `sugcatid` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `petid` (`petid`),
 KEY `feedback` (`feedback`),
 KEY `deviceid` (`deviceid`),
 KEY `ownrid` (`ownrid`)
) ENGINE=InnoDB AUTO_INCREMENT=1215274 DEFAULT CHARSET=latin1

First add the following indices:

ALTER TABLE pet
  ADD INDEX catid_date_index
      (catid, date) ;

ALTER TABLE petfb
  ADD INDEX petid_feedback_index
      (petid, feedback) ;

Then try this query:

SELECT pet.*
     , ownrs.id AS ownrunique
     , ownrs.super

     , ( SELECT COUNT(*)
         FROM comments
         WHERE pet.id = comments.petid
       ) AS noOfCom

     , ( SELECT MAX(comments.comDate)     --- Unfortunately, you need
         FROM comments                    --- 2 subqueries if you want
         WHERE pet.id = comments.petid    --- 2 results from a table
       ) AS lastCom                       --- (comments)

     , ( SELECT COUNT(*)
         FROM petfb
         WHERE pet.id = petfb.petid
           AND feedback IN (1, 100)
       ) AS newLikes

     , ( SELECT COUNT(*)
         FROM petfb
         WHERE pet.id = petfb.petid
           AND feedback IN (5,10)
       ) AS newDisLikes

     , ownrs.name
FROM
        ( SELECT pet.*
          FROM pet
          WHERE catid = 21
          ORDER BY `date` DESC
          LIMIT 0, 10
        ) AS pet
    INNER JOIN
        ownrs
            ON ownrs.uniqueID = pet.ownerID
ORDER BY p.date ;


Want to change a timestamp date to an integer YYYYMMDD I have this input "2017-07-21 15:31:04.853453" and I want it to return "20170721" as output.  How can I do it using cast or convert function?

My column input is the current time (CURRENT_TIMESTAMP).

From cast(CURRENT_TIMESTAMP as date) I have the output "2017-07-24", then I can do several concatenates with substrings. It should work but I wonder if there isn't any other way to get "integer YYYYMMDD".

As ypercubeᵀᴹ mentioned, this works in SQL Anywhere:

select CONVERT(char(8),current_timestamp,112) from dummy


What is Collation Compatibility_60_406_30001 in SQL Server We have an SQL Server 2014 (recently upgraded from 2008 R2) instance where one of the databases has some strange settings for collation:

In the Properties window for the database we have the following:

Under the "General"-tab and Maintenance it says: Collation:  Compatibility_60_406_30001
Under the "Options"-tab the Collation is blank in the drop Down (all though many collations are available for choosing in the list)

I tried googling the collation Compatibility_60_406_30001, but the search came up empty.
Anyone have any suggestions on what to do here? So far I have not seen any errors becaus of this, but I would like to choose a valid collation for this database also.

Looks like you have an old compatibility collation, which comes from SQL Server 2005 or SQL Server 2000.
The format of this compatibility collation name is:

Compatibility_SortOrderDecimal_lcidHex_CompStyleHex

Explanation:

SortOrderDecimal 60 = Case-insensitive Scandinavian dictionary sort order, without case preference
lcidHex (Locale id) 0x406 = 1030 (Danish)
CompSytleHex Default Unicode comparison style 0x30001 = Ignore case, Ignore Kana, Ignore width

You can change the database collation through the following code:

USE master;
GO
ALTER DATABASE YourDatabaseName
COLLATE Danish_Norwegian_CI_AS;
GO

If you must have backward compatibility with the SQL collation, you could use the SQL_Scandinavian_CP850_CI_AS collation.


Postgresql: Max Connections per server / database I'm building a new, multitenant application using microservices and Postgres RDS in AWS. Each tenant will have their own database within the RDS instance (additional RDS instances/servers will be added when needed). There will be three microservices that will need access to each tenant database. One microservice will serve UI activity and the other two will handle background transactions, not bound to the UI.

I'm currently using C3p0 for connection pooling for each microservice attached to each tenant database (using a map to manage the tenant connection pools). When a call is made, the tenant id will be passed as part of the payload, determine which connection pool to use then invoke the sql statement. For reference, each tenant will have 20-2000gb of database data.

My question is around postgress max connections. Is the max connections for each database or the database server? I know Postgres can handle thousands of databases but how do you maintain connections to all of these databases?

Suppose I have 100 tenant databases on one database server. Each microservice has a connection pool set to have 5 connections. That would be 15 connections per tenant, or 1500 connections to the database instance/server. Now, I have to have redundancy on the microservices, so I'll have at least 2 of each microservice running, which would double the total database connections to 3000. Add more microservice instances for scalability and my connection count to the database server again increases. To keep it simple for this conversation, I'd scale all the microservices together as a group when there is additional load, so 3 sets of microservices requires 4500 connections and so on.

Will this even work? Am I thinking about connection pooling and Postgres connections correctly? The plan is to use separate databases per tenant for various reasons, unless that is, there is a better way to deploy that makes backup/restores simple, provides data isolation at the SQL level, etc.

I've read through numerous articles with performance issues with one database and a schema per tenant so I think that option is ruled out. The other alternative would be one database with shared tables for all tenants and using the tenant id as part of the primary key, but given the need to keep data isolated, i'm pulling that off the table for consideration.

max_connections from postgresql.conf is for the entire server, but CONNECTION LIMIT from CREATE|ALTER DATABASE command is for that specific database, so you have your choice.

You might barely get away with 4500 connections, but only if the vast majority of them don't do anything the vast majority of the time.  In which, case, why have them?  Why does each microservice need a connection pool of 5?

  I've read through numerous articles with performance issues with one database and a schema per tenant so I think that option is ruled out.

There are lots of bad articles out there, or ones based on 15 year old experiences.  A lot has changed in 15 years.  Which articles are they? And did they actually say that a database per tenant was better than a schema per tenant, or just that a schema per tenant was bad with no comparison to the alternative?


How to enforce data consistency across one-to-one relationship in an inheritance hierarchy Consider this design for a typical school database:

Person:
-----------------
FirstName
LastName
SocialSecurityNumber
Phone
Email

Student:
-----------------
Grade

Teacher:
-----------------
Specialty

As you see, there are three tables in this design. One table holds the general information about the abstraction, and two other tables hold specific data about concrete entities. Students and Teachers tables have one-to-one relationship with Persons table. So far there is no problem.

However, based on this design, there is this possibility that we have a record with Id 30 in Persons table, and because of any reason (like manual script execution against database) we insert two records with the same Id in Students, and Teachers tables. This way, a person with Id 30 is both a teacher, and a student at the same time.

Well, that makes sense in the context of a school database. But there are some contexts that derived tables are mutually exclusive, thus an entity from one concrete type can not logically be an entity from the opposite type too.

How can I prevent overlap Id insertion across derived tables in hierarchical database designs? I know I can achieve that with triggers, but I think code smell in using triggers.

Notes: I'm using SQL Server, and Entity Framework calls this design Table per Type (TPT).

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc.

For DBMS:s that support queries in check constraints you can do something like:

ALTER TABLE Student Add constraint ...
         CHECK ( (select type
                  from person p
                  where p.<key> = <key>) = 'Student' )

If you DBMS does not support this type of construction you can add a super key in Person consisting of the primary key + the type attribute. Add the type attribute to Teacher and Student, add a check constraint that guarantees the type in those "subtables", and a foreign key that includes the type attribute:

ALTER TABLE Person ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Person ADD CONSTRAINT ... UNIQUE (<key>, type_attribute);

ALTER TABLE Student ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Student ADD CONSTRAINT ... CHECK (type_attribute = 'Student')
ALTER TABLE Student ADD CONSTRAINT ...
     FOREIGN KEY (<key>, type_attribute)
     REFERENCES Person (<key>, type_attribute);

Now it is not possible to add a student as a teacher and the other way around. That there really is a student/teacher for a person with that attribute has to be guaranteed through the transaction that adds the information.


Why does subquery use parallelism and join doesn't? Why does SQL server use parallelism when running this query which uses a subquery but it doesn't when using a join? The join version runs in serial and takes around 30 times longer to complete.

Join version: ~30secs

Subquery version: <1second

EDIT:
Xml versions of query plan:

JOIN version

SUBQUERY version

As already indicated in the comments it looks as though you need to update your statistics.

The estimated number of rows coming out of the join between location and testruns is hugely different between the two plans.

Join plan estimates: 1

Sub query plan estimates: 8,748

The actual number of rows coming out of the join is 14,276.

Of course it makes absolutely no intuitive sense that the join version should estimate that 3 rows should come from location and produce a single joined row whereas the sub query estimates that a single one of those rows will produce 8,748 from the same join but nonetheless I was able to reproduce this.

This seems to happen if there is no cross over between the histograms when the statistics are created. The join version assumes a single row. And the single equality seek of the sub query assumes the same estimated rows as an equality seek against an unknown variable.

The cardinality of testruns is 26244. Assuming that is populated with three distinct location ids then the following query estimates that 8,748 rows will be returned (26244/3)

declare @i int

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = @i

Given that the table locations only contains 3 rows it is easy (if we assume no foreign keys) to contrive a situation where the statistics are created and then the data is altered in a way that dramatically effects the actual number of rows returned but is insufficient to trip the auto update of stats and recompile threshold.

As SQL Server gets the number of rows coming out of that join so wrong all the other row estimates in the join plan are massively underestimated. As well as meaning that you get a serial plan the query also gets an insufficient memory grant and the sorts and hash joins spill to tempdb.

One possible scenario that reproduces the actual vs estimated rows shown in your plan is below.

CREATE TABLE location
  (
     id       INT CONSTRAINT locationpk PRIMARY KEY,
     location VARCHAR(MAX) /*From the separate filter think you are using max?*/
  )

/*Temporary ids these will be updated later*/
INSERT INTO location
VALUES      (101, 'Coventry'),
            (102, 'Nottingham'),
            (103, 'Derby')

CREATE TABLE testruns
  (
     location_id INT
  )

CREATE CLUSTERED INDEX IX ON testruns(location_id)

/*Add in 26244 rows of data split over three ids*/
INSERT INTO testruns
SELECT TOP (5984) 1
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (5984) 2
FROM   master..spt_values v1, master..spt_values v2
UNION ALL
SELECT TOP (14276) 3
FROM   master..spt_values v1, master..spt_values v2

/*Create statistics. The location_id histograms don't intersect at all*/
UPDATE STATISTICS location(locationpk) WITH FULLSCAN;
UPDATE STATISTICS testruns(IX) WITH FULLSCAN;

/* UPDATE location.id. Three row update is below recompile threshold*/
UPDATE location
SET    id = id - 100

Then running the following queries gives the same estimated vs actual discrepancy

SELECT *
FROM   testruns AS tr
WHERE  tr.location_id = (SELECT id
                         FROM   location
                         WHERE  location = 'Derby')

SELECT *
FROM   testruns AS tr
       JOIN location loc
         ON tr.location_id = loc.id
WHERE  loc.location = ( 'Derby' )


Is there a way to get the contnents of an RPG program using SQL? So, I've got myself a query to list our RPG programs:

SELECT OBJNAME
FROM
    (SELECT OBJNAME AS SCHEMA FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALLSIMPLE', 'LIB')) S) AS SCHEMAS
    ,LATERAL (SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS(SCHEMAS.SCHEMA, 'PGM')) X) AS PROGRAMS
    LEFT JOIN QSYS2.PROCEDURES
        ON QSYS2.PROCEDURES.PROCNAME = PROGRAMS.OBJNAME
        AND QSYS2.PROCEDURES.PROCSCHEMA = PROGRAMS.OBJLONGSCHEMA
WHERE
    PROCNAME IS NULL
    AND SCHEMAS.SCHEMA = 'MYLIB'
;

Now, is there any way, given the name of an RPG program, to get the contents of it via SQL?

(Or, failing SQL, some other way to get the contents from within a C# application?)

Depends on your definition of "contents"...

At v7.3 and more at v7.4 IBM has added some services for program info, module info, ect...
IBM i Services (SQL)

But if you're thinking "contents" as in a source list...

Then you'd have needed to have the program compiled with DBGVIEW(*LIST|*COPY|*ALL) and then use the debugger APIs to retrieve the source...


Recovering MySQL root password (XAMPP) I posted this on StackOverflow and was told to come here.

I have an XAMPP MySQL installation and want to recover the root password. I am aware that questions have asked this already, however they are Linux methods of doing it (I'm on Windows) and I can't seem to figure out what exactly the Linux commands translate to in XAMPP - I tried looking for a place to configure the arguments to not use network etc but couldn't. This is a localhost installation.

This resetroot.bat method did not work for me, even after following the instructions above.

My XAMPP uses this DB version:

Server: MariaDB
Server version: 10.0.17-MariaDB mariadb.org binary distribution

However, using this answer, I was able to do it manually.

Go to your **xampp\mysql\bin** folder
Edit my.ini and insert skip-grant-tables below [mysqld]
Restart MySQL
Set new password for your root user by running UPDATE mysql.user SET
Password=PASSWORD('new_password') WHERE User='root' in phpMyAdmin in
the mysql database (or just leave it like this if MySQL cannot be
accessed from remote hosts)
Remove the skip-grant-tables in the my.ini file


How to segment account info and profile info? I am putting a rough SQL database design together and have gotten stuck on how to segment information about the users.

(Note before I get into anything, stored passwords are salted and hashed, I just write "password" for brevity)

For reasons I can't go into due to NDA, our database will have two user groups - human users and fake users. Human users will have login information (screen name, email address, password). Fake users only need a screen name. All users, human and fake, need profile information (first name, last name, gender, location, etc). Most of the profile information is only displayed on the user's profile page. However, depending on the user's settings, either their real name or screen name may be displayed under their avatar throughout the front-end. Email address is not visible in the user's public profile.

I can't decide how to segment this information into tables. My first instinct was to have three separate tables,

"accounts" (for humans - screen name, email, and password)
"user_profiles" (first name, last name, gender, location, etc)
"fake_user_profiles" (screen name, first name, last name, gender,
location, etc).

But this runs into several problems:

Each user, human or fake, needs a unique ID - no human should have
the same ID as any fake user. I would have to enforce UIDs across two separate tables
I have two almost identical tables with
only one field different between them

My next idea was to put all of the users, human or fake, together. Then I'd have two tables:

"accounts" (screen name, email, password)
"profiles" (first name, last name, gender, location, is_human, etc)

With this approach, it would make sense to put the UID for each user (human or fake) into the "accounts" table. But what would I do with the "email" and "password" fields that should be non-null for humans but serve no purpose for fake users?

Would there be a better approach than either of the two I've described? Would it be better to put all the information in one table?

Unless there is a security problem (you want someone to view some information, but not the other), there is no need to; however in many sql platforms you can deny access to specific fields in security or just create a view. The only other reason you would want to have two tables is if you have a business reason to segregate the data to two separate tables. Otherwise, if you are constantly needing to get information from table A and table B at the same time, you have to join each time which will add to overhead.


Importing schema to new or different tablespace Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated?

As an example, I have exported BLOG_DATA from OLDDB, where all user data is stored in the USERS tablespace.

On NEWDB, I would like to import the BLOG_DATA schema, but store the user objects in the BLOG_DATA tablespace, created specifically for this user.

I have created the BLOG_DATA user, created the BLOG_DATA tablespace and set it as the default tablespace for that user and added an appropriate unlimited quota.

CREATE TABLESPACE blog_data DATAFILE SIZE 1G;

CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;

GRANT connect,resource TO blog_data

The schema was exported from OLDDB with something like

exp blog_data/secretpassword@OLDDB file=blog_data.dmp

After reading Phil's excellent answer below, I found myself wondering:

Since data has no other place to go than the default tablespace - the only tablespace the user has a quota on - will this effectively force imp to put all user objects in that default tablespace?

imp blog_data/secretpassword@NEWDB file=blog_data.dmp

Would this then place the entire blog_data schema in the blog_data tablespace on NEWDB? Is there any reason why this wouldn't work or that I would run into problems with certain objects etc?

update:

I did a quick test and found this to be the case. Imp places objects in the default tablespace for that user, provided it can't place it in the original tablespace (eg. the tablespace does not exist). Full explanation: http://www.dolicapax.org/?p=57

Still, I suppose that using Data Pump like Phil suggests might be the preferred option.

It's not actually possible to specify a different tablespace when importing using the oracle imp utility. However, as a workaround, you can pre-create the tables by doing a ROWS=N import into the USERS tablespace, then alter table mytable move tablespace BLOG_DATA; for each table to move them to the new tablespace, then do the import again with the IGNORE=Y parameter to ignore the table creation errors and import all of the data.

If the data was exported using Data Pump (expdp), (as an aside, everyone should be using this these days, rather than the old legacy exp/imp utilities) you can easily import into another tablespace using the REMAP_TABLESPACE parameter.

eg:

impdp scott/tiger@ZOMG file=blog_data.dmp directory=mydir remap_tablespace=USERS:BLOG_DATA


MongoDB: Aggregation workaround by helper collection Since I regularly run against mongodb's document size limit and also aggregation framework's group limitations >16M, depending on the amount of data I need to process, I look for a simple workaround.
The solution must also fit for single nodes (and must not require multiple mongos).
Saying in few words, it's an aggregation for finding

(a) the number (count) of uniques in a field and group the number by another field
(b) the frequency or count how often a unique id appeared.

An example could make the task easier to understand: Lets say there is a collection with 2 fields, the last and surname.

(ex a) How many different surnames exist for each last name? Result could be: There are 1000 unique (distinct) surnames for last name "smith", 500 different surnames for people with last name "schulz" etc.

=> this could also be solved with distinct count and/or mapreduce etc. no limitation is exceeded. anyways, it seems to be a slow table scan and aggregation framework does the task much faster.

(ex b) How popular are surnames for each last name? Result: With the last name "smith" there are different 500 sur names which appear only once, 200 sur names which appear 2 times, 45 are listed 3 times our data, ...

=> here it is very comfortable to work with mongodb's aggregation framework which handles this task fast and properly. However, trying the task with lots of uniques in a 15GB collection, it worries about the 16M limitation.
The first workaround was successfull but is only a quick'n'dirt non-scaleable trick:
Instead of processing all the data and $group afterwards, I filter ($match) for each $group-item. With the example from above, I look up all distinct last names, run one aggregation per lastname and reduce the pipeline data before aggregation starts.
This avoids the 16M limit issue if the amount of data for a single $group-item (last name) is not too much. => only quick'n'dirty workaround.
Another idea is a help collection for the COUNTs.
I could iterate each input document and write into another collection the surnames and one field per lastname with the counter.
E.g. surname=John count_with_Lastname.smith=10,  count_with_Lastname.brown=2, ...
Building this helper collection is very slow, isn't it? When incrementing +1 which is 1 update/write per input document, I fear this could take forever :-(
Is there a best-practice or tips for solving this task (withouth multiple nodes and without Hadoop or alternatives)?
Please let me know

Once you go beyond the limitations you mention (until $out is supported at least), then your best option is Map Reduce.  If you are going to run multiple Map Reduce jobs, I would recommend version 2.4 at least (uses V8 engine, muti-threaded).  For an example of how to do a unique count with Map Reduce, take a look here:

http://cookbook.mongodb.org/patterns/unique_items_map_reduce/