Failover multiple Basic Availability Groups We are planning to create multiple BAGs for SQL Server 2016 Standard Edition. My question is, should I need to same listener for all BAG?

Let's say we have 3-4 AG's i.e AG1, AG2, AG3 and so on.

If one of our AG1 failed, I need to failover all AGs to other node not just one.
How can i achieve the same using script or job?

This is a difficult scenario to truly handle. I would probably start with running a job on your secondary replica that checks the state of all the databases, and should it become primary for one of them then performs the ALTER AVAILABILITY GROUP [AG] FAILOVER; command for all of the others.

The biggest challenge with this is also running jobs on the primary, and taking care to not accidentally try to fail one of those databases back. You could manage this by checking the AlwaysOn extended event session and looking for failovers there, and do not do work if the database was failed away (as the most recent captured event), this way you don't end up bouncing the databases around all over the place.

You can use sys.fn_hadr_is_primary_replica to check and see if any particular database is the primary on your system.


How to close mongodb idle connection(active: false)? I have an application running on node and using the mongodb library to establish connection and closing it. But the problem is, from the mongo serverStatus() command i can see that there is still 2k+ current connections still available.

> db.serverStatus().connections
{ "current" : 2993, "available" : 835867, "totalCreated" : 220841 }

and most of it are having the active: false status.

My questions is, is there a way for me to force close these idle connections which after some time will affect my application with an timeout error.

I tried using the killOp() command but these only works if there is still opid value listed in these idle connections.

Appreciate some feedback on how can handle this kind of situations.

This can be avoided by setting up the maxIdleTimeMS parameter of Connection Pool Options

  This maxIdleTimeMS parameter sets the maximum number of milliseconds
  that a connection can remain idle in the pool before being removed and
  closed.

Set this parameter as per your requirement. So the idle connections will be closed once the time limit exceeds.


Joining on 3 different keys as they're set I have these tables:

TABLE vessels
ID int PK
organization_id int FK
fleet_id int FK
name VARCHAR

TABLE settings
ID int PK
organization_id int NULL FK
fleet_id int NULL FK
vessel_id int NULL FK
some_value INT

In table settings, some_value can be either 1, 2, 3, 4, 5, 6 or 7

Some entries in the settings table are linked to a vessel_id based on it's fleet_id or organization_id

How do I go about selecting the settings for a vessel, if a vessel has settings via it's fleet_id or organization_id ?

Try this:

SELECT settings.* FROM vessels INNER JOIN settings ON setting.organization_id=vessel.id
UNION
SELECT settings.* FROM vessels INNER JOIN settings ON setting.fleet_id=vessel.id
UNION
SELECT settings.* FROM vessels INNER JOIN settings ON setting.vessel_id=vessel.id


redis newbie - how to create hash within a hash? I want to create this type of a structure in redis: (its basically json data)

{
    "id": "0001",
    "name":"widget ABC",
    "model": "model123",
    "service":"standard",
    "admin_password": 82616416,
    "r1":
        {
            "extid":"50000",
            "password":"test123",
        },
    "r2":
        {
            "ext":"30000",
            "password":"test123",
        },
}

What I've tried so far:

I've tried to create a hash without the "sub" hashes, just to make sure I have the basics down pat.
So here's what I started with from the redis-cli:

HMSET widget:1 id 0001 name 'widget ABC' model 'model123' service standard admin_password 82616416
HMSET widget:2 id 0002 name 'widget ABC' model 'model123' service standard admin_password 12341234

That seems to work. I can see that I have 2 "records" of widget data in a widget collection.

But my attempt to create a record with the r1 data fails.

This is what I tried:

HMSET widget:3 id 0002 name 'widget ABC' model 'model123' service standard admin_password 12341234 r1{extid 50000}

This creates a hash key "r1{extid" with a hash value of "50000}"

Any suggestions would be appreciated.  I think my problem is my lingo.  I just don't know enough about the redis syntax to know what words to use in my google search.

Perhaps I just have to "flatten" the data like so:

HMSET widget:3 id 0002 name 'widget ABC' model 'model123' service standard admin_password 12341234 r1_extid 50000 r1_password test123 r2_extid 30000 r2_password test123

Thanks.

Redis doesn't support nested data structures, and specifically it doesn't support a Hash inside a Hash :) You basically have a choice between two options: either serialize the internal Hash and store it in a Hash field or use another Hash key and just keep a reference to it in a field of the outer Hash.


Display a List of used Table and Column Goal:
Display detailed information if database A use database B:s table and its column. if yes, the list will display what is the name of the database B:s table and column that is being used in database A.

Purpose:
Database A has about 200 View and 400 tables and database B has about 154 View and 165 tables and I don't wanna review each table and view in order to locate what table and column it use from database B. The main purpose is to reduce the time.

Problem:
I do not know how to do it?

Information:
*I'm using SQL server 2008 R2

The catalog view sys.sql_expression_dependencies gets you close. For example:

SELECT
  [A view] = s.name + '.' + v.name, 'references',
  [B object] = d.referenced_database_name + '.'
             + d.referenced_schema_name + '.'
             + d.referenced_entity_name
FROM DatabaseA.sys.views AS v
INNER JOIN DatabaseA.sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
INNER JOIN DatabaseA.sys.sql_expression_dependencies AS d
ON v.[object_id] = d.[referencing_id]
WHERE d.referenced_database_name = N'DatabaseB';

It does not provide column names, however.

Unfortunately other catalog views like sql_dependencies are unreliable for this kind of thing, since they don't follow dependencies outside the scope of the current database. And it's deprecated anyway. However there are more reliable views, like sys.dm_sql_referenced_entities:

SELECT
  [A view] = s.name + '.' + v.name, 'references',
  [B column] = r.referenced_database_name + '.'
             + r.referenced_schema_name + '.'
             + r.referenced_entity_name + '.'
             + r.referenced_minor_name
FROM DatabaseA.sys.views AS v
INNER JOIN DatabaseA.sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
CROSS APPLY DatabaseA.sys.dm_sql_referenced_entities
(
  QUOTENAME(s.name) + '.' + QUOTENAME(v.name), N'OBJECT'
) AS r
WHERE r.referenced_minor_id > 0
AND r.referenced_database_name = N'DatabaseB';

Some other links that might help:

Understanding SQL Dependencies
Troubleshooting SQL Dependencies

Also understand that dependencies are hard, they've changed the approach several times since SQL Server 2000, and we've had a long history of employing workarounds to ensure that the information is current and consistent (but still tend to fail on a regular basis).


Unable to start SQL Server Browser service in Sql server 2005 we are unable to start the SQL browser service on one of our machine with below error:

I believe this to be the reason, why we could not connect to the instance with below error:

I tried troubleshooting as mentioned here, by changing value in registry from 1 to 0, but still no luck.

SQLservices are also running.

Please help on how to resolve this!

@Mark- screenshot after running commands provided:

Check if another process has claimed port 1434:

netstat -ano | findstr 1434

This will give you the process id of the offending process, which you can then identify:

tasklist | findstr /c:"<process id>"


Strange Issue with SQL Server to Oracle Linked Server Select Query I have a SQL Server (2014 SP2) with a linked server to an Oracle 11 database.

I have a very simple select statement to an Oracle view which I know should return around 140k rows. But here's the thing, when I run it in SQL Server I immediately see records in the results window, but all of a sudden, the query hangs when it has so far returned only around 2000 rows and it just sits there forever doing nothing else. Sometimes, another few thousands rows appear before it again hangs.

The wait state on the query is on OLEDB which is to be expected. I'm thinking some sort of Network bottleneck...?

Sometimes I do get an error after a while: Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle"

Before anyone suggests it, 'allow in process' is ticked on the provider settings.

I'm looking for ideas to troubleshoot this at either the SQL Server side or the Oracle side to check that it's not a database config issue.

Just for anyone facing this mysterious issue in the future...

This particular issue was resolved by removing packet inspection rules on TCP Port 1521 (Oracle default) within the VMWare NSX. This was causing a bottleneck of data coming out of our Oracle infrastructure (IBM AIX) and back into our NSX infrastructure.


How to implement paging in an effective way? I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by retrieving only the first 50 results from the database and sending them over to the client. Then I will provide a possibility to jump to the second page to retrieve the next 50 results etc. (something similar what e.g. google offers)

The question is what is the effective way of implementing paging. I want to make sure that mssql uses cache as much as possible and that the same is not executed once again everytime I change the paging.

There are more clients who are querying the database in the same time. Used sql engine: MS SQL 2005

My ideas were:

Use prepared sql statemenst to ensure execution plan sharing
use ROW_COUNT variable to retrieve only the needed rows

But is it really the most effective way? Or do you think that it would be better to retrieve the whole result set and implement paging in the code which sends the data over to the client?

Thank you for your tips!

Regards,
Tomas

The query will be executed every time. SQL Server does not cache results.

Proper paging won't be implemented until SQL Server 2011, but until then your options (as you identified) are:

ROW_NUMBER() and queries on demand
client caching

A prepared SQL statement will be executed every time too.

If you have a fat client then caching is local to the client. This is mostly OK unless you have millions of blobs for example.

In our web client we render all the results but only show the top 100 and have a "Show all" button than expands a hidden DIV with rows 101+. We don't cache in the web server and we don't offer paging.


I cannot grasp the definition of serializable Everybody says that

  "Serializable" means that for any given set of concurrent transactions, there is a serial ordering of those transactions which produces the same result.

They also give an example

  suppose you have an attribute whose initial value is 10; and you have two transactions - one which doubles the value, the other which adds seven. Two different clients initiate the two transactions. Under isolated semantics, the only acceptable values for this attribute are 27 (the result of doubling first then adding 7) and 34 (the result of adding 7 first and then doubling). Any other value (such as 20 or 17) would be an error.

Which "same result" do we have in the example? Which reordering can we have to produce the same result?

Which "same result" do we have in the example? Which reordering can we have to produce the same result?

The opening quote is an incorrect statement of serializable isolation.

