How to quantify TEMPDB usage in relation to other databases hosted on same hardware? Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN:

Config1 (sql 2008):

8 disks @ raid 10 data
2 disks @ raid 1 logs
2 disks @ raid 1 tempdb
2 disks @ raid 1 hyper-v

(based on SQL best practice suggesting isolating tempdb)

Config2 (sql 2008):

8 disks @ raid 10 data + tempdb mdf
4 disks @ raid 10 logs + tempdb logs
2 disks @ raid 1 hyper-v

Current setup (sql 2005):

8 disks @ raid 10 data + tempdb mdf
4 disks @ raid 10 logs + tempdb logs

Under current setup windows performance montior confirms :

MDF writes spike , reads consistent
LDF writes consistent, reads non-existent

Using the following query (most accessed tables per database) i can accumulate the "accesses values" and compare the usage of each database relative to each other:

SELECT
DB_NAME(ius.database_id) AS DBName,
OBJECT_NAME(ius.object_id) AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed
FROM sys.indexes i
INNER JOIN sys.dm_db_index_usage_stats ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
WHERE
ius.database_id = DB_ID()
GROUP BY
DB_NAME(ius.database_id),
OBJECT_NAME(ius.object_id)
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

What i am finding is the following:

db.2 = 8,943,628,393

db.S = 360,805,802

db.L = 660,469

db.tempDB = 173,094

Not sure this is a good way to quantify the usage of tempDB however. The script looks at tables but tempDB doesnt contain tables. I understand its a caching mechaism but how does it work and how can i quantify it in relation to my other DBs to help me make a decision on disk layout.

Thank you

Scott

Scott,

Check this statement out:

SELECT
    [Database] = DB_NAME ( qt.dbid ),
    [Execution Count] = SUM ( qs.execution_count ),
    [Total Execution Time] = SUM (qs.total_elapsed_time / 1000000.0 ),
    [Total CPU Consumption] = SUM ( qs.total_worker_time ),
    [Total Reads] = SUM ( qs.total_physical_reads ),
    [Total Writes] = SUM ( qs.total_logical_writes ),
    [Average Execution Time] = SUM ( qs.total_elapsed_time / qs.execution_count / 1000000.0 ),
    [Average CPU Consumption] = SUM ( qs.total_worker_time / qs.execution_count / 1000000.0 ),
    [Average Reads] = SUM ( qs.total_physical_reads / qs.execution_count ),
    [Average Writes] = SUM ( qs.total_logical_writes / qs.execution_count )
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text ( qs.sql_handle ) qt
LEFT JOIN sys.databases o ON qt.objectid = qt.dbid
GROUP BY qt.dbid

This should give you all of the information you need.  If all you're interested in is disk usage just keep the total reads/writes and average reads/writes.


Discovering databases with specific table on a PostgreSQL server Is there a way for a non-admin PostgreSQL user to discover which databases it can access on the server it is connected to, more specifically which of these databases have a table with a specific name that the user has enough permissions to see (and possibly query)?

As far as I can tell, the MySQL equivalent would be something like:

SELECT t.TABLE_SCHEMA FROM information_schema.`TABLES` AS t
    WHERE t.TABLE_NAME = 'the_table_name'

(This would be for PostgreSQL 8.4 preferably, but I'm also interested in solutions for 9.1 if this has changed.)

Not in a single query. The information_schema.tables view and the pg_catalog.pg_class table are per-database. You can't query tables across multiple databases in PostgreSQL without hacks like DBLink.

A PostgreSQL database is a bit more like an independent MySQL server, and a PostgreSQL schema is more like a MySQL database. If you want to be able to query across multiple namespaces in PostgreSQL, create them all as schemas within one database, instead of in multiple databases.

To list tables in all databases must connect to each database of interest. You can list them from select * from pg_catalog.pg_database where datistemplate = 'f';. The pg_catalog.pg_database table is global across all databases, so the same results are obtained by connecting to any database.


Breaking changes between 2008R2 RTM going to 2008R2 SP3? I have a few A/P FCIs running 2008R2 RTM, and I'd like to upgrade the passive node to SP3, fail over, and then upgrade the formerly passive node.

Has anyone else attempted this? Are there breaking changes in the Service Pack?

I do not have a suitable QA environment to test, and I can only find breaking changes between release versions.

Thanks

Consider this as answer

SP3 is new and thoroughly tested its highly unlikely it would create any issue but again I would recommend you not to proceed without any testing and that too when Cluster is involved. Also asking whether anybody faced issue will lead you in problem, what if somebody writes 'Yes I have done and its working fine' yes they are correct it worked in there environment but I am sure your and his environment are not same and after applying SP if something stops working who would you catch other than Microsoft.

I have applied SP3 in my environment and every thing is going good as of now. But if you ask my plan of action I have now deployed in DEV then I will deploy in UAT and then after a month, because couple of SSIS packages run monthly, I would apply in production.

Please create an environment and test first believe me it will save you lot of hassle.

If still you want to proceed

1.. Backup system and user databases. Use TSQL backup command [This link is external to TechNet Wiki. It will open in a new window.] or SQL Server Management studio GUI [This link is external to TechNet Wiki. It will open in a new window.] to backup system and user databases. Since you backup system and user databases you don't need to backup jobs, SSIS packages, mail profiles, linked servers  and logins as all of these would be included in MSDB and Master database backups.

If you use analysis services backup Analysis Service configuration files, databases and repositories. Backup files present at location
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Data\
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Config\ directory.
Make sure enough space is available on drive where resource/system databases are present. Resource database [This link is external to TechNet Wiki. It will open in a new window.] is utilized during service pack upgrade. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
Service pack upgrade would require a downtime of application. Make sure application does not access database during service pack upgrade. Make sure you stop all jobs and activities on database before proceeding. you don't need to shutdown database services to apply service pack.
Make sure you are administrator on the system and always run the service pack executable with administrator privileges.

Please do read This BOL


Specifying the node to connect in db2 I have 2 nodes in my db2 catalog. How would I include the node on which I want to connect inside the db2 connect paramters?

 Node 1 entry:

 Node name                      = DB2HOST1
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = host1.sample.com
 Service name                   = 60000

 Node 2 entry:

 Node name                      = DB2HOST2
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = host2.sample.com
 Service name                   = 60000

This is my query:

    db2 connect to DBASE user user1 using password1 <-- works
    db2 connect to DBASE user user1 using password1 node db2host1 <-- doesn't work

Possibly, you could catalog the databases under a unique alias for each node:

db2 catalog db DBASE as DBASE1 at node DB2HOST1
db2 catalog db DBASE as DBASE2 at node DB2HOST2


Get one row for each value in xml column I have a table, called product_variants, with one integer column and one xml column containing a set of foreign key values.

If I ran,

select product_id, options_xml from product_variants

This would be a typical result

product_id  |  options_xml
132         |  <options><option_id>1</option_id><option_id>8</option_id></options>

I would like to somehow select a new row for each option_id.

The result I want,

product_id  |  option_id
132         |  1
132         |  8

Thank you in advance!

you can use xmltable() for that:

select p.product_id, t.option_id
from product_variants p
  cross join xmltable('/options/option_id'
                      passing p.options_xml
                      columns
                         option_id integer path '.') as t

Online example


Best index plan for a username column inside an innodb table I have a username column in my table: username VARCHAR(50) I want to index this column, so I can find a username easily and send messages to users like: "this username has already been taken." So what is the best index plan. the table use innodb engine. Can I use fulltext index?

Enforcing username uniqueness
If you want to enforce uniqueness for the values stored in your username column and you also have a user_id column set up as the PRIMARY KEY (PK) of the relevant table, you might be interested in the implementation of a

UNIQUE index

for the username column.
Such situation implies that, at the logical level, the username would be an ALTERNATE KEY (AK). An AK is a column, or a combination of columns, that holds values that uniquely identify a certan row but was not chosen as the PK of the pertinent table; each table can have zero, one or more AKs. As suggested above, they are usually defined in a SQL DDL structure via a UNIQUE constraint which is served, at the physical level, by a UNIQUE index.
Logical and physical levels
As it is paramount to distinguish logical from physical aspects when constructing a relational database —independently of the platform of choice—, you might find the concepts and links included in this answer of help.
Displaying a warning
If you want to display a message to the people trying to enter username values that have already been taken, you should develop such functionality in the code of the application programs that have access to your database.
Setting up the length of a column
If you don’t want to allow username values with a length greater than 18 characters, then you might like to define the column size accordingly, i.e., VARCHAR(18), so you may find of value the following content from the MYSQL reference manual about

the CHAR and VARCHAR Types,

from which I will stand out the following paragraph:

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

Prefix and Fulltext indexes
@Ypercube has enriched this answer with responses to your individual questions about some types of indexes:

Can I use Prefix index?

Technically you could but there is no reason for that. Prefix indexes are rarely - or never - useful in MySQL. If you want to restrict the length of the usernames to 18, the column should be VARCHAR(18) and not (50). If you allow only ASCII characters in the usernames, the column should have an ascii character set and not UTF-8. And even if you keep the VARCHAR(50), the index should still be in the whole column, not a prefix.

What about a Full Text index?

No, you don’t need them either. Full text indexes are for when one wants to search words or phrases in large texts. Not useful in your case. A simple (unique) B-tree index would be the best.


Assembly is not authorized for PERMISSION_SET=UNSAFE when creating a CLR assembly The login running all these commands is a member of sysadmin server role on a SQL 2012 Developer instance. It is the owner of the DB where this is being deployed to. EXTERNAL ACCESS ASSEMBLY permission has been granted to the login in this DB. Also tried everything below as the sa login to no avail.

alter database test set trustworthy on
go
create assembly [icsharpcode.sharpziplib]
from 'C:\Workspace\111\icsharpcode-SharpZipLib-4f2d664\bin\Release\ICSharpCode.SharpZipLib.dll'
with permission_set = UNSAFE --< This works!
go
create assembly OutOfRowCompression
from 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'
with permission_set = UNSAFE --< This fails!
go

The last command fails with:

  Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly
  'OutOfRowCompression' failed because assembly 'OutOfRowCompression' is
  not authorized for PERMISSION_SET = UNSAFE.  The assembly is
  authorized when either of the following is true: the database owner
  (DBO) has UNSAFE ASSEMBLY permission and the database has the
  TRUSTWORTHY database property on; or the assembly is signed with a
  certificate or an asymmetric key that has a corresponding login with
  UNSAFE ASSEMBLY permission.

After getting the error I signed the assembly with a new asymmetric key key1.pfx and added it to the master DB:

CREATE ASYMMETRIC KEY key1
FROM executable
FILE = 'C:\Workspace\Sandbox\OutOfRowCompression\OutOfRowCompression\bin\Debug\OutOfRowCompression.dll'

Verified that the key exists:

Created a login from this key and granted God mode:

create login clr_key1 from asymmetric key key1
go
GRANT EXTERNAL ACCESS ASSEMBLY TO clr_key1
go

Still the same error from the assembly creation command.

Tried building the assembly OutOfRowCompression with SAFE and UNSAFE access in Visual Studio 2012 - no difference.

I feel I have satisfied all requirement presented by the error message. It should work. What am I missing?

Your GRANT statement is incorrect. You only granted the ability to set Assemblies to EXTERNAL_ACCESS, not to UNSAFE. UNSAFE is less restricted than EXTERNAL_ACCESS, and so granting UNSAFE ASSEMBLY to a Login includes – implicitly – the EXTERNAL ACCESS ASSEMBLY permission.

You need to use:

GRANT UNSAFE ASSEMBLY TO [clr_key1];

P.S. You need to check the two DLLs you are importing for why they need to be marked as UNSAFE. If it is due to storing values in static variables then that could result in unexpected results if two sessions execute this code at the same time.


Shrink a large log file for a "Throw Away" database I have a copy of my prod database on a development server.  I am using it to test a deployment script.

But when I run it, it says that the log file is full.

I checked and it is at 10 GB (Almost the size of the full database).

I don't need this database but for a few more hours (after that it is going to get dropped).

Is there any way to get rid of the log file gracefully?  (It seems wrong to just go delete it.)

NOTE:
I have run this:

select log_reuse_wait, log_reuse_wait_desc, name
from sys.databases
where name = 'ProdCopyDatabase'

And the result is:

log_reuse_wait  log_reuse_wait_desc    name
2               LOG_BACKUP             ProdCopyDatabase

You have two options:

1) Run a LOG backup of the database:

