PostgreSQL - How can I manage WAL archiving for different databases on a server? I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of them. I didn't find anything about multiple databases in PostgreSQL documentation or anywhere else. Or I didn't find anything about specifying the database name for WAL backup.
It will be appreciated if someone can help me with this.

You can't split WAL archiving out so one database goes in one WAL archive stream and other DBs go in another WAL archive stream. A single write-ahead log is shared by all databases in a PostgreSQL server ("cluster" in PostgreSQL parlance). All databases managed by a postmaster have the same WAL. This is a sometimes unfortunate limitation of the current replication/PITR/archiving design and is true for the in-development-at-time-of-writing 9.3 and all and prior releases.

If you want to separate the WAL archiving, you have to separate the databases into different clusters. You can do this by running more than one instance of PostgreSQL on a server; they simply have to have different data directories and different listening ports. Some packagings of PostgreSQL provide built-in tools to do this, like the pg_wrapper tool used on Debian and Ubuntu, which has pg_createcluster. Alternately, you can always just use initdb to create a cluster and pg_ctl to start the cluster after editing the new cluster's postgresql.conf to set an appropriate port.

Tools like pgbouncer may be used, if desired, to multiplex the multiple clusters transparently, so pgbouncer listens on port 5432 (the default Pg port) and decides which server to send the connection to based on which database it asks for. Generally it's easier to just specify a port in the connection setup of the applications that use each database, though.

On PostgreSQL 9.2 and below you must allocate each server an exclusive, non-overlapping shared memory region for its shared_buffers. One server cannot make use of the other server's shared_buffers space when the other server is not busy. That means you are wasting some system resources. PostgreSQL 9.3 improves this by allocating shared_buffers out of anonymous memory, giving the OS more freedom about how it manages it.

If you don't want to run multiple clusters, you can use alternative replication or backup solutions based on out-of-tree tools like Slony-I, Bucardo, etc to manage your data replication and archival.


Extra tables are added when I create a new Database This is a really weird problem. When I create a new DB from a script I'm getting extra tables added. These aren't system type tables but regular tables that look like they may have come from a past project. There are 3 of them, each with 2 columns, either ints or nchar(10) with no keys, constraints, triggers, or indices.

I have no idea where this is coming from or how I can fix it. Right now I just delete the tables but it's a bit annoying and I'd like to fix the issue.

Here is a script I wrote that reproduces the error:

CREATE DATABASE X;
GO

USE X;
GO

CREATE TABLE Y(
  YId INT IDENTITY CONSTRAINT pk_yid PRIMARY KEY(YId));
GO

CREATE TABLE Z(
  ZId INT IDENTITY CONSTRAINT pk_zid PRIMARY KEY(ZId));
GO

After I run this script my DB is created, but it has 5 tables instead of 2.

Running SELECT * FROM INFORMATION_SCHEMA.TABLES just lists my extra tables as BASE TABLE, exactly the same as the ones I defined (all other columns are the same as well).

This is happening on SQL Server 2008 R2
(Microsoft SQL Server Management Studio 10.50.2500.0)

Someone added tables to the model database.

  When a CREATE DATABASE statement is issued, the first part of the
  database is created by copying in the contents of the model database.
  The rest of the new database is then filled with empty pages.

  If you modify the model database, all databases created afterward will
  inherit those changes. For example, you could set permissions or
  database options, or add objects such as tables, functions, or stored
  procedures.


Is forcing T-SQL query to use parallelism OK? It can be done by calling the trace flag 8649 in a query option.

OPTION (QUERYTRACEON 8649)

This trace flag causes the cost of the query to be 0, which will always be below the cost threshold for parallelism and therefore the query will be considered for a parallel plan.

Does attempting to force a T-SQL query to use parallelism in this way have any downsides?

sample tutorial how to force parallelism

It has one big downside that is stated at the end of that post:

  Even experts with decades of SQL Server experience and detailed
  internal knowledge will want to be careful with this trace flag.  I
  cannot recommend you use it directly in production unless advised by
  Microsoft

Interesting to use when testing but not really for production.


Understanding about SQL index on DateTime Column I have about 1 million records. When searching through the date range, it is surprisingly slow. Then I do the google search and someone suggested to do the index on the "DateTime" column. I am not convinced because I always thought index is for O(1) search - which means equality search; and since my query is a date range search, how can it be possible to improve the performance?
But, out of curiosity, I tried anyway and to my surprise, it does improve the performance significantly.
Here is my table
tblOrder
--------
OrderId (Primary Key)
CurrencyId
ProductId
...
...
...
OrderDate DATETIME

I am using linqtosql and the code is:
return (from rec in tblOrders.AsNoTracking()
        where (rec.CurrencyId == CurrencyId) && (rec.OrderDate >= startDate) && (rec.OrderDate < endDate)
        select rec).ToList();

I created a composite index on both CurrencyId and OrderDate. The result is a huge improvement. My question is, how is it possible? Why do indexes work on range search (not equal but >= or <=).
Can someone explain to me how does SQL index work in regards to date range search?
Thank you...

B-tree indexes don't work by comparing only for equality. They're designed (the algorithm is designed) to use ranges.

Figure 1.3 shows an index fragment to illustrate a search for the key “57”. The tree traversal starts at the root node on the left-hand side. Each entry is processed in ascending order until a value is greater than or equal to (>=) the search term (57). In the figure it is the entry 83. The database follows the reference to the corresponding branch node and repeats the procedure until the tree traversal reaches a leaf node.

References

The Search Tree (B-Tree) Makes the Index Fast See especially section 1.3, B-Tree traversal
SQL Server index structure and concepts


Will an index that is a partial key of the begining of another index perform any better than the longer index? If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches the simpler index. For example if I had a non clustered index (TransactionDate, ClientID, State) and my search criteria is only TransactionDate and ClientID what search performance gains would I achieve by creating a second index of just TransactionDate and ClientID. Index maintenance (inserts, updates, deletes) and disk space usage is of no consideration. Low level detail of how sql server implements and utilizes indexes would be greatly appreciated.

The data types are TransactionDate DATETIME with a zero timestamp, ClientID VARCHAR(6), and State CHAR(2). The table has about 16 million rows, 100 dates with about 160,000 records for each day and 500 clients. The table is loaded daily with all the records having a new date.

There really isn't even any guarantee that the optimizer will use the index in the first place.  The difference of only one column between the two indexes is (for the most part) trivial.  But if it did, any performance gain you would see (if any) would be trivial.