The definition of the serializable isolation level in the SQL-92 standard contains the following text (emphasis mine):

  A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

The example given is consistent with that definition.

The text above is taken from my article The Serializable Isolation Level


Why a job of dbms_job is BROKEN I have Oracle job that uses dbms_job package. I see that it becomes BROKEN:
Select job,what,BROKEN from dba_jobs;
-------- ------------ -----------
1   statspack.snap; Y

I would like to know the reason it's BROKEN
ASAIK for scheduled job we have dba_scheduler_job_log view where we can see the fail reason, but what about dbms_job jobs?

And that is one of the reasons why everyone should forget DBMS_JOB and use DBMS_SCHEDULER.
DBMS_JOB jobs do not have history or any logging enabled. If a DBMS_JOB job fails, that should be reported in the database alert log. If a DBMS_JOB fails 16 times in a row, it will be automatically marked as broken.
Your job either failed 16 times in a row and was marked broken, or someone manually set it as broken.


Availability Group Clustering - Cluster Group Preferred Owner We have a three node AG. Two nodes in a local data center, one in a different subnet DR site.  Am I correct that the Cluster Name should have two IPs, just like the listener?

Also, is there a way to set the preferred owners of the Cluster Name and Witness so it won't try to fail over to the DR site if the primary nodes are up?

Additional Info: We had an outage from patching a secondary node, we couldn't figure out why a secondary rebooting caused the AG & Cluster to go down.  We opened a support ticket with Microsoft and they recommended we change the possible owners of the Cluster Group to exclude DR.  So what appears to have happened is the Cluster Group tried to fail over to the DR site, it couldn't come online because there was no subnet IP. That took just long enough that quorum was lost because the witness didn't come online, and the secondary was rebooting.

Yes, you need to add a DR site IP address for the cluster name.  It may not be critical if you aren't using the cluster name for anything, but it is certainly a best practice and considered the correct configuration.

You don't really need to be concerned about the core cluster resources (cluster name, fileshare witness, etc.) failing over to the DR site.  The cluster will run just fine if that occurs, and if the network to the DR site is interrupted, the two nodes in the local data center will arbitrate for ownership of the core resources and one will bring them online.  This will not cause the availability groups to go offline and come back online--that will only occur if the AG primary can't retain ownership of the AG resources.

Having said that, you can set the possible owners of the cluster name resource on the Advanced tab, but in the case where you need to fail over to the DR node, you would need to change it again in order for the resources to come online on the DR node.


How to get rid of these Key Lookups? I have this query:

    SELECT TOP (20) table1.Vit_Codigo
    ,table1.Vit_Nome
    ,table1.Vit_Apelido
    ,table1.Vit_codigoStatus
    ,table1.Vit_Numero
    ,table1.Vit_Ano
    ,table1.Vit_CodigoTiposExames
    ,table3.Esp_Setor
    ,table2.TEs_Descricao
FROM table1
LEFT JOIN table2 ON tb_Vitima.Vit_CodigoTiposExames = table2.TEs_Codigo
LEFT JOIN table3 ON table3 .Esp_Codigo = table1.Vit_CodigoEspecialidade
WHERE (rtrim(ltrim(lower(tb_Vitima.Vit_Rg))) = 'n/a')
    AND (
        table1.Vit_codigoStatus IN (
            3
            ,4
            )
        )
ORDER BY table1.Vit_DataAberturaReal DESC

And these plans:

I made this index:

create index IX_SELECTTOP20_INCLUDE
    on table1
    ( Vit_Rg ,Vit_codigoStatus )
include (
Vit_Codigo , Vit_Nome , Vit_Apelido , Vit_Numero ,
               Vit_Ano , Vit_CodigoTiposExames )

...but it seems the code is using the old index.

So, Why is my index still using key lookup?

Neither index appears to be "covering". To avoid a key lookup, you must at least INCLUDE every column involved in your SELECT, WHERE, and ORDER BY.

The reason the older index is being used is that it's better for the query. That's partially because the index on Vit_DataAberturaReal prevents a sort operator in the plan - because the data is already sorted properly. It's also because the first column in the new index is useless in your query non-sargable. If you can remove the RTRIM, LTRIM, and LOWER, then the performance may be better.

If the index is only to support this query, you may consider adding WHERE Vit_codigoStatus IN (3,4) to your index definition.

Ideally, the WHERE columns would be part of the index itself - not in the INCLUDE. If I were to make an index specifically to support this query, it would probably be along the lines of:

CREATE INDEX [IX_SELECTTOP20_INCLUDE]
    ([Vit_DataAberturaReal])
INCLUDE(
    [Vit_Codigo], [Vit_Nome], [Vit_Apelido], [Vit_codigoStatus],
    [Vit_Numero], [Vit_Ano], [Vit_CodigoTiposExames],
    [Vit_CodigoEspecialidade], [Vit_Rg])
WHERE [Vit_Rg] = 'n\a' AND [Vit_codigoStatus] IN ( 3, 4 )

Note: this will not support the LTRIM(RTRIM(LOWER(...))).


Postgres sequence usage I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly.

They both have the same objects unshared for tables, however, sequences are shared, and dev is the owner. I just use normal create statements.

I want to grant all to junit for the sequence but this fails with:

dev=> grant all on sequence serial to junit;
WARNING:  no privileges were granted for "serial"
GRANT

After this junit has no select rights on the sequence, how can I fix this?

I also would like to know whether there are recipes for this kind of setups, where I can use the same DDL for the two different users without modifying the script? I've been looking into schemas but I haven't found the solution so far.

The warning

WARNING:  no privileges were granted for "serial"

indicates that the granting role doesn't have the necessary privilege to grant privileges on the object in question.

Either execute the GRANT command as the role owning the sequence.
Or do the same as superuser (default superuser is postgres).
Or GRANT the necessary privilege to the role dev so that this role can in turn grant it to others:

GRANT ALL ON SEQUENCE serial TO dev WITH GRANT OPTION;

The name serial for a sequence isn't particularly wise, but since serial is not a name of a base type and not a reserved word, it wouldn't cause any immediate trouble.

Aside:
Unfortunately the manual uses the name serial in the CREATE SEQUENCE and ALTER SEQUENCE examples. I feel like a suggestion to pgsql-docs@postgresql.org might be in order ...


When was the last time my table was vacuumed? I am running Postgres 9.6.
I have recently been reading of the importance of VACUUMing in postgres. I am looking for a SQL statement that could return a list of my tables and when was the last time they were vacuumed. Does a statement like this exist?

Yes, it does:

SELECT last_vacuum,
       last_autovacuum
FROM pg_stat_all_tables
WHERE schemaname = 'myschema'
  AND relname = 'mytable';


Extension created for public schema not available within database So, I create the citext extension for schema public and I can see it's there, but then it's not available within the database in that schema. The database is in public as well, because it's created from the postgres user and no schema is specified at the creation. Here's a screenshot with some more data:

So, you can see that the extension is there, but once I connect to the database, it's not available anymore and I basically have to install it within the database. Any ideas how to make it available initially for the database as well?

PgSQL v9.6

Short answer

You are actually using (at least) two databases. You are installing CITEXT in the wrong one.

Longer answer

In the first database (whose name is not shown), you install CITEXT. This database is probably the postgres database, which is created by default when you install PostgreSQL.

Then you use the \c command and you switch to another database (sensordata). You need to create your extension in this database.

That is, you should do:

\c sensordata
CREATE EXTENSION citext;
\dx

If you need to use the citext module in more databases, you need to install it in each one. The extensions on the other databases don't matter. Databases do not interfere with each other.

Every PostgreSQL cluster (=database installation) has (by default) one database named postgres. If you use psql, it is the one to which you connect "by default". You don't actually need to have a postgres database. I guess the postgresdatabase is where you installed the CITEXT extension.

Within every PostgreSQL database, there is, by default, one public schema.

PostgreSQL does not work like (for instance) MySQL, where you can acces several databases at once through a single connection (provided you have a user with the right permissions).

In PostgreSQL you access one database through a single connection. Within a database, you can access as many schemas as needed. The role that schemas play in PostgreSQL is nearly the same as the one played by a database in MySQL.

See also:

Relationship between catalog, schema, user, and database instance
What's the difference between a catalog and a schema in a relational database?


SQL Server Transactional Replication from 2005 to 2017 As per the msdn, Subscriber version depends on the type of publication: A Subscriber to a transactional publication can be any version within two versions (n-2) of the Publisher version. For example: a SQL Server 2012 Publisher can have SQL Server 2014 and SQL Server 2016 Subscribers; and a SQL Server 2016 Publisher can have SQL Server 2014 and SQL Server 2012 Subscribers.

I am having a requirement where I need to transactionally replicate a SQL Server 2005 to 2017, and as per the above msdn note we cannot do it, right?

But when I created a test environment and configured replication from 2005 to 2017 it is working fine. Any explanation to that? I am not able to understand why is it happening.

EDIT: I Should clarify this is not a long-term solution. You need to upgrade to at least SQL 2014 or higher which support running your DB in 2008 mode.

You should attempt to test a later edition. Perhaps 2008 compatibility-mode will work just fine. Until then, the following would apply:

Traditional answer: That is because it is not supportable, I e. They will not help you if you run into stability issues and errors with the version of BCP used in replication, when you attempt to replicate objects that are either depreciated or no did not yet exist (DATE being on example).

You might lose data and they do not want to support something that was made in the early 2000s. That sounds scary! But obviously one cannot force everyone into the latest version in Production.

A traditional approach would be to use existing replication from one distribution DB until you get to the supported version.

  Replicate from 2005 to 2012 and from that 2012 version to 2016 and
  from 2016 to your present version.

  The key here is what version your Distribution database is, not so much the instance level. So it is not entirely necessary to have lots
  of instances here.

But this is a pain, and clearly not helpful in your situation. You need a solution that can help you buy time for your dev team to test a supported environment.

Instead, use SQL Server Management Objects (SMO)