BACKUP LOG foo TO DISK='X:\BackupLocation';

2) Set the recovery model to SIMPLE and checkpoint it.

ALTER DATABASE foo SET RECOVERY SIMPLE;
CHECKPOINT;

This is all part of SQL Server's different recovery models.  Based on you working on this in DEV, I'd probably go the second option since it sounds like you don't need to worry about point in time recovery.

Note, this will simply clear out the active transactions, not reduce the actual file size.  Once this is complete, you can then shrink the log file.


SQL query max aggregate selecting correct nonaggregate columns? I am confused about the max() aggregator, I think this query makes sense...
select title, vote_avg, floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...> from films
   ...> where (vote_avg, decade) in (
   ...>   select max(vote_avg), floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...>   from films
   ...>   where vote_cnt > 100
   ...>   group by decade
   ...> )
   ...> and vote_cnt > 100
   ...> group by decade
   ...> order by decade desc;
title                            vote_avg  decade
-------------------------------  --------  ------
Homecoming: A Film by Beyoncé    8.6       2010
Spirited Away                    8.5       2000
Dilwale Dulhania Le Jayenge      8.9       1990
The Empire Strikes Back          8.4       1980
We All Loved Each Other So Much  8.6       1970
Psycho                           8.4       1960
12 Angry Men                     8.4       1950
The Great Dictator               8.4       1940
City Lights                      8.4       1930
Sherlock Jr.                     8.2       1920
The Immigrant                    7.5       1910
The Great Train Robbery          7.2       1900

but I am not sure why this one works?
select title, max(vote_avg), floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...> from films
   ...> where vote_cnt > 100
   ...> group by decade
   ...> order by decade desc;
title                            max(vote_avg)  decade
-------------------------------  -------------  ------
Homecoming: A Film by Beyoncé    8.6            2010
Spirited Away                    8.5            2000
Dilwale Dulhania Le Jayenge      8.9            1990
The Empire Strikes Back          8.4            1980
We All Loved Each Other So Much  8.6            1970
Psycho                           8.4            1960
12 Angry Men                     8.4            1950
The Great Dictator               8.4            1940
City Lights                      8.4            1930
Sherlock Jr.                     8.2            1920
The Immigrant                    7.5            1910
The Great Train Robbery          7.2            1900

I'm using sqlite3, which of these queries is better practice? (Or are they both incorrect?)

Such a bare column in an aggregate query is not allowed by the SQL standard. But SQLite allows it, and returns the intuitively correct result:

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum.

If you want to write portable SQL, don't use this feature.


Oracle Compatible Parameter to set Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?

The parameter is described in the Upgrade Guide. You cannot downgrade a database to a version that is below the version of the COMPATIBLE parameter. The COMPATIBLE parameter controls how data is written to disk. But it does not control the usage of optimizer features. There are a lot of parameter to control the oprimizer features. The OPTIMIZER_FEATURES_ENABLED is a parameter that you have to set appropriately to enable the optimizer features of the Oracle version. Tom Kytes discusses the difference of these two parameter in this article.


What happens if you lose the connection during a huge insert? What happens when you lose your connection with the DB during a huge insert. Will part of the data be inserted? Will nothing be inserted? What is the reason for this?

The example statement I am picturing is:

INSERT INTO user
  (variable)
values
  (?),
  (?),
  (?),
  ...,
  (?)

I am specifically interested in MySQL or SQL Server.

For SQL databases that are ACID - A is for Atomic meaning all or none of a transaction will occur. When partial data is loaded and the connection stops, there is no choice in a ACID constraint but to drop all of the data.

For MySQL Innodb (default) is ACID but there is a MyISAM engine which isn't.


Installing Netezza 6.0.8 ODBC driver on Windows Server 2008 Below are four Netezza drivers that are available for Netezza 6.0.8.  I installed the second one on Windows Server 2008, and it placed it into the system32 folder (64 bit folder).  However, when I set up the System tab ODBC drivers, and used the same configuration and credentials that I use on Windows XP, the connection test failed.

Do I need to do something special on Windows Server to get the Netezza driver working?  Or perhaps I installed the incorrect driver?  Does anyone know the difference between these?

[Not really an answer as I'm just stating the obvious but I don't have enough rep to comment.]

The four driver files are the ODBC driver, the JDBC driver, and the 32 and 64-bit OLEDB drivers. The ODBC driver is of course the correct one but there's a small check box in one of the installer dialogs which allows you to choose 32-bit and/or 64-bit. Did you select the correct one?

I've just tried this on Windows Server 2012 and Windows 7 and both work exactly the same for me without any extra steps. The driver I have is called nsqlodbc.dll, version 7.0.0.27274, in C:\Windows\System32 (and I also have a 32-bit version in C:\Windows\SysWOW64). I can connect from both the 32-bit and 64-bit ODBC Data Source Administrator tools although I got my password wrong several times so that's certainly worth checking.

Have you also checked that the advanced options are the same, SSL for instance?

Possibly there's a security setting on your Server box that's preventing the connection but on 2012 Server there didn't appear to be anything by default.


Division Email Template DB Design First question for me on the DBA SA site!

This is for an Java email notification app using MySQL for backend.

I have a small number of groups in our organization, [Marketing, Commerce, Infrastructure, Design]. They all are looking to utilize a small number of email templates for this app. Currently all of the groups utilize all of the templates [AAR Summary, Awareness, Daily Status, Hourly Deploy Update].

My question is what is a very simple, scalable way to design these two?
My first thought was to have two tables:

Division
    - DivisionID
    - DivisionName

Template
    - TemplateID
    - TemplateName
    - AssociatedDivision

However, I don't want to have duplicate templates for different divisions. In the same regard, a comma delimited list would help but I have no clue how I could query for all divisions and all templates each division has available.

There is currently a solution in place that is less than ideal. This solution is driving what people can and can not subscribe to.

Essentially, user x can subscribe or not to each template for each division.

EXAMPLE:

              AAR Summary    |    Awareness    |    Daily Status    |    Hourly Deploy
            -------------------------------------------------------------------------------
Marketing |       X                   X
Commerce  |       X                   X                  X                     X
Infra.    |                           X
Design    |                           X                  X

Help, if you need more info just let me know?

I think this is a many to many relation ship, and the suggested way for that is to have a table for the relation. i.e.:

Division
- DivisionID
- DivisionName

Template
- TemplateID
- TemplateName

division_template
- DivisionID
- TemplateID


Sql Server 2014 update path advice I recently inherited a SQL Server 2014 installation and I'd like to install updates. The virtual machine runs Windows Server 2016 standard. The server has about 50+ databases with 400Gb of data. I'm a developer and don't have much DBA knowledge.