The reason for this is in how the B-Tree index is implemented by SQL Server.  Both indexes are equally capable of satisfying the predicate (WHERE clause) and therefore locating only the rows that meet the search parameters.  The only difference between the two would be how many pages in the index SQL Server would have to read in order to locate the required rows.  And how many pages will ultimately be determined by the size of the index rows (Storage size in bytes of (TransactionDate, ClientID) vs. the alternative, with only the size of the State column being the difference between the two.

Just as an example, if a single index row was 60 bytes, SQL Server would only have to read 2 pages in order to locate a row up until there were around 2.5 million rows in the index.  Then it would only require 3 pages to be read until the table reached more than 300 million rows.

So, what does that mean?  Whether it is looking through an index of 2 or all 3 columns, SQL Server is still only looking through the same number of pages to locate the rows, unless the State column is so large as to create a significant difference in the size of a single row, thus causing the index pages to fill considerably faster.  Any noticeable performance difference would only be caused if SQL Server had to read through more levels of the index to satisfy the query (3 levels vs. 4 levels, etc) and a single column of text, especially if State is a 2 char state abbreviation, simply won't be enough to make a significant difference.

If you really want to boost performance of the query, and the table is quite large, you might be better off exploring table partitioning, or if not on Enterprise Edition perhaps a partitioned view, all depending on whether there are certain ranges that are primarily searched and others that are searched much less often.


How to get the foreign and primary key details for a table in SQL Server 2008 I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys.

Is this query that lists the primary keys correct:

select syssc.name as schemaname , cast(c.name as  varchar(255)) as foreign_table ,
cast(p.name as varchar(255)) as primary_table
from sysobjects f
inner join sysobjects c on  f.parent_obj = c.id
inner join sysreferences r on f.id =  r.constid
inner join sysobjects p on r.rkeyid = p.id
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
inner join sys.tables syst on rc.id=syst.object_id
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id
where  f.type =  'F' AND c.name in ('table name' )

This is the query that i use now to get the foreign keys

select syssc.name as schemaname ,cast(c.name as  varchar(255)) as foreign_table ,
cast(p.name as varchar(255)) as primary_table
from sysobjects f
inner join sysobjects c on  f.parent_obj = c.id
inner join sysreferences r on f.id =  r.constid
inner join sysobjects p on r.rkeyid = p.id
inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
inner join sys.tables syst on rc.id=syst.object_id
inner join sys.schemas syssc on syst.schema_id=syssc.schema_id
where  f.type =  'F' and p.name in ('table name')

Kindly check whether these queries are the right ones and the efficient ones.

You should become familiar with the new catalog views. The old sysobjects, syscolumns etc. have been deprecated and are only provided for backward compatibility reasons - they shouldn't be used for new development and old code should eventually transition to the new objects.

Here is a query that will get both sides of the columns involved in foreign keys:

SELECT
        constraint_name   = OBJECT_NAME(fkc.constraint_object_id),
        foreign_schema    = SCHEMA_NAME(child.[schema_id]),
        foreign_table     = child.name,
        foreign_column    = child_cols.name,
        referenced_schema = SCHEMA_NAME(referenced.[schema_id]),
        referenced_table  = referenced.name,
        referenced_column = referenced_cols.name
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.tables AS child
    ON fkc.parent_object_id = child.[object_id]
    INNER JOIN sys.tables AS referenced
    ON fkc.referenced_object_id = referenced.[object_id]
    INNER JOIN sys.columns AS referenced_cols
    ON fkc.parent_column_id = referenced_cols.column_id
    AND referenced_cols.[object_id] = referenced.[object_id]
    INNER JOIN sys.columns AS child_cols
    ON fkc.referenced_column_id = child_cols.column_id
    AND child_cols.[object_id] = child.[object_id]
    WHERE referenced.name = 'table name'
    -- if you want the tables that a child table references, use this instead:
    -- WHERE child.name = 'table name'
    ORDER BY fkc.parent_column_id;

And this one is much simpler, just for the tables involved:

SELECT
    constraint_name = name,
    foreign_schema = OBJECT_SCHEMA_NAME(parent_object_id),
    foreign_table = OBJECT_NAME(parent_object_id),
    referenced_schema = OBJECT_SCHEMA_NAME(referenced_object_id),
    referenced_table = OBJECT_NAME(referenced_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'table name'
-- if you want the tables that a child table references, use this instead:
-- WHERE OBJECT_NAME(parent_object_id) = 'table name';

EDIT adding some links to documentation (these mostly fall within the SQL Server 2008 R2 hierarchy but they're roughly equivalent for SQL Server 2008 as well):

Catalog Views
Querying the Catalog Views
Querying the Catalog Views FAQ
Metadata Functions


Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona? I develop an application that uses MySQL and explicitly uses InnoDB table engine.  In a few cases we have clients using this application with MariaDB or Percona.

I understand that MariaDB and Percona is a "drop in replacement", but my understanding is that this only means you do not have to migrate your data to switch from MySQL to MariaDB or Percona.  It says nothing about the SQL syntax being identical for applications that interface with these databases.

I am aware that the DDL for virtual columns are not identical between MySQL and MariaDB, but I'm not clear if this is a difference between InnoDB and Xtradb, or MySQL and MariaDB.

Or to put this question another way, is my applications guaranteed to run on MySQL, MariaDB and Percona so long as they are at equivalent versions and all tables are InnoDB?

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent.

MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5.

With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some features were implemented in one but not other, some were implemented in both but with some differences (GTID, virtual/computed columns, differences in optimizer, ...).

So the answer is: There should be no difference in "standart" queries between using InnoDB/XtraDB or even MyISAM as both are "under" the SQL level of MySQL/MariaDB structure. You only have to be aware of differences in syntax for new features in 5.6+/10.0+ and those are not because of the engine differences (virtual columns for example are not handled by InnoDB afaik, but by the SQL layer - permanent computed column is just another normal column for InnoDB with the higher layer supplementing the data and the non-stored variant does not "touch" the engine at all and is always computed after the engine returns the columns needed).


Added New Subscriber but Replication Not working I have one Merge Replication setup with one Publisher and 5 to 6 Subscribers. We have added one additional subscriber to this replication and when we try to sync it's giving the following error.
Error Messages

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_genhistory_75711cc468114cd2ad98d5573a252083' and the index name 'c2MSmerge_genhistory_75711cc468114cd2ad98d5573a252083'. The duplicate key value is (1334936). (Source: MSSQLServer, Error number: 1505)

Actions Performed to Resolve Issue
I tried the following actions, but it did not help resolve the issue.

Created New Snapshot, but didn't work.
Changed agent profile with verbose history/big query timeout, but still didn't work.
I found one duplicate entry in the dbo.MSmerge_genhistory table with generation=1334936. I removed one entry and tried to sync, but it still didn't work. Then I removed both entries and kept them in a backup table and tried to sync, but again same error.

Kindly suggest what to do.

Adding comment answer as answer

The solution was to remove one of the duplicate records from the MSmerge_genhistory table and then re-run the snapshot agent to generate a new snapshot.

This is because the snapshot is a series of flat files with the exported data. Removing the duplicate entry from the DB doesn't remove it from the snapshot files, only regenerating the snapshot will do this.

Another possible option is cleaning up the merge metadata first then generating the snapshot.


Installing MySQL from a noinstall Zip Archive - Should I Overwrite System Tables and Data? I am performing a MySQL upgrade on a system that had MySQL installed using the noinstall Zip archive approach. (It's installed in c:\mysql). In order to keep with this approach, we are avoiding the MySQL Installers and MSI's. More information on noinstall Zip Archives can be found here.

We were upgrading from a lower 5.5.* version to 5.5.52, and the upgrade was a success for the most part. As directed (and recommended) in this documentation, we simply copied the 5.5.52 install over the existing c:\mysql directory.

In doing so, we copied over (overwrote) everything, including the \data\mysql\* files that were in the 5.5.52 installation package, which includes the default system files, such as user table files (i.e. user.frm, user.MYD, user.MYI), and many other various tables, and artifacts.

Due to these system tables and artifacts being written over, we lost our existing database MySQL users. i.e. the only users in the table are the root users with empty passwords.

There's probably other system level details that were stored in the MySQL tables that we lost.

(Thankfully this was our QA environment, and we did take a mysqldump beforehand)

Question:

Is there a way to avoid this data loss (i.e. not copy over the data directory), and still maintain the integrity of the upgrade?

For example could we skip overwriting the data directory altogether? Or perhaps skip it, and run the mysql_upgrade tool (which we need to run anyways), though I'm not sure if this is relevant to this context.

After talking to some MySQL admins, I came to a conclusion on how to go about this. We can do the below steps:

Take a MySQL dump of all the databases
Overwrite all the installation folders, including the database folders and files
Re-apply the MySQL dump backup
Run mysql_upgrade

However, you may notice that there's no conceptual difference between the steps above, and simply not copying over the data file as part of the installation.

If you avoid copying over the data file, the steps become the following:

Overwrite all the installation folders, excluding the data folder.
Run mysql_upgrade


Public Database for Mobile Application What are the proper steps to hosting a public database. Currently I a building a mobile application with Adobe Flash Builder. I have used Microsoft SQL Server, My SQL as well as WAMP Server. All of these I have been told are private databases and thus will only allow my application to how the information as long as they are on the same internet connection. I have also been told that port forwarding my database isn't safe either.

In this case what is the best approach to making my database public or what other source should I use to make my application accessible to world wide users.

For this scenario, the database server should not be publicly exposed, for obvious security reasons.

You should expose a public interface (API) of your application, probably by using something like web services. The mobile application instances then only communicate directly with the API.

This separation (decoupling) allows for all sorts of good things to happen, such as easier scale-out/load balancing.


Entity Relationship Diagram for Hotel I tried creating an ERD model for hotel reservation and billing. Are the cardinalities correct, I'm a bit confused about that. And are there any attributes that are not in the right place? If someone could help me and go over this I would appreciate it. Thank you.

This looks mostly complete, though the BookingID field in Guest seems a little pessimistic. What will you do if a guest wants to stay at the hotel again in the future? As you already have a relation between Guest and Booking via GuestID, you can probably remove BookingID from Guest. A similar thing can be done in Bill as, if you know the BookingID then you know the GuestID. So GuestID can be removed from Bill … if you choose, of course. You don’t need to make either change.


Postgres ODBC driver versions On the Postgres ODBC driver page the name for each file is of the form:

psqlodbc_09_01_0100-x64.zip
psqlodbc_09_01_0100.zip
psqlodbc_09_01_0100-1.zip
psqlodbc_09_01_0200-x64.zip
psqlodbc_09_01_0200.zip
psqlodbc_09_01_0200-1.zip
psqlodbc_09_02_0100-x64.zip
psqlodbc_09_02_0100.zip

I'm trying to make sense of the file names, and haven't been able to find any documentation. The 09_01 is clearly the major/minor version and the x64 is pretty clear. What do the middle terms, like 0100-1 mean?

I found the following in the source code:

  This is version 07.05.0001. For those that don't know or don't
  recall how the versioning works, the major and minor number track
  the latest release of PostgreSQL that the driver is expected to work
  with. The revision number is a bit more complex. The first two digits
  are the version number, where even numbers are development versions,
  and odd are release. The last two digits are the build number.

  In other words, this driver is build 01 of development version 00, and
  is expected to work with PostgreSQL 7.5dev. :-) The leading zeros are
  an ODBC thing...

I guess one cannot get any better answer than this :)


Receiving "Query processor could not produce a query plan because of the hints defined in this query" when using FORCESEEK but can use FORCESCAN? I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total).
One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that table within a certain date range.
Seems like a good candidate for a filtered index to me.
This is my filtered index:
CREATE NONCLUSTERED INDEX IX_Filtered_Table1_DateField1
ON Table1 (DateField1, PrimaryKeyField)
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

This is my query:
DECLARE @MinPrimaryKeyId BIGINT = 2854868995

SELECT TOP 500000 PrimaryKeyField
INTO #Results
FROM Table1 WITH(INDEX(IX_Filtered_Table1_DateField1))
WHERE DateField1 >= '2/24/20'
and DateField1 < '4/14/20'
and PrimaryKeyField > @MinPrimaryKeyId
ORDER BY PrimaryKeyField ASC

When checking out the execution plan, I notice it's using my filtered index but it's defaulting to an Index Scan operation instead of an Index Seek.
If I try try to use the FORCESEEK query hint (in addition to my index hint), I get the classic error:

Query processor could not produce a query plan because of the hints
defined in this query

Why would the query processor be unable to generate an execution plan using a Seek operation on this index when it can do a Scan operation with it? (If I switch out my FORCESEEK query hint with a FORCESCAN it works, which I know is no different than not using a hint at all here.)

SQL Cannot "Seek" as the order of this index is by date first and then by PrimaryKey. As you are selecting with a date that match the full content of the index and as the PrimaryKey are not ordered (they are but only within each specific date), SQL have to scan it.
Try to change your index for this one:
CREATE NONCLUSTERED INDEX IX_Filtered_Table1_PrimaryKeyField
ON Table1 (PrimaryKeyField)
WHERE (DateField1 >= '2/24/20' AND DateField1 < '4/14/20')

Sql Should then be able to use it (as the date match) and seek into the PrimaryKeyField (and the hint will probably not be required as this will probably what SQL will decide to do anyway)


Are there any unforseen down sides to using views to abstract away from cryptic table names? We have to report against a very large and complex vendors HR/Payroll database which has cryptic table names such as A100M, C755V, X50AA etc.

In order to make writing queries more human friendly, I have suggested we create views with meaningful and descriptive names to abstract away from the cryptic table names using very simple views such as:

CREATE VIEW Employees
AS
SELECT
   *
FROM dbo.Y654AS

So I can then simply write a query such as:

SELECT
    EmployeeId
    ,Forename
    ,Surname
FROM dbo.Employees

Other than the vendor changing the table names in an update (which is very unlikely), are there any gotchas with doing this which I may have missed?

For the use you describe, using synonyms for the tables should be a better solution.  A synonym is basically an alias for an object name, so you wouldn't need to define a SELECT statement as is required for a view.  Basically the only way a synonym can break is if the table is dropped.


Select Which Database Mail Profile is used in SQL Server 2008 R2 I am confused about Database mail profiles in SQL Server 2008 R2. When setting up severity notifications as well as "notify operator tasks" in maintenance plans, what determines what mail profile is used?

Since "operators" are part of SQL Server Agent, I'm guessing it'll use whatever mail profile you've configured that to use.

To review/change it, right click SQL Server Agent in Management Studio, and choose Properties. Go to the "Alert System" page, and look for the "Mail profile" drop-down menu near the top. You may also have to check "Enable mail profile", and set "Mail system" to "Database Mail" if you haven't already.


How to link several tables? I'm a newbie at Database Designing. What I want to do is perform several actions at once.

I have a main users table which holds all the data for a user at a website. Then I have a primary key as the user_id in this table. This user_id is also the referred in various other tables.

So, what I want to do is connect all those table data to the main table so that when the main user is deleted from the users table then all the data related to that user is also deleted. How can I do that?

I know there is a function of foreign keys somewhere but I'm a really amateur programmer.

You may find this question of value: https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete

Highlights:

Your tables should be set up with foreign keys with DELETE CASCADE.

Example:

CREATE TABLE categories (
    id int unsigned not null primary key,
    name varchar(255) default null
);
CREATE TABLE products (
    id int unsigned not null primary key,
    name varchar(255) default null
);

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE ON UPDATE CASCADE
);