it can do a lot. Move a DB, Objects, include dependencies, transfer user, permissions, SIDs, plus more. Super easy to setup and very secure.
SSDT allows a GUI version where you can pick and choose your objects (have to be predefined, though) and could migrate from your 2005 instance to your 2017. Technically it is. Or using replication and should be covered (test of course).
Related Links: INSTALLING SMO - Microsoft
TutorialGateWay has a decent guide to start. Transfer SQL Objects Task in SSIS

Again, unsupported means you will be the only one receiving blame and help will not be offered. Don’t stay in 2005, but perhaps this will make the transition smooth.

Microsoft on Supportable Replication


Transaction log still full after backup A database in an availibility group on MS SQL 2014 with a decent log file which at some occasions has been shrinked because of some major jobs filling it up. Usually don't shrink logfiles but as there's a lot of other databases that don't grow at the same time it has been done instead of extending disk, it's a VMWare environment. Now the logfile has grown a lot again, filling up the disk. The log disk was extended, transaction logs backed up and trying to shrink the file about 10-20 GB but there's still only 200 MB free space in the logfile.

Explanation to this problem was that replication halted on one database in Availability group. Transaction log backup was successful but log file could not be emptied because  replication to secondary server was paused. I did not notice this looking at the databases initially. And didn't notice that log_reuse_wait_desc was showing AVAILABILITY_REPLICA on the primary server (looking at the wrong line in result). But when checking again after Shankys reply I discovered that and Availability group Dashboard showed that replication had halted. Resumed replication, backed up log again and then I was able to shrink logfile to a more accurate size.


Database Tuning Advisor CLI "dta" missing My Windows workstation can launch the GUI for the Database Engine Tuning Advisor, but the CLI called "dta" is missing.  Why?

Background

The Database Tuning Advisor is a SQL Server tool for suggesting changes to indexing and other things.  It can be run two ways: A GUI, and a CLI.  The GUI can be launched by searching for "Database Engine Tuning Advisor," or from SSMS.  The CLI can be accessed with the "dta" command.

The GUI runs just fine, but not the CLI.  According to the dta Utility documentation, the CLI is called "dta"

dta
[ -? ] |
[
      [ -S server_name[ \instance ] ]
      { { -U login_id [-P password ] } | -E  }
...

But when I try to run it, from either a user or Administrator command window, it's not found:

C:\WINDOWS\system32>dta
'dta' is not recognized as an internal or external command,
operable program or batch file.

What files named "dta" are present?

I used cygwin to search for all files which name starts with "dta".  Some are found, but no "dta.exe":

$ find -name 'dta*' -ls
562949953516183    0 drwxrwx---   1 SYSTEM   SYSTEM          0 Jun 12 09:03 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server/140/Tools/Binn/schemas/sqlserver/2004/07/dta
1125899906937496   52 -rwxrwx---   1 SYSTEM   SYSTEM      52868 Aug 22  2017 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server/140/Tools/Binn/schemas/sqlserver/2004/07/dta/dtaschema.xsd
1688849860388529   20 -rwxrwx---   1 SYSTEM   SYSTEM      16813 Jun  5 06:14 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dta.exe.config
1688849860388532  220 -rwxrwx---   1 SYSTEM   SYSTEM     225080 Jun  5 06:04 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dtaengine.exe
1688849860388534   20 -rwxrwx---   1 SYSTEM   SYSTEM      17902 Jun  5 06:14 ./Program\ Files\ (x86)/Microsoft\ SQL\ Server\ Management\ Studio\ 18/Common7/dtashell.exe.config
199565758487855892    1 -rwxrwx---   1 wayne    None          592 Jun 11 09:22 ./Users/wayne/AppData/Roaming/Microsoft/Windows/Recent/dta_tutorial.lnk
10414574138517174    4 drwxrwx---   1 wayne    None            0 Jun 11 09:47 ./Users/wayne/Documents/dta_tutorial

What's installed

Windows 10.0.18363 Build 18363
SQL Server 1027 Standard Edition (14.0.2027.2)

SQL Server Replication
Full Text and Semantic Extractions for Search

SQL Server Management Studio 18.5.1
Database Engine Tuning Advisor v18.5.1

History

The workstation had SQL Server 2014 installed
It had a prior version of SSMS installed
I upgraded to SQL Server 2017
I upgraded SSMS to version 18.1
dta was available, but it's -g (GUI) switch wasn't working.  It complained about an expired or missing license.
A license error is what I've seen when SSMS is updated but the prior version of SSMS is invoked, very similar to how the "dta" tool was behaving
Under the hypothesis that the upgrade had not gone well and my dta was associated with a prior version of SQL server, I decided to start from a fresh install
I uninstalled anything associated with SQL Server 2014, SQL Server
2017, and SSMS.
I did a fresh install of SQL Server 2017
I installed all updates for SQL Server 2017
I installed SSMS 18.1
I found that the dta command was no longer present.

DTA used to be a part of SQL Server, but it moved to SSME.  During the upgrade from SQL Server 2014 to SQL server 2017, two things happened:

SQL Server 2014 DTA.EXE was left behind
SSMS 18 did not add the directory containing DTA.EXE to the path

Therefore, dta.exe was brining up the SQL Server 2014 DTA, which no longer had license information and was failing.
The fix is to add the SSMS directory containing DTA.EXE to the path, before any other SQL Server directories.  See https://stackoverflow.com/q/44272416/238886 for how to edit the path in Windows 10.  Here's the directory to add.  Make sure it's above the other SQL Server directories, as shown here:


Is there a "best-practices" type process for developers to follow for database changes? What is a good way to migrate DB changes from Development to QA to Production environments? Currently we:

Script the change in a SQL file and attach that to a TFS work item.
The work is peer-reviewed
When the work is ready for testing then the SQL is run on QA.
The work is QA tested
When the work is ready for production then the SQL is run on the production databases.

The problem with this is that it is very manual. It relies on the developer remembering to attach the sql or the peer-reviewer catching it if the developer forgets. Sometimes, it ends up being the tester or QA deployer who discovers the problem.

A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.

Our setup: Our development shop is full of developers with a lot of DB experience. Our projects are very DB oriented.  We are mainly a .NET and MS SQL shop. Currently we are using MS TFS Work Items to track our work. This is handy for code changes because it links the changesets to the work items so I can find out exactly what changes I need to include when migrating to QA and Production environments. We are not currently using a DB project but may switch to that in the future (maybe that is part of the answer).

I am very used to my source control system taking care of things like this for me and would like to have the same thing for my SQL.

In a VS environment, I've always used database projects to implement the update scripts. I tend to use unimaginative names like "DatabaseUpdate17.sql" or "PriceUpdateFebruary2010.sql" for my scripts. Having them as database projects lets me tie them to Team Server tasks, bugs (and if we did code reviews, to them as well). I also include in each database (that I have authority over) a table specifically for the collection of changes to the schema.

CREATE TABLE [dbo].[AuditDDL](
    [EventID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [EventData] [xml] NULL,                    -- what did they do
    [EventUser] varchar(100) NOT NULL,         -- who did it
    [EventTime] [datetime] DEFAULT (getdate()) -- when did they do it
    )
GO

Well, that takes care of 3 of the 6 Ws.

CREATE TRIGGER [trgAuditDDL]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO AuditDDL(EventData, EventUser)
SELECT EVENTDATA(), original_login()
GO

I include an insert statement to log the beginning of a patch as well as the end of a patch. Events happening outside of patches are things to look into.

For example, a "begin patch" insert for "patch 17" would look like:

INSERT INTO [dbo].[AuditDDL]
           ([EventData]
           ,[EventUser])
     VALUES
           ('<EVENT_INSTANCE><EventType>BEGIN PATCH 17</EventType></EVENT_INSTANCE>'
           ,ORIGINAL_LOGIN())
GO

Since it also catches when indices are rebuilt, you'll need to run the following every month or so to clear out those events:

DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"ALTER_INDEX")]') =1
GO

DELETE FROM AuditDDL
WHERE [EventData].exist('/EVENT_INSTANCE/EventType/text()[fn:contains(.,"UPDATE_STATISTICS")]') =1
GO

Earlier version previously posted on Server Fault.

In a SOX and PCI-DSS compliant environment, you will never have access to the production servers. Therefore the scripts need to be clear and exercised beforehand. The comments at the top of the update scripts include lists of new tables, stored procs, functions, etc as well as lists of modified tables, stored procs, functions, etc. If data gets modified, explain what is being modified and why.

  A secondary problem is that you sometimes end up needing to manually coordinate changes if two separate tasks change the same database object. This may just be the way it is but it still seems like there should be some automated way of "flagging" these issues or something.

I've never come across a tool that lets us track this automatically. Previous employers used a principle of "database owner" - one and only one person who is personally in charge of the database. This person won't be the only developer working against that database, but rather all changes have to go through them. This has worked reasonably well to keep changes from colliding and damaging each other.


which mysql version to use - 5.1 or 5.5? I am designing a new database. There are going to be around 1000 write queries per second. There may be as much as 10000 read per second.

Which MySQL version is recommended?

I came across a post on this site which says that 5.5 might be slower than 5.1 in some cases but some tuning will make it good enough. If tuning is done, then is it advisable to use 5.5? Or 5.1 performs better?

Edit

Here's the link to the post I referred to above - https://stackoverflow.com/questions/9202825/mysql-5-5-perfomance

There is no substitute of tuning MySQL. Left unconfigured, there are cases when even MySQL 4.1 outperforms MySQL 5.x on the same level playing field.

Here are my past posts on the subject

Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Feb 08, 2012 : MySQL 5.5 perfomance (StackOverflow)
Feb 22, 2012 : MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast) (ServerFault)
Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

Bottom Line

You should always use the most stable release of MySQL because there are just certain bug and features that are fixed that do not carry over to the next major release.

Note that