Current plan is to update SQL Server to 2014 SP3 (right now it's RTM). Are there any problems I should be aware of?

I do plan on trying it out on a non-production server first - but unfortunately can't get the same exact Windows version.

Is there some script or utility to healthcheck SQL Server installation? I doubt that manually running sql statements will be very thorough. I only found Brent Ozar's blitz so far.

You actually want to keep up to date on service packs in SQL < 2014 as you won't get support after awhile if you don't.

The support policy can be found here.

Generally installing Service Packs is safe but for any production system please make sure you have a strong backup model in place and hopefully a test environment to simulate all changes for production ahead of time to validate that they work.


MySQL: Transaction and Deadlock I have table "tbl_data", where have a lot of rows (3 - 5 million).
I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table.
If I start *.jar file (for example, "run.jar") and after INSERT/UPDATE process I start "run.jar" sequentially, there no problems - works OK.
If I start "run.jar" and then start "run_01.jar" (jar file with the similar logic: INSERT/UPDATE into "tbl_data"), I can get error:

  com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
  Deadlock found when trying to get lock; try restarting transaction

All actions at "run.jar" executes in transaction, like:

  START TRANSACTION; INSERT INTO tbl_data ...;UPDATE tbl_data ...; COMMIT;

So, I need to have ability make INSERT/UPDATE from several sources at the same time without blocking table for modification (Every jar file UPDATES/INSERTS its own rows in "tbl_data" so it is no problem for data in modification data at the same time).

If you are totally sure about the separation status of your data, you can set the global isolation level to READ COMMITTED or even to READ UNCOMMITTED. The first blocks less from the index, which should be enough in your case. The second one arranges for all SELECT statements to be non blocking. This is as low as you can go.

Whatever you choose, you should set the global isolation level back to REPEATABLE READ as soon as you finished inserting your data.

global means that this applies to all connections, which are established. So this change also affects connections beside your jarfiles. If you have the possibility to change the jarfiles, you should set the connection based isolation level with the SESSION keyword instead in both jarfiles.


connection issue / Oracle Express 11g in windows7 32bit I have an issue with Oracle Express 11g in windows7 32bit.
Everytime when I try to conncet to the database I get this error message.
How can I fix it please!

You most likely need to set the ORACLE_SID environment variable. This error usually means you are trying to connect to a database that doesn't exist. Also check that ORACLE_HOME is set correctly. If that doesn't fix it, check if the database service is started. If it is, check the alert log for error messages.


Oracle TimesTen vs Oracle database in-memory Oracle has recently announced its latest Oracle database in-memory feature in 12c.

Oracle already had an in-memory database TimesTen and now I was wondering as to what the the differences between these two beasts?

There's a massive difference. TimesTen is an dedicated in-memory database.

Oracle in-memory is just an added optional extra for Oracle 12c that lets you mark data, given certain indexing rules (you drop them), as in-memory. Oracle then caches the data in memory in both a row and columnar fashion, enabling fast retrieval for both OLTP and reporting workloads. It requires no code changes for existing Oracle-based applications - completely transparent & can offer significant performance improvements.


Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10 Postgres 10 gains the ability to use International Components for Unicode (ICU) collations rather than depending on host OS implementations. See More robust collations with ICU support in PostgreSQL 10 by Peter Eisentraut.
So how exactly does one specify an ICU collation when creating the database?
I did read a tip in the CREATE DATABASE to use template0 rather than template1 if template1 might have been created using encoding and collation other than what you want for your new database. And when using template0, you need to enable data connections to override the default.
And I read in Postgres 10 doc page for Collation Support that:

und-x-icu (for “undefined”)
ICU “root” collation. Use this to get a reasonable language-agnostic sort order.

So it seems und-x-icu might be a good value to use when establishing a new database that will include multi-lingual text. When a certain language is known to focus on a particular language, the SELECT command can specify another collation such as German de-x-icu. Otherwise, fallback to the default of und-x-icu.
So I tried the following SQL to create a database in Postgres 10 Beta 2.
CREATE DATABASE timepiece_
TEMPLATE 'template0'
ALLOW_CONNECTIONS TRUE
CONNECTION LIMIT -1
ENCODING 'UTF8'
LC_COLLATE 'und-x-icu'
LC_CTYPE 'und-x-icu'
;

Fails with an error:

[42809] ERROR: invalid locale name: "und-x-icu"

So I stuck on an appended .utf8 as seen in the doc for the old-fashioned collation syntax.
CREATE DATABASE timepiece_
TEMPLATE 'template0'
ALLOW_CONNECTIONS TRUE
CONNECTION LIMIT -1
ENCODING 'UTF8'
LC_COLLATE 'und-x-icu.utf8'
LC_CTYPE 'und-x-icu.utf8'
;

Fails just the same:

[42809] ERROR: invalid locale name: "und-x-icu.utf8"

Was my Postgres cluster built with ICU libraries? Here is the result of calling pg_config. The last line is 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'. I guess that means my build includes the ICU libraries but I am not sure.

/Library/PostgreSQL/10Beta2/bin/pg_config --configure
'--with-icu' '--prefix=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx' '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--with-bonjour' '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-uuid=e2fs' '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include:/opt/local/Current/include/security' '--docdir=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx/doc/postgresql' '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 'CFLAGS=-isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk -mmacosx-version-min=10.8 -arch i386 -arch x86_64 -O2' 'LDFLAGS=-L/opt/local/Current/lib' 'ICU_CFLAGS=-I/opt/local/Current/include' 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'

➠ How does one specify the ENCODING, LC_COLLATE, and LC_TYPE to use the new ICU collations?
➠ What ICU collation names you would suggest for English-oriented databases? For Western European multi-lingual databases?
➠ What are the possible ICU collation names?

First let's note that at the time of this answer, PostgreSQL 10 in still in beta stage. Some issues about the ICU integration and how it's documented are still under  discussion, and there might be changes before a GA release.

  ➠ How does one specify the ENCODING, LC_COLLATE, and LC_TYPE to use
  the new ICU collations?

It's not possible currently (and probably not documented well enough, or not clear enough in the error message: when it says invalid locale name, it means from the set of locales provided by libc).
This is discussed in this thread on the developers mailing list:
Can ICU be used for a database's default sort order?

  ➠ What ICU collation names you would suggest for English-oriented
  databases? For Western European multi-lingual databases?

It mostly matters if you have specific sort requirements. In this other thread of the mailing-list:
What users can do with custom ICU collations in Postgres
10
some concrete examples are given of ICU collations behaving specially compared to what libc can do.

When not having specific requirements, I think most applications won't care whether they use an ICU collation or a libc en_US collation, except for portability. One of the big points of ICU is to get exactly the same ordering across all operating systems for a given locale, which is really not the case of libc. I guess that the pre-created en-x-icu would be fine for general English, and for West-european languages, I'm not sure why any collation would  be better than und-x-icu.

As a side-note, it's not a good idea to use any other collation than C when you don't really care about the sort rules, because C as a collation will outperform any language-aware collation. And C is portable across operating systems.

  ➠ What are the possible ICU collation names?

Although initdb populates pg_collation with a list of pre-defined collations, a complete list would be subject to combinatorial explosion,
as ICU collation names are formed by assembling tags, as listed here:
http://unicode.org/reports/tr35/tr35-collation.html#Setting_Options

Currently the doc does not mention explicitly that you can do:

CREATE COLLATION mycoll (locale='my-set-of-tags', provider='icu');

but I think work is happening right now to improve that in the doc.

Another issue that might be worth pointing out is that there are multiple ICU major versions out there, and the list of pre-defined collations that initdb instantiates differs significantly between these versions, especially ICU pre-53 versus post-53. This has been put in evidence in another discussion:
Crash report for some ICU-52 (debian8) COLLATE and work_mem values

EDIT: in the next beta or RC, the list of ICU collations created by initdb will change thoroughly , as mentioned in this discussion. The list will made by iterating on ICU locales rather than collations, and as a result the question "What are the possible ICU collation names?" will have to be reconsidered with the new values.


What is blocking and how does it happen? I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?

Analogies

Sometimes it helps to use analogies away from computers.

Let's say you have a ball and two children.  Only one child can have the ball at any one time.  However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.

The other child is blocked from that resource.

If we compare this to the TV, for example, several children can watch TV at any one point.

Locks

If we move over to the database world, we see that there are different ways to use resources (just like our two examples above).  We can perform "reads" or we can perform "writes".

When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV.  However, if we want to write the data, then we need to make sure that no one else is looking at it.  If they are reading it while we're writing it, they will get "dirty" reads.  (Meaning, they'll see the data partially written out, which will be invalid.)

In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.

Read Lock

You can have several different connections reading from the same datasource at any given time.  But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data.  Others can, however, take out Read Locks of their own on that same piece of data.

Exclusive Lock

If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock.  This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).

When a connection has an exclusive lock on the data, no other connections may read from the data.  This helps prevent dirty reads by insuring that no one can read the data while its being written.

Blocking

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it.  It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Compare this to the case with a child holding the ball.  The child holding the ball is blocking all other children from holding the ball.

Deadlock