School Database Schema Im working on a simple school system which handles student registration and schedule. Furthermore, the system should handle different type of schools Such as Kindergarten, Primary, Secondary, and High (Pre-school and k12)..
Im not an expert in DB design but still follow what i can from what i learn through reading and practice.

Student
Parent
Student_parent (If there is more than one parents want to be in the system)
School (Where the system can check student belong to which level (Primary or Secondary ..etc))
Subject (All the subjects in the schools)
Class (Basically Schedule-timetable)
Classroom (Every room and lab in the school (Basically facilities of the school))
Attendance (Not yet)
Marks (Not Yet)

are the tables relationships sufficient or need to redesign it?
Any problem with this basic schema?
Is it sufficient for the system?
How to Implement terms (Semester 1 and semester 2) as well as Years?
What about when the year ends. How to transfer students to a new year (How to implement this)?
I hope to get some points on improvements or issues with the schema before start programming it.
Thank you

.
.
Edit: implementing John Herbert suggestions.
.
.
Implementing John points except of the last one because there are no departments in the school and the number of students really subjective to the year.

changed tables names with prefixes to group them.
Modified few field as suggested by John for better search and grouping
Added table term and connected it with school (KG- Primary- Sec..etc)
Modified field sizes from Int(11) to something smaller as needed

DB Schema After Editing

after implementing these things someone cant help but wander. Will i need to add indexes for performance in the future?
Where will indexes possibly be needed ?
I hope this can benefit someone who is interested in DB design.

All in all I think your Key Relationships are straightfoward enough and clear enough that I could easily find my way. You'll probably find a few places to change as you mock-up and eventually code your stored procedures and other processes. Here are my thoughts as I looked through it.

Replace parent with something more generic, like guardian. This use of language opens the door for other authority figures for the student, such as grandparents and adopted parents, as well as allow for a flag that indicates the person is an emergency contact or is allowed to pick the child up.
Separate the field [Full_Name] into two fields; [Given_Name] and [Surname]. This helps when sorting by name during programming. (It's better to have the two Williams sisters show up next to each other on a report than all the Mary's of the school grouped together).
Suggest moving the [term] field under the [subject] table into the [class] table. This field can be used to indicate if Bobby Tables was in that class during the Spring of 2010 or the Fall of 2009, with that specific teacher in a specific room. I would have another table that had the date ranges for those terms. (As a side note, INT(11) may be a little overkill for it. A hundred years of one-week terms would only really result in 5000 terms, with would only need INT(4))
On the subject of promoting students to the next year, it could be done with a new field under the [student] table, with a simple INT(2) to indicate the year of their education. A stored procedure that is run at the end of the school year could check their credits earned and move them up.
A consideration can be made also to include in the [subject] and [teacher] tables would be a department, such as Math, Science, or English. This could be used to help group together all the Science teachers, or show all the History classes that are available that term, especially if the school is large enough to have more than one session of a particular class.


SQl code performance What measurement do you know if this sql code is efficient based on execution plans?
How do you know if this sql code is efficient based on execution plans?

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers]
WHERE [Country] = 'Germany'
ORDER BY [CompanyName]

SELECT [CustomerID], [CompanyName], [City], [Region]
FROM [Northwind].[dbo].[Customers]
WHERE [Country] = 'Germany'

If you're asking why one of these plans is more expensive than the other one:

Your first plan performs a sort on a column that is not indexed (or not part of the index that is being used to retrieve the data). Therefore once the data is retrieved, because you've asked for an ORDER BY, the results have to be re-sorted (since the clustered index will return the data in a different order).

An index that will help this specific query with the sort be more efficient might be (and I'm not suggesting this would be ideal):

CREATE NONCLUSTERED INDEX cou_com ON dbo.Customers(Country, CompanyName)
  INCLUDE (CustomerID, City, Region);

But please don't go create that index without much more proper investigation. An index might help this one query but if your system is DML-bound (meaning more write activity than read activity) that could just make it worse. If this query is not slow and you are just concerned that it has a higher relative cost than some other query, this is not where you should start optimizing IMHO.

If you're asking generically how to analyze execution plans:

This is not really the purpose of the site. For some resources (books and e-books) on performance tuning in general, I would check out this question and its answers. In particular, Grant Fritchey's free e-book SQL Server Execution Plans.

You may also be interested in the free SentryOne Plan Explorer. It presents a lot more information - information that, in Management Studio, is hidden in Properties panels, incorrect in some cases, or simply not available at all. I blogged about how to present the benefits of this tool to other users, but it could be a tutorial of sorts as well.


Availability Group Replica delay looking for ideas for diagnosing the root cause of the following issue. We have 3 availability nodes Primary,Synchronous replica, Asynchronous replica. no load other than T-Log backups exists on the Synchronous replica. At a certain time all replicas start lagging behind. The lag increases over a period of 6 hours. Data is still being written but very slowly. Suddenly the replicas start catching up and are back up to date within a period of around 30 minutes.
No errors in any of the SQL Server Error logs.
No blocking of the DB STARTUP process or any of the redo processes on either of the replicas (one has no users on it)
HADR_DATABASE_FLOW_CONTROL has moved into the top waits for the Primary
On Secondary top waits are normally
PARALLEL_REDO_WORKER_WAIT
REDO_THREAD_PENDING_WORK
PARALLEL_REDO_TRAN_TURN

During the issue this stayed much the same other than
PARALLEL_REDO_FLOW_CONTROL existed.

No evidence of a long running large transaction on the primary. Given that all replicas were affected it points to a problem with the primary. No index maintenance type jobs running. Can anyone think of any ddl,dml operations or other task that could cause this type of behavior?
thanks

Environment is all SQL2016 CU9, disks are local on all machines

Considering the presence of high HADR_DATABASE_FLOW_CONTROL waits on the primary, and the combination of PARALLEL_REDO_FLOW_CONTROL and PARALLEL_REDO_TRAN_TURN waits on the secondary, it sounds like parallel redo might not be a good fit for your workload.

  Can anyone think of any ddl,dml operations or other task that could cause this type of behavior?

Microsoft calls out one example in their blog post on parallel redo:

  PARALLEL_REDO_TRAN_TURN - Only happens in a readable secondary replica when new insert triggers page-split system transaction, or record update in a heap table generates a forwarded record

If your workload results in lots of page splits or forwarded records, then you should consider disabling parallel redo on the secondary by turning on documented trace flag 3459 (note: as a permanent solution, you will want to set this as a startup trace flag in SQL Server Configuration Manager):

DBCC TRACEON (3459,-1);

If you need to revert back to using parallel redo on your build, you will need to restart the SQL Server service after disabling the trace flag.  This was fixed (to not require a restart) in SP1 CU10 and SP2 CU2.  It doesn't look like it's fixed in the RTM branch.  See this KB for details: https://support.microsoft.com/en-us/help/4339858/fix-parallel-redo-does-not-work-after-you-disable-trace-flag-3459-in-a

More broadly, there are several fixes for issues with parallel redo in SP1 and SP2.  You should consider patching SQL Server to see if that resolves your issue as well.

If you want to confirm that page splits or forwarded fetches are actively occurring, try running sp_BlitzFirst:

EXEC dbo.sp_BlitzFirst;

If you want to confirm that your heaps have lots of forwarded records, you can use a DMV query like this one (note: replace Your_Table_Name with the name of a heap in your database):

SELECT
    OBJECT_NAME(object_id) AS table_name,
    forwarded_record_count,
    page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'Your_Table_Name'),
    DEFAULT,
    DEFAULT,
    'DETAILED'
);


SQL query for finding a set of columns that create a unique key for a table I have a table with a dozen or so columns and no primary key defined for it. There are several million rows stored in it. Is there a SQL query I could use that would return the combination of columns that will give a unique key for the table? It is specifically an Oracle database.

First, it seems like the wrong way of approaching the problem. I would start by investigation what is supposed to be unique according to the business rules.

Nevertheless, you will have to investigate the power set of column combinations. The cardinality of the power set on n is 2^n, but the empty set can be ignored. So if you have 10 columns this means 2^10-1=1023 combinations to investigate. It's probably easiest to use a host language to generate the sql, but you can use group by cube to get the combinations. Assuming columns c1, c2, c3:

  with t(a,b,c) as ( values ('c1','c2','c3') )
  select a,b,c
  from t group by cube (a,b,c)

A  B  C
-- -- --
-  c2 c3
-  -  c3
-  c2 -
-  -  -
c1 c2 -
c1 -  -
c1 -  c3
c1 c2 c3

Now you can generate the sql by looping over this result set:

select c2,c3 from t group by c2,c3 having count(1) > 1

if no rows exists the column combination is a candidate for a key. Just verify that no sub set of this combination is also a candidate.