MySQL 5.0 is up to 5.0.96, but the bugs that MySQL 5.5 Addressed still reside in 5.0.96
MySQL 5.1 is up to 5.1.67, but the bugs that MySQL 5.5 Addressed still reside in 5.1.67

There are many exciting things that MySQL 5.5 features tuning for the following:

Multicore Processing

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance

Semisynchronous Replication

Aug 05, 2011 : Is MySQL Replication Affected by a High-Latency Interconnect?
Oct 29, 2012 : Installing and enabling both master and slave semisynchronous plugins on same server

Buffer Pool Instances : Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?


Can't get any fragmentation of index for testing in SQL Server database I am filling my table in my SQL Server database with some data and I have partitions. I have one clustered index in the table. The problem I am facing is that I am not getting any fragmentation which I need in order to test. After reading some articles, I came to conclusion that I need to use file groups, but I am not sure how to achieve that.

If you're trying to artificially induce fragmentation, insert the data "backwards". That is, if your clustering key is an integer column ascending, load the data in descending order by that same integer column. You should get page splits aplenty.


Postgres `log_disconnections = on` does not log disconnects I have surely a basic problem and am undoubtedly overlooking a simple issue..
I have configured Postgres 12.4 with log_disconnections, but the log file does not show any disconnects. (It is logging connections, as expected with log_connections = on.)
I have connected and disconnected with both psql (exiting psql with \quit) and the Ruby pg library.  In both cases, no disconnect message was logged.
During testing, I used SELECT * FROM pg_stat_activity WHERE datname = 'foo' to verify a connection was present (idle) while I was connected, and gone when I quit psql or calling pg/Ruby conn.finish.
Why isn't the server logging disconnections?
The server configuration is basic:
max_connections = 100           # (change requires restart)

shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option

max_wal_size = 1GB
min_wal_size = 80MB

log_connections = on
log_disconnections = on
log_timezone = 'America/Toronto'

datestyle = 'iso, mdy'
timezone = 'America/Toronto'

lc_messages = 'C'           # locale for system error message
lc_monetary = 'C'           # locale for monetary formatting
lc_numeric = 'C'            # locale for number formatting
lc_time = 'C'               # locale for time formatting

default_text_search_config = 'pg_catalog.english'

The server log file:
2021-01-18 09:36:18.774 EST [45512] LOG:  starting PostgreSQL 12.4 on x86_64-apple-darwin18.7.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.17), 64-bit
2021-01-18 09:36:18.777 EST [45512] LOG:  listening on IPv6 address "::1", port 5432
2021-01-18 09:36:18.777 EST [45512] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-01-18 09:36:18.778 EST [45512] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-01-18 09:36:18.825 EST [45513] LOG:  database system was shut down at 2021-01-18 09:35:38 EST
2021-01-18 09:36:18.828 EST [45512] LOG:  database system is ready to accept connections
2021-01-18 09:44:36.433 EST [45868] LOG:  connection received: host=[local]
2021-01-18 09:44:36.434 EST [45868] LOG:  connection authorized: user=testuser database=foo application_name=pry
2021-01-18 09:45:37.299 EST [45962] LOG:  connection received: host=[local]
2021-01-18 09:45:37.300 EST [45962] LOG:  connection authorized: user=testuser database=foo application_name=psql

Postgres started with pg_ctl -l path/to/log -D path/to/pgdir start.

To debug non-working configuration settings, you can verify at runtime what the value is, and where it came from by querying the view pg_settings
select setting, source, sourcefile, sourceline
from pg_settings
where name = 'log_disconnections'

The columns sourcefile and sourceline will tell you where the currently active value was taken from.
If you find a value from postgresql.auto.conf the value was applied through ALTER SYSTEM and the correct way to remove it, is to use ALTER SYSTEM again.


Low cardinality fields: integer or string A Ticket has the statuses:

new
in_progress
on_hold
closed

I could make ticket.status a string (on_hold) or an unique int(2). It is indexed.

Int pros: smallest size in index

Int con: low clarity for BI and evolving schema (3 compared to closed)

String pro: clear data navigation

String con: takes more space to index, gives less performance for same RAM

I imagine a string index wont take too much space over an int index if its low cardinality. Is it premature optimization to choose integer if the field has low cardinality and is not part of a compound index?

I use Postgres with SQLAlchemy, Python ORM.

I would definitely normalize for big tables. You could use an integer or int2 column with a FK constraint to a lookup table.

For small tables either of your ideas is ok. If in doubt, stick to the guidelines of the project.

I would use neither. Instead:

For small tables: use an enum. The documentation:

  An enum value occupies four bytes on disk.

For big tables: use a "char" field (1 byte) as FK to a lookup table. A good choice for very small sets of lookup values. And each value can be a mnemonic for the referenced state:

status_id | status
----------+------------
n         | new
i         | in_progress
o         | on_hold
c         | closed

It also depends on the complete picture. You only actually save space (and gain performance) in table and indexes if it's not lost to alignment padding anyway. More:

Configuring PostgreSQL for read performance

For character types (except for "char"), collation rules may be relevant, too. More:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


Trigger: Only raise error if related record was NOT created between now and last Sunday I have a trigger that prevents inserts into a DELETES table under certain conditions:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE
    v_upd_row NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_upd_row
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
    AND sde_state_id = :new.deleted_at;

    IF v_upd_row = 0 THEN
         raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END ;

I want to enhance the trigger so that it only raises an error if the related record in the base table was NOT created between now and last Sunday at 11:59 PM.

The relationship would be based on the OBJECTID column.

This is what the base table looks like:
CREATE TABLE A_TEST_BASE_TABLE
(
  OBJECTID          INTEGER NOT NULL,
  SHAPE             SDE.ST_GEOMETRY,
  CREATED_USER      NVARCHAR2(255),
  CREATED_DATE      TIMESTAMP(6),   --<<-- the relevant field
  LAST_EDITED_USER  NVARCHAR2(255),
  LAST_EDITED_DATE  TIMESTAMP(6)
)

What would it take to do this?

If I understood you correctly, then see if this helps:

select created_date from that table
check if it is lower than previous Sunday's 23:59

why? Because I presume that it can't be created in the future

How to find that moment in time? One option is to use NEXT_DAY function which returns the "next" Sunday, but - if you subtract 7 (days) from it, you ge previous Sunday which is what you want. Then add 23:59 to the truncated date value and that's it.
Words into code:
SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi, day') right_now,
  2    trunc(next_day(sysdate, 'sunday') - 7) + 23/24 + 59/(24*60) previous_sunday_2359
  3  from dual;

RIGHT_NOW                                              PREVIOUS_SUNDAY_235
------------------------------------------------------ -------------------
29.08.2020 22:39, saturday                             23.08.2020 23:59:00

SQL>

Put into the trigger:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE
    v_upd_row  NUMBER;
    v_cre_date date;
BEGIN
    SELECT COUNT(1) INTO v_upd_row
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
      AND sde_state_id = :new.deleted_at;

    SELECT MIN(a.created_date) INTO v_cre_date
    FROM a_test_base_table a
    WHERE a.objectid = :new.sde_deletes_row_id;

    IF v_upd_row = 0 AND
       v_cre_date < trunc(next_day(sysdate, 'sunday') - 7) + 23/24 + 59/(24*60)
    THEN
       raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END;


I/O is frozen on database databasename On SQL Server 2005 log report, everyday I receive the following message:

I/O is frozen on database databasename.

Is it serious ?
How to resolve it ?

VSS is being used to backup your database.

Edit: [Thanks Colin]

This causes the logged 'I/O is frozen' message.
It is not serious.
No need to resolve it.

Be warned, your database restore chain may be impacted, in which case it is serious.
If you backup strategy is weekly full, daily diff, hourly tran per day - 8am -5pm; a daily snapshot VSS backup will cause your diff's to fail.


How to check if a query (CRUD) was made on a database I'm sorry for the title but I have a problem which I can't sum up in a sentence.
I have a database with 100 tables and probably a thousand columns. Now, every time something changes in the database, for example:

New table is created
New column is created (Insert)
Column is updated (Update)
Column is deleted (Deleted)
And a column is read (Read)

I would like to know.
From what I have read there are 3 options but I have an issue with all three:

SQL Profiler (soon to be obsolete)
Extended Events (too complex for me)
Open MSSQL query field and go crazy

For now, I am trying to go with the third. I am hoping to use sys.object or sys.dm_db_index_usage_stats and any other table to get what I want.
So, my question is: Do you think there is a better alternative or not?

I would have thought that the Audit functionality would meet your requirements.
If you search for SQL Server Audit you'll get lots of hits - it is very configurable but too big to describe fully here.
Have you looked at that ?


MS Access: adding column if not exist (or try/catch it) this is an easy question, but I can't find out the solution...

I have to check if a column exists and add it if the test is negative. How can I do with only sql code?

This is the syntax for SQL Server:

IF NOT EXISTS (
  SELECT *
  FROM   sys.columns
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]')
         AND name = 'ColumnName'
)
BEGIN
    --STUFF HERE
END

and for MS Access, using only SQL code... what is the right syntax to do it?

UPDATE: also a way to do a try cath statement would be ok, I only need to not add anything if it doesn't exist... so, also a try catch is ok, I think. Also try catch is easily possible to use in sql server... and for access?

UPDATE 2: I have done this:

If Not Exists (Select Column_Name
           From INFORMATION_SCHEMA.COLUMNS
           Where Table_Name = 'TabTessereVeicoli'
           And Column_Name = 'TAGA')
begin

    ALTER TABLE TabTessereVeicoli ADD TAGA text(25) NULL;

end

but I get the error "SQL statement not valid. Expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE." Why? How can I do an alter table after an IF?

Access doesn't support the IF EXISTS syntax in queries; I think your error is coming from that. It's also very difficult to do any sort of procedural logic using Access SQL. Your alternatives are to run the ALTER TABLE statement and handle the error elsewhere or use VBA to conditionally run the statement.


How to extract Procedure, Function, etc. Source Code from an Oracle Export I have generated an EXPDAT.DMP file of an Oracle database with the command:

exp userid=usr/pass@db owner=own rows=n compress=n

I then ran the following command to generate a file containing the 'Create Table...' statements:

imp userid=usr/pass@db full=y indexfile=output.sql

This is great for the Create Table statements, but I also want the Create Procedure / Create Function / Create View source code. I can see them there in the .DMP file, but I haven't found a way to extract them.

Is it possible to extract this information from the EXPDAT.DMP file? Or will I need to use another method?

The purpose is to have the source exportable to files that can be source-controlled.

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL.

You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention.

It will be a lot cleaner than trying to split a single file.


Minimum permissions required for mail attachment in DbMail I know that SQL DbMail requires for attaching, a windows login or a sysadmin privilege is required.

This is somewhat similar to  minimum dbMail permissions which was asked by me at an earlier point.

I want to understand whether without sysadmin what is the minimum privilege required for attaching files.

As seen in the TechNet article on sp_send_dbmail:

  Execute permissions for sp_send_dbmail default to all members of the
  DatabaseMailUser database role in the msdb database. However, when the
  user sending the message does not have permission to use the profile
  for the request, sp_send_dbmail returns an error and does not send the
  message.

As for attachments, the SQL Server service account (the actual Windows/AD account that the SQL Server process runs on) is also going to need read access to the actual folder where the file is stored.


How to script a table? When I right-click a table in Object Explorer and click "Script Table" I can get SSMS to script my table.

Can I do that with T-SQL code?

I'm after the "CREATE TABLE xx" code that SSMS generates, so that I can take the script and use on other projects. I need all the bits and pieces, including PRIMARY KEY, CONSTRAINTs, "WITH (PAD_INDEX = " and schema declaration.

I'm on SQL Server 2014

From 2012 onwards this is possible and can be done with help OF sys.dm_exec_describe_first_result_set DMV.

  This dynamic management function takes a Transact-SQL statement as a
  parameter and describes the metadata of the first result set for the
  statement

You can refer here for a read on this sys.dm_exec_describe_first_result_set

Also, refer to this post explaining the same. Is there a way to generate table create script in TSQL?


MySQL and PostgreSQL Licence issue Our Company wants to use MySQL for one software product. This product is not open-source. Is it legal to use it freely ?

If not, how about PostgreSQL ? Is this okay for our purpose ?

Postgres (not Postgre) is absolutely free. You can do anything you want with it.

For MySQL you will most probably need a commercial license if you plan to redistribute your application with a bundled database. Not sure if this is required if your application just needs MySQL but I think this also requires a commercial licenses because it is considered a derivative work

But I'm not a lawyer. So if you want to be 100% sure, talk to Oracle and a lawyer.


RMAN log file with date and name in Oracle 11gR2 in Linux I have a script and I want to create my log file with datetime and with unique name. It's my script:

.......
.......
rman target/  LOG='/u01/backup/logs/rman.log' <<EOF
.......
.......
.......
EOF

How can I do it? Thank you.

If I were doing this in the bash shell on Linux, I'd use the date command:

$ date '+%y%m%d-%H%M%S'
20191115-111213

$ LOG_PATH="/u01/backup/logs"
$ UNIQUE="NAME"

$ rman target / LOG="${LOG_PATH}/rman.${UNIQUE}.$(date '+%y%m%d-%H%M%S').log" <<EOF
. . .
EOF

$ ls ${LOG_PATH}
rman.NAME.20191115-111314.log

Remember that you'll need to clean up all these log files, from time to time.


ERROR: Failed to convert parameter value from a String to a DateTime I have faced this error: Failed to convert parameter value from a String to a DateTime when trying to pass multiple selected dates from checkboxlist to my parameter to be used in sql.

I have tried it with other datatypes such as nvarchar and it works when I pass multiple selected values to 1 stored procedure parameter and return the select statement using dynamic sql to populate my gridview.

Ps. In my webserver i'm displaying in checkboxlist as e.g 31-Aug-2013, using DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";. In my sql database, it is displayed as e.g 2013-08-31.

During debugging selectedDATE do read it as e.g 2013-08-31. however there is error at da.Fill(ds); line