I know you didn't ask this, but it's only one more step to get to deadlocks (and it's related very directly to blocking).

Deadlocks can happen when you have two connections that each have a lock, but they want each others resource.  In this scenario, it's like two children that each has a ball, but wants the other's ball.

Like children, these connections are not willing to share at all.  Each connection needs access to both of the resources in order to continue.  However, they are in a state of permanent blocking.  In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.

Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.

So, the concept of a deadlock is where you have two resources that are blocking each other.

Here, you can read more about all the different types of locks SQL Server has to offer and the different resources that can cause blocking/deadlocks.  The article is old, but it still applies for SQL Server 2000 through 2008 R2.  (There are a few more types of locks added to later versions of SQL Server, but that will give you a starting point.)


Which string variables in mysql support utf-8? I have a table that stores string from a user and is displayed in a web form.
But as I see when the user inputs e.g. in a language of cyrilic alphabet (I assume is UTF-8) garbage are displayed back.
I did a show create table and I saw that the table is defined as LATIN-1 and the column that stores the string is defined as TEXT.
I am not clear on what type of data does TEXT stores. Is it only for ASCII? Should I be using a different data type? Which would be the most appropriate?

You'll have to specify a character set of UTF8 on the table schema.  See http://dev.mysql.com/doc/refman/5.5/en/charset-syntax.html

Depending on your needs you can specify table defaults which then apply to all unspecified text columns (char/varchar/text) or you can specify on a per column level.

You'll also need to have your applications to specify a UTF-8 character encoding.  The specifics on how to do this will depend on the language you are using.


pt-table-checksum and mysql 5.7 with binlog_format=ROW How to use pt-table-checksum in mysql 5.7 where binlog_format=ROW is default

I also need binlog_format=ROW to be set this way in relation to other parameters in the mysql configuration

# pt-table-checksum h=localhost,u=checksum,p=xxx
Replica Worker-Intel-i7-2600-16gb has binlog_format ROW which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.

I have tried to read about the limitations, but doesn't really understand what would/could happen if --no-check-binlog-format is used

If I set binlog_format=MIXED I still get the error!?

with --no-check-binlog-format you would not be able to checksum from master to cascading slaves aside from master's immediate replicas, --no-check-binlog-format also sets the tool's session to STATEMENT format but does not change the global status. there should not be any issues when you use the option but we recommend you test them first.


How to retain good records when inserting from stored procedure and still catch the error message? I am trying to catch the error message when inserting records into a temp table from a stored procedure without rolling back everything.  To illustrate, here is what I am trying to do:

CREATE TABLE #TEMPTABLE (some fields....)

BEGIN TRY
     INSERT INTO #TEMPTABLE
     EXEC SomeStoredProcedure @param1, @param2
END TRY
BEGIN CATCH
     ' Log error message in here
END CATCH

When executing just the INSERT INTO statement outside of a try catch block from within SSMS, I get a message similar to the following:

  Msg 8115, Level 16, State 2, Procedure SomeStoredProcedure, Line 337
  Arithmetic overflow error converting expression to data type int.
  The statement has been terminated.

BUT..., I also get some records inserted into #TEMPTABLE (perfectly fine for my requirements since I can't modify the existing SP but it still returns the data needed).

If I use the try catch block, I can capture the error message but #TEMPTABLE is empty.

Is there a way to capture the error message but still retain the records in #TEMPTABLE?

Honestly, I don't really need the try catch but this behavior got me curious.

Here is a sample code to reproduce the behavior I am experiencing:

-- SomeProc declaration
CREATE PROCEDURE [dbo].[SomeProc] AS BEGIN
CREATE TABLE #TEMP(ID INT NOT NULL)

INSERT INTO #TEMP VALUES(1)
INSERT INTO #TEMP VALUES(1 / 0)

SELECT ID FROM #TEMP END
GO

The following won't insert any records on #TEMPTABLE but will return error number

CREATE TABLE #TEMPTABLE(ID INT NOT NULL)

BEGIN TRY

INSERT INTO #TEMPTABLE
EXEC dbo.SomeProc END TRY BEGIN CATCH
SELECT @@ERROR END CATCH

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE

The following will insert a record on #TEMPTABLE but will not return any error number

CREATE TABLE #TEMPTABLE(ID INT NOT NULL)

INSERT INTO #TEMPTABLE EXEC dbo.SomeProc

SELECT @@ERROR  -- returns 0

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE

The TRY...CATCH block is causing the execution of the stored procedure to terminate before it has a chance to return the values in #TEMP.

Try this stored proc:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP
    VALUES (1);

    INSERT INTO #TEMP
    VALUES (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID
    FROM #TEMP;
END
GO

Running it, like this, without a TRY...CATCH, allows all the statements in the proc to run:

EXEC dbo.SomeProc;

The output:

  Msg 8134, Level 16, State 1, Procedure SomeProc, Line 12
  Divide by zero error encountered.
  The statement has been terminated.
  Run the SELECT

However, if you run it inside a TRY...CATCH:

BEGIN TRY
    EXEC dbo.SomeProc;
END TRY
BEGIN CATCH
    PRINT (ERROR_MESSAGE());
END CATCH

You see only the error message:

  Divide by zero error encountered.

The PRINT (N'Run the SELECT'); never runs, and indeed the SELECT ID FROM #TEMP; never runs either.  Hence no rows are returned, and nothing can be inserted into your #TEMPTABLE

From the MSDN documentation on TRY...CATCH:

  If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block.

As always, the devil is in the details.  TRY...CATCH always aborts the code inside the BEGIN TRY...END TRY code block if any error over severity 10 occurs that does not close the database connection.  Execution is immediately passed into the BEGIN CATCH...END CATCH block, even if this means aborting code in a stored proc.

Be aware that if an error occurs on any row the entire insert will not happen. The only reason you're seeing this error, and a row being inserted, is because you have two insert statements, one that runs to completion, and one that throws an error.  Take for instance:

IF OBJECT_ID('dbo.SomeProc') IS NOT NULL
DROP PROCEDURE dbo.SomeProc;
GO
CREATE PROCEDURE [dbo].[SomeProc]
AS
BEGIN
    CREATE TABLE #TEMP
    (
        ID INT NOT NULL
    );

    INSERT INTO #TEMP
    VALUES (1);

    INSERT INTO #TEMP
    VALUES (1)
        , (2)
        , (3)
        , (4)
        , (1/0);

    PRINT (N'Run the SELECT');

    SELECT ID
    FROM #TEMP;
END
GO

Using this stored proc, either inside a TRY...CATCH or with no TRY...CATCH block, will only result in a single row being inserted into the #TEMPTABLE - none of the rows in the 2nd insert will be present in the output.


How to check which table is called which trigger in postgresql? I have ten tables and five triggers in my database.
I don't know the trigger name, but I need to edit the trigger.
So I need to know which table is called to which trigger?
Using,

SELECT tgname
FROM pg_trigger;

we can print all the trigger name. But it did not show which table is called to which trigger.
Can anyone explain to me?

You need to join that to pg_class:

SELECT trg.tgname,
       ns.nspname||'.'||tbl.relname as trigger_table
FROM pg_trigger trg
 JOIN pg_class tbl on trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE NOT tgisinternal


mysql has two pid files I just finished re-building my (5.7) slave from a hot backup. When I issue
 systemctl start mysqld

it just "sat" there for over 20 minutes. It usually takes less than 10 seconds for the service to start. When I look at my server status, I see two mysql processes
mysql     1938     1  0 15:25 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
mysql     1940     1 99 15:25 ?        00:11:48 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root      4024  3122  0 15:37 pts/0    00:00:00 grep --color=auto mysqld

I stopped and restarted my service and there are two different PIDs showing up again
 [root@bi-db002-prd ~]# systemctl status mysqld -l
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start) since Mon 2020-08-17 15:46:45 UTC; 45s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7066 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  Control: 7088 (mysqld)
   CGroup: /system.slice/mysqld.service
           ├─7088 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
           └─7092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 17 15:46:45 bi-db002-prd systemd[1]: Starting MySQL Server...

I have never seen that. Is there something incorrect with my configuration?

yes this is normal, mysql can so handle multiple requests, like apache webserver or firfox which do the same.
actually 2 is quite low, , if the number of requests/connections increases so will the number of proce4sses(till max Connections is reached). Two means that you and a free process are at the moment  connected


Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a large relational data warehouse?

For example, assume I have a 100,000-row Customer dimension with a Postal Code column and a multi-billion-row Sales fact table with a CustomerID foreign key column. And assume I want to query "Total 2014 sales by customer's postal code".  Simplified DDL is like this (omitting many columns for clarity):

CREATE TABLE Customer
(
    CustomerID int identity (1,1) NOT NULL PRIMARY KEY CLUSTERED,
    PostalCode varchar(50) NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

CREATE TABLE Sale
(
    SaleId int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    SaleDateTime datetime2 NOT NULL,
    CustomerId int NOT NULL FOREIGN KEY REFERENCES Customer(CustomerID),
    SaleAmount decimal(10,2) NOT NULL
);

Where it gets interesting is that customers may have moved during the year so the same customer may have different postal codes. And it's even remotely possible that a customer moved away and then moved back, meaning there could be multiple history records for the same customer with the same postal code!  My query of "sales by postal code" should be able to calculate correct results regardless of how customers' postal codes change over time.

I understand how to use temporal tables to query the customer dimension alone (e.g. SELECT * FROM Customer FOR SYSTEM_TIME FROM '2014-1-1' TO '2015-1-1') but I'm not sure how to most accurately and efficiently join to the fact table.

Is this how I should query it?

SELECT c.PostalCode, sum(s.SaleAmount) SaleAmount
FROM Customer c FOR SYSTEM_TIME FROM '2014-1-1' TO '2015-1-1'
    JOIN Sale s ON s.CustomerId = c.CustomerId
WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1'
    AND c.SysStartTime >= s.SaleDateTime
    AND c.SysEndTime < s.SaleDateTime
GROUP BY c.PostalCode

And what are the performance considerations that I should watch out for when making queries like this?

I think, in your case a derived table is necessary to isolate querying number of mutations of postcodes  per customer:

SELECT c.postalcode
, sum(s.SaleAmount) SaleAmount
, count(postcode_mutations.customerid) as CntCustomerChangedPostCode
FROM dbo.Sale s
JOIN dbo.Customer c on s.customerid = c.customerid

LEFT JOIN (
SELECT
    CustomerID
FROM [dbo].[Customer]
FOR SYSTEM_TIME FROM '20140101' TO '20150101'
GROUP BY CustomerID
HAVING COUNT(DISTINCT PostalCode) > 1
) postcode_mutations on s.customerid = postcode_mutations.customerid

WHERE s.SaleDateTime >= '2014-1-1' AND s.SaleDateTime < '2015-1-1'
GROUP BY c.PostalCode

upd: Since the query is supposed to serve DWH/Analytics scenarios, the columnstore indexing is an option to check. I also made some benchmarks previously for a 10 million rows table.


Migrating triggers, functions and procedures from Oracle to PostgreSQL We run a Java based application which uses Oracle as its backend. While they are rather simple and don't overly use Oracle specific functions, we have lots of triggers, function and procedures written in pl/sql.

Now we are forced to run the same application with a PostgreSQL backend too, this means we will run two instances of our code, one connecting to Oracle and the other to PostgreSQL.
We know that we will need to make some adjustments, but hope to keep them at a minimum.

Is it possible to transfer most of our triggers, functions and procedures to PostgreSQL ? I have had a look at the migration guide and heard rumors that PostgreSQL only supports C as a language for writing triggers and so forth.

To give you an idea of which complexity I am talking about:

CREATE OR REPLACE FUNCTION "STAGE"."NEST"
(X IN number, Y IN number, Z IN NUMBER)
RETURN number

AS

NL number;
BEGIN
  select count(*) into NL from ABC where foo_id = X
        and Y < A and Z > B order by Z;
  RETURN NL;
END;

And a trigger:

CREATE OR REPLACE TRIGGER "STAGE"."SOMETHING"
  BEFORE INSERT
  on  "CONFIG"
  for each row
begin
   SELECT CONF_ID_SEQ.NEXTVAL INTO :new.CONF_ID FROM DUAL;
END;

You can certainly write triggers, functions and stored procedures (since Postgres 11) in languages other than C - the most popular is probably PL/pgSQL which is similar to Oracle's PL/SQL.

You can also write functions in SQL which is more efficient for simply "query encapsulation".

I would implement the function in your question as a simple SQL function:

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
  select count(*)
   from ABC
   where foo_id = X
     and Y < A
     and Z > B;
$$
language SQL;

I remove the order by from that statement as it is useless for a query that returns a count(*)

The same function implemented with PL/pgSQL would be:

CREATE OR REPLACE FUNCTION stage.nest(x integer, y integer, z integer)
RETURNS bigint
AS
$$
declare
  l_count bigint;
begin

  select count(*)
    into l_count
  from ABC
  where foo_id = X
    and Y < A
    and Z > B;

  return l_count;
end;
$$
language SQL;

But in general PL/pgSQL should only be used if you do need procedural logic (loops, IF statements etc). Otherwise language sql is more efficient.

You don't really need a trigger in Postgres automatically generate ID values from a sequence. Just define the column as serial and things will be taken care of. If you want to force the use of a sequence, define the column as integer generated always as identity

Nevertheless the trigger would be very similar in Postgres, with the biggest difference that Postgres uses a trigger function that is referenced when defining the trigger.

create function f_config_trg()
  returns trigger
as
$$
begin
  new.CONF_ID := nextval('confi_id_seq');
end;
$$
language plpgsql;

create trigger config_trigger
  before insert on config
  for each row
  execute procedure f_config_trg();

You can find much more details on PL/pgSQL, functions and trigger functions in the manual

Unrelated to your question: don't get into the habit of using quoted identifiers in Postgres (or Oracle for that matter). They are much more trouble in the long run than they are worth it.


Why is an index I created not being used in a query execution? Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be.

I have a query inside an indexed view, like this:

SELECT      [a].[ListingId],
            -- lots of other columns from [a], [b], [c], [d] etc
FROM        [dbo].[Listings][a]
INNER JOIN  [dbo].[ListingFeatures][b] ON [a].[ListingId] = [b].[ListingId]
INNER JOIN [dbo].[ListingBuildingDetails][c] ON [a].[ListingId] = [c].[ListingId]
INNER JOIN [dbo].[ListingLandDetails][d] ON [a].[ListingId] = [d].[ListingId]
WHERE       [a].[IsVisible] = 1
AND         [a].[IsLive] = 1
AND         [a].[AgencyCompanyId] IS NOT NULL

Note the 3 predicates.

I created a covering index for the predicates, and included the ListingId column, hoping the query optimizer would use the index then do a KEY LOOKUP to fetch the other columns from that table, then do a clustered index seek to get the other table data.

Here's the index definition:

CREATE NONCLUSTERED INDEX [IX_Listings_ForListingSearchView] ON [dbo].[Listings]
(
    [IsVisible] ASC,
    [IsLive] ASC,
    [AgencyCompanyId] ASC
)
INCLUDE (   [ListingId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Quite the contrary, here's the estimated execution plan:

My questions:

Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?
I noticed if i change the query to just fetch a.ListingId and not the rest of the columns from a, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then?
Why is the query optimizer doing an clustered index scan and not a seek?
This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?

Thanks everyone for your help. I think i've/we've answered the questions.

  Why isn't the optimizer using my covering index? Is it because it thinks it's cheaper to scan the whole table?

Because the optmizer has to read most rows in table, it's preferring the clustered index.

  I noticed if i change the query to just fetch a.ListingId and not the rest of the columns from a, it uses my index (and the reads/cost seems much lower, due to avoiding the index scan). Should I use this in a subquery then?

No. Optimizer is doing the right thing. Forcing an index hint proves that, when looking at cost difference.

  Why is the query optimizer doing an clustered index scan and not a seek?

See first answer

  This query is the one existing in an index view. When the index view needs to be updated, is this the actual query that runs to update itself? My thinking was yes, which is why I'm trying to optimize it. If the index view update doesn't use this query, then I won't bother?

As requested, I split this out into a different question


can a table in 2nd normal form have a FD in which the non prime attribute depend on a super key having an extraneous attribute? For 2NF every source I can get hands on says the following:

  The non prime attribute must be fully dependent on a candidate key.

But can the non prime attribute be dependent on a super set of the candidate key? I mean can the left side contain an extraneous attribute.

Also, please explain, if it is necessary that all non prime attributes have to be fully dependent upon on any of the candidate keys . Can't one non prime attribute just be dependent on another non prime attribute?

You are asking:

  But can the non prime attribute be dependent on a super set of the candidate key ? I mean can the left side contain an extraneous attribute.

If a set of attributes is a candidate key, then it determines all the other attributes, including non prime attributes. So, if you add attributes on the left side nothing changes with respect to the definition of 2NF, the only important thing is that a non prime attribute cannot depend on a proper subset of a candidate key.

Then, you are asking:

  Can't one non prime attribute just be dependent on another non prime attribute?

If in a relation schema a non prime attribute depends from another non prime attribute we can only say that this dependency does not violates the original Codd’s definition, that requires only that every non prime attribute must be “fully dependent on each candidate key of R”, which can also be expressed as: does not exists a dependency in which the left part is a proper subset of a candidate key and the right part is a non prime attribute.

So, for the same reason, if a dependency contains in the left part a proper subset of a candidate key plus a non-prime attribute, this does not fall under the Codd’s definition, meaning that it does not violate such definition.

Finally, note that the 2NF has not interest neither in practice nor in theory: it  has only historical interest. In fact, a relation is said properly normalized if it is in Boyce-Codd Normal Form or in Third Normal Form (or in Fourth or greater normal forms). In fact in many important database books you can find formal algorithms to bring a relation in BCNF or 3NF, but not in 2NF.


Call dblink from a trigger function I have an environment where I have to move data from one database to another once a day. I Try to solve this by calling 'dblink' from a triggered function.

The following statement I can execute from commandline:

SELECT fieldA, fieldB, fielbC
INTO temp_table
FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw',
            E'SELECT fieldA, fieldB, fieldC FROM data_table')
     AS temp_table(fieldA integer, fieldB integer, fieldC integer);

But when trying to embed this into a pgsql procedure it won't work.

I'm trying this by using the execute command:

CREATE OR REPLACE FUNCTION archive() RETURNS trigger AS $$
DECLARE
BEGIN

    execute
    '
      SELECT fieldA, fieldB, fielbC
      INTO temp_table
      FROM dblink(''dbname=dbname port=5432 host=a.b.c.d user=user password=pw'',
                  E''SELECT fieldA, fieldB, fieldC FROM data_table'')
          AS temp_table(fieldA integer, fieldB integer, fieldC integer);
    ';

    return new;
END;
$$ LANGUAGE plpgsql;

I get an error telling me that EXECUTE of SELECT ... INTO is not implemented and I could use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS

Does anybody has some experience with this? I'm struggling for hours now :-(

As you have nothing dynamically built in your query, you don't have to use EXECUTE at all.

Inside the function body, you can simply do an INSERT if temp_table already exists:

INSERT INTO temp_table (fielda, fieldb, fielbc)
SELECT a, b, c
  FROM dblink('dbname=dbname port=5432 host=a.b.c.d user=user password=pw',
              E'SELECT fielda, fieldb, fieldc FROM data_table')
       AS t(a, b, c);

If the table is not there yet, you can replace the INSERT line with

CREATE TEMPORARY TABLE AS
...

Note that in your present approach (SELECT ... INTO temp_table) the table that is being created is not a temporary table, and on the next run the trigger will run into an error saying the table already exists.


How to run this MySQL statement? See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html:
I copy it and paste on MySQL command line but it stop at every semicolon.

I wonder how to make these sentences work? I really don't know why.

mysql> CREATE PROCEDURE doiterate(p1 INT)
-> BEGIN
->   label1: LOOP
->     SET p1 = p1 + 1;

ERROR 1046 (3D000): No database selected
mysql>     IF p1 < 10 THEN
    ->       ITERATE label1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the
                    manual that corresponds to your MySQL server
                    version for the right syntax to use near
   'IF p1 < 10 THEN
      ITERATE label1' at line 1
mysql>     END IF;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
                    manual that corresponds to your MySQL server
                    version for the right syntax to use near
   'END IF' at line 1
.......

delimiter |
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
END;
|
delimiter ;

If you don't set another delimiter than ; the statement will end at the first ; and your procedure definition will be incomplete. You  need to tell MySQL that the statement should end at the delimiter you defined. After that you can set the delimiter back with delimiter ;


Why would a file generated by mysqldump be substantially larger than the data directory I have a MariaDB server - 5.5.60
I have a database xyz that has both MyISAM and InnoDB tables
The majority of the tables are MyISAM but in this database in particular, the data is mainly in InnoDB tables.

If I run du -sh /var/lib/mysql/xyz I get 11M

If I run mysqldump -u root -pXXX "xyz" > xyz.sql the resulting file is 371M

I can use the xyz.sql file to restore the database just fine.  With other databases I have had all MyISAM tables and never noticed much of a difference in size.

Why is the .sql file so much larger than the actual database directory?  Is there something I should do differently when using mysqldump with a database with InnoDB tables?

this is normal.

with a version 5.5.60 default settings for innodb innodb_file_per_table = 0
it means - data stored not in files inside the database folder, but in global innodb file.

You can check it in /var/lib/mysql
file - ibdata1

after version 5.6 default value for innodb_file_per_table settings is 1

ibdata1 is still extremely important, but data stored in separate files similar for myisam.


Where to store data on AWS? I'm using AWS to host my application, and I'm using mongodb as db. I have installed mongodb on amazon ec2, but now I have no idea about where I should store all of my data. Can I store all of my data on ec2 itself as my db will grow with time?

Yes.

Think of EC2 as a machine. It has a disk attached - a EBS (Elastic Block Store), and the disk size is your limit. Lets say your ec2 instance has a 20gig disk. If you database grow over 20gig, you will have to resize your attached disk.

I am not aware of a auto-scalling method for EC2 disks.

But you can simply make a snapshot of a EBS, create another EBS from this snapshot with you desired size, detach the old disk from your machine and attach the new one. This new disk will have all of the old disk data (its a snapshot, a clone) but will be bigger in size. Lets say you make it 30gigs this time.

After that, if you are on a Linux EC2 Machine, you'll have to run a command within the machine to resize the disk to the new value. Its as simple as that.
Just the other day my machine started behaving bad, I checked and it had a disk full. The whole proccess took a couple of minutes and was done entirely on the EC2 dashboard plus a simple ssh command on the machine.

Full details here: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-expand-volume.html


Problems migrate database from 4 to 5.5 mysql I have a server with 4.0 mysql version innodb engine.

I need to migrate to other server with 5.5 version (linux) ok

I dump the full database using mysqldump, when i try to restore on the 5.5 server it has a problem, because the file generate in the 4.0 set the variable on the sql scritp TYPE=INNODB and mysql 5.5 accepts only ENGINE=INNODB format on the time table creation and it abort the process.

I search on google, call any friends and nothing! the sql file has 16 gigabytes. Is not possible edit with an file editor.

error exemple:

CREATE TABLE tabadi (
nAdicao char(3) default '0',
nSeqAdic char(3) NOT NULL default '',
cFabricante varchar(60) NOT NULL default '',
vDescDi varchar(15) NOT NULL default '',
xPed int(6) NOT NULL default '0',
nItemPed int(6) NOT NULL default '0',
nDI varchar(12) NOT NULL default ''
) TYPE=InnoDB

  ERROR 1064 (42000) at line 19: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the
  right syntax to use near 'TYPE=InnoDB' at line 9

Ps: I did try to change TYPE TO ENGINE and its solved the problem.

Can anybody help me? Thanks a lot.

This should be as simple as piping the output of mysqldump through a utility that can manipulate the data stream with a regular expression pattern match, like sed or perl.

mysqldump [options] | perl -pe 's/^\)\sTYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

For any line that begins with ) TYPE=InnoDB this will replace the starting portion of the line with the new string ) ENGINE=InnoDB and leave the rest of the line intact.  It won't do anything to lines that don't match (they'll pass right through).

For quick and simple validation that this does what you want, use the --no-data option in mysqldump to dump only the table structures, not the data, and examine the resulting file.  If it looks right, you can repeat the process without --no-data.

A similar expression in sed should be this:

mysqldump [options] | sed 's/^) TYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

...however, I'm not as conversant in sed as I am in perl, so I'd recommend the first approach, although I tested both versions and they appear to work identically.

Both perl and sed should be already present on any *nix system, and both are freely downloadable for Windows.  On Windows, you'll probably need " rather than ' surrounding the expressions.


How to add a user with access to a single view? I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a user and giving that user appropriate permissions to select from the view.

I followed the wizards for creating a login and a user and then added my view in the Securables section with the grant box checked for select. Everything seemed fine but when I logged in as that user and tried to do a "Select * from MyViewName" it told me that the select permission was denied.

I just recreated the user (this time just using SQL instead of the wizard) and explicitly granted select permissions and now it is giving me the error: Msg 916, Level 14, State 1, Line 2
The server principal "username" is not able to access the database "unrelated_db" under the current security context. (I don't know why it's trying to access the unrelated database...)

I really don't know where to go from here. Again, basically all I need is to create a user that I can give to the third party to have them connect to our database and select from this view.

Please don't use the UI for this. It's a confusing mess.

It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:

USE your_db;
GO
CREATE USER username FROM LOGIN username;
GO
GRANT SELECT ON dbo.MyViewName TO username;
GO

EDIT here is an example of a script that will lead to the error you mention.

First, create some table in the unrelated_db:

CREATE DATABASE unrelated_db;
GO
USE unrelated_db;
GO
CREATE TABLE dbo.foo(bar INT);
GO

Now create a relatively restricted login:

USE [master];
GO
CREATE LOGIN username WITH PASSWORD='foo', CHECK_POLICY = OFF;
GO

Now create a database where the view will live, and add the login as a user:

CREATE DATABASE velojason;
GO
USE velojason;
GO
CREATE USER username FROM LOGIN username;
GO

Now create a function that will reference the table in the other database, and a synonym to the other table:

CREATE FUNCTION dbo.checkbar()
RETURNS INT
AS
BEGIN
    RETURN
    (
      SELECT TOP (1) bar
        FROM unrelated_db.dbo.foo
        ORDER BY bar
    );
END
GO
CREATE SYNONYM dbo.foo FOR unrelated_db.dbo.foo;
GO

Now create a local table:

CREATE TABLE dbo.PaymentDetails
(
  PaymentID INT
);
GO

Now create a view that references the table, the function and the synonym, and grant SELECT to username:

CREATE VIEW dbo.SomeView
AS
  SELECT
    p.PaymentID,
    x = dbo.checkbar(), -- function that pulls from other DB
    y = (SELECT bar FROM dbo.foo) -- synonym to other DB
    FROM dbo.PaymentDetails AS p;
GO
GRANT SELECT ON dbo.SomeView TO username;
GO

Now try to execute as username and select only the local column from the view:

EXECUTE AS USER = 'username';
GO
  -- even though I don't reference any of the columns
  -- in the other DB, I am denied SELECT on the view:
SELECT PaymentID FROM dbo.SomeView;
GO
REVERT;
GO

Result:

  Msg 916, Level 14, State 1, Line 3 The server principal "username" is
  not able to access the database "unrelated_db" under the current
  security context.

Now change the view to not reference any external objects, and run the above SELECT again, and it works:

ALTER VIEW dbo.SomeView
AS
  SELECT
    p.PaymentID
    --x = dbo.checkbar(),
    --y = (SELECT bar FROM dbo.foo)
    FROM dbo.PaymentDetails AS p;
GO

Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view sys.sql_expression_dependencies, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.

DECLARE
  @dbname   SYSNAME = N'unrelated_db',
  @viewname SYSNAME = N'dbo.SomeView';

SELECT DISTINCT
    [This object] =
    OBJECT_SCHEMA_NAME([referencing_id])
      + '.' + OBJECT_NAME([referencing_id]),
    [references this object] =
    OBJECT_SCHEMA_NAME([referenced_id])
      + '.' + OBJECT_NAME([referenced_id]),
    [and touches this database] = referenced_database_name,
    [and is a(n)] = o.type_desc,
    [if synonym, it references] = s.base_object_name
FROM sys.sql_expression_dependencies AS d
LEFT OUTER JOIN sys.objects AS o
ON o.[object_id] = d.referenced_id
LEFT OUTER JOIN sys.synonyms AS s
ON d.referenced_id = s.[object_id]
AND s.base_object_name LIKE '%[' + @dbname + ']%'
WHERE OBJECT_ID(@viewname) IN (
        referenced_id,
        referencing_id,
        (SELECT referencing_id FROM sys.sql_expression_dependencies
        WHERE referenced_database_name = @dbname)
) OR referenced_database_name = @dbname;

SQL Server isn't just going to try to access unrelated_db for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)

You may also want to check sys.dm_sql_referenced_entities, however this function returns nothing useful in the example above.


Error importing SQL Azure Bacpac to SQL Server 2014 LocalDb Downloaded a v12 SQL Azure bacpac, and trying to import it into a SQL Server 2014 LocalDB... I get:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not load schema model from package. (Microsoft.SqlServer.Dac)

------------------------------
ADDITIONAL INFORMATION:

Internal Error. The database platform service with type     Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid.     You must make sure the service is loaded, or you must provide the full type name of a valid database platform service. (Microsoft.Data.Tools.Schema.Sql)

------------------------------
BUTTONS:

OK
------------------------------

I have tried installing the Data-Tier Framework Jan 2016 Preview which did not make any difference (both x64 and x86).

Any suggestions?

Turns out the solution is pretty simple, you need to ensue you have sql Server 2014 sp1 installed. can't believe I missed that!


Convert Log table days in a state This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following...

Input Table
Date    Item   Moved to Box
Oct-1   1      BoxA
Oct-6   1      BoxB
Oct-8   1      BoxC
Oct-9   1      BoxB
Oct-16  1      BoxC
Oct-17  1      BoxD

And I am trying to convert it into this

Expected Output
Item    Box    Duration
1       BoxA   5
1       BoxB   9
1       BoxC   2
1       BoxD   *unimportant

*It doesn't matter what the query returns for BoxD (the only box move in without a box move out) as it is discarded.

For the example hopefully you can see that the input table represents a log of when an item moves from box to box and the expected output is cumulitively how long each item spends in each box.

My first thought was to do a table join with itself and do some date min/maxing for each record to try to find the exit date from a box, then sum the results but it seems pretty process intensive.

How would someone approach this in an efficient way?

select item,
       sum(duration),
       moved_to
from (
  select item,
         lead(move_date)  over (partition by item order by move_date) - move_date as duration,
         moved_to
  from movement
) t
group by item, moved_to
order by item, moved_to;

SQLFiddle example: http://www.sqlfiddle.com/#!4/dff2c/1


Previous Requirement

Below query is far improve version of previous one.

 ;WITH CTE as
 (
  select CalanderDateTime,PtID,EventDateTime ,GapinMinutes
 ,ROW_NUMBER()OVER(ORDER BY CalanderDateTime)rn
 from #t
 )
--,CTE1 AS(
-- select c.* from cte c
--cross apply(select rn from cte c1
-- where c1.GapinMinutes>3 and
-- (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1)) )ca
-- )
 select CalanderDateTime,PtID,EventDateTime ,GapinMinutes
 from cte c
 where not exists (
 select rn from cte  c1
 where c1.GapinMinutes>3 and
 (c.rn>c1.rn and c.rn<(c1.rn+c1.GapinMinutes+1))
 )

let the index be commented now.you don't need the commented CTE1.

New Requirement, (Here your output require is quite change and I am only working on part of your dataset).

Main trick is to implement it seamlessly as part of your main query.

create table #ttt (RN int,CalanderDateTime datetime,PtID int,EventDateTime datetime,GapinMinutes int)
insert into #ttt values
(1  ,'2017-09-23 08:34',NULL ,        NULL          ,   NULL)
,(2  ,'2017-09-23 08:35',NULL ,        NULL          ,   NULL)
,(3  ,'2017-09-23 08:36',NULL ,        NULL          ,   NULL)
,(4  ,'2017-09-23 08:37', 1   ,  '2017-09-23 08:37'  ,     1)
,(5  ,'2017-09-23 08:38', 1   ,  '2017-09-23 08:38'  ,     4)
,(6  ,'2017-09-23 08:43', 1   ,  '2017-09-23 08:43'  ,     3)
,(7  ,'2017-09-23 08:44', NULL,       NULL           , NULL )
,(8  ,'2017-09-23 08:45', NULL,       NULL           , NULL )
,(9  ,'2017-09-23 08:46', NULL,       NULL           , NULL )
,(10 ,'2017-09-23 08:47', 2   ,  '2017-09-23 08:47'  ,     3)
,(11 ,'2017-09-23 08:48', NULL,      NULL            ,NULL  )
,(12 ,'2017-09-23 08:49', NULL,      NULL            ,NULL  )
,(13 ,'2017-09-23 08:50', NULL,      NULL            ,NULL  )

CREATE clustered INDEX x_ix11 ON #ttt(rn);
CREATE INDEX x_ix1 ON #ttt(ptid);

--drop index x_ix11 on #ttt

select t.RN ,t.CalanderDateTime
, case when t.PtID is not null then t.ptid else ca.ptid end ptid
,t.EventDateTime ,t.GapinMinutes
from #ttt t
outer apply(select top 1 t1.ptid
from #ttt t1
where t.rn>t1.rn and t1.PtID is not null
and t.ptid is NULL
 order by t1.rn desc)ca

Minor change in query.

Index should not be created by understanding just part of dataset.

Here dataset is very small.But after creating index aim getting one Index Seek and one index scan i.e. query plan seem improve.

IMHO, given outer apply is one best way to do so (80%) .

But with million of rows already,  outer apply increase the  the rows by 50%. inequality operator t.rn>t1.rn is doing problem

May be it can be implemented from view itself.

It belong to which table and how many rows this table returns ?

Tried to optimize your main query as well as implemented new requirement

This part of query only indicate that what correct should be done,it may not run as it is.you have to fix the bug.

With VentHours   AS
(
    SELECT sig.PatientID ptID, convert(DateTime,sig.Time) t,

        LEAD(sig.Time) OVER (ORDER BY sig.PatientID, Sig.Time)) LeadTime,

         LEAD(sig.PatientID) OVER (ORDER BY sig.PatientID, Sig.Time) LeadPatient,
        p.rAdmitDate adt,
        p.rDischDate dit
        FROM Reporting.dbo.VIEW_Vent_Signals sig
        inner join Reporting.dbo.ICU_Adult_Patients p ON sig.PatientID = p.ptID

),

    Select ts.ID rn
    , case when vh1.ptid is not null then vhi.ptid else vh.ptID end ptid
    , ts.DateTime,  case when vh.t is null then ts.DateTime else vh.t end t, vh.adt, vh.dit
    , Case When DateDiff(MINUTE,sig.Time,LeadTime BETWEEN 2 AND 720 THEN 1 ELSE 0 END et
    ,Case When sig.PatientID = LeadPatient AND DateDiff(MINUTE,sig.Time,LeadTime) between 0 and 720
        THEN DateDiff(MINUTE,sig.Time,LeadTime) ELSE 0 END VGM
        ,vh1.ptid
    from Reporting.dbo.AUX_DateDimension ts
    left outer join VentHours vh ON convert(DateTime,ts.DateTime) = vh.t
    outer apply(select top 1 vh1.ptid
from VentHours vh1
where vh.ID>vh1.ID and vh1.PtID is not null
 order by vh1.id desc)ca


Best practice - Log shipping / replication for archive / reporting database We have a setup with a product that has a production database (many customer installations). The production database has been around many years and there are some performance issues that is getting worse. However the customers like to keep the data for generating different kind of reporting and statistics. Sometimes it also import to audit product logs.

So what we want to do is setup an archive / reporting database that the customer handles themselves and the production only holds "active" data and is maintained by a clean-up job that removes old dates.

There are many users +10000 that access the production database daily but very few that access the archive database.

What is the best way to achieve this, build in replication, log shipping or write a synchronisation mechanism?
Performance issues on the production database?

If we for instance decides for logshipping, is it possible to prevent that delete operations on the production database are propagated to the archive database?

Thanks,

I would recommend to use log shipping but only if you do not want to prevent delete operations. Log shipping keeps the target database the same as the source database.
Log shipping is a relatively simple mechanism, it just backups and copies over the transaction log and applies it to the target database. It is a bit simpler to setup and to maintain than Replication.

Replication also keeps the source database completely in sync with the target database. With transactional or snapshot replication there is no possibility to exclude delete operations.
The only exception is that in merge replication exclusion of DELETE statements is still possible. However, this possibility will be removed in future versions of SQL Server (see the first note in https://msdn.microsoft.com/en-us/library/ms146947.aspx). So that is nr. 1 of your worries I think. Some more considerations can be found here: https://technet.microsoft.com/en-us/library/ms151206(v=sql.105).aspx.

Performancewise there also can be a big impact. This depends a lot on the database and the operations on it. So test it in a test environment to see if the performance loss is acceptable.
Personally, I'm not a huge fan of merge replication as I have experienced many problems with it.

Summarizing, if you want to prevent delete operations I would recommend to write your own synchronization mechanism. If you don't want to do that, and can live with the issues mentioned above, merge replication will also be usable.


Replace query result with case/when failing Using MS SQL, I like to replace the information in the OEM_Description column when the column Item translation (IT).Description is NULL and when the language setting of the customer C.[Language Code] is English (ENG) into the original item description (I.[Description])

I have come up to this query:

SELECT
    "OEM_Description" =
CASE
   WHEN  (C.[Language Code] = 'ENG' and IT.[Description] = NULL)
   THEN I.[Description]
  ELSE IT.[Description]
END,
C.Name as Customer_Name

However the information is only being replaced if I test on "C.[Language Code] = 'ENG'" as soon as i add the test for IT.[Description] = NULL nothing is replaced anymore.

What do I wrong?

You don't mention what DBMS, but you probably need IS NULL instead of = NULL. However,  you're creating a complex case when this is a texbook use of COALESCE ():

SELECT
    "OEM_Description" = COALESCE( IT.[Description], I.[Description] ),
    C.Name as Customer_Name

Alternate solution based on comment:

IS NULL is not a function but a replacement for = NULL in your original like this:

SELECT
    "OEM_Description" =
CASE
   WHEN  (C.[Language Code] = 'ENG' and IT.[Description] IS NULL)
   THEN I.[Description]
  ELSE IT.[Description]
END,
C.Name as Customer_Name


Attribute and Value tables on SQL - Is this a good practice? I'm not a SQL expert and I'd like to ask for the best practices on the following schema.

On my current project, we're using Attribute and AttributeValue tables quite often. I'm thinking this might not be a good practice since it involves joins when we need that data. Example:

Given the Person table, there's two more tables that basically contain "extra" data about person; those are PersonAttribute and PersonAttributeValue.

I don't have a strong argument to prevent this practice on my team, but I'd like to hear what SQL experts have to say.

When you start using a relationnal database as a key/value store, that's normally a good indicator that you are dealing with schemaless/semi-structured data. SQL databases aren't best suited for the task and there are better alternatives such as NoSQL databases that will offer you great advantages over traditional relationnal databases, such as simplicity of design and performance gains.

Note: Schemaless data isn't the only reason that makes NoSQL databases attractive, for instance they also greatly simplify the tedious Object-relational mapping problem.

It's quite common these days to use multiple different storage technologies and the reason is simple: it's better to use the right tool to solve a problem elegantly than trying to adapt one to solve them all.

However, sometimes it isin't an option to choose your persitence layer for different reasons and you will have to find a way to deal with the problem with the tools you have.

The first thing I would do in your case is to check wheter the data you are trying to store really needs that level of schema flexibility and why?

Here's an example of a few valid use cases (in my opinion):

You need to allow the creation of new entities at runtime and be able to define and change their schema dynamically.
You need to store lots entities of the same kind where each of them could have arbritrary properties (think of various activities in an activity feed). I emphasis on lots because otherwise you can generally solve the problem using single table inheritance, concrete table inheritance or class table inheritance if it doesn't lead to an explosion of tables.

Now, both of these could probably still be solved using a standard relational approach but
the solution would feel wrong. For instance, to solve #1, nothing prevents you use a solution where tables are dynamically created and altered and for #2, nothing prevents you from manually managing an explosion of tables, but I think there are more maintainable alternatives.

If your reason isin't listed above, I strongly advise you to validate that you actually need this kind of flexibility by reviewing what your solution would look like if you used a standard approach. If you only need to create a few additionnal tables, that's generally the best path since there are many drawbacks for the various alternatives I propose below.

Practical solutions (not perfect) to store semi-structured data:

First of all, if applicable, identify carefully the common properties for all the different type of entities and create a table ready to hold that structure. That will consist of the semi part of our semi-structured data.

Then:

Add a column, let it be called "data", where you will store a serialized representation of your schemaless data in your data-interchange format of choice, such as JSON or XML.

ADVANTAGES:

Reads from the DB are very fast since you do not have to perform any joins.
You can store any kind of complex object hierarchy, as long as the
serialization format supports it.

DOWNSIDES:

The data has to be deserialized to be useful (it's generally fast enough not to cause any performance issues).
Searches are more complex and would rely on full-text searches (I've never used this solution when the data had to be searchable and have no idea how it would scale)
The data is denormalized and would have to be kept in sync, if applicable.
If you are too concerned about having to sync your data since it's denormalized,       an approach I've experimented is to store computed properties instead of the property    value. For instance, instead of storing the value "John Doe", you would store meta-data that allows to fetch the data dynamically at read, such as: id, table_name, field_name. You can implement memoization to make the reads faster. However I do not recommend this approach if you need a scalable solution.

Create another table, let it be called "EntityAttribute" that will look like (that's basically what you have I believe):

 id int NOT NULL IDENTITY(1, 1) PRIMARY KEY

 entity_id int NOT NULL

 [key] varchar(50) NOT NULL

 [value] sql_variant NOT NULL

Please note that both solutions also have the disadvantage of being unable to rely on database contraints to enforce data integrity.

In conclusion, make sure to clearly identify the problem and your needs first. Check if a solution can be efficiently implemented using the tools your have. If it cannot, check if there are other tools you could use that would suit your needs. Also, have a look at alternative ways of solving your issue with the technologies you have. I haven't highlighted all the advantages and drawbacks of the alternative solutions and there are probably other alternatives as well, so be sure to look up other sources before making any choice.


Changes in Oracle Database implicit conversion rules for select statement Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while the new hardware is running Oracle 11G R2. Everything went smoothly and the application ran normally again.

The last few days our users reported problems with the software. After some research the application runs a sql statement which tries to filter a varchar column with a number thereby forcing an implicit conversion of the column.

In the previous version of Oracle everything went fine but now an "ORA-01722: invalid number" is thrown.

Might there be some setting we have missed for the new database?

We already contacted the developing company but it will take some time.

Sample table and data:

create table conversion (
  col1 varchar2(10)
  );

insert into conversion values ('test2');
insert into conversion values ('42');
insert into conversion values ('test3');

Simplified statement throwing the error:

select count(*) from conversion where col1 = 42;

Thanks in advance and best regards
Jan

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :)

Don't really see any solutions here except updating the SQL query. This is why nobody recommends the usage of implicit conversions. They could also cause a slight decrease of performance.

EDIT: If you don't have access to the query and have to wait for the dev team, but instead you have enough privileges on the database, and know for sure that the VARCHAR column holds only number, you could try to alter the table and convert the column type.


PostgreSQL: Calculate balance working backwards from current balance I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, withdrawals are negative).

I need to create an account summary that contains each transfer for an account, as well as the balance resulting from that transfer.

Example:

+--------------------------+--------+---------+
|      activity_date       | amount | balance |
+--------------------------+--------+---------+
| 2015-12-24T20:27:00.670Z |     10 |     180 |
| 2015-12-19T12:13:50.085Z |   -275 |     170 |
| 2015-12-18T23:56:22.513Z |     10 |     445 |
| 2015-12-18T23:54:46.880Z |     50 |     435 |
| 2015-12-17T03:32:10.707Z |   -120 |     385 |
| 2015-12-12T03:56:50.775Z |     35 |     505 |
| 2015-12-11T23:09:40.211Z |    -20 |     470 |
| 2015-12-03T01:17:59.460Z |    -10 |     490 |
| 2015-11-23T15:39:35.003Z |    500 |     500 |
+--------------------------+--------+---------+

Since I only have the current balance for the account, I need to start there and work my way backward to a given date. In a spreadsheet, I would calculate the new balance by subtracting the amount of the transfer from the previous balance, but I'm having trouble translating this into SQL.

Schema

CREATE SCHEMA temp;

CREATE TABLE temp.balances (
  account_id      INT
, current_balance INT
);

CREATE TABLE temp.transfers (
  account_id      INT
, activity_date   TIMESTAMP
, amount          INT
);

INSERT INTO temp.balances (account_id, current_balance)
VALUES (1, 180);

INSERT INTO temp.transfers (account_id, activity_date, amount)
VALUES (1, '2015-12-24T20:27:00.670Z', 10)
, (1, '2015-12-19T12:13:50.085Z', -275)
, (1, '2015-12-18T23:56:22.513Z', 10)
, (1, '2015-12-18T23:54:46.880Z', 50)
, (1, '2015-12-17T03:32:10.707Z', -120)
, (1, '2015-12-12T03:56:50.775Z', 35)
, (1, '2015-12-11T23:09:40.211Z', -20)
, (1, '2015-12-03T01:17:59.460Z', -10)
, (1, '2015-11-23T15:39:35.003Z', 500);

SQL

WITH transfers AS (
  SELECT *
  FROM temp.transfers
  WHERE account_id = 1
    AND activity_date BETWEEN '2015-10-01'::DATE AND CURRENT_TIMESTAMP
  ORDER BY activity_date DESC
)
SELECT
  t.activity_date
, t.amount
, (LAG(balance, 1, b.current_balance) OVER (ORDER BY t.activity_date DESC)::INT) - (LAG(t.amount, 1, 0) OVER (ORDER BY t.activity_date DESC)::INT) AS balance
FROM transfers t
JOIN temp.balances b ON b.account_id = t.account_id

This query fails because I am trying to access the value of balance that is being generated by the window function. I looked into WITH RECURSIVE, which seems like the right technique to use, but couldn't get it to work. I'm also not sure if it can be used in conjunction with other WITH clauses, which I would need to do.

So my question is: How can I calculate the balance after each transfer, working backward from the current balance, in PostgreSQL 9.3?

Update

Using a slightly-modified version of Julien's answer below, I tried this:

WITH transfers AS (
  SELECT *
  FROM temp.transfers
  WHERE account_id = 1
    AND activity_date BETWEEN '2015-10-01'::DATE AND CURRENT_TIMESTAMP
)
SELECT
  t.activity_date
, t.amount
, b.current_balance - SUM(t.amount) OVER(ORDER BY t.activity_date DESC) AS balance
FROM transfers t
JOIN temp.balances b ON b.account_id = t.account_id

...which is almost correct, except that the balances are shifted up one row.


Error with my trigger. What should I do? This trigger fails when it's called:

create or replace TRIGGER UNIQUE_PHYSICIAN_TRG
    BEFORE INSERT ON UNIQUE_PHYSICIAN
    FOR EACH ROW
    BEGIN
      <<COLUMN_SEQUENCES>>
      BEGIN
        IF INSERTING AND :NEW.UNIQUE_ID IS NULL THEN
          SELECT UNIQUE_PHYSICIAN_SEQ.NEXTVAL INTO :NEW.UNIQUE_ID FROM SYS.DUAL;
        END IF;
        IF INSERTING AND :NEW.FSA IS NULL THEN
          SELECT SUBSTRING(inserted.POSTAL_CODE, 0, 3) INTO :NEW.FSA FROM INSERTED;
    END IF;
      END COLUMN_SEQUENCES;
    END;

The line:

  SELECT SUBSTRING(inserted.POSTAL_CODE, 0, 3) INTO :NEW.FSA FROM INSERTED;

seems to be the root of the problem. Is something wrong syntactically?

Oracle doesn't have an inserted (or a deleted or an updated) table.  That's something that SQL Server provides in a trigger.  The Oracle substring function is also substr not substring.

My guess is that you just want

IF :new.fsa IS NULL
THEN
  :new.fsa := SUBSTR(:new.POSTAL_CODE, 0, 3);
END IF;


Key/value table: what is a good way to organize it if value could be foreign key? Suppose a have a structure of two tables

Table Key names:

|_Key name_|_Parent_key_id_|
| key1     |  NULL         |
| key2     |  key1         |

Table Key values:

|_Key__|_Value__|_Version__|
| key1 | value1 | version1 |
| key2 | value3 | version1 |
| key1 | value2 | version2 |
| key2 | value3 | version2 |

So key values are unique within the Version and keys follow nested structure.

The goal is to organize tables the way that key values could be a string, number (integer or double) or reference (id) to a row from another table.

My thoughts are

Store string, number, id in a single VARCHAR column and make nullable column Reference table name in Key names - so if the column is not NULL, then value is an id.
I'm not feel very good of it because then I face a problem of no type check, and for example floating point number could be written with , or . as decimal separator.
Make 3 nullable columns instead of Value: String value, Number value, Id reference - if one has value, others should be NULL. Again I need Reference table name column to specify which table I reference.

I wonder if some best practices are there. Couldn't find them by myself.

Thank you in advance. We use PostgreSQL.

If this is not explicitly intended for some very flexible system, you better normalise this into (at least) 3NF tables with columns for each fact type (not data type). In a key/value architecture like this it is very hard to implement constraints (as you write yourself in thought 1)

If you are sure this key/value architecture is what you want (ask yourself twice) then:
Use a separate table for each datatype. A key/value table for string values, a key/value table for integers, a key/reference table for references. At least type checking is possible and storage is efficient. Still no other constraints.
Takes lots of SQL to get all the values for one key, but clean tables.


Assistance with MySQL query I have following tables

Tags

id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1

products

id | proudct_name
1|product1
2|product2

product_tags

id | product_id | tag_id
1|1||1
2|1|2
3|2|1

i need retrieve only those product which belongs to both tag1 and tag2

  select * from `products` INNER JOIN product_tags ON products.id=product_tags.product_id

INNER JOIN tags ON tags.id=product_tags.tag_id WHERE product_tags.tag_id=1 AND product_tags.tag_id=2

But my query return empty result

js fiddle

http://sqlfiddle.com/#!9/34f43a/1/0

If I've understood you correctly, you want something like this (see fiddle here):

SELECT
  p.id AS p_pid, p.product_name AS p_name,
  p.product_category_id AS p_cat_id,
  pt.product_id AS pt_pid, pt.tag_id AS pt_tag_id, -- other product_tag fields...
  t.tag_name AS t_name,
  t.slug AS t_slug_name                            -- other tag fields...
FROM products p
INNER JOIN product_tags pt
  ON p.id = pt.product_id
INNER JOIN tags t
  ON t.id = pt.tag_id
WHERE pt.tag_id = 1 OR pt.tag_id = 6
ORDER BY product_id, tag_id;

Result:

p_pid     p_name p_cat_id pt_pid pt_tag_id        t_name    t_slug_name
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         6            Mi              mi
    1   product1        4      1         6            Mi              mi
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         6            Mi              mi
    2   product2        4      2         6            Mi              mi

Notice in the fiddle how I've arranged the SQL. I've used aliases and meaningful names for my fields, prefixing them with the table initials. You mightn't want to do that for the final query, but it sure helps when constructing your query and  you have different, say, product_ids coming in from different tables through the JOINs. The aliases also help with legibility along with liberal use of spaces.

I think because of this lack of clarity, you missed this piece of code:

WHERE product_tags.tag_id=1 AND product_tags.tag_id=6

which, when turned into this, becomes much more legible:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

Now, it's glaringly obvious where the error is - you can't have a tag_id = 1 AND = 6 at the same time, hence your empty result set from your original query. So, just changing that line to:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

gives the result which is I believe what you were looking for. If not, let me know and I will endeavour to correct any problems/misunderstandings!

Oh, yes, a couple of final words. Please always include your version of MySQL - it's particularly important with this RDBMS as they've introduced many important features recently! Also, see the importance of providing good sample data - good questions get good answers here and yours has now become a good one with the addition of the thorough fiddle (+1)!