Poor SELECT performance on recheck cond with data and large (millions of rows) partial index Query:

EXPLAIN (ANALYZE, BUFFERS) SELECT
    COUNT (*) AS "count"
FROM
    "Posts" AS "Post"
WHERE
    "Post"."createdAt" > '2015-08-19 14:55:50.398'
AND "Post"."new" = TRUE;

Index:

CREATE INDEX posts_new_createdat_idx ON "Posts" ("createdAt")
WHERE
    NEW = TRUE

Plan:

Aggregate  (cost=389915.59..389915.60 rows=1 width=0) (actual time=4234.772..4234.773 rows=1 loops=1)
  Buffers: shared hit=254427
  ->  Bitmap Heap Scan on "Posts" "Post"  (cost=14415.81..387990.63 rows=769985 width=0) (actual time=123.805..3859.150 rows=1138854 loops=1)
        Recheck Cond: (("createdAt" > '2015-08-19 14:55:50.398+00'::timestamp with time zone) AND new)
        Rows Removed by Index Recheck: 8238790
        Buffers: shared hit=254427
        ->  Bitmap Index Scan on posts_new_createdat_idx  (cost=0.00..14223.32 rows=769985 width=0) (actual time=122.601..122.601 rows=1138854 loops=1)
              Index Cond: ("createdAt" > '2015-08-19 14:55:50.398+00'::timestamp with time zone)
              Buffers: shared hit=3114
Total runtime: 4234.989 ms

Schema:

CREATE TABLE "public"."Posts" (
    "id" int4 NOT NULL DEFAULT nextval('"Posts_id_seq"'::regclass),
    "actionId" int4,
    "commentCount" int4 DEFAULT 0,
    "facebook" bool,
    "featurePostOnDate" timestamp(6) WITH TIME ZONE,
    "forcedPrivate" bool,
    "instagram" bool,
    "isReported" bool,
    "likeCount" int4 DEFAULT 0,
    "note" text COLLATE "default",
    "photo" varchar(255) COLLATE "default",
    "private" bool,
    "new" bool,
    "popular" bool,
    "twitter" bool,
    "userId" int4,
    "objectId" varchar(255) COLLATE "default",
    "createdAt" timestamp(6) WITH TIME ZONE,
    "updatedAt" timestamp(6) WITH TIME ZONE,
    "activityLogId" int4,
    "weightLogId" int4,
    "workoutId" int4,
    "workoutLogId" int4,
    "thumbnail" varchar(255) COLLATE "default"
)

Data:

new = true for 99% or records
Any posts older than 2 weeks can be ignored (from the count and the index)

Database Details:

Host           = Amazon AWS
Engine         = PostgreSQL 9.3.10
Instance Class = db.r3.8xlarge
Storage Type   = SSD
IOPS           = 3000
StorageAmount  = 500 GB

As you can see, the partial index is very large. Is there a way to better index so that the recheck condition is not so heavy?

Add the second predicate of your query to the partial index as well:

WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398'

Your timestamp is probably a moving target, but I am going to assume you have lots of old rows that are excluded in most of your queries and only few "younger" rows are of interest. A typical use case. You can cut off old rows in your partial index.

CREATE INDEX posts_new_createdat_idx ON "Posts"("createdAt")
WHERE "new"
AND   "createdAt" > '2015-08-01 00:00';  -- replace with useful timestamp

This only makes sense if you can reduce the size of the index to a fraction. Else, the effect is typically not worth the trouble.

Replace with an opportune timestamp to cut off as many rows as possible while still covering relevant rows for your queries.

Modern versions of Postgres are smart enough to understand the index is applicable to queries cutting off at a later timestamp. For older versions you have to add the verbatim index condition redundantly to make the query planner use this partial index.

This index will be used by your query immediately as is. (I just removed some more noise):

SELECT COUNT(*) AS "count"
FROM  "Posts"
WHERE "createdAt" > '2015-08-19 14:55:50.398'
AND   "new";

Related:

Add datetime constraint to a PostgreSQL multi-column partial index
"Recheck Cond:" line in query plans with a bitmap index scan

Aside 1: "new" = TRUE is just a noisy way of stating "new" for a boolean value.

Aside 2: Don't use reserved words or CaMeL case or other illegal identifiers so you don't have to double-quote all the time. Very error prone.


MySQL upgrading from 5.7 to 8 - Throwing strange error and won't start up I followed the instructions on the MySQL web site and got MySQL server 8 mostly installed but it won't launch because it fails pretty quickly during the upgrade process. The apt-get portion installed all the files. But when it launches this is what is added to my error.log file and I have no clue what to make of it and Google is not being helpful.