ASPX.CS

    protected void Page_Load(object sender, EventArgs e)
    {
        DATE.DataTextFormatString = "{0:dd-MMM-yyyy}";

        using (SqlConnection conn = new SqlConnection(dbConn))
        {
            try //Call stored procedure
            {

                SqlCommand cmd = new SqlCommand(spddl, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (!IsPostBack)
                {
                    DATE.DataSource = ds.Tables[0];
                    DATE.DataTextField = ds.Tables[0].Columns["DATE"].ToString();
                    DATE.DataBind();

                }
                if (IsPostBack)
                {
                    Bind();
                }

            }

            catch (Exception i)
            {
                bool exception = true;
                if (exception == true)
                {
                    //txtMessage.Text += e.Message;
                }
            }
        }
    }

public void Bind()
   {
           using (SqlConnection conn = new SqlConnection(dbConn))
       {
           using (SqlCommand cmd = new SqlCommand(spretrieve, conn))
           {
                 String selectedDATE = String.Empty;

                if (DATE.SelectedValue == "All")
                {
                    selectedDATE = "DATE";
                }
                else
                {
                    foreach (ListItem item in DATE.Items)
                    {
                        if (item.Selected)
                        {
                            DateTime dtTemp = Convert.ToDateTime(item.Value);
                            selectedDATE += "'" + dtTemp.ToString("yyyy-MM-dd") + "',";
                        }
                    }

                    selectedDATE = selectedDATE.Substring(0, selectedDATE.Length - 1);
                }

               cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@param", SqlDbType.DateTime).Value = selectedDATE;

               conn.Open();
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataSet ds= new DataSet();
               da.Fill(ds);
               GRIDVIEW.DataSource = ds.Tables[0];
               GRIDVIEW.DataBind();

           }
       }

SQL

ALTER PROCEDURE [dbo].[SP]

@param nvarchar(512)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM TABLENAME WHERE [COLUMN] IN (' + @param + ')'
EXEC sp_executesql @sql;
END

You need to convert date into nvarchar first through your query:

Convert(varchar,[date_attribute],106)


Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
Pro: No null fields.
Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
Pro: Only have to use one table for CRUD operations.
Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and dependent on the intended purpose of the database?

EDIT: A specific question I have regards the overlapping nature of the table "NetworkDevice". This table is meant to hold network information for any device with a hostname and/or IP address, whether it is a computer, switch, or router. Is the overlapping nature of this table something that could cause problems, or is it okay to implement it this way?

Thank you in advance for any input provided. Please ask if any additional information is needed.

Physical implementation of subtyping in a database is a complex issue.  Unless you have a situation where it offers compelling advantages (see below for one or two examples) it adds complexity into implementation while providing relatively little value.

Having done this with really complex subtyping (applicaitons and sentences on a court case management system, disparate combined-risk commercial insurance contract structures) I guess I have some observations on this.  Some significant corner cases are:

If the total number of database fields across the subtypes is relatively low (say: less than 100) or there is significant commonality between subtypes then splitting the subtypes out into separate physical tables is probably of little value.  It will add significant overhead to reporting queries and searches.  In most cases it's best to have a single table and manage your subtyping within the application.  (Probably the closest to your problem)
If your subtyping is very disjoint, and different subtypes have type-dependent data structures hanging off them (i.e. child tables or more complex structures), then subtype tables make sense.  In this case, each subtype probably has relatively little commonality within the application (i.e. there is probably a whole subsystem within the application dedicated to that subtype).  Most reporting and querying will probably occur within a given sub-type, with cross-type queries mainly being restricted to a handful of common fields.  (Court case management system)
If you have a large number of subtypes with disparate attributes and/or a requirement to make this configurable then a generic structure and supplementary metadata may be more appropriate.  See this SO posting for a rundown on some possible approaches.  (Insurance policy administration system)
If you have a very large number of fields with little commonality across your sub-types and little requirement to query across sub-type tables (i.e. nothing much in the way of multi-way outer joins against your sub-type tables) then sub-type tables may help to manage the column sprawl.  (Pathologically complex version of your problem)
Some O/R mappers may only support a particular approach to managing sub-classes.

In most cases physical sub-type tables in a DB schema are a bit of a solution in search of a problem, as they potentially have undesirable side-effects.

In your case, I assume you have a relatively modest number of sub-types and a manageable number of attributes.  Your diagram and question don't indicate any intention to hang child tables off the records.  I would suggest that you consider going with the first option suggested above and maintaining one table and manage the sub-typing within your application.


Making a new database onto existing AWS RDS instance I know that we can create multiple databases on a single AWS RDS instance. I have an existing MySQL instance and was wondering what steps are required to create an additional database on that server? Does it involve creation of a new user and so forth?

CREATE DATABASE db2;

There does not have to be a different user for each database.  But you might want to do it if you want to keep, say, two applications, each using one of the databases, from accidentally stepping on each other.

GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'host-name' IDENTIFIED BY 'password1';
GRANT ALL PRIVILEGES ON db2.* TO 'user2'@'host-name' IDENTIFIED BY 'password2';

Usually I have a separate user for each database; sometimes I have a user that touches multiple databases.

(This answer applies to MySQL and MariaDB in general.)


Is adding the ‘tbl’ prefix to table names really a problem? I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’.

On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read it”.

Questions and considerations

It this really a problem? Because I’m prefixing tables with ‘tbl’ since my first dba job (the senior DBA told me to do that for organization).
Is this something that I need to get rid of? I made some tests, copying a really big table and giving it the ‘tbl’ prefix, while keeping the other one without it, and I didn’t notice any performance issue.

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it.

In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the month is complete, then the books are sealed and they can't modify values.

It's amazing how much financial data a financial services firm generates... Something we didn't realize with our test data set was the volume of data was going to make their month end procedures untenable. It took an increasingly long time to delete out the "current month's data" prior to replacing it with the new trial run.

We had to do something to make it faster for processing without breaking the uncatalogued list of "who knows what" that all depends on the MonthlyAllocation table. I decided to play magician and whip the tablecloth out from underneath them. I went old-school and used a Partitioned View. The data already had  an IsComplete flag so I made two tables - each with contrary check constraints: MonthlyAllocationComplete, MonthlyAllocationInComplete

I then created the partitioned view with the same name as the original table: MonthlyAllocation. No process was any the wiser about the physical change we made to the database. No reports broke, none of the analysts with direct access reported any issues with that "table" before or after.

Cool story bro, but where you going?

What if they had a naming convention there, tbl_MonthlyAllocation? Now what? Do we spend lots of man hours going through every ETL, every report, every ad-hoc spreadsheet in the organization and updating them to use vw_MonthlyAllocation? And then of course all those changes go through the Change Board and that's always a quick and painless process.

You boss might ask: What's the reward to the company for all that work again?

The other option becomes we leave this view named as tbl_ and not spend all that time testing, updating and deploying code. Which becomes an amusing anecdote you explain to all the new hires, and those with short attention spans, that have to work with the database as to why you are inconsistent with the naming of objects

Or you don't double encode objects with redundant metadata. The database will happily tell you what is a table, what is a view, what is a table valued function, etc.

Naming conventions are good, just don't paint yourself into a corner with them.


SQL How to copy text from one table (from one column) to another table (to one column), preferably with IF the target column is empty I have two tables in a database and I wish to copy text from one column of the table to another column in another table.

Lets say I have two tables called 'articles' and 'info'. I have to copy articles.article column text into info.description for the same articles, which can be identified by a so called 'Codename' and not ID. Preferably IF the column info.description is empty. I intend to use WHERE category = 4 to limit myself to only some articles, but that shouldn't be a problem and if the IF statement is correct even unnecessary.

Tables layout (Not whole, but enough for here):

Articles (ID, Codename (VARCHAR), Article (VARCHAR))
Info (ID2, Codename (VARCHAR), Description (VARCHAR))

So basically, I would like to give articles with no description a basic description with their name and some data, that is written beside the name.

Although previous answer was correct, and I am thankful to @McNets, I found an easier way to do it.

UPDATE info i, articles a
SET i.Description = a.Article
WHERE i.Codename = a.Codename
AND i.Description IS NULL;


Testing AFTER INSERT Trigger I have the following table:

CREATE TABLE Train(
    Train_No integer PRIMARY KEY,
    Loco_No integer REFERENCES Locomotive(Loco_No),
    Back_Loco_No float REFERENCES Locomotive(Loco_No),
    Train_Length float CONSTRAINT Train_Len CHECK(Train_Length <= 400),
    Train_Weight float CONSTRAINT Train_Weight CHECK(Train_Weight <= 10000)
);

What I have done is written a trigger to test the Train_Length is less than 400 as followed:

create or replace
trigger TRAIN_LENGTH
AFTER INSERT ON TRAIN
REFERENCING NEW AS New
FOR EACH ROW
BEGIN
   IF :new.Train_Length> 400 THEN
       RAISE_APPLICATION_ERROR(-20000,'Train weight has exceeded maximum train length');
  END IF;
END;

I believe the only way to test that this trigger is working is by inserting some data into my train table. However I did notice that looking at my CREATE_TABLE statement I have a CHECK constraint on my Train_Length <= 400. I have temporarily tried inserting some data into my table by doing:

INSERT INTO Train VALUES (9,1,NULL,500,100);

This throws up:

SQL Error: ORA-02290: check constraint (K0954342.TRAIN_LEN) violated
02290. 00000 -  "check constraint (%s.%s) violated"

I believe that the cause of this would be because the value 500 violates the check constraint as the Train_Length <= 400. Also I noticed that my trigger is called AFTER INSERT ON TRAIN which means that really and truly the trigger is only ever called after insert on the train table. BUT! Because I have my check constraint on my Train table being Train_Len CHECK(Train_Length <= 400) this means that this is being called before the trigger is even called.

SO I believe the only way to check that my trigger does work is to drop my Train table and remove the check constraint Train_Len CHECK(Train_Length <= 400) and then try to re-run the following INSERT:

INSERT INTO Train VALUES (9,1,NULL,500,100);

If someone can tell me if this is correct?

Your analysis is correct. You don't need the trigger because the constraint is doing the same job. Anyway, to drop the constraint use:

Alter table train drop constraint train_len;


Does a huge key length value for a mulibyte column affect the index performance? When I look at the EXPLAIN results, the key len value is always calculated based on the actual column length multiplied on the maximum number of bytes for the chosen encoding. Say, for a varchar(64) using utf8 encoding the key len is 192.
Does this number affect performance in any way and should I reduce it when possible? I mean, does it make MySQL to reserve some space somewhere that remain unused, or it's just a maximum possible value while the used space is based on the exact data length?
So the actual question is: if I have a column that contains only Latin letters and numbers, should I change its encoding to latin1 from utf8 in regard of the space occupied by the index/overall index performance?

The number is a worst-case estimate. Remember that EXPLAIN doesn't typically read the data, so it has no idea if your data contains only a short string of 1-byte characters on average, or if it contains a full 64 characters each 3-bytes per character.
The performance of the query is much more influenced during execution by the number of pages read, and that varies more directly with the actual data. Short string values will allow more index entries to fit per page, therefore fewer pages need to be read. This is something the EXPLAIN cannot predict.
I would recommend do NOT change your encoding to latin1 solely for the sake of making EXPLAIN output "less confusing." This is not a good reason, and it's bound to limit you later if you want to support UTF-8 characters in that column.
In today's era, there's virtually no reason to use the latin1 character set. I recommend setting all strings to utf8mb4 as a default.
If the column has something like strings of hex digits for a uuid or a hash value, that's different. You could set the character set to ascii. But that's for a reason of the data, not for the convenience of someone reading the EXPLAIN.


Is it possible to create encrypted backups using encrypted by password certificate? I have encrypted by password certificate, which I want to use for backup encryption. The issue is I am not able to use the certificate (in T-SQL statement or maintenance plan) because I am not able to open it  - there seems not to be OPEN CERTIFICATE command.

I want to have certificate encrypted by password as our goal is to have only one user who will be responsible for creating/inserting the certificates in the SQL Server Instances. No one else will be able to export the certificate and restore an encrypted backup.

Is it possible, or in order to have encrypted backups I always must have the certificate encrypted by the database master key?

As far as I can see: No, it is not possible to use a Certificate that is protected by a password with the BACKUP command; the Certificate or Asymmetric Key needs to be protected by the Database Master Key (DMK).

The syntax of the BACKUP command statement does not have means of specifying a password for either a Certificate or Asymmetric Key. No, you do not OPEN a Certificate (or even an Asymmetric Key). You only OPEN Symmetric Keys and Database Master Keys (which are symmetric keys).
When attempting to use a Certificate that has been protected with a password, the following error occurs (emphasis added):

  Msg 33101, Level 16, State 1, Line 123
  Cannot use certificate 'BackupEncryption', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

I had high hopes that creating a Certificate that was protected by the DMK which had its protection by the Service Master Key (SMK) removed would have worked since that usually forces you to first execute OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'; in order to do anything with the DMK, and I did have to do just that in order to create the Certificate, BUT, once the Certificate was created (and yes, I did execute CLOSE MASTER KEY;), the BACKUP statement still worked without the DMK needing to be explicitly opened. That is odd since the private key of the Certificate shouldn't be usable without executing OPEN, and the private key is definitely required for encrypting stuff (trying to do an encrypted Backup with a Certificate that has had its private key removed will fail).

Sooooo, it looks like you will need to handle this outside of SQL Server. As in:

Execute the backup
encrypt the .bak file

You can accomplish this in a few ways:

Via stored procedure:

BACKUP...
xp_cmdshell to encrypt

SQL Agent Job

T-SQL step executing BACKUP...
OS command OR PowerShell step doing the encryption

CMD script:

SQLCMD.EXE executing BACKUP...
encrypt


Create an "int" column forced between 0 and 3 I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible?
My goal would be to have this column so that I could just execute a increment or decrement command, to increment or decrement the value in the database.
Example
If value in DB = 0
I execute something like UPDATE xxx SET yyy = yyy-- WHERE zzz
Value would still be 0
If value in DB = 3
I execute something like UPDATE xxx SET yyy=yyy++ WHERE zzz
Value would still be 3
If value in DB = 3
I execute something like UPDATE xxx SET yyy=yyy-- WHERE zzz
Value would still be 2
If value in DB = 1
I execute something like UPDATE xxx SET yyy=yyy-- WHERE zzz
Value would still be 0

thank you for your answers. I am using SQLite3 local on my laptop for testing purpose, I am not sure what the "real" database will be.
So for that reason and because it does not seem easy to restrict values in a certain range, I have solved my problem in the software itself.

Using the "AND" condition to check if values are in the interval I want: "UPDATE xxx SET xxx WHERE xx AND xx"

Thank you all.


mysql function for 2 rows (same name) with group by I have my table:

id   name   salarry   month   year
1    mike   1000      1       2017
2    john   1200      1       2017
3    mike   1100      2       2017
4    mike   900       1       2017

my results now is:

id   name   sallary   month   year
2    john   1200      1       2017
1    mike   1100      1       2017

I want to show the sallary of mike separatelly by month:

id   name   sallary   month   year
1    mike   1000      1       2017
2    mike   1100      2       2017
3    john   1200      1       2017

the mysql code:

SELECT  `id` ,  `name` , MAX( sallary ) ,  `month` ,  `year`
FROM  `test`
GROUP BY  `name`
LIMIT 0 , 30

depending from what You are looking for, it could be:

SELECT  MIN(`id`) as id,  `name` , MAX( sallary ) as salary ,  `month` ,  `year`
FROM  `test`
GROUP BY  `name` ,  `month` ,  `year`

or because mike have 2 salary in Jan 2017 (and of course it same Mike ;) ):

SELECT  MIN(`id`) as id,  `name` , SUM( sallary ) as salary ,  `month` ,  `year`
FROM  `test`
GROUP BY  `name` ,  `month` ,  `year`

because as unique identifier You choose Name - MIN(id) allow avoid errors when MySQL default settings - ONLY_FULL_GROUP_BY

Specially for Rick James :)