2018-09-07T12:25:54.281373Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 3662
mysqld: /export/home/pb2/build/sb_0-29394547-1530206990.8/release/mysql-8.0.12/sql/dd/upgrade/table.cc:1075: bool dd::upgrade_57::add_triggers_to_table(THD*, TABLE*, const String_type&, const String_type&): Assertion `t->get_event() >= t_type && (t->get_event() > t_type || t->get_action_time() >= t_time)' failed.
12:27:19 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=300
thread_count=1
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 134902 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x55ae33b103a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f082a824da0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char*, unsigned long)+0x3d) [0x55ae31bc258d]
/usr/sbin/mysqld(handle_fatal_signal+0x423) [0x55ae30dfcde3]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890) [0x7f083e593890]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7) [0x7f083c98fe97]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141) [0x7f083c991801]
/lib/x86_64-linux-gnu/libc.so.6(+0x3039a) [0x7f083c98139a]
/lib/x86_64-linux-gnu/libc.so.6(+0x30412) [0x7f083c981412]
/usr/sbin/mysqld(+0x1abc55b) [0x55ae31b8155b]
/usr/sbin/mysqld(dd::upgrade_57::migrate_all_frm_to_dd(THD*, char const*, bool)+0x562) [0x55ae31b82042]
/usr/sbin/mysqld(dd::upgrade_57::fill_dd_and_finalize(THD*)+0xf2) [0x55ae31b554f2]
/usr/sbin/mysqld(+0xde1e96) [0x55ae30ea6e96]
/usr/sbin/mysqld(+0x1f920af) [0x55ae320570af]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db) [0x7f083e5886db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7f083ca7288f]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2018-09-07T12:27:20.027024Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.12) starting as process 4161

I know this is all that is being added because I am adding a few blank lines to the log just before trying to launch MySQL and this code is all that comes up after my injected blank lines.

I am running Ubuntu 18lts server. MySQL 5.7 rand fine and my database had no errors because I checked it before trying the upgrade.

I suspect this may be caused by a bug that happens when in-line upgrades go from 5.5 to 5.6 to 5.7 then finally 8.0.

The suggestion to deal with this is to run optomize table on every single table while in version 5.7. Once the 8.0 upgrade is attmpted, it is too late because you cannot go back.

I am giving up, rebuilding this server from scratch, then install 5.7 and perform the optomize table then try the upgrade to 8.0 again.

Good thing I have another 5.7 server running and a full backup of all the data.


Can't create new tablespace for PostgreSQL data. Permissions denied For some reason I don't seem to be allowed to set my PostgreSQL data folder to a drive other than the C drive (Windows 8 machine).

1) If I try to do so when installing PostgreSQL, the installation fails. Therefore I need to choose the C drive to make installation work.

2) If I try to move the data folder later on (https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows), PostgreSQL service stops running.

3) Finally, if I try to create a tablespace in the D drive, I am not allowed to do so.

When I run the following query:

CREATE TABLESPACE drive_D
OWNER postgres
LOCATION 'D:\PostgreSQL\data'

I get the following error:

ERROR:  could not set permissions on directory "D:/PostgreSQL/data": Permission denied
SQL state: 42501

I am an admin on that machine and I have made sure that I have 'full control' on the D drive.

I also followed advice given on this link "How to create a tablespace on Windows 7 for Postgresql 9.2?". I gave 'NETWORK SERVICE' full control on the D drive, but that didn't solve the problem.

For some reason I don't seem to be allowed to change my PostgreSQL data folder to a drive other than the C drive.

This is not the correct way to do that anyway.  Creating a tablespace and moving the whole cluster are different things.

  I am an admin on that machine and I have made sure that I have 'full control' on the D drive.

It doesn't matter who you are, what matters is who is the service account running PostgreSQL, and what permissions that account has.


Extended Events Login audit - Reduce login noise We are looking for the optimal sql login audit solution, with usable filtering.
Thus far we have looked into Server Audits and profiler but have concluded that extended events
look most promising, unless anyone can show us otherwise.
Question is, is there any good way to filter unnecessary login events? e.g. when I connect using SSMS, immediatly about 10 login events are added and continue to pile up if I leave SSMS connected to a instance. Why is that?
This is our current implementation:
USE master;
GO
-- Create the Event Session
IF EXISTS
    (
        SELECT
                  *
            FROM  sys.server_event_sessions
            WHERE name = 'ServerLoginAudit'
    )
    DROP EVENT SESSION ServerLoginAudit ON SERVER;
GO
EXECUTE xp_create_subdir 'D:\audits\Sessions';
GO
CREATE EVENT SESSION ServerLoginAudit
    ON SERVER
    ADD EVENT sqlserver.login
        (SET
             collect_database_name = (1)
           , collect_options_text = (1)
         ACTION
             (
                 sqlserver.sql_text
               , sqlserver.nt_username
               , sqlserver.server_principal_name
               , sqlserver.client_hostname
               , package0.collect_system_time
               , package0.event_sequence
               , sqlserver.database_id
               , sqlserver.database_name
               , sqlserver.username
               , sqlserver.session_nt_username
               , sqlserver.client_app_name
               , sqlserver.session_id
               , sqlserver.context_info
               , sqlserver.client_connection_id
             )
        )

    ADD TARGET package0.event_file
        (SET filename = N'D:\audits\Sessions\ServerLoginAudit.xel', max_file_size = (20), max_rollover_files = (2))
    WITH
        (
            STARTUP_STATE = OFF
          , TRACK_CAUSALITY = ON
        );
/* start the session */
ALTER EVENT SESSION ServerLoginAudit ON SERVER STATE = START;
GO

Reason for the numerous connections
Those are connections caused by SSMS to provide you mainly the visual structure of object explorer. Also, When you're using SSMS, each new tab you open is a different session (and more connections - see The multiple connections of SSMS). IntelliSense also creates extra connections as you'll see if you remove the predicate on the query I suggested as a workaround.
try connecting with sqlcmd Utility like this on a CMD prompt:
sqlcmd -A

When I connected with SQLCMD only one log was reported on the Extended Event Session you provided.
Workaround
Instead of filtering the data you collect I suggest you filter the data you query afterwards, here's one way to do that:
WITH
    event_data AS (
        SELECT CAST(event_data AS XML) AS event_data_xml
        FROM fn_xe_file_target_read_file('D:\audits\Sessions\ServerLoginAudit*.xel',NULL,NULL,NULL)
    )
    , event_data_fields AS (
        SELECT
             DATEADD(ms, -DATEPART(ms, event_data_xml.value('(/event/@timestamp)[1]', 'datetime')), event_data_xml.value('(/event/@timestamp)[1]', 'datetime')) AS interval
            , event_data_xml.value('(/event/data[@name="database_name"]/value)[1]', 'varchar(250)') AS database_name
            , event_data_xml.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(250)') AS client_app_name
            , event_data_xml.value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(250)') AS client_hostname
            , event_data_xml.value('(/event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(250)') AS server_principal_name
            , event_data_xml.value('(/event/action[@name="nt_username"]/value)[1]', 'nvarchar(250)') AS nt_username
        FROM event_data
    )
SELECT
      interval
    , database_name
    , client_app_name
    , client_hostname
    , server_principal_name
    , nt_username
FROM event_data_fields
WHERE
    client_app_name NOT IN ('SQLServerCEIP', 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
GROUP BY
      interval
    , database_name
    , client_app_name
    , client_hostname
    , server_principal_name
    , nt_username;

With that method, if you don't find the info you seek at first, you can reduce the predicates to show all data collected and have a more accurate report of all the connections that happened at the time. Also, if you filter the data you collect by the application name, it would leave a breach on your audit because one can Change the Application Name of SSMS and login to your server unnoticed.


TSQL Solution to backup ALL databases on ALL instances of a server? I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But I've thought a lot of things were impossible with computers so hopefully I can solve my problem in a different way.

I've already created a PowerShell solution that uses SMO to backup all databases on all instances, but due to security reasons I'm unable to have that solution work without making the account for the proxy that runs the Agent job a local administrator on the server.

As an alternative I wrote a T-SQL script that will be ran as an Agent job and take either full, differential, or transaction backups on all databases in a SQL Server instance every 15 minutes.

    DECLARE @sql nvarchar(MAX) = N'';

--Check time for full backup
IF (DATENAME(weekday,getdate()) = 'Sunday' AND convert(time,getdate()) > '00:00' and convert(time,getdate()) < '00:15')
    BEGIN
        SELECT @sql += 'BACKUP DATABASE ' + QUOTENAME(name)
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__'
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.bak'''
                    + ' WITH COMPRESSION
'
        FROM sys.databases
        WHERE name <> 'TempDB';
    END
--Check time for differential backup
ELSE IF (convert(time,getdate()) > '00:00' and convert(time,getdate()) < '00:15')
    BEGIN
        SELECT @sql += 'BACKUP DATABASE ' + QUOTENAME(name)
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__'
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.diff'''
                    + ' WITH COMPRESSION, Differential
'
        FROM sys.databases
        WHERE name <> 'TempDB' and name <> 'Master';
    END
--Take transaction log backup
ELSE
    BEGIN
        SELECT @sql += 'BACKUP LOG ' + QUOTENAME(name)
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__'
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.trn'''
                    + ' WITH COMPRESSION
'
        FROM sys.databases
        WHERE name <> 'TempDB' and name <> 'Master';
    END
print @sql;
exec sp_Executesql @sql;

This script works fine, however it only backs up ALL databases on ONE instance.
The goal is to backup ALL databases on ALL instances. An even better solution is to backup all databases, on all instances, and on all servers. From my current knowledge it seems like there are three solutions, (hopefully more based on answers)

Use this script in an Agent job on every instance. This will work
but just seems like such an ugly solution.
Figure out how to give the Agent proxy the minimum amount of
permissions to successfully run the PowerShell script which is instance aware
Look into creating a master server. I'm not familiar with this option but I know the master server is aware of other servers, and i may be able to get a list of all databases on all servers.

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among other things).

The question is though: Is there a reason why you want to back up all databases on all instances from one server? Having all your backup eggs in one basket means that none of your backups get done for as long as that master server (or at least SQL Server Agent) is offline. Having backup jobs on each server is the standard for good reason (regardless of how they're administered).

Rather than writing your own backup statements, I highly recommend using Ola Hallengrens Maintenance Solution (the whole maintenance package is worth using). It just works, and works very, very well. It gives you options you won't have thought of, but will come to need in the future.

Armed with that script, you can iterate through a list of your instances, and execute MaintenanceSolution.sql (which you downloaded from Olas site) against each server. I've done this using a batch file that uses SQLCMD with a trusted connection to each server, and outputs results to a CSV for each server (assuming you've got privileges on each instance). If you use the Registered Servers view in SSMS, you can also execute that script against all servers in a given group at once.

Outside of that, you're looking at something like redgate SQL Backup Pro (I've no affiliation with them), Idera SQL Safe Backup (again, no affiliation) to do what you want, but those products do cost money outside of their respective trial periods.

Either of these solutions will give you far greater safety than scheduling backups for all databases on all servers from one instance.


Why is there a difference in the character limit for VARCHAR2 in SQL and PL/SQL? While working with some large scripts I encountered errors related with the 4000 character limit that VARCHAR2 has in SQL but not in PL/SQL.

As explained here the VARCHAR2 datatype has a 4000 character limited, while the PL/SQL has a much larger limit of 32,767.

Why does that difference exist? Wouldn't have been better to have both VARCHAR2 with the same limit? It seems a rather confusing and prone to error decision.

Update

I updated my question to add ansible's comment. Oracle VARCHAR2 and PL/SQL VARCHAR2 limit are on bytes, not in characters. This can have significant impact in multibyte characters system.

It is just a fact of life.  Different history, different beliefs, different goals.

Your might find this post on PL/SQL and its goals helpful in seeing some of the differences:
http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/03_types.htm

It amusingly starts with:

  Like--but oh how different! --William Wordsworth

There are ANSI Standard data types, but I believe that VARCHAR2 is an Oracle standard, not a part of ANSI.  It appears from history that Oracle's VARCHAR2 was designed to overcome the limitations of their earlier VARCHAR implementation.

Having said that, practically every SQL dialect has some unique datatype that is not accepted by all other SQL dialects. (I primarily work with MS SQL Server, but do at times need to absorb data from other systems.)

Fortunately there are a number of online resources to help you in mapping one datatype to another as you move between the products of multiple vendors.  Usually it is not too difficult.  Note: it is not only strings (such as CHAR,VARCHAR) that can have conflicting definitions.


Help with design of sales database Disclaimer: I am a beginner in the field of database.

I am trying to design a database in PostgresQL which will record the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM approach.

Here is the problem with the design.

This is the simplified description of my schema.

I have a Clients table, an ItemDetails table and a RateChart table, among others.

The Clients table records details for each client, including their address.

The ItemDetails table records details for each item we sell to our clients, including a short description for the items. We do update the description from time to time.

The RateChart table has the different rates for items for each client.

Now, I also have to save the details of each and every sale. So, I have an Invoice table, and InvoiceItems table.

The Invoice table saves the invoice number (primary key), the date, the total amount of the order and the client name(foreign key, refers to client name in client table).

The InvoiceItems table, on the other hand, records the items sold, the quantity of each item sold and the rate at which it is sold.

Now, here's the problem.

The items sold in InvoiceItems list is a reference to the ItemDetails table where details of all the items are saved. Now, the details of items are changed from time to time. For example, the description of items are changed. So, whenever I change those item details, those changes will cascade down to this InvoiceItems table, and will make the older invoice records erroneous, because that was not the description when the product was sold. Same problem lies with the Client field in sale table. Whenever I change an attribute any record in the client table, the changes will cascade down to the sale table.

Here is the schema for illustration.

Now, the solution I have thought of is highly inelegant. I have considered saving the client details for every sale in the Invoice table, and the item details in InvoiceItems. Of course, that would be a great deal of duplicate data.

Another solution might be to keep the defunct details of items and clients, which would mean polluting my ItemsDetails and Client table with records no longer valid. Of course, I can add a boolean IsValid column, but then again, the solution does not seem to be succinct.

Any help would be heartily appreciated.

You have mentioned the two common solutions to this use case - point-in-time capture and history tracking.

Of the two, point-in-time capture is the more robust and easier for audit purposes. You capture exactly what was presented to the purchaser at the time they agreed to the sales contract. There is no argument about subsequent data creep. I have worked on a couple of enterprise implementations which adopted this approach. As a mental sop you can justify the duplication of values by saying the two tables have different semantics - the source is what is available and the destination is an event capture.

With history tracking you have to be absolutely rigorous ensuring every update or insert by every user, application, utility, ETL job, batch load and whatever, always, for the entire life of the system, sets the "from" and "to" dates, and nothing ever, ever deletes anything. This is hard (although temporal tables make it easier).


Help with Cybernetics SAN Configuration There are tons of articles out there recommending RAID/ LUN/ drive configurations for SQL Servers, but most leave me still questioning as they usually state "it depends" and for this reason I'd like to provide some specifics and get some more direct recommendations:
I have a client who already installed SQL Server on a Cybernetics SAN and here are the specifics:

•   SQL Server 2008 R2 installed on a virtual server
•   Storage:
 o       Cybernetics miSAN-D8/T16 SAN iSCSI storage appliance
 o       4+1 GbE ports
 o       8 GB SAN controller cache
 o       USB and eSATA ports
 o       Two miniSAS device
 o       8 drive bays all filled with 600 GB SATA-II 7.2K drives (5.4 TB total)
 o       All 8 drives set up in RAID 6
 o       Three logical drives:
       1. C (156 GB) – for system and apps (IIS, web app, SQL, SSIS, CA ArcServe backup app, VIPRE antivirus)
       2. E (1.64 TB) – for data, logs and tempdb
       3. F (1.80 TB) – for backups

The SQL Server is more read intensive, with data bulk loaded nightly like a data warehouse and the web app used for looking up and reporting on the data.
Periodically the client experiences sluggishness, sometimes severely, for short periods with the web application.  I’ve also noticed Brent Ozar’s Blitz script reveals slow writes to the E drive at times.
I am wanting to suggest the client reconfigure their SAN as follows:

C (  156 GB, raid 1) – for system and app files.
D ( 1200 GB, raid 10/5) – for sql data files.
L (  444 GB, raid 1) – for log files.
T ( 1200 GB, raid 5) – for tempdb data files.
Backup to a different san or network location and archive to tape.

But at the very least I believe the client should do the following:

Move backups to a different SAN or network location, in order to free up the F drive for SQL use as well as for better DR.
Move log files and tempdb data files to the F drive.

Suggestions?

SQL Server 2008 R2 installed on a virtual server

IMO, the SQL Server VM should live with the rest of the VMs. If this device goes kaput (the Cybernetics website says nothing about the number of controllers in this model range, so presumably there's only one), you don't want all your eggs in one basket. Hopefully there are other, better protected devices available.

  The SQL Server is more read intensive, with data bulk loaded nightly like a data warehouse and the web app used for looking up and reporting on the data.

You haven't said how big the data warehouse is or what the load time SLA is, but I strongly suspect RAID 5/6 is not going to be appropriate for that, particularly since 7.2k RPM drives are involved. High drive latency combined with RAID 5/6 is two feet in the grave for write performance.

The client can also look at optimizing the bulk loading process to be more efficient. It's likely there's inefficiencies in this area.

  Move backups to a different SAN or network location, in order to free up the F drive for SQL use as well as for better DR.
  Move log files and tempdb data files to the F drive.

Move the backups for DR: Yes, definitely. If there's space available on another device, use it.

Since the storage is all a big pool of RAID 6, the performance characteristics will be the same regardless of which logical volume is used, so I don't see any point in moving things around unless more space is needed.

  I am wanting to suggest the client reconfigure their SAN as follows:

Aside from moving the backups off this SAN, this configuration will probably make things worse:

Using RAID 1 for a single database log file is fine, but as soon as you have more than one log file, that's random access, and performance will nosedive.
Reads from and writes to data files were distributed over the 8 drives before, now the same load will be on 3 drives.

I would recommend using all 8 drives in one pool of RAID 10, which is the only RAID level that makes sense given the workload and the available hardware, and this is only 1 drive less usable space than your proposed configuration. There's just not enough wiggle room to micromanage the physical pools here. The whole thing will need to be redone if the initial sizing is wrong, or if the sizing changes drastically over time. Using one pool gives way more flexibility.

Now having said all of this, depending on how big the data set is, it's usually a really good ROI to buy more RAM so that SQL Server needs to do fewer physical reads in the first place, thus increasing performance. But that won't help with the write workload, of course.


SQL Server Connection property applicationIntent I have a doubt in ApplicationIntent connection property of SQL Server.
The doc say it has two values ReadWrite and ReadOnly. If I set the value as ReadOnly, will it allow only read queries or will it also support update queries to SQLServer?

I am asking this ,keeping read only routing in AvailabilityGroups of SQLServer in mind!

Hopefully the documentation for the SQL Server Native Client clears things up for you, specifically this section on Specifying Application Intent

  The keyword ApplicationIntent can be specified in your connection string. The assignable values are ReadWrite or ReadOnly. The default is ReadWrite.

  When ApplicationIntent=ReadOnly, the client requests a read workload when connecting. The server enforces the intent at connection time, and during a USE database statement.

Since you mentioned that this is in the context of Availability Groups (AGs), specifying an intent of ReadOnly will connect you to the readable secondary in your availability group (as long as you are connect to the listener, and you're using Enterprise edition).

The enforcement, then, is due to the fact that the secondary is read-only - you can try to perform data modifications through this connect, but they will fail.

Note: for more complex setups, with multiple readable secondaries, you can Configure read-only routing as well


Number Sequence With Gaps In an Oracle PLSQL function I want to generate a special kind of sequence number. It starts with 00000001, but when 00899999 is reached, I want to have 01000001 as its next number. This pattern repeats when 01899999 is reached: The next number then should be 02000001.

To put it another way: My goal is to skip the numbers in the sequence in the range between ##900000 and ##999999, where ## denote two digits.

How can this be done on an Oracle database?

If you're looking for an algorithm:

Add 1 to previous number;
if (current number + 100000) % 1000000 = 0, add 100001 to current number.

So:

899998 + 1 = 899999; (100000 + 899999) % 1000000 = 999999; return 899999.
899999 + 1 = 900000; (100000 + 900000) % 1000000 = 0; 900000 + 100001 = 1000001; return 1000001
1000001 + 1 = 1000002; (100000 = 1000002) % 1000000 = 100002; return 1000002
...
1899998 + 1 = 1899999; (100000 + 1899999) % 1000000 = 999999; return 1899999
1899999 + 1 = 1900000; (100000 + 1900000) % 1000000 = 0; 1900000 + 100001 = 2000001; return 2000001
and so on.

One implementation:

CREATE TABLE myNumSeq (lastNum int); INSERT INTO myNumSeq (0);
Create a function, fn_GetNextNum(), that works something like this:

DECLARE @theNum int;
BEGIN TRANSACTION;
SELECT TOP 1 @theNum = lastNum + 1 FROM myNumSeq FOR UPDATE;
IF (@theNum + 100000) % 1000000 = 0
BEGIN
    SET @theNum = @theNum + 100001;
END;
UPDATE myNumSeq SET lastNum = @theNum;
COMMIT TRANSACTION;
RETURN @theNum;

Add error handling as appropriate. I haven't used Oracle regularly in about 18 years, so please consider the above pseudocode. I leave the Oracle-specific details (can't recall if it has both procedures and functions, or the return mechanism used for something like this if it only has procedures) to someone familiar with that.

I'm sure this could be written more concisely, but for single-record additions it should work fine. If you're adding records in bulk, using a variation on a number table would probably be quicker than a function.


Convert to CTE or Window function or a set or a merge statement Is anyone knows how to convert the below code using a CTE or Window function:

UPDATE a
SET a.linked_pmt_id = b.linked_pmt_id, a.looptag = '1Mb-NBCBZ'
FROM tblname_KS a
    JOIN (SELECT * FROM tblname_KS with (NOLOCK) WHERE pmt_id = linked_pmt_id) b
    ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND
       ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND
       a.prop_city = b.prop_city
WHERE   a.linked_pmt_id IS NULL AND
        (a.status <> 'U' AND  a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
        a.prop_city IS NOT NULL AND
        a.prop_zip IS NULL
GO

Thanks

Without knowing your RDBMS, and with the very vague description you're giving, here's my initial approach.

It's basically taking your Subquery and converting it to CTE.

I'd suggest a more elaborate WHERE clause on that CTE, as right now it returns All rows from that table (which can take a while depending on how much data you have).

Things get a bit hairy, as you're inner joining a table to itself, and using that join to update THAT same table.  I'd tread very lightly and ensure you have a good test environment.

Maybe converting "a" to CTE is a better approach than converting "b" to CTE (since "a" actually has a WHERE clause).  Or maybe having BOTH of them as CTE is an even better approach to simplify the UPDATE.

;WITH CTE_tblsname_KS
AS
(SELECT linked_pmt_id
      ,pmt_id
      ,prop_name
      ,prop_addr1
      ,prop_city
FROM tblname_KS (NOLOCK)
)

UPDATE a
SET a.linked_pmt_id = b.linked_pmt_id
  , a.looptag = '1Mb-NBCBZ'
FROM tblname_KS a
INNER JOIN CTE_tblsname_KS b
ON ISNULL(a.prop_name,'NULL') = ISNULL(b.prop_name,'NULL') AND
   ISNULL(a.prop_addr1,'NULL') = ISNULL(b.prop_addr1,'NULL') AND
   a.prop_city = b.prop_city
WHERE   a.linked_pmt_id IS NULL AND
    (a.status <> 'U' AND  a.addr_type NOT IN ('GIS LOCATION','ASSIGNED LOCATION') AND a.addr_type IS NOT NULL) AND
    a.prop_city IS NOT NULL AND
    a.prop_zip IS NULL


Does fast bcp replicate in sybase sql statement replication? Is it possible to replicate fast bcp using sql statement replication in sybase?

No. Fast BCP is an unlogged operation.  Sybase Replication Server relies on scans of the Transaction log for data to be replication.  The Rep Agent thread scans the transaction log in the Primary Database (PDB) for commits.  Once a committed transaction is found in the Transaction log, the SQL statements are forwarded to the Replication Server.  If an action is not logged, like Fast BCP, then no entry is created in the transaction log, and therefore it can't be replicated.

Slow BCP transactions will replicate, as Slow BCP is a logged operation.

In Sybase ASE 15.7, Fast-logged BCP has been introduced, but I am not sure about whether that is supported for Replication yet.


Returning multiple result sets from server-side procedures in Postgres 11 This blog post by a Postgres consulting company mentions that the new server-side procedure support in Postgres 11 would be able to return multiple result sets.

➥ Did this feature indeed appear in the Postgres 11 release?

If so, can you explain how it works briefly? Show some code example?

you can return multiple result sets from a procedure - similar to the way it has always been possible with a function:

create procedure getdata(result_one inout refcursor, result_two inout refcursor)
as
$$
begin
  open result_one for
    select *
    from (values (1,2,3), (4,5,6)) as t(a,b,c);

  open result_two for
    select *
    from (values ('one'),('two'),('three'),('four')) as p(name);
end;
$$
language plpgsql;

However, displaying the results is a bit cumbersome - at least in psql:

postgres@localhost/postgres> \set AUTOCOMMIT off

postgres@localhost/postgres> call getdata(null, null);
     result_one     |     result_two
--------------------+--------------------
 <unnamed portal 1> | <unnamed portal 2>
(1 row)

postgres@localhost/postgres> fetch all "<unnamed portal 1>";
 a | b | c
---+---+---
 1 | 2 | 3
 4 | 5 | 6
(2 rows)

postgres@localhost/postgres> fetch all "<unnamed portal 2>";
 name
-------
 one
 two
 three
 four
(4 rows)

postgres@localhost/postgres>

Some SQL clients can display the results automatically though.


MySQL query using alias to count certain years from DATETIME I have a table which consists of an ID count, a DATETIME field named orderDate showing when a customer bought an item. I am trying to create a query which takes ID and orderDate, to find all dates which were ordered in 2015 using an alias and count them.

For example, my output table should contain IDs and a count of all the orders placed in 2015 for that ID in a single row.

My query, which I have been using to try make it work with very little success is:

SELECT ID,YEAR(orderDate) as lastyear
from t1
HAVING lastyear = 2015

I am aware that I cannot use WHERE, so it's a case of using HAVING, but I cannot for the life of me right now figure out how to use the alias to count.

I am using MySQL 5.1.

This query will work for any given year based on today

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT ID,GROUP_CONCAT(orderDate ORDER BY orderDate) orderDates,COUNT(1) orderCount
FROM t1
WHERE orderDate >= MAKEDATE(YEAR(NOW()) - 1,1)
AND   orderDate <= MAKEDATE(YEAR(NOW()),1) - INTERVAL 1 DAY;
GROUP BY ID;

Running this query now will give you all of 2015.

Running this query next year will give you all of 2016.

NOTES

MAKEDATE(YEAR(NOW()) - 1,1) gives you the first day of last year

MAKEDATE(YEAR(NOW()),1) - INTERVAL 1 DAY gives you the last day of last year

The reason I use

SET group_concat_max_len = 1024 * 1024 * 100;

The default for group_concat_max_len is 1024 (1K). I set it to 100M in case the list is long.

This query will work faster if you have a comound index on (ID,orderDate)

If you do not have such an index, then create one:

ALTER TABLE t1 ADD INDEX ID_orderDate_ndx (ID,orderDate);

COMMENTARY ON YOUR QUESTION

With regard to your original query

You cannot use HAVING without a GROUP BY in the strictest sense (See MySQL Documentation on MySQL Handling of GROUP BY)
There is no need for HAVING

HAVING is the same as a WHERE except it is against an aggregated set of data.
Using WHERE just gets the data before aggregation

Using HAVING complicates the query because you would have to structure the query for counting multiple years and then select just 2015. Using WHERE gets data only from 2015. Then, you can aggregate by ID.


What is the minimum input length required for sql injection? Perhaps this belongs on the 'golf code' stack or elsewhere, if so, apologies and I'll remove it.

I have been working on a older application and re-writing SQL statements as postgres functions to help avoid exposure to SQL injection. I got to thinking, that if something was in the form of

SELECT * FROM Whatever WHERE id = user_input_here

then what would be the shortest user_input_here that could allow Bobby Tables or one of his friends to do their dirty work? If the user_input string had already been constrained to a very few characters, presumably no mischief could be done. (Perhaps we know the 'id' is always exactly 6 characters.) Presumably you'd want at least one dummy character for the original query, then a semicolon before your injected code, what could be the shortest valid statement after that?

Understand that I am not advocating this as a protection method, I'm just curious.

Probably one of the shortest injections are used by sqlmap, for example: "union select version();" (note the leading space).

I can't think of anything shorter than ';call boom()-- - which exploits multiple-query feature of LibPQ, and - obviously - requires top-secret application-specific knowledge.

This code is exploitable:

import sys
import psycopg2
conn = psycopg2.connect(dsn='')
cur = conn.cursor()
sql = "SELECT reltuples, relpages FROM pg_class WHERE relname = '{}'".format(sys.argv[1])
cur.execute(sql)
for row in cur:
    print(row)

Exploited:

$ python exploitable.py pg_class
(342.0, 12)
$ python exploitable.py "';call boom()--"
Traceback (most recent call last):
  File "exploitable.py", line 6, in <module>
    cur.execute(sql)
psycopg2.InternalError: Boom!
CONTEXT:  PL/pgSQL function boom() line 1 at RAISE


GUID vs Auto Increment Insert performance I'm testing the performance of inserts between Auto increment integers vs GUIDs (v4) in tables with a lot of data. Following this blog post, i was expecting seeing a difference. But right now with more than 6 millions rows, i don't see any difference. These are the definitions of my tables:

Auto Increment

CREATE TABLE `auto` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`))