From original question, we can see -  id, can not be used as IDENTIFIER

so queries example with MIN and MAX for id - could be (and this realistic live scenario) - first or most resent contact with person

Before we go to next form of query - first we need decide mike(id=1) for Jan 17 and mike (id=4) for Jan 17, it is same person? and salary for Jan - it MAX or SUM (as I include in my examples).


Can we restrict the number of connections to a database in SQL Server? I have a database on SQL Server. My web application contacts this database. Is there a way in which I can restrict the number of connections to this database through some settings on the SQL Server?

Seems like we can do it :

To configure the user connections option

In Object Explorer, right-click a server and click Properties.
Click the Connections node.
Under Connections, in the Max number of    concurrent connections
box, type or select a value from 0 through    32767 to set the
maximum number of users that are allowed to connect    simultaneously
to the instance of SQL Server.
Restart SQL Server.

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


Partitioning / indexing an extremely large table I'm working on indexing and partitioning a single data warehouse table that weighs in around 500 GB. The table is a heap, has over a hundred TEXT columns, and the TEXT_IN_ROW option is enabled. I didn't design this table, and I don't have any capacity to change it in the immediate future.

I've been tasked with partitioning it. We're tackling this using a copy of the database on a test server. It can push about 2 GB per second to the SSD RAID arrays, so I/O isn't a significant bottleneck, and it's got 16 cores (2 NUMA nodes), and 64 GB of RAM.

My approach is to disable all the nonclustered indexes, create a partition function and partition scheme (about 12 partitions, all on the PRIMARY filegroup - they're using this to enable rolling maintenance and provide more localized inserts for nightly ETL, and not to distribute I/O), then build a clustered index for the table using this partition scheme.

I'm creating the clustered index and partitioning the table as follows:

CREATE CLUSTERED INDEX CX_DailyTable ON DailyTable (LoadDate, SeqNumber)
  WITH (SORT_IN_TEMPDB = ON) ON monthly_on_primary (LoadDate)

Obviously, it's taking a long time (3 hours so far as of this post), and I certainly don't expect it to be quick. What worries me slightly is that tempdb is now pushing nearly 1 TB and steadily climbing, despite the current table being around half that size. The MS docs I've read suggest the tempdb space usage should be about the size of the final table/clustered index.

http://msdn.microsoft.com/en-us/library/ms188281.aspx

  If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. The sort runs contain the leaf rows of the index.

Are their estimates incorrect? Is tempdb being used for substantially more than just the sort runs? Or is creating this clustered index somehow doubling the size of the table? (Seems pretty unlikely; it's a rather wide table, and I estimate we're getting an extra 4-8 bytes per row, plus non-leaf pages by adding a clustered index.)

My approach is to disable all the nonclustered indexes [...] then build a clustered index for the table using this partition scheme.

Creating a clustered index on a heap automatically rebuilds all nonclustered indexes (even disabled ones). The nonclustered indexes are rebuilt but not partitioned. Assuming the desired end state is a partitioned clustered table with aligned indexes, rebuilding the nonclustered indexes to be non-aligned is entirely wasted effort.

  What worries me slightly is that tempdb is now pushing nearly 1 TB and steadily climbing, despite the current table being around half that size. The MS docs I've read suggest the tempdb space usage should be about the size of the final table/clustered index.

The question of sort space is very complex. To understand all the details (including the effect of parallelism) you would need to carefully read an entire series of posts by the SQL Server Query Processing Team. Converting a heap to a partitioned clustered table with parallelism enabled is probably pretty close to worst case.

At it's most basic (neglecting most of the important information in the QP Team's posts), you are asking SQL Server to run a query like:

SELECT *
FROM DailyTable
ORDER BY
    $partition.monthly_on_primary(LoadDate),
    LoadDate,
    SeqNumber;

This query will not execute quickly, regardless of where you choose to write the sort runs that do not fit in memory to. Add to that the work of actually building a complete new copy of the entire data set in separate rowsets, and the work involved in rebuilding the nonclustered indexes pointlessly...

Advice

There are many considerations in getting this change to work efficiently. The important ones are to avoid sorting at all where possible, and to use parallel minimally-logged bulk load wherever possible.

The details of that depend on details not contained in the question, and a full solution is beyond an answer here. Nevertheless, the outline of an approach that has worked well for me personally in the past is:

Extract the existing data using bcp to one file per final partition
Drop the existing table and create the new one
Load the new table using parallel minimally-logged bulk load

The per-partition data extract needs be ordered on (LoadDate, SeqNumber). Ideally, you would avoid a sorting operation. If you have an existing nonclustered index on (LoadDate, SeqNumber) you can extract data in the right order without sorting if you construct the query correctly.

Once per-partition data has been extracted to separate files (this can be done in parallel if your hardware is up to it), the source table can then be dropped, freeing space. A new partitioned heap or clustered table is then created, and bulk loaded with the pre-sorted data, possibly also in parallel.

Done right, the entire process requires no more than 1x data size and achieves the fastest possible data transfer rates in both directions, with the least amount of log use.


"History-based" database design approach? A few years ago I've stumbled upon a database design approach, but I can't find any information on this for the love of my life.

Its main feature was normalizing tables in a way where every "property" of entities was stored in their own table. E.g. a user entity, normally stored in a single table with an id, email and other fields, was split into tables for each field. So users' email address would have been stored in a table with a field to contain a user's current email address and a datetime (when this email address was set or last updated) with a user id or similar. The model would track every change to a user's email address by creating new records in this table with updated timestamps. (Not sure if this made sense.)

This approach was documented online and it had an online tool to visualize such a model (as a graph) and it had a way to export the visually created model as an SQL schema (with full support for MSSQL).

Anyone have an idea about the name of this design approach?

Edit.: I remember that the author of this approach (or the visual modeller that is/was available online) mentioned that the tool was able to produce database schema (or "optimized JOINs") for MSSQL since it has (had) the ability to efficiently to "prune tables".

Edit.: By "prune tables" I meant "eliminate tables".

Your description could be of SQL Columnstore indexes

This index uses column-based data storage and query processing
stored in a column-wise data format
To reduce fragmentation of the column segments and improve performance, the columnstore index might store some data temporarily into a clustered index called a deltastore and a btree list of IDs for deleted rows. The deltastore operations are handled behind the scenes. To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.


Does unique email in user table violate normal forms? USER(userID, email, name, address, ...)
userID

primary key
incrementing bigint

email

unique identifier
can be modified by user

To me this seemed like a good idea, but then I started to wonder: does the uniqueness of 'email' violate the third normal form (3NF)?

To extend the hole idea why index doesn't matter
A indexed filed onlys describes that every row is unique, but this doewsn'z means that it is dependend from other columns than the promary key

A relation is in third normal form if there is no transitive dependency for non-prime attributes

following this definition, it still stands an indexed email field is  dependent on the primary key and does not depend on other columns it is non transitively dependent
Which means that it correspnds teh 3NF


Alter Time of DateTime Field I am needing to alter the time piece of a datetime field.  I tried to use this syntax, but it gives me an error of

  Incorrect syntax near the keyword as

What syntax can I use to alter the end time of my datetime field?

case when CAST([pcpts].stopit As DateTime)+CAST(case when CAST([pcpts].stopit As Time) > '11:00:00.000' then '11:00:00.000' ELSE CAST([pcpts].stopit As Time) end as datetime) As [SI]

EDIT
What I am after is if the time value for [pcpts].stopit is > 11:00:00.000 I want my query to show 11:00:00.000,

if the time value for [pcpts].stopit is < 11:00:00.000 then show [pcpts].stopit

Examples would be:
[pcpts].stopit = '01/01/2014 10:00:00.000'
query output should show '01/01/2014 10:00:00.000'

[pcpts].stopit = '01/01/2014 16:00:00.000'
query output should show '01/01/2014 11:00:00.000'

First thing is you will need to stop performing shorthand math operations like + against date/time values. This breaks when you switch to the newer types, which you should do.

For this particular problem, you can simply use a CASE expression to either (a) return the datetime as is, or (b) convert to date and then add 11 hours.

CASE WHEN DATEPART(HOUR, col) < 11
  THEN col
  ELSE DATEADD(HOUR, 11, CONVERT(datetime, CONVERT(date, col)))
END


How to add a PostgreSQL 10 identity column to an existing table? I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.

Is it also possible to add an identity column to an existing table?

For example, with the old SERIAL columns, I can do:

ALTER TABLE sourceTable ADD  COLUMN ogc_fid SERIAL;

With the identity columns, is there an equivalent of above? I tried:

ALTER TABLE sourceTable ADD  COLUMN ogc_fid GENERATED BY DEFAULT AS IDENTITY;

But got an error:

  ERROR:  syntax error at or near "BY" LINE 1: ...R TABLE sourceTable
  ADD  COLUMN ogc_fid GENERATED BY DEFAULT

I'm thinking about replacing the SERIAL columns with IDENTITY columns in my scripts, and just wanted to check if this is feasible.

You need to declare the type of the column, too (INT or BIGINT or
SMALLINT):

ALTER TABLE sourceTable
    ADD COLUMN ogc_fid int                -- int or bigint or smallint
        GENERATED BY DEFAULT AS IDENTITY;

Also be sure to use the latest point release. As the IDENTITY has only been recently added, there were bugs affecting this particular command before Postgres 10.2. See:
How to add a PostgreSQL 10 identity column to an existing table with rows?


Should frequently accessed tables containing large blobs with one-to-one relationships be normalized and columns split into two tables? I have a frequently accessed table containing 3 columns of blobs, and 4 columns of extra data that is not used in the query, but just sent as result to PHP. There are 6 small columns (big int, small int, tiny int, medium int, medium int, medium int) that are used in the queries in the WHERE/ORDER BY/GROUP BY.