GUID

CREATE TABLE `guid` (
`id` CHAR(32) NOT NULL,
PRIMARY KEY (`id`))

The code is the same as the referenced blog post, insert 100K rows in one transaction several times. As I said before with more than 6 millions rows, there is no difference in performance, it is practically the same. I am trying to figure out why. The script is in a C# y and the GUID is being generating in the application (not in MySql). To be more explicit what i'm doing is a for loop from 1 to 1M, send inserts to the DB, and each time i reach 100K rows commit the transaction and measure the elapsed time on committing.

My hardware: Mac mini i5 2.3 GHz, 16GB Ram, 960GB SSD, but the script is running on a Fusion virtual machine with 4GB of Ram with Windows 10 x64, MySql server it is installed on the virtual machine.

MySql version: 5.7

So, Am I missing something?. Do I need more data?.

Thanks in advance.

What you are missing is the setting of innodb_buffer_pool_size (assuming you are using InnoDB), and how it compares to the size of the index on id.  The "more data" needed:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW TABLE STATUS LIKE 'guid';

When id is AUTO_INCREMENT or some kind of "increasing" timestamp, all the work will be done at the "last" block of the index.  Hence, very little needs to be cached to avoid I/O.
When id is a uuid/guid/md5/etc, the cache space used (buffer_pool) grows as the table grows.  This is because the id is jumping around "randomly".  You won't notice much performance hit until the buffer_pool is no longer big enough.  Then things gradually hit the fan.