The server has very low memory, around 1GBs, and so the cache is not enough to improve the performance one on the large table. I've indexed the last 6 small columns, but it doesn't seem to be helping.

Would it be a good solution to split this large table into two?
One table containing the last 6 columns, and the other containing the blobs and extra data, and link it to the previous table with a foreign key that has a one to one relationship?
I'll then run the queries on the small table, and join the little number of rows remaining after filtering to the table with the blobs and extra data to return them to PHP.

Please note, I've already done this, and I managed to decrease the query time from 1.2-1.4 seconds to 0.1-0.2 seconds. However I'm not sure if the solution I've tried is considered good practice, or is even advisable at all?

Using InnoDB and the Barracuda file format permits you to deploy the Dynamic row_format. This configuration will aid you due to the way it will store the BLOBs off page. Queries not using the columns with the BLOBs shouldn't need to touch their pages;

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-row-format-dynamic.html


How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on spesific tables to several developers who are in the SQLQRY AD account. How can i do that without extracting them out of the group?

I need to give just update and delete permission on specific tables to
  several developers who are in the SQLQRY AD account. How can i do that
  without extracting them out of the group?

If you are using the same AD user for all your developers I would advise you to start using AD groups and adding your developers to these groups. See here for a Q/A on using one AD login for multiple users. Afterwards you could follow the steps below.

Permissions are granted to all members of the AD group or none.

If you want to grant permissions to some of these users, you would have to add a different ad group or add the users separately.

Afterwards you can grant the update and delete permissions to that group or create a new role with the permissions and add the group / users to it .

Example commands:

USE [master]
CREATE LOGIN [SQLQRY_2] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [DBName]

CREATE USER [SQLQRY_2] FOR LOGIN [SQLQRY_2];
CREATE ROLE [NewRole];
GRANT INSERT,UPDATE,DELETE ON dbo.test to [NewRole]; -- table1
-- Table 2,3, ...

ALTER ROLE [NewRole] ADD MEMBER [SQLQRY_2];

  They are defined in SQLQRY AD account and they don't have update and
  delete permissions out there. So, will my role overwrite to my deny
  rule?

If you have deny permissions specified on writing data to the tables then granting corresponding write permissions will not work. DENY has a higher precedence than grant.


Why choose a top query and temporary table instead of a cursor for a loop? When it comes to looping over a large set of data in T-SQL, the majority of examples I see on the Internet use a pattern like this:

declare @someVariable int
declare @remainingRows int

select someColumn from someTables into #someTempTable

select @remainingRows = count(*) from #someTempTable
while @remainingRows > 0 begin

    select top 1 @someVariable = someColumn from #someTempTable

    -- Do stuff

    delete top 1 from #someTempTable

    select @remainingRows = count(*) from #someTempTable
end

This seems to be way more common than samples of using cursors like this:

declare @someVariable int

select someColumn from someTables into #someTempTable

declare @someCursor cursor for select someColumn from #someTempTable
open @someCursor
fetch next @someVariable from @someCursor

while @@fetch_status = 0 begin

    -- Do stuff

    fetch next @someVariable from @someCursor
end

close @someCursor

I sometimes hear mentioning of performance reasons, but to me that's counter-intuitive; wouldn't a forward-only read-only cursor iterating over a set of data be way more efficient than constantly querying and updating a temporary table? I understand that if it was a cursor iterating over a table with the wrong options that the table could be locked for updates, but a cursor over a non-shared temporary table wouldn't have that problem, would it?

Bonus question; if cursors are OK, would a cursor over a regular query that uses snapshot isolation be safe so I can avoid the annoyance of manually creating a temporary table, i.e.:

DECLARE @someCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT something
FROM tables

No, a loop is not necessarily faster than a cursor, though some people are more comfortable with them. I went through the loop writing phase myself at one time.  Also cursors come in several flavors, so choosing the right type of cursor is an important detail.

Your question is probably answered by Aaron Bertrand (from 2012), since he ran several comparative tests, at:

https://sqlblog.org/2012/01/26/bad-habits-to-kick-thinking-a-while-loop-isnt-a-cursor

In short, he found that the proper cursor ran significantly faster than several loop constructs.

Reading through his comparisons should help you feel comfortable with cursors.

But only when they are needed.


How to aggregate datapoints in a table? Suppose I have following table -

CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER);

I want to aggregate the data by calculating average of every 10 points in the table.

i.e. If table has 20 data points the result is two aggregate points. 1st aggregate point the average of 1-10 data points, and 2nd of 11-20.

Is this possible using a SQL query?

Since you have not disclosed the RDBMS you intend to use (by the time of writing), I feel authorized to post a PostgreSQL-specific solution:

WITH rownums AS (
    SELECT *, row_number() OVER (ORDER BY col1) AS rownum
    FROM avg_test
),
averages AS (
    SELECT avg(col2) OVER (ORDER BY rownum
                           ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS average,
           rownum
    FROM rownums
)
SELECT average
FROM averages
WHERE rownum % 10 = 0
;

In the first CTE (called rownums) I simply added row numbers according to ordering by the timestamp.   This was necessary for being able to find every 10th row later. (If there were an additional unique key on this table, this yould be unnecessary.)
In the averages CTE I compute a moving average on every row.  The window is set so that the current row and nine preceding give the numbers to be averaged.
Lastly, I just simply return every 10th row.

Both row_number() and avg() are window functions here.  MySQL lacks this functionality, and, as far as I know, H2 too.  So porting the abov query to these will involve a lot more trickery, I think.

Try it at SQLFiddle.


WAL Streaming Failure PostgreSQL 9.4 I'm trying to setup streaming replication but I'm stuck, please help!

I followed the steps in https://wiki.postgresql.org/wiki/Streaming_Replication

On Master I have
(postgresql.conf)

archive_mode = on
wal_level = archive
max_wal_senders = 5
wal_keep_senders = 150

(pg_hba.conf)

host replication user-with-rep IP/32 md5

On slave I have
(recovery.conf)
primary_conninfo = ' host=IP port=5432 user=USER password=PWD '
standby_mode = 'on'
restore_command = 'copy "\\path\%f" "%p" '

I start services on primary and then do a backup with

psql -c "SELECT pg_start_backup('label', true)"

Then I copy all files from primary to standby with

xcopy from\path\* to\path /s /i /e

finally

psql -c "SELECT pg_stop_backup()"

When I execute pg_ctl start on my standby I get the following

LOG:  database system was interrupted; last known up at 2015-05-27 14:16:41 EDT
LOG:  entering standby mode
LOG:  restored log file "000000010000000000000028" from archive
LOG:  redo starts at 0/28000090
LOG:  consistent recovery state reached at 0/280000B8
LOG:  unexpected pageaddr 0/24000000 in log segment 000000010000000000000029, offset 0
LOG:  started streaming WAL from primary at 0/29000000 on timeline 1

And on my master I have

LOG:  database system was shut down at 2015-05-27 16:00:14 EDT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

And then nothing else happens, please I really need help with this, I appreciate all comments. Any questions, please feel free to ask.

I would like to have this setup first and then do replication slots.

From your log, it looks like you were successful starting streaming replication.

  LOG:  unexpected pageaddr 0/24000000 in log segment 000000010000000000000029, offset 0

  LOG:  started streaming WAL from primary at 0/29000000 on timeline 1

To see more in your log, want to change the logging parameters:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql/9.4'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

Adding hot_standby = on to your postgresql.conf also makes it easier for debugging as well.

Though, what you really should be doing to see if replication is working in your setup is something like this:

Create a table and generate traffic:

CREATE TABLE t1 (a int, b int);

INSERT INTO t1 (a,b)
    SELECT random()*100000, random()*100000
             FROM generate_series(1,1000000);

Check the status on the primary and secondary with these queries:

Primary:

SELECT * FROM pg_current_xlog_location();

Hot Standby:

SELECT * FROM pg_last_xlog_replay_location();

If the locations are exactly the same, or very close together, then everything is working just fine.

Hope that helps. =)


Parameter evaluation against harcoded string always return false In Mysql workbench I am trying to make query whit parameter.

But I have strange behavior when I try to compare parameter with static strings.

Why I do not get any rows form query like this ?
When I run query , I do get header rows, but I do not get any row.
I am sure that there is rows in table calls.
Forgive me if this too obvious but I in MsSql I did this million times without issues.

set @kontakt ='fo' ;
select
*
from calls
where (@kontakt = 'fo');

I tried it and it is working as expected:
http://sqlfiddle.com/#!9/b92cd1/1

However, some clients deals with every one statement as a single transaction. Try to add all of your code to one transaction and execute it.

I tested that in MySQL client (command line in Linux) and on SQL Fiddle (Link above)


Why SQL Server installs multiple services for AS and RS, but only one for IS? When I install multiple SQL Server instances on a same computer, every instance gets its own SQL Server Service for Analysis Services and Reporting Services. But I get only one service for Integration Service, why?

Integration Services is not "Instance aware". The rest (Analysis, Reporting, DB) are.

Note that SSIS doesn't need to be instance aware either. The service itself is pretty pointless and doesn't need to running. You simply need the SQL Server common binaries installed to run packages. The service is for SQL Server Agent and SSMS to browse packages etc

Links:

http://www.ssistalk.com/2009/11/04/ssis-what-does-the-ssis-service-actually-do-anyway
http://support.microsoft.com/kb/942176


Open source tools for Oracle schema compares I'm looking for an open source tool to generate DDL scripts.  Many tools call these synchronization scripts.  We essentially want to compare schema of different environments (ie: DEV to QA, QA to PROD) to manage object migrations/deployments a little easier.  Do open source tools exist like this for Oracle?

You might want to have a look at Liquibase - according to the Liquibase documentation, it can perform diffs (I'm not 100% sure whether this requires using Liquibase to version your schemas, though).