Let's carry things even farther...  Let's say the buffer_pool that can hold 1M entires, but there are 20M entries.  When the next row is to be inserted, it has a 1M/20M chance that the desired block is currently cached in the buffer_pool.  That is, only 5%.  Or, a 95% chance of a "miss" -- almost always requiring a disk hit.  I/O is the major cause of sluggish SQL.

More discussion:  http://mysql.rjweb.org/doc.php/uuid

Bottom line:  If you have a huge table, guids will kill performance, whether it is the PRIMARY KEY or some other index.


how to convert a master cluster to a slave cluster? I am learning about PostgreSQL streaming replication and so far I have learned how to create a slave cluster using:

pg_basebackup -x -R -D main -h host -p port

I also have learned that I can promote a slave to be a master like this:

pg_ctl -D main promote

or

pg_ctlcluster 9.4 main promote

(if you are working on Ubuntu).

What I like to do now is to say that my previous master is now a slave of the new master. So far I have learned that I can do it by removing the data directory of server, and running pg_basebackup again. This means that I have to wait until all of my files are copied back from the new master the old one, which means a lot of data traffic.

Is there another way to do the same thing without copying all the data again?

This means that I have to wait until all of my files are copied back from the new master the old one, which means a lot of data traffic

When using pg_basebackup, yes.

But, if you're confident that the existing files at the destination are almost identical to the source, rsync may be used. The rsync remote-update protocol is able to identify what parts of files changed to minimize the amount of data to transfer.

See examples of uses on the postgres wiki: Binary Replication Tutorial / Starting Replication with only a Quick Master Restart

My personal experience with rsync versus pg_basebackup is that rsync is good if the "new master" has not drifted that much from the state when it became master, like a few minutes of transactions on a moderately busy system. Otherwise it still works but pg_basebackup appears to be faster.


Best way to set up database tables I have 1 user table and want to associate it with multiple other tables depends on if user have added such information (modules) and want to know what is the best way to do it.

main table (id,name,email,gender, etc ...)

other tables

conditions (id, user_id, name, etc ... )
results (id, user_id, desc, etc ... )
tests (id, user_id, date, etc ... )
etc

Currently, I'm thinking of adding the a column to the main table (called modules) and add the table names as a comma separated string or array.

(id,name,email,gender, etc ... , modules)

Retrieve the field for each user, find the table names on the list and start looking for rows on each table that match the user id.

I want to know if there's a better way to do this. Right now it seems very inefficient and will require many queries for each user.

Update:
I want to create something like a profile page that go through the different tables and list all the conditions/results/etc for a particular user in its own widget/box/div.

You have no need to add a "modules" field to your main/users table.  Just JOIN on the tables -- that's what Relational Databases are for.  INNER or OUTER joins should be used depending on your needs.

For example, this will return a user with their conditions.  If the user doesn't have any conditions, it will return NULL for those fields, but it will still return the user:

SELECT *
FROM User U
   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins


Simple select query taking too long I have been having a hard time with this query, I have a webpage which is supposed to run this query a dozen of times, and with an execution time up to 40 - 50 seconds per query, you can see how this is becoming quite problematic as the database keeps growing.

Query:

select distinct(scanid)
from misspelled
where word = ?

knowing that the table 'misspelled' can grow up to a dozen million rows, is there any way to make this work ?

Thanks, here are more details:

Query:

select distinct(scanid)
from misspelled
where word = ?

Explain Analyze:

HashAggregate  (cost=76018.68..76018.79 rows=11 width=8) (actual time=38359.639..38359.823 rows=1071 loops=1)
  Group Key: scanid
  ->  Bitmap Heap Scan on misspelled  (cost=1014.12..75918.78 rows=39960 width=8) (actual time=49.946..38319.196 rows=37993 loops=1)
        Recheck Cond: (word = 'Facebook*'::text)"
        Heap Blocks: exact=28920
        ->  Bitmap Index Scan on test_scanid  (cost=0.00..1004.13 rows=39960 width=0) (actual time=40.541..40.541 rows=37993 loops=1)
              Index Cond: (word = 'Facebook*'::text)
Planning time: 197.561 ms
Execution time: 38360.440 ms

Also available at: http://explain.depesz.com/s/7ttn

Table definition:

CREATE TABLE misspelled
(
  word text,
  url text,
  occnum BIGINT,
  proper BOOLEAN,
  scanid BIGINT,
  CONSTRAINT misspelled_scanid_fkey FOREIGN KEY (scanid)
      REFERENCES scans (scanid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT misspelled_word_url_scanid_key UNIQUE (word, url, scanid)
);

CREATE INDEX test_scanid
  ON misspelled
  USING btree
  (word COLLATE pg_catalog."default", scanid);

http://pastebin.com/D6jrT2NC

Version 9.4.0

If you need anything else please let me know.

Thanks

You have almost 40000 rows where word='Facebook*'. I suppose that these rows are somewhat randomly scattered on the whole table. This means that Postgres has to read tens of thousands of random blocks from a disk, which might be slow.

You can try to cluster rows that have the same word together. You can use the command cluster misspelled using test_scanid;. This would take some time and block the table until done, so plan accordingly.


prevent clustered index insert operator on unqualified indexed view Does anyone know a workaround for this?  Essentially, the stored procedure forces an insert operator against the indexed view, even though the rows don't qualify.  As a result, there is a cast error.  However, for ad hocs, sql correctly eliminates the view from consideration.

Consider the following schema:

create table testdata (
    testid int identity(1,1) primary key
  , kind varchar(50)
  , data nvarchar(4000))
go
create view integer_testdata with schemabinding
as
select cast(a.data as int) data, a.kind, a.testid
  from dbo.testdata a
 where a.kind = 'integer'
go
create unique clustered index cl_intdata on integer_testdata(data)
go
create procedure insert_testdata
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) values (@kind, @data)
end
go

These all work:

insert into testdata (kind, data) values ('integer', '1234');
insert into testdata (kind, data) values ('integer', 12345);
insert into testdata (kind, data) values ('noninteger', 'noninteger');
exec insert_testdata @kind = 'integer', @data = '123456';
exec insert_testdata @kind = 'integer', @data = 1234567;

This fails:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

A comparison of the "estimated execution plans":

insert into testdata (kind, data) values ('noninteger', 'noninteger'):

exec insert_testdata @kind = 'noninteger', @data = 'noninteger':

Thank you for providing a full script to recreate the problem.

I tested it with SQL Server 2014 Express.

When I add OPTION(RECOMPILE) it works:

ALTER procedure [dbo].[insert_testdata]
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data)
  values (@kind, @data)
  OPTION(RECOMPILE);
end

When I run this in SSMS:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

I get this message:

(1 row(s) affected)

and a row is added to the table.

What version of SQL Server are you using? I vaguely remember that in versions before 2008 this OPTION(RECOMPILE) behaved a bit differently.

  I'm working with an existing data structure and need a fast lookup on
  unique integer values stored in subset of nvarchar(4000)'s, a filter
  on another column defines that subset of rows.

In this case it may be better to use filtered index instead of indexed view:

CREATE UNIQUE NONCLUSTERED INDEX [IX_DataFiltered] ON [dbo].[testdata]
(
    [data] ASC
)
WHERE ([kind]='integer')

Optimizer should use this index when the WHERE filter of the query matches exactly the WHERE clause of the index.

Yes, here index is on nvarchar column which may be not the best thing, especially if you join this table with an int column of another table, or try to filter values in this column using int values.

Another variant that comes to mind is persisted computed column that converts nvarchar to int. In essence it is very similar to your view, but the persisted nvarchar values that are converted into int are stored with the same table, not in a separate object.

CREATE TABLE [dbo].[testdata](
    [testid] [int] IDENTITY(1,1) NOT NULL,
    [kind] [varchar](50) NULL,
    [data] [nvarchar](4000) NULL,
    [int_data]  AS (case when [kind]='integer' then CONVERT([int],[data]) end) PERSISTED,
PRIMARY KEY CLUSTERED
(
    [testid] ASC
))

CREATE UNIQUE NONCLUSTERED INDEX [IX_int_data_filtered] ON [dbo].[testdata]
(
    [int_data] ASC
)
WHERE ([kind]='integer')

With this set up I tried to use your original stored procedure to insert rows and it worked even without OPTION(RECOMPILE).

Actually, it seems that the main reason why the above persisted column works is that I use CASE. If I add CASE to the definition of your view, the stored procedure works without OPTION(RECOMPILE).

create view integer_testdata2 with schemabinding
as
select
    case when a.kind='integer' then CONVERT(int, a.data) end as data
    , a.kind, a.testid
from dbo.testdata a
where a.kind = 'integer'
go


SQL Server 2012 Create index with Sort In TempDb On - Gains False? If I create an index e.g

Create nonclustered index(Path, Value, Id) with(sort_in_tempdb=on);

It doesn't fail but when looking at the index in SMMS, it says False. Any ideas why?

EDIT: Can not find any schema to query for the value of this setting. Tried sys.Indexes but with no luck.

//Daniel

Option 'Sort in tempdb' valid only while creating an index. And after index has been created it (an option, not index) becomes senseless. Any other operations like inserts, updates and deletes does not apply sorting in tempdb, or even apply, but this has no any relevance to this option value used while creating index


Is it possible to perform skip N rows and fetch M rows without full scan Let's say I've got this query:

SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
   WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME;

Now if I want to show only the first ten rows between 50 and 60, the only way I can think of is to first run the above query with ROWNUM pseudocolumn and then select from the result of this query. Something like this:

SELECT EMP_ID,LAST_NAME FROM
 (SELECT  ROWNUM RNUM,EMP_ID,LAST_NAME FROM
  (SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
     WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME))
  WHERE RNUM BETWEEN 50 AND 60;

But this way, I'll end up first fetching all the employees whose positions are 1 or 3 and then extract the 10 rows among them. I find this a bit inefficient. Is there any way to achieve this without making full scan first?

The standard approach to get rows N through M is to do something like

SELECT *
  FROM (SELECT a.*, rownum rnum
          FROM (SELECT emp_id, last_name
                  FROM employees
                 WHERE positionID in (1,3)
                 ORDER BY <<something>>) a
         WHERE rownum <= 60) b
 WHERE rnum > 50

Note that you need to include the order by in the query in order for it to make sense.  Oracle may use a table scan (depending on whether or not positionID and/or the column(s) in the order by are indexed) but there will be a stopkey limiter so Oracle knows it can stop scanning the table (or the index) once 60 rows have been read.

In 12.1 and later, you can simplify this a bit

SELECT emp_id, last_nae
  FROM employees
 WHERE positionID in (1,3)
 ORDER BY val
OFFSET 50 ROWS
 FETCH NEXT 10 ROWS ONLY

More examples of using the new row limit clauses in 12.1


Cannot delete domain login from SQL SERVER 2012 We are in the process of trying to clean up some old accounts on one of our AlwaysOn clusters.

This particular account refuses to play nice and allow itself to be deleted.

USE [master]
GO
DROP LOGIN [PROD\dba007]
GO

Error:

Msg 15173, Level 16, State 1, Line 4
Server principal 'PROD\dba007' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

The permission it's complaining about is a connect permission to the HADR_ENDPOINT

Select perm.* from sys.server_permissions  perm
INNER JOIN sys.server_principals prin ON perm.grantor_principal_id = prin.principal_id
where prin.name = N'PROD\dba007'

class   class_desc  type    permission_name state   state_desc
105     ENDPOINT    CO      CONNECT         G       GRANT

Now the next logical thing would be to revoke the connect permissions.

USE master;
REVOKE CONNECT ON ENDPOINT ::HADR_endpoint from [PROD\dba007]

But this produces the message

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

The only server role it is a member of is PUBLIC.

How can I find out what exactly is preventing me from revoking these permissions so I can drop the user?

Thanks.

Check to see if they own the endpoint itself:

SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name
FROM sys.database_mirroring_endpoints;

If so, you'll need to change the endpoint owner. Say the endpoint is called Mirroring, and you want to change the owner to SA:

ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa;


Select Statement with RegEx Replace - DB2 9.7 In a db2 9.7 select statement's field, I need to perform a global regular expression that replaces multiple white spaces (between words) with exactly one space. The regular expression would be base on something like this:

\s{2,}

The query (without thinning out redundant white space) looks like this:

select
  random_degrees_of_redundant_white-space_between_words as fieldName
from tableName

The contents of a result looks like this:

I like  to   put    varying      numbers      of    whitespaces

     between

   words.

I've seen examples of people claiming to achieve this in DB2 9.7 via xmlquery and fn:replace, but I can't seem to accomplish this myself by looking at their examples.

Would someone be kind enough to convert my abstract example into something I can use as a model for achieving this in db2 9.7?

Please do not provide answers that achieve this via custom functions or stored procedures. I want to achieve this using an independent select statement on a default installation of DB2 9.7 (without installing any addons to db2).

You could use the the

  replace

function like this:

select replace('Text   with  random      spaces', '  ', ' ')
  from yourtable

The second argument shows two spaces and the third is a single blank.

EDIT:

For multiple blanks this sould work - natively:

SELECT c1, length(c1), xmlquery('fn:replace($c,"[ ]+", " ")'  PASSING c1 AS "c")
  FROM test1


Why can i not call COUNT on a varray column in a select statement using oracle I have a simple array type definition like this

  CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS  VARRAY(10) OF VARCHAR2 (100);

Now I have a column items of type LIST_OF_NAMES_T in my table TABLE1

Why am I getting an error when selecting the count of the array in a select statement like this:

SELECT items.COUNT FROM TABLE1;

Oracle tells me

ORA-00904: "items"."COUNT ": invalid identifier

10.2 documentation: Using Collection Methods

  The following apply to collection methods:

  Collection methods cannot be called from SQL statements.

  ...

11.2 documentation: Collection Methods

  A collection method invocation can appear anywhere that an invocation
  of a PL/SQL subprogram of its type (function or procedure) can appear,
  except in a SQL statement.

Versions before 12c:

SQL> CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS  VARRAY(10) OF VARCHAR2 (100);
  2  /

Type created.

SQL> create table table1(c1 LIST_OF_NAMES_T);

Table created.

SQL> insert into table1 values(LIST_OF_NAMES_T('A', 'B'));

1 row created.

SQL> commit;

SQL> create function f1(p_list_of_names list_of_names_t) return number as
  2  begin
  3    return p_list_of_names.count;
  4  end;
  5  /

Function created.

SQL> select f1(items) from table1;

 F1(ITEMS)
----------
         2

In 12c and later, you can define functions on the fly, so this also works:

SQL> with function f2(p_list_of_names list_of_names_t) return number as
  2  begin
  3   return p_list_of_names.count;
  4  end;
  5  select f2(items) from table1;
  6  /

 F2(ITEMS)
----------
         2