How to create Always Encrypted Certificate for specific time period? I have configured Always Encrypted on a custom database using SQL Server 2016. By default, it creates the certificate with a validity of one year.

Is there any workaround to create this certificate for two or three years at the very beginning stage?

I don't want to rotate the key every year in our production environment rather create the certificate at least for two or three years.

Experts advise please.

When this first came out I had a blog post on this, creating the certificate via Powershell: https://justdaveinfo.wordpress.com/2015/10/15/always-on-encrypted-generating-certificates-and-column-encryption-key-encrypted_value/

Checking the documentation for the Powershell cmdlet New-SelfSignedCertificate you should be able to use "-NotAfter (Get-Date).AddMonths(24)"

https://docs.microsoft.com/en-us/powershell/module/pkiclient/new-selfsignedcertificate?view=win10-ps


How to copy data from one database to another database efficiently? I have a conceptual question.

I have two databases which theoretically have the same structure but one of them is a SQLBase database and the other one is a SQL Server database.

One database (the SQLBase database) already contains a lot of data. This data should be transferred to the other (SQL Server) database via Select and Insert.

How can I do this data migration with the highest performance?

My first approach was to sort all the tables in a list where the tables which contain foreign keys will be stored behind the referenced tables. But with this solution it will be impossible to start parallel processing.

The second idea was to create a custom type which contains the tablename and the tablenames of the referenced tables and a bool flag which stores whether the data in the table have been copied. This type is stored for each table in a list. Then I start a new thread that checks before copying whether the referenced tables have already been created for each table. If not, I execute Thread.Sleep() after which I will check it again.

Is there a well performing approach to this problem?

What about using one of the existing ETL tools? Talend ETL or Pentaho Data Integration are two examples (performance-wise PDI is better).
You can design an ETL job then schedule to run it as you like.


How to JOIN by date columns? I have two tables with the structure of

CREATE TABLE table1
(
Month date,
Value1 varchar(255),
PRIMARY KEY(Month)
) ENGINE=InnoDB

CREATE TABLE table2
(
Day date,
Value2 varchar(255),
PRIMARY KEY(Day)
) ENGINE=InnoDB

In table1, the date values are stored in the form of

2018-04-01
2018-03-01
2018-02-01
2018-01-01

and in table2

2018-04-01
2018-04-02
2018-04-03
2018-04-04
2018-04-05

How can I JOIN two tables to have the month value (Value1 from table1) for each row of table2?

The desirable result

Day           Month        Value1        Value2
2018-04-01    2018-04-01
2018-04-02    2018-04-01
2018-04-03    2018-04-01
2018-04-04    2018-04-01
2018-04-05    2018-04-01

If you aren't sure that all of your dates in the second table end are on the first day, you can be sure the comparison on year/month with EXTRACT( YEAR_MONTH FROM datecol) like this,

SELECT
  t2.Day,
  t1.Month,
  t1.Value1,
  t2.Value2
FROM Table2 t2
LEFT JOIN Table1 t1
  ON EXTRACT(YEAR_MONTH FROM t2.Day) = EXTRACT(YEAR_MONTH FROM t1.Month)

It is likely faster regardless, unless you have an index on t1.month


How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also? I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables every 15 minutes. I am looking to optimize query performance by adding partitions and sorting.

All queries of this data are predicated on an integer field with about 350 distinct values.The leftmost CCI has 100M records and 125 columns. There are three child CCIs that have that same integer field. CCI 2 has 15M records and 150 columns, CCI 3 and 4 both have about 30M records and 25 columns each.

Of these 350 distinct integers the distribution of record count in the leftmost table is as follows:

5% Greater than 1M
46% Greater than 100K
83% Greater than 10K

Additionally, there are nine other rowstore tables that also join to the CCIs. These have trickle inserts, are children of the CCIs, and they all contain the same integer field. These rowstores have similar or smaller record volumes, < 10 columns each, two contain LOBS, and two undergo mass-updates frequently (these updates are also predicated on the ID field).

How many partitions should I make?

Should I partition the rowstore tables also?

Are there important considerations I am overlooking?

Note regarding the "sorting" I mentioned earlier:

A date field in the leftmost CCI is often a secondary predicate in these queries, therefore I am looking into re-sorting that CCI by date every four weeks or so as maintenance. I will achieve this sort by dropping the CCI, adding a clustered rowstore index on the date, dropping that index, and then re-adding the CCI with MAXDOP=1. I am also looking at sorting the child CCIs by the join key to their parent.

Benefits of partitioning a CCI:

Query performance can be improved because a minimum level of rowgroup elimination is guaranteed, despite how the data gets loaded or modified. Most of the generic SQL Server partitioning guidance out there doesn't take this into account.
Improved flexibility with maintenance operations in that you can do rebuilds at the partition level or do reorgs at the partition level (after partition switching out). You can also send different partitions to different filegroups, but I need to caution you that doing so will almost never improve performance. Filegroups are a maintenance feature. Increasing the file count can improve performance sometimes. Depending on your storage setup you almost certainly want the data relevant to your queries to be spread over multiple files to improve I/O.
Partition elimination covers more scenarios than rowgroup elimination on the same column. For example, a filter of WHERE ID < 0 OR ID > 10 will not quality for rowgroup elimination but will qualify for partition elimination.
Looping by partition can be helpful when performing maintenance operations that require all rows to be changed. For example, suppose you're adding a new column to a table that can be derived from existing columns in that table. Partitioning allows you to efficiently split that work into batches if desired.

Downsides of partitioning a CCI:

Without maintenance the number of rows in delta rowgroups can dramatically increase. Consider an unpartitioned CCI that is loaded with parallel inserts at MAXDOP 8. At most you'll have 4194304 rows in the delta store. If the table is changed to have 50 partitions it's now possible to have 209715200 rows in the delta store.
Query plans for inserts and deletes into the columnstore may contain a sort operator as a child of the DML operator. If this sort cannot get enough memory you can end up with extreme performance degradations. I recommend only modifying one partition at a time if using parallel insert.
If you choose your partition function unwisely you could end up with partitions that are too small. Many people will point you to the 1048576 row limit for a rowgroup as the ideal size, but personally I consider the benefits of getting there to be overblown. You probably do want to avoid many tiny partitions if you can help it though.
If you have too many partitions in your table or your database then bad things might happen. Unfortunately, this isn't very well defined and it's hard to find a credible source for what "too many partitions" actually means. I've heard of and seen issues with query compilation times. There was a recent answer here about DBCC CHECKTABLE as well.

Applying the above to your scenario: with the row counts that you have you shouldn't run into any of the really bad cases. For query performance, some folks need really fast query execution times and they need to skip as many rowgroups as possible. Others just need a minimum level of rowgroup elimination because most of the work done in the query is outside of the columnstore scans. That makes it difficult for someone on the outside to give you a recommendation for the number of partitions. For the 100 million table, anything from 4-100 could be reasonable.

You could try testing some of your queries with different numbers of rows in the partitions to see how performance changes. That can be simulated by creating copies of the tables or by creating a partition function on one table with deliberate skewness and changing what ID you filter by. If you take what results in good enough query performance and verify that you won't have any issues with loading data then you should be good.

The rowstores aren't relevant to the question, or rather, they're a totally different question. Partitioning is not the right tool to improve performance on rowstore query. I've seen performance gains on systems just by partitioning columnstore tables and leaving the rowstore tables alone.


How to set a restart on a TSQL Job? I currently have an important TSQL job that hangs probably once a week.

It moves data from one database to another.

It is on a schedule running every 5 minutes indefinitely throughout the day.

Sporadically, it will hang usually around 1 am in the morning.  When I say hang I mean it just gets caught at 1am never finishes its current scheduled run in turn it never moves on to the next scheduled run.

In order to get it to stop I have have to stop the job.  Disable the job.  Kill the associated SPID.  Re-enable and restart the job.  Sometimes this gets it running but other times I have to restart the SQL Agent itself.

There is really no rhythm or reason as to why this happens datawise.  Sometimes it will go weeks without issue other times it occurs once or twice a week.

I am just wondering if there is possibly a way to have this job restart it self regularly in the morning so it doesn't hang.  I am assuming someone else has had an issue like this before.  Any direction here is greatly appreciated.

There is no blocking in the database.  I am not looking to fix a blocking issue.  Looking for a way to determine if the job is hung and restart it or something along those lines.

I have reviewed sp_who2 regularly and never seen any blocking with this particular job.

I have dealt with some long running jobs in the past and the best way I have found to identify them is to run a separate job that checks on that sort of thing.   Thomas LaRock put together a nice script that does that, and you can modify it to either email you or if you wanted you could have it just stop the job as well.   In my case, the job literally just hangs, but it is not being blocked and in fact in a lot of cases it is doing nothing at all.  But since for whatever reason it never stops the job it will never run that next schedule.

HOW TO: FIND CURRENTLY RUNNING LONG SQL AGENT JOBS

I have put his code to use and added an email notification to it for myself, and it works great.   As long as you know your threshold's for what is a long running job or even if you just want to look at an individual job, this should get it done for you.   It would be easy enough to have your code find that it is running too long and then to within the same script call

exec msdb.dbo.sp_stop_job @job_name 'YourJobHere'

which would then allow the next schedule to pick up where this left off.  I have found that I like to see if there is anything else going on as opposed to just stopping the job but it will work either way.

Best of luck.


How to add a column with a foreign key constraint to a table that already exists? I have the following tables,

CREATE TABLE users (id int PRIMARY KEY);

-- already exists with data
CREATE TABLE message ();

How do I alter messages table such that,

a new column called sender is added to it
where sender is a foreign key referencing the users table

This didn't work

# ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;
ERROR:  column "sender" referenced in foreign key constraint does not exist

Does this statement not create the column as well?

You just have to add another step - in fact PostgreSQL is already telling you that: column "sender" referenced in foreign key constraint does not exist.
The FOREIGN KEY (aka parent) column has to already exist in order to make it an FK.
I did the following (from here and the documentation). Note that the parent column has to have a UNIQUE constraint (or be the PRIMARY KEY) but it doesn't have to be NOT NULL. This is because NULLs are not equal to each other, nor are they equal to anything else - each NULL is considered UNIQUE in its own right!
CREATE TABLE x(a INT UNIQUE NOT NULL);

CREATE TABLE y(b INT);

ALTER TABLE y ADD COLUMN c INT NOT NULL
CONSTRAINT y_x_fk_c REFERENCES x (a)   -- if x (a) doens't exist, this will fail!
ON UPDATE CASCADE ON DELETE CASCADE;  -- or other Referential Integrity Action

A couple of points to note (see the fiddle here) - an attempt to insert a value into y (c) which is not in x (a) fails and the constraint name is given in the error message.
The fiddle has NOT NULL constraints on x (a) and on y (c). Unless I have a really compelling reason, I always declare my columns as NOT NULL - it helps the optimiser and reduces the potential for confusion/error. You can experiment yourself with the fiddle to see what happens when you leave out the NOT NULL on either (and both) field(s) - the behaviour isn't always intuitively obvious!
ALWAYS give your foreign keys meaningful names. Being told that key "SYS_C00308108" is being violated is not very helpful. See the fiddle here for Oracle's behaviour under these circumstances the key name will vary from fiddle to fiddle, but is some arbitrary string beginning with SYS_... (comes after the long dbfiddle generated tablename).
Evan Carroll in his answer here believes that auto-generated names are OK - I've shown why that is not a good idea for Oracle (at least up to 18c), but I also feel that it's not a good idea for PostgreSQL either - potential problems for portability if nothing else.
I would like to credit Evan Carroll for pointing out that the addition of the new field and the FOREIGN KEY creation and the CONSTRAINT (with specified name) can be added in one step and not two steps as I originally said) - so please give him credit for that if you feel like upvoting me - I do go into more detail however.
Considering the statement in your question:
ALTER TABLE message ADD FOREIGN KEY (sender) REFERENCES users;

It would be a "nice-to-have" if the RDBMS could automatically create the field you want with the data type matching the referenced field.
All I would say is that changing DDL is (or at least should be) a rarely used operation and not something that you'd want to be doing regularly. It also risks adding to an already fairly substantial documentation.
At least PostgreSQL tries to do something reasonable - it concatenates the table name, the FOREIGN KEY field name and fkey. Furthermore, when you do name the constraint yourself,  the error message will add DETAIL: Key (c)=(7) is not present in table "x". to give something that might make sense to a human being (unlike Oracle - see the end of the PostgreSQL fiddle).


Determine Reason for Deadlock in MySQL We've been seeing some deadlocks occur but have not been able to reproduce.  Is there any way to determine, after the fact, what queries were running, etc to figure out why deadlock occurred?

SHOW INNODB STATUS will give you the last detected deadlock.


Setting up Data Level Application Alerts in SQL Server Solutions are available for providing alerts when any job fails in SQL Server.

Is there any solution available for setting up data level alerts - for e.g.

if data hasn't updated in USER tables for lets say 2 hours, we get an ALERT
if any USER table, specific column lets say has aggregate of more than 100% (which is not right) then we get an alert
if any specific column which should not have value (lets say greater than 10), then we get an alert

We can create a table for this type of alerts and then run may be any SSIS package or TSQL every 15 mins - and anything which doesnt match the benchmark value set for alerts we get email.

Is any aware of any solution developed for this type of need

Regards

No, there is not any built-in feature that does exactly this.  I'm also not aware of an off-the-shelf product that does what you're looking for.

However, based on the description of your requirements, it seems like a SQL Server Agent job would work really well!

Set up an agent job that runs every 15 minutes, has steps that run the different checks you're interested in, and sends emails to your support team using sp_send_dbmail.

To get started, find the "SQL Server Agent" node in SSMS, right click on "Jobs," and choose "New Job."  There's a detailed walkthrough of all the options here: Create a Job | Microsoft Docs


How to build the database for subdomain multisite? I'm building a kind of "multi site application" based on wildcard subdomain. And every site will of course have the same files but also the same database structure. But it's not necessary for each and other to share tables. They will never use each other's datas.

So the best would be if they had their own tables, in their own databases. But then it's more difficult to develop the service since I have to do all the changes to all the databases when I change something.

So the best would be if every database could mirror each other in the terms of structure.

Is this possible? Or what is the most recommended solution for this situation?

The problem lies on the border of the DBA and the software engineering.

There are multiple solutions, all depending on, exactly how close database structures are you wanting.*

As the site databases (thus, also the frontend engine using your DB) are more closer and closer to eachother, so should you use the more and more closer options also for the DB.

Likely (but not sure), that you will have at least a little part of your database, which will be the same, for example the user data.

Your options depend also on the applied database. For example, mysql has no schemas, but knows the cross-database queries. PostgreSQL can make cross-db queries only by extensions, but you can configure schemas for it.

The options, in the decreasing order of "close-ness":

Having a multi-tenant DB. It essentially means, you will have the same DB for all the sites. On this way, your database structure will be more complex, with foreign keys directed by each per-db record to the corresponding db data. It makes also your queries more complex. Note, queries using many "WHERE"-conditions become quickly sub-optimal.
Having a multi-schema solution: using a single cross-site schema, added by a per-site schema for all the sites. This can't be done with MySQL, but it would work well with PostgrSQL.
Having a single cross-site DB and a per-site DB for all the sites. This either requires to have cross-db queries, which may be hard to synthetize with foreign keys. However, you may have different database versions in the per-site dbs.
You may have also entirely different databases. In this case, you will likely need to have some replication/synchronization, either by triggers or by scripts.

Changing your decision in a late phase of the development, or during the maintenance, is a major refactoring work, so decide wisely as early as possible.

Note, if you want to use different software versions simultanously, you will likely need to have same database versioning and (1) falls out.

*A remark: the Stack Exchange has the same situation: they have a multi-tenant website, all with nearly the same engine and probably with the same database format. Probably they decided behind (3).


MySQL aggregate anomaly I was checking selectivity of some columns for an index.
Where is this "ignore what I give you" behaviour documented?

This gives 4,851,908, 4,841,060, and 1,000,052

SELECT
     COUNT(*),
     COUNT(DISTINCT Col1), COUNT(DISTINCT Col2)
FROM Sometable;

This gives 4,843,634 unique pairs as per MySQL extension

SELECT COUNT(DISTINCT Col1, Col2) FROM Sometable

The following are wrong: the individual COUNT(DISTINCT colx) all give the 4,843,634 unique pair count regardless of any filler column or expression order.

I expected COUNT(DISTINCT Col1) = 4,841,060, and COUNT(DISTINCT Col1) = 1,000,052.

SELECT COUNT(DISTINCT Col1), COUNT(DISTINCT Col2) FROM Sometable

SELECT COUNT(DISTINCT Col2), COUNT(DISTINCT Col1) FROM Sometable

SELECT COUNT(DISTINCT Col1), 1 AS Filler, COUNT(DISTINCT Col2) FROM Sometable

But this give correct values again with another aggregate (like with COUNT(*) above)

SELECT COUNT(DISTINCT Col1), MAX(col1) AS Filler, COUNT(DISTINCT Col2) FROM Sometable

Questions, in case it wasn't clear:

Why does COUNT(DISTINCT Col1), COUNT(DISTINCT Col2) behave like COUNT(DISTINCT Col1, Col2)
Why is another aggregate required to make it work?

It looks like you are hitting this regression bug:

  select count(distinct N1), count(distinct N2) from test.AA" works incorrectly
  ...
  "This bug happens when a unique index exists"

One of the suggested workarounds is to use sql_buffer_result


Fact to Fact relationship in DW Design I have a situation where I have an hierarchical collection of items that I want to report on.

Let's say I want to track each individual item sold, so I have a ItemSellFacts table. But those items are sold as part of a shopping cart and I want to track those too, so I have a ShoppingCartFacts.

This second fact table is more like pre-processing of the previous fact table, where I keep track of max/min item price in the cart and so on. I could generate all that based only in the ItemSellFacts everytime, but this second table is where we want to make live dashboards to plug on.

So it seems natural to me that I can reference the shopping cart key as a foreign key in the item sell fact. Of course I'll avoid issuing a join between those two fact tables like the plague, but several people told me not to do that even so.

Is this really a flaw in the design? What could be the alternative?

The way I'm interpreting your use of the term "shopping cart", is it's just the collection of items purchased at one time by a customer. At my company we refer to that as a "Sales Order". To handle Sales Orders there are two methods you can use: 1) it often makes sense to have a Header level Fact table and a Detail level Fact table and seperate information into one of those two tables, or 2) you have one Sales table that's denormalized where you keep all your sales information.

More info on option #1:

The header level table would unique by Sales Order (or Shopping Cart) as long as you have a unique ID for each of them. And can include information that's specific to the entire Order like who placed it (since the person placing the order can't vary by line item). And then the Line Item table would be unique by Sales Order and Line Item (aka Product) and would include the information like Quantity, Price, etc. The benefits of this set up are there's less data redundancy, but the downside is the need for more joins to connect the two tables.

More info on option #2:

The other option I mentioned is have one bigger table that includes both header level information and detail level information. So even though all line items have the same customer you still list it on each item. And you would still want a Sales Order ID or Shopping Cart ID to help distinguish all the items on an order. The benefit of this is less joining and easier to query, the downside is more data redundancies and the chance for errors.

So part of your question though is whether you should have a foreign or not. If you went with Option #2 then you definitely wouldn't have a foreign key. With Option #1 you can or can not have a foreign key it just depends. For a lot of database models it gets really tricky having a fact table point to a fact table. Because when you're loading those tables you need to be extra careful to always make sure your records exist in the first table before you try to load the table with the Foreign Key. Sometimes what we do instead of using a foreign key is we instead have a Stored Procedure that checks if all the values in Column A exist in Column B and if not reports any exceptions and we just check and review that so we have an informal Foreign Key that we just keep an eye on.

Just some ideas. Hope this helps.


Database design - need someone to guide me I'm probably doing this wrong, but here it goes.

I'm trying to build something like very basic CRM. I know there are plenty of them, but I want something to learn actually.

Requirements:

Some companies are never contacted before. That's why I created two additional tables contact_emails and company_emails.
The contacted company may have assigned contact (person).
A person can have phones and emails.
One person can be assigned to multiple companies
One company can have multiple persons

Diagram:

Questions:

Am I on the right track?
What can be improved and how?

Learn Table Inheritance (aka subtype/supertype)

Individual   : Legal Party (":" meaning "inherits")
Organization : Legal Party

Email          : Address
Phone          : Address
MailingAddress : Address
WebURL         : Address

LegalParty -< PartyRelationships >- LegalParty
LegalParty -< ContactInfo >- Address

Read up on existing data model patterns. Don't re-invent the wheel


Function for generate a huge and possible random relationship between tables I have this two tables and N:M relationship between them:

Let's said I am managed to create a lot of media and emails records but I need to create the relationship between them. I can do this programmatically but I don't want to waste time on that if I can do in someway using MySQL functions or any other way. So, any advice, code example, query example?

Lets say that the media tables has 1K rows and the emails one has 100K rows.

Then this will insert into the emails_media_xref all possible combinations, 1K x 100K = 100M, that is a hundred million rows.
Warning, this may take some time:

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT
    emails.id, media.id
FROM emails
  CROSS JOIN
     media ;

If you want to insert a row for every email, that is 100K rows (random, different choice of media per email):

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT
    emails.id,
    ( SELECT media.id
      FROM media
      ORDER BY RAND()
      LIMIT 1
    )
FROM emails ;

If you want to insert (up to) @N rows for every email, that is a  maximum of N x 100K rows (random, different choices of media per email). This will not be exactly N rows per email because of the duplicates (which the DISTINCT will remove):

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT DISTINCT
    emails.id,
    ( SELECT media.id
      FROM media
      ORDER BY RAND()
      LIMIT 1
    )
FROM emails
  CROSS JOIN
    ( SELECT 1
      FROM media
      LIMIT @N           -- pick a @N here
    ) AS x ;

If you want to insert a fixed number of rows, say 2000, totally randomly.
Warning, this may take even more time than the first query:

INSERT INTO emails_media_xref
    (emails_id, media_id)
SELECT
    emails.id, media.id
FROM emails
  CROSS JOIN
     media
ORDER BY RAND()
LIMIT 2000 ;


Postgres continuous backups - should WAL numbers be sequential after pg_start_backup call? I'm using PostgreSQL WAL archiving and pg_start_backup as outlined in the Continuous Archiving section of the Postgres manual to backup a large database.  Twice a month we do a full backup and every other night we just copy the WALs over to our backup space so if there's ever an issue we just do a restore of the last full backup + the WALs that have been created since then.

I've noticed that the WALs are sequential except where pg_start_backup is called (or technically it may be when calling pg_stop_backup I'm not sure which) at which point it skips a few.  Is this normal behaviour?  I know when calling pg_stop_backup moves to the next WAL segment but I'd expect that to be the next number incrementally or am I missing something?

For example on the 14th of March we had the following WALs after a 1st of March full backup:
000000010000008500000090
000000010000008500000091
000000010000008500000092
000000010000008500000093

After the backup it then went to:
000000010000008500000096
000000010000008500000096.00000020.backup

After this normal WAL creation resumed:
000000010000008500000097
000000010000008500000098
and so on

So should there be a 94 or 95 in this case?

I'm in the process of doing a test restore but as that involves copying 60GB (compressed) of data between two servers in different continents, I'd prefer to know sooner rather than later if I'm going to have an issue!

At one point in the distant past, a comment in the docs was changed from saying "sequential" to saying "ever-increasing". Source

This wording is still in the 9.1 docs for WAL internals. So I'd say that you shouldn't expect a gapless sequence.


SQL Server Reporting Services 2017: Install Cumulative Update SQL Server Reporting Services are no longer part of the installation media since SQL Server 2017.

How to update an existing Installation of SSRS 2017 to the most current version?

We’ll periodically update the SSRS 2017 download/installer such that it’s always the latest “Cumulative Update.” If you have an earlier release of SSRS 2017 installed and you run the installer, it’ll prompt you to “Upgrade Reporting Services” to install the update.

The Reporting Services installer is separate from the SQL Server installer and SQL Server CU installers. We’re not currently releasing it through WSUS, but we’d love to hear more customer feedback on that topic.

Reference: SQL Server 2017 Reporting Services now generally available


Many to many unique combination So I originally thought I was going to have a one to many relationship in a database with the following tables

ENTITY_JSON_MAP
ATTRIBUTE_JSON_MAP

Originally I was going to have an FK in ATTRIBUTE_JSON_MAP pointing to ENTITY_JSON_MAP.
I was going to the Attribute table with

alter table ATTRIBUTE_JSON_MAP add unique (NAME, JSON_MAP_ENUM, ENTITY_JSON_MAP_FK)

Now however I've realized that I need to make it a many to many relationship, so I've created a mapping table, but now I've lost the ability to make sure that I don't get duplicates of that combination.

I was hoping there was still a way to do this. Inserting/deleting tables doesn't need to be fast, as the table will not be inserted or deleted at run time.

If I understand you correctly, your original

  Unique (name, json_map_enum, entity_json_map_fk)

Is made up of

  name: name of the attribute
  json_map_enum: the position of this attribute in the entity
  entity_json_map_fk: reference to entity

You should be able to enforce this in the mapping table as well.

CREATE TABLE Entity_Attribute_Map (
    ATTRIBUTE_JSON_MAP_fk int references ATTRIBUTE_JSON_MAP(id),
    entity_json_map_fk int references entity_json_map(id),
    JSON_MAP_ENUM int,
    Primary Key (ATTRIBUTE_JSON_MAP_fk, entity_json_map_fk, JSON_MAP_ENUM)
)


Storing multi-select data with single-select data I've got a database with some multi-select fields.  To keep it simple, let's just say those fields are MatchGender and Interests.  I want users to be able to find people who match (hence Gender is a multi-select; they can choose to match against one or both genders) and interests/hobbies.  Along with those fields, there are several "single-select" fields such as the user's city, state, their gender, etc...

I'm trying to figure out the best way to store this data so it's easily searchable.  Do I store the multi-select data in a single field that is comma delimited?  Do I store it in a separate table with a UserID?  Pros/Cons of each?

By the way, this is a SQL Server back-end database that will be accessed through an ASP web interface.

Specific answers
First of all, here is my personal approach to your specific questions:

Do I store the multi-select data in a single field that is comma delimited?

No, I do not recommend you to proceed that way. Since you have the chance to make use of a relational database management system (RDBMS), I personally consider that it would be highly convenient and reasonable to design and implement your database in a relational fashion. I am convinced that there is no need to use a format as limited as CSV when one can take advantage of the robustness of relational tools. If you opt for the CSV method, you would be adding unnecessary complexity to your project.

Pros. CSV format has its use in certain contexts, e. g., supplying information interchange between applications that handle data in incompatible formats. Another feature of CSV is that it is easily modified by spreadsheet aplications such as MS Excel or LibreOffice Calc. Anyway, due to the fact that you are going to use a RDBMS, I do not think any of these facts is relevant in terms of database design or storage in a relational platform. If you come across the need to deliver some of your data in CSV format in the future, then you can export your structured data to a comma delimited file without undermining your database soundness.

Cons. As you may already know, by storing CSV data in a single column, you would be breaking First Normal Form. This way, having unnormalized data (nonsimple or multivalued columns), you lose many of the essential technical advantages that are provided by RDBMS which are firmly grounded in relational theory. Since many people have rightly enumerated the abundant problems that arise by storing data in CSV format in an RDBMS, I will only mention that you would be putting at risk the data integrity (which is widely considered one of the most important properties of a database), the performance of your system would be substantially diminished and you will end up re-implementing in your own application many of the capabilities that RDBMS offer natively, which have been vastly optimized over the years.

Do I store it in a separate table with a UserID?

Yes, to me that is the best approach and can be easily implemented in a relational system by means of normalization.
As I understand your specifications, MatchGender represents the gender of potential partners with which a given User is interested in getting in touch. So, the assertions a User seeks one-to-many MatchGenders and a MatchGender is sought by one-to-many Users are business rules of particular importance in this specific context.
If the above is correct, then there is a many-to-many relationship between User and MatchGender, and this kind of scenarios are very common in relational database development. I will model this aspect and offer an implementation example below, in my suggestion.

Pros. You can expect predictable results since your database will be built on relational principles. You can take full advantage of RDBMS capabilities, such as aggregate functions, JOINs, referential integrity, etc. You can prevent data redundancy and full table scans. Your database structure will be more flexible for future changes or extensions. You can deliver database performance that largely meets the needs of your clients. Your data retrieval will be straightforward.

Cons. At first glance, the relational approach may seem a bit cumbersome, since it requires the database designers and programmers to be more disciplined but, as the project grows, that discipline will pay off.

Other relevant aspects of your scenario
I want to describe some other points about my understanding of your situation. This way, I think that the statements a User pursues one-to-many Hobbies and a Hobby is pursued by one-to-many Users are important business rules as well. So, there is also a many-to-many relationship between the entities User and Hobby, and this also has to be modelled and implemented.
I assume that you will have a set of Hobbies that will be inserted into the database by some sort of system administrator. If so, a HobbyCode that uniquely identifies a given occurence of a Hobby may be defined and you can set it as the meaningful PRIMARY KEY of such table.
Modeling and implementation suggestion
Therefore, to deal with your situation, I propose the following IDEF1X data model:

And then, the next DDL can be derived and physically implemented with ease from such data model:
CREATE TABLE Gender
(
    GenderCode      CHAR(1)  NOT NULL,
    Name            CHAR(11) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Gender PRIMARY KEY CLUSTERED
    (
        GenderCode ASC
    ),
    CONSTRAINT UIX_Gender_Name UNIQUE
    (
        Name
    )
)

CREATE TABLE Hobby
(
    HobbyCode       CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    Descriptionn    CHAR(90) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Hobby PRIMARY KEY CLUSTERED
    (
        HobbyCode ASC
    ),
    CONSTRAINT UIX_Hobby_Name UNIQUE
    (
        Name
    )
)

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATETIME NOT NULL,
    GenderCode      CHAR(1)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY CLUSTERED
    (
        UserId ASC
    ),
    CONSTRAINT UIX_UserProfile_FirstName_LastName_and_BirthDate UNIQUE
    (
        FirstName ASC,
        LastName  ASC,
        BirthDate ASC
    ),
    CONSTRAINT FK_UserProfile_Gender FOREIGN KEY
    (
        GenderCode
    )
    REFERENCES Gender (GenderCode)
)

CREATE TABLE UserMatchGender
(
    UserId          INT      NOT NULL,
    GenderCode      CHAR(1)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserMatchGender PRIMARY KEY CLUSTERED
    (
        UserId     ASC,
        GenderCode ASC
    ),
    CONSTRAINT FK_UserMatchGender_UserProfile FOREIGN KEY
    (
        UserId
    )
    REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserMatchGender_Gender FOREIGN KEY
    (
        GenderCode
    )
    REFERENCES Gender (GenderCode)
)

CREATE TABLE UserHobby
(
    UserId          INT      NOT NULL,
    HobbyCode       CHAR(2)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserHobby PRIMARY KEY CLUSTERED
    (
        UserId    ASC,
        HobbyCode ASC
    ),
    CONSTRAINT FK_UserHobby_UserProfile FOREIGN KEY
    (
        UserId
    )
    REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserHobby_Hobby FOREIGN KEY
    (
        HobbyCode
    )
    REFERENCES Hobby (HobbyCode)
)


Adding wiredtiger nodes to a non-wiredtiger replica set Situation

I have 3 nodes on a Replica Set (P+S+S). Mongodb version 2.6.9. I would like to migrate all the nodes to 3.0.4 wiredtiger.

When I need to do that without downtime, I do the following workflow:

setup a new server with 3.0.4
add the node to the replica set
remove one of the old nodes
go to step 1

Problem is that with my set of data, adding a node with the "rs.addnode" tactic just doesn't work. The process takes forever and starts the initial syncronization again and again. To walkaround this, I normally do a cold backup from one of the old nodes to the new one. That takes the data and the local database, the node joins the replica set without initial syncronization.

Problem is that I cannot do that because the file system is not the same. If I can put one with wiredtiger, I can do the cold backup trick with the other ones.

Attempts I made with no luck:

reduce size of my database
reduce the Oplog size
try with a dump backup / restore
rs.add

I know there is a way to add a node with a setting --fastsync. But to do that, I need to have a copy from the primary, and the files are not the same. Not sure if that will do the trick with dump backup / restore. I would know if when the node takes the new member as a valid secondary, I can restart the node without the fastsync or I should put that setting forever.

This is my mongod.log when it fails with the initial sync:

I INDEX    [rsSync]         building index using bulk method
I INDEX    [rsSync] build index done.  scanned 3 total records. 0 secs
I REPL     [rsSync] initial sync cloning db: config
I REPL     [rsSync] initial sync data copy, starting syncup
I REPL     [rsSync] oplog sync 1 of 3
I NETWORK  [rsSync] Socket recv() timeout  x.x.x.x:x
I NETWORK  [rsSync] SocketException: remote: x.x.x.x:x error: 9001 socket exception [RECV_TIMEOUT] server [x.x.x.x:x]
I NETWORK  [rsSync] DBClientCursor::init call() failed
E REPL     [rsSync] 10276 DBClientBase::findN: transport error: x:x ns: local.oplog.rs query: { query: {}, orderby: { $natural: -1 } }
E REPL     [rsSync] initial sync attempt failed, 9 attempts remaining

SOLUTION (see comments)

configure ulimits:

sudo nano /etc/security/limits.conf
# add at the bottom
mongod       soft    nproc   64000
mongod       hard    nproc   64000
mongod       soft    nofile  64000
mongod       hard    nofile  64000
mongod       soft    fsize   unlimited
mongod       hard    fsize   unlimited
mongod       soft    cpu     unlimited
mongod       hard    cpu     unlimited
mongod       soft    as      unlimited
mongod       hard    as      unlimited

configure keep alive

sudo nano /etc/sysctl.conf
# add at the bottom
net.ipv4.tcp_keepalive_time = 120

Note: I did that on the new member and on the existing ones

To migrate data between different storage engines in MongoDB 3.0 you will need to perform an initial sync to the new replica set node or use mongorestore to seed a new replica set with your data.

If you want to add a node with a new storage engine to a live production replica set, rs.add() is the correct approach. To ensure a smooth upgrade I would also upgrade all existing nodes to the latest version of MongoDB 3.0.x with MMAP storage (the default) before adding your new WiredTiger node(s).

The key information in your question (and comments) is:

socket exceptions related to networking timeouts:

I NETWORK  [rsSync] Socket recv() timeout  x.x.x.x:x
I NETWORK  [rsSync] SocketException: remote: x.x.x.x:x error: 9001 socket exception [RECV_TIMEOUT] server [x.x.x.x:x]

deploying on Azure, which has some known issues with timeouts

As per the MongoDB production notes on Azure, you should reduce the TCP keepalive setting to avoid network timeouts:

  The TCP keepalive on the Azure load balancer is 240 seconds by default, which can cause it to silently drop connections if the TCP keepalive on your Azure systems is greater than this value. You should set tcp_keepalive_time to 120 to ameliorate this problem.

As further preparation for successful initial sync with WiredTiger you should review the MongoDB production notes for your operating system.

Since you mention using Linux, I would recommend checking:

ulimits are appropriately set. WiredTiger creates more files than MMAP (separate data & index files per collection rather than per database) so will use more file handles than MMAP nodes in the same replica set. If you don't increase the ulimits from the default you may find the initial data transfer completes but initial sync fails at the index build stage with a fatal error like Too many open files.
the WiredTiger data directory is on a volume using the XFS file system. As at the time of writing, there are known performance issues (such as throughput stalls) with EXT4.

The production notes in the MongoDB manual are updated regularly based on issues and experiences reported by MongoDB users, so should be part of your preflight checklist for production O/S deployments or upgrades.


How can I automate copying DB2 table data between remote Servers? I have two IBM DB2 servers at separate remote locations.

I need to copy data from several tables on one database on the first server to another database in the second server.

Previously I've used the IBM data studio tool to export the data and Replace Load it into the other table in the second server.

I am in need of a way to automate this.  Probably through command line shell scripts.

How can I accomplish this?

I think you may have answered your own question. I would use command line shell scripts. Seeing as you are on Linux, you have several to choose from (sh, ksh, and perl to name a few).

Another thing that could make things easier is if you catalog the remote server and database locally. Then you can connect to it and work with it without your script having to worry about ssh'ing over to another box.


Error starting MongoDB on CentOS When I run the mongod --dbpath /data/db command to start MongoDB, I get the following error message:

oot@vps [/]# mongod --dbpath /data/db
2015-11-17T21:10:54.048+0000 SEVERE: terminate() called, printing stack (if implemented for platform):
0x121d451 0x121c6d8 0x7f77f06c7bd6 0x7f77f06c7c03 0x7f77f06c7d22 0x771858 0x1262158 0x121ceb9 0x76fc75 0x76ff69 0x7f77efdfbd5d 0x766529
 mongod(_ZN5mongo15printStackTraceERSo+0x21) [0x121d451]
 mongod() [0x121c6d8]
 /usr/lib64/libstdc++.so.6(+0xbcbd6) [0x7f77f06c7bd6]
 /usr/lib64/libstdc++.so.6(+0xbcc03) [0x7f77f06c7c03]
 /usr/lib64/libstdc++.so.6(+0xbcd22) [0x7f77f06c7d22]
 mongod(_ZN5boost15throw_exceptionINS_21thread_resource_errorEEEvRKT_+0x1c8) [0x771858]
 mongod(_ZN5boost6thread12start_threadEv+0x138) [0x1262158]
 mongod(_ZN5mongo27startSignalProcessingThreadEv+0x299) [0x121ceb9]
 mongod() [0x76fc75]
 mongod(main+0x9) [0x76ff69]
 /lib64/libc.so.6(__libc_start_main+0xfd) [0x7f77efdfbd5d]
 mongod() [0x766529]

I am new to MondoDB and Linux servers, so really not sure where to start with this, there doesn't seem to be any usefull info in the error.

Has anyone else had anything similar? Or any ideas on where to start with fixing it?

In my case this turned out to be caused by not enough memory

I concluded this after running some of the commands found in this article:
http://www.cyberciti.biz/faq/linux-check-memory-usage/

egrep --color 'Mem|Cache|Swap' /proc/meminfo will output how much memory is available. Don't forget that Mongo itself will require memory, so even if you have got a bit of free mem, it may not be enough.

I restarted my server, which killed a few of the memory sucking processes, and this for now has solved my problem.


MariaDB: Insert records (list of tuples) into table if not exists Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='1' AND col2='2'
) LIMIT 1;

INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='3' AND col2='4'
) LIMIT 1;

Generally speaking, as rule of thumb the less queries the better which is why I want to rewrite the sql queries into one query. This should make it even faster when having millions of datasets as the WHERE clause would only iterate through the table once, hence this should be possible:
WHERE col1 in ('1','3') AND col2 in ('2','4')

But when I try multiple select statements in a single query like:
SELECT * FROM
(SELECT '1','2') AS tmp1,
(SELECT '3','4') AS tmp2

SQL throws this error:
#1136 - Column count doesn't match value count at row 1
The problem is with SELECT * FROM (SELECT '1','2') AS tmp.
How can I use several tuples (1, 2) (3, 4) in the subquery for it meet the WHERE condition?
Is there a shorter and faster way to achieve this?
Any feedback is highly appreciated!
Please ignore the data types; the numbers should be formatted as int not as varchar.

UPDATE
When executing the following:
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '7','8') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='7' AND col2='8'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '9','9') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='9' AND col2='9'
) LIMIT 1)) t1;

SQL throws: Duplicate column name '9'

Your SELECT with two select can be JOINed or UNIONìted
As you want to insert two rows, you need UNION like this
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='1' AND col2='2'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='3' AND col2='4'
) LIMIT 1)) t1;

I don't see why it should give you an error
INSERT INTO `test` (col1, col2)
SELECT * FROM
((SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='1' AND col2='2'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '3','4') AS tmp
WHERE NOT EXISTS (
    SELECT * FROM `test`
    WHERE col1='3' AND col2='4'
) LIMIT 1)
UNION
(SELECT * FROM (SELECT '9','9') AS tmp  WHERE col1='9' AND col2='9')
) t1;


How can I reset the counters from pg_stat_database view? I know I can reset some counters by running:

select pg_stat_reset()

but some counters like xact_commit aren't reset.

How can I do it?

I've tried also:

pg_stat_reset_shared('database')

but doesn't work.   :-(

Any ideas, please?

I could store the last counters and substract for the next time.

The solution is simple, store previous values and substract.
No need to reset anything in Postgres.
In this way meaningful stats can be obtained:

(newValue-oldValue)/numberOfSeconds


Import a large MySQL dump 68GB on RDS instance I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM).

The import takes more than six hours:

More than 500 tables
I have 2 large tables: the first one 57GB, the second 21GB
InnoDB for all tables
The commands used are the following:

mysql -u root -p{db_pwd} -h {db_host} -f -e "\

SET foreign_key_checks=0; SET unique_checks=0; SET autocommit=0; \

source /tmp/dump.sql; \

SET foreign_key_checks=1; SET unique_checks=1; SET autocommit=1;"

How can I optimize my import with removing indexes, or by means of other strategy, on these large tables?

I tried with:

   innodb_flush_log_at_trx_commit = 0
    innodb_autoinc_lock_mode=2
    innodb_write_io_threads = 16
    innodb_log_file_size = 1073741824 (1 Go)
    innodb_log_buffer_size = 8388608  (8 Mo)
    innodb_buffer_pool_size = 3/4 RAM

but apparently no impact.

You may try to edit your mysqldump file to remove indexes. It's just a text file with SQL statements, so you need to somehow find CREATE TABLE statements in it and remove KEY lines from them (except from PRIMARY KEY). But that can hardly help with the speed because InnoDB index creation is quite efficient and doesn't take overhead time compared to separate index creation after the import.

If you have limited time access to the RDS instance, I would recommend to deploy the dump on your local server/PC (it may take even longer), then make a dump of only smaller tables to apply on RDS separately, then think how to move the larger ones. LOAD DATA INFILE may be not much faster than mysqldump so it's worth considering copying tablespaces (e.g. "cold backup method" here: https://dev.mysql.com/doc/refman/8.0/en/innodb-migration.html ).


SQL Server: Can Page Verify be set to Checksum on any database, or must I check compatibility? In Microsoft SQL Server, I have some older databases set to "Torn Page Detection" rather than "Checksum" page verify.

Do I need to determine if a given SQL Server database is 'compatible' with the "Checksum" page verify setting, or is it something that can be applied to any SQL database?

Are the databases even aware what their setting is? It is a setting that is handled transparently by SQL Server correct?

I realize that the change will only affect pages changed after the config takes effect.

Update. I can see my answer being a bit unclear updated it to make it more clear

*Short Answer: No *

You don't need to check compatibility, yes it is something that can be applied to any database. It will be compatible, even if it is an 80(SQL Server 2000) compatibility mode database, you can enable Checksum, and likely should.

This is transparent in that your tables don't look or act differently, your apps don't know about it, etc. As for the "database knowing" it depends on how you mean it. From the app connecting? No. But the SQL Server storage engine that interacts with the files knows it, so it does the checks on write, but again that is transparent to your app and users.

One more consideration:

You are correct, only pages changed after it takes effect is a good thing to keep in mind. Doing index rebuilds at some point after doing this is one good way to touch a whole lot of pages, and not a bad idea.

And watch performance/CPU overhead. There is a "cost" associated with Checksum, though that cost isn't as high as you might fear. This article, though written for 2005, talks about some of those concerns, but again it is rare to have an issue from this there.


Create Table Every X Rows I have a SQL Table that we are selling off some of the data, so I need a quick way of splitting the main table into sub tables every 50 rows.  Table nomenclature does not matter, I am just stuck on a way of selecting 50 different rows for each table with the table not holding a primary key or numeric field.

How would one go about this in SQL Server 2008?

Since you say you can change the original table:

Alter original table, adding new column batch_id (default NULL).
Run the following

DECLARE @rowCount int = 1
       ,@batchId  int = 1
;

WHILE @rowCount > 0
BEGIN
    UPDATE TOP (50) myTable
       SET batch_id = @batchId
     WHERE batch_id IS NULL
    ;
    SELECT @rowCount = @@ROWCOUNT
          ,@batch_id = @batch_id + 1
    ;
END;

Then, simply create new tables myTable_batch_x, where x is 1 through @batchId - 1, and assigned records based on the stored batch IDs.

If you can't modify the original table, but have space to make a copy of it, create a copy with the new column, and proceed as above.

Alternately: add a my_row_id column (integer) to the original table, as an IDENTITY column:

ALTER TABLE myTable
  ADD COLUMN my_row_id int IDENTITY(1,1) NOT NULL;

This will create the new column, and automatically populate it with sequential integers starting from one.

Using the new value, assign rows to batches (1-50, 51-100, etc.). To get the rows that go into a given batch:

SELECT *  -- or, actual column list - you can exclude my_row_id that way
  FROM myTable
 WHERE (((my_row_id - 1) / 50) + 1) = @batch_no
;

Since my_row_id is an integer, if we divide it by another integer any fractional result will be dropped. So, 0/50 = 0, ..., 49/50 = 0, 50/50 = 1, .... To include my_row_id = 50 in batch 1, we simply subtract 1 from my_row_id. And, to start with batch_no 1 instead of 0, we add one to get our batch_no.

NOTE: As noted in the comments, you can simplify the code slightly. If you use IDENTITY(50,0) when adding my_row_id, then the WHERE clause could be:

 WHERE (my_row_id / 50) = @batch_no

The first 50 rows would be numbered 50-99, and this equation would evaluate to 1 for them; for the next fifty rows (100-149), it evaluates to 2; and so on.


Attaching / creating database on network drive - Access is denied I have two computers, computer A is with SQL Server but has no space left, the other is computer B which has free space.
I'd like to create a new database on computer B. This is what I've done so far:

I've created a shared folder called shared on computer B, and mapped it on computer A as a network drive X:/ (or also \ADMIN-PC\shared).
set privileges on the folder so that Everyone can do everything
I've checked that I can access the drive X from cmd
I've run SQL Server Management Studio as Administrator, and logged in using Windows Authentification
then I've tried to create database

CREATE DATABASE [networked] ON  PRIMARY
( NAME = N'networked', FILENAME = N'\ADMIN-PC\shared\networked.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'networked_log', FILENAME = N'\ADMIN-PC\shared\networked_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Which gives me this

Directory lookup for the file "\\ADMIN-PC\shared\networked.mdf" failed with the operating system error 5(Access is denied.)

I've also tried to attach existing database on the network drive, with same result. How should I set the privileges so that I can create/connect to database on network drive?

Just to state the full limitations, this is SQL 2008 R2 and above, and it's best to use fully qualified names for the shares rather than mapped drives.

Are both computers on a domain? The SQL service account (or if it's NETWORK SERVICE then the computer account instead like DOMAIN\COMPUTER$) needs a few things:

Full control on the share.
Full control on the NTFS folder on the other side of the share.
If you're attaching files, your own account needs the same permissions.

That last one is a little doozy. As you mentioned you were running SSMS as Administrator (which is one issue sometimes). Otherwise you may be having double-hop issues; which you could eliminate by running it from the source machine over RDP (if you aren't already).

I feel like I'm not telling you anything you don't know already. When things get sticky there are two tools:

xp_fileexist and xp_cmdshell (to check SQL's access to various parts, like can it ping the network address, can it see the share, can it echo something to a file there, etc).
When you're out of all options, run the sysinternals utility Process Monitor on the share destination (fine for Windows, not an option when it's NetApp or something else). You can add a filter on access denied, these pop up when you try to access the share, and you can dig into the account details being used for the access and getting denied.

Hope that helps a little.


Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default? Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior.

-- Table: staging.mytable

-- DROP TABLE staging.mytable;

...

WITH (
  OIDS=FALSE
);

ALTER TABLE staging.mytable
OWNER TO postgres;

1 Why oids=false?

OIDS=FALSE/TURE,  the default setting depends upon the default_with_oids configuration parameter，The parameter is off by default。in PostgreSQL 8.0 and earlier, it was on by default.

2 Why ownered by postgres?

Please check the role you use log in  when using  pgAdmin3 ， if you log in as postgres role ,then  the owner of  new created table will be the postgres role.


Generating 50k rows of unix time entries auto-incremented 5 mintes each I am relatively new at using mysql, or any DB for that matter, so let me know if I'm doing this in a round-about way.

I have 4 similar tables with two rows columns each - unixtime + count. They all look something like this:

mysql> select * from wifiexporttotal limit 5;
+------------+-------+
| epoch      | count |
+------------+-------+
| 1446292800 |   298 |
| 1446293100 |   332 |
| 1446293400 |   375 |
| 1446293700 |   439 |
| 1446294000 |   412 |
+------------+-------+

etc...

These are log entries rounded to the nearest 5 minute interval for approx 6 months with around 50k entries each.

I want to join these in a single table with unixtime + 4 columns of values, and have done so successfully. However, there are some timestamps without a log entry in any of the four tables, and I would like for these to show zero instead of missing skipping rows.

So my question is - How can I generate a table with 50k rows and a single column that starts with a bigint (1446332400 for nov 1st in my case) and auto increments with 300 (5 minutes) per row? I assume I could LEFT JOIN the other 4 tables with this table on the timestamp and get an unbroken string of 5 minute intervals for the 6 months this way.

Plan A:  (Drawback: you might still have gaps.)

Find all epoch values from the 4 tables using UNION DISTINCT.
LEFT JOIN from that table to the other 4 tables.

Plan B:  (Drawback: I don't say how to do step 1.)

Build a TEMPORARY table with every 5-min time for the next N months.
LEFT JOIN ...

Plan C:  (Drawback: Step 1.)

Upgrade to MariaDB.
Use a "sequence" table to 'virtually' generate enough integers.
Use some simple arithmetic to convert the integers to epochs.
LEFT JOIN ...


Return Parents With Only One Child In a Parent and Child relationship, I'm trying to return Parents that only have one Child and a where clause applied to the only child firstname like 'tom'

t-sql pseudo: parents with only one child and that child's name is tom

select p.*

from parents p
  inner join children c on c.parentId = p.id

where count(c.id) = 1 -- ??? with only 1 child ???
  and c.firstname like 'tom'

Any ideas?

Assuming the primary key of children is (horror) id:

select p.*

from parents p
  inner join children c on c.parentId = p.id

where c.firstname like 'tom'
  and not exists
      ( select 1 from children c2
        where c2.parentId = p.id
          and c2.id <> c.id
      ) ;

or using GROUP BY:

select p.*

from parents p
  inner join
    ( select parentid
      from children c
      group by parentid
      having min(id) = max(id)          -- you could use instead:  count(*)=1  here
         and min(firstname) like 'tom'
    ) c
    on c.parentid = p.id ;


SQL: return values as column table I am running this query to get all returns for a list of tickers:

select return1
from STOCK_DATA
where TDATE between 20130101 and 20131231
and TICKER in (SELECT DISTINCT TICKER FROM STOCK_DATA)

However, it stacks all returns for the tickers into one column, but I would like to have a table with return1 columns per each ticker?

Perhaps you need to run this as a GROUP BY aggregation using the GROUP_CONCAT function.

SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER;

CAVEAT #1

In your DB Session, you may need to extend the length of [GROUP_CONCAT} output because its default limit is 1024. Run this before each connection to set limit to 1M:

SET group_concat_max_len = 1048576;

CAVEAT #2

The above query will only give you ticker_returns if the stock symbol was traded with the specified TDATE range. If you want all tickers to show up regardless of whether or not it had returns in the specified TDATE range, do this:

SELECT A.TICKER,IFNULL(ticker_returns,'<No Returns>') ticker_returns
FROM
(SELECT DISTINCT TICKER FROM STOCK_DATA) A
LEFT JOIN
(SELECT TICKER,GROUP_CONCAT(RETURN1) ticker_returns
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER) B
USING (TICKER);

If you want a count for each TICKER, just count the number of commas in the ticker_returns and add 1 if the ticker_turns is not null. If you want a query with the count for each ticker, do this:

SELECT A.TICKER,IFNULL(ticker_return_count,0) ticker_return_count
FROM
(SELECT DISTINCT TICKER FROM STOCK_DATA) A
LEFT JOIN
(SELECT TICKER,COUNT(1) ticker_return_count
FROM STOCK_DATA WHERE TDATE BETWEEN 20130101 and 20131231
GROUP BY TICKER) B
USING (TICKER);

GIVE IT A TRY !!!

For increased query performance, please create this index during your off hours:

ALTER TABLE STOCK_DATA ADD INDEX (TDATE,TICKER,RETURN1);

This should speed up the query.


How to migrate external procedure from Oracle Listener to Postgres Consider that I'm using C or java external procedure called from my Oracle 12.2 Listener (EXTPROC listener parameter).
That Oracle database will be migrated to Postgres.

My question is how to migrate the C or java external procedure?

Should I install C or java extension for Postgres, and then compile these procedures inside my Postgres database?

You can rewrite it as a normal C or PL/Java function in PostgreSQL.

If it is important for you to run the function in a separate process, give PL/Container a try.


Many-to-one Subselection in single query I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2.

To create a simple example, lets say T1 is Cars, and T2 is a table of imperfections. So, a car can have 0..N imperfections, and we store these imperfections in T2 as integers.

I would like to select * from only those cars in Cars that contain imperfections i1 AND i2.

Performing an OR instead is pretty easy:

SELECT * FROM cars AS T1
  WHERE EXISTS (
    SELECT imperfection FROM Imperfections as T2
      WHERE T1.uid = T2.uid AND (imperfection = 1 OR imperfection = 2)
  );

I've been trying set logic using intersection, but at this point, I'm wondering if I'm over complicating it.

You could have two separate exists clauses:

SELECT *
FROM cars AS T1
WHERE
    EXISTS
    (
        SELECT * FROM Imperfections as T2
        WHERE T1.uid = T2.uid AND imperfection = 1
    )
    AND EXISTS
    (
        SELECT * FROM Imperfections as T2
        WHERE T1.uid = T2.uid AND imperfection = 2
    );

This means two left semi joins to your Imperfections table, but if you have an index on UID and Imperfection, those should be trivial.

There are two advantages to using WHERE EXISTS over JOIN in this case.  The first is that a left semi-join stops after the first match, so it can be quicker than a join (which needs to go through all records).  The second is that if you somehow don't have good data constraints and have two records in Imperfections with the same UID and Imperfection, you could end up with duplicate rows back in your main query.


How to change VARCHAR length in PRIMARY KEY in SQL Server 2014 I need to change the VARCHAR length from 20 to 25 in a code column in a library table in SQL Server 2014, but SSMS wants to DROP and RE-CREATE all my tables where the library table in question is linked to.

Is there a way to change the VARCHAR length of a PRIMARY KEY without dropping all the tables?

As you are increasing the length you can just do an alter column, for example:

CREATE TABLE dbo.T1
    (
      C1 VARCHAR(20) NOT NULL
                     CONSTRAINT PK_T1 PRIMARY KEY ,
      C2 INT
    );
GO

INSERT  INTO dbo.T1
VALUES  ( 'test', 1 ),
        ( 'test2', 2 );
GO

ALTER TABLE dbo.T1 ALTER COLUMN C1 VARCHAR(25) NOT NULL;

Further reading on the ALTER TABLE syntax at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql


Select rows other than first selected row in a table My table looks like

 id     ledgerID    Amount  Debit or credit     TransactionType
 --------------------------------------------------------------
  1         1        500       dr               Sale1
  2         2        300       cr               sale1
  3         3        400       dr               sale1
  4         1        1000      dr               sale2
  5         2        200       cr               sale2

Now I like to select this table like

sno  LedgerID   Transaction     Dr        Cr
-----------------------------------------------
1    1          sale1          500
         2      300 cr
         3      400 dr
2    1          sale2         1000
         2      200 cr

How to do this?

You can split the debit/credit columns using a CASE syntax:

(CASE WHEN [Debit or credit]='cr' THEN Amount END) AS cr,
(CASE WHEN [Debit or credit]='dr' THEN Amount END) AS dr

As for the "sno" column, I presume that the answer you're looking for is something like

DENSE_RANK() OVER (ORDER BY TransactionType) AS sno


It depends on what you are going to do with the data, as it always does.  It's usually dangerous to assume that the data is never going to be used in some other way.  Your question illustrates this nicely.  The original design of "summaries" assumed that weekly summarization would always be the intended use, and now you want daily summarization.

The structure of the summaries table (or view) is what can be called crosstabulation.  Crosstabulation is frequently used in spreadsheets as a convenient way of displaying data that has been summarized along two dimensions.  See the pivot table feature for more details.

Crosstabulated data is not in first normal form.  The folks who invented first normal form were thinking about something when they invented it.  They were thinking about keyed access to all data.  The power of keyed access to all data is central to the simplicity and power of the relational data model. You can look up more detail under first normal form.

Based on this, I would say that one row per line item is the best structure for this data, assuming that no finer granularity can be obtained.  If you want to summarize by day, or by week, or by month or whatever, you can summarize by using a pivot operation in a view.

This will take some computer resources, to be sure.  Computer resources are usually cheap, compared to "we can't do what you want because the data isn't organized that way".

As far as existing code goes, just create views that make the data look the way  it needs to look for the existing code, and tweak the existing code to use the view.  Unless the functionality of the existing code needs to be changed as well.

Edit based on comments:

You probably know more about cross tabulation than you think you do.  Look at the diagram you provided at the top of your question.  That's a cross tabulation.  It tabulates by product down the left side, and by week number across the top.  The cells contain some kind of aggregate statistic, like the total count of bath sheets sold in week 2.

It's probably a summary of invoice data, or something like that.


Error: Could not read Block X of relation base/Y/Z We have some serious issues with our PostgreSQL-Server (Version 8.4). Our webapplication uses jdbc to connect to the PostgreSQL Server. Suddenly our webapplication cant connect to the PostgreSQL Server. We get the PSQLException: Broken Pipe. Cause of this, i tried to connect via pg_admin. This works, but i'm getting errors like this:

ERROR:  could not read block 32570 of relation base/16390/2663: read
only 0 of 8192 bytes

I tried to make a dump to backup the data and this doesnt work too:
(Look on the edit at the bottom of the side)

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not read block 32570 of relation
base/16390/2663: read only 0 of
8192 bytes
pg_dump: The command was: LOCK TABLE
public.results_233_top100_disease_state_karyotype IN ACCESS SHARE MODE

I tried to get some information with
SELECT oid, relname FROM pg_class WHERE oid=2663

The result was this:

2663 ; "pg_class_relname_nsp_index"

During my effort of collecting some information, i read, that recreating that specific index could help. So reindexed it with the following command:
REINDEX INDEX pg_class_relname_nsp_index

This didnt helped at all, and now i'm pretty helpless. Has somebody an idea what i can do ? Another point is, that we do weekly backups. Is it possible to overwrite the data folder: /var/lib/postgresql/8.4./main with our data from the backup?
EDIT: I fixed the problem with reindexing the table that had the error. Now i'm getting another error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: invalid page header in block 1047 of relation base/16390/16398
pg_dump: The command was: COPY public.data_1 (sampleid, feature, value) TO stdout;

Before doing anything else, read and act on: http://wiki.postgresql.org/wiki/Corruption .

Most likely you have disk or file system problems.

If you suspect any kind of DB corruption for whatever reason you should stop the DB and copy the entire database at the file system level before attempting any recovery.

Once you've done that, then you can look into possible repairs. You'll probably have some significant data loss, so your goal should be to get it working to the point where you can pg_dump the damaged databases, re-initdb, and reload.

If you have a recent backup, now would be a good time to think about using it.


Best practices for selecting partition boundary ranges to maintain empty partitions at both end? I have partitioned the table based on a date datatype partitioning column.

The data for the column starts at "2014-01-01" and ends at "2015-01-01". I know that, the partitions should have empty partitions at left and right ends. So, I selected the following boundary ranges while creating the partition function. The partition function is shown below,

create partition function PfDateRange(date)
as range right for values('2014-04-01','2014-07-01','2014-10-01','2015-01-01')

I would like to hear some tips or good practices for selecting boundary ranges.

Also, at future the data may get inserted which were greater than the last boundary range. So how to maintain empty partition at the right end?

Thanks in advance.

Let me start by mentioning that these 4 boundaries result in following 5 partitions.  Partitions 1 and 5 are unbounded one one side and partitions 2, 3, and 4 are constrained to quarterly data:

partition 1: values less than '2014-04-01'
partition 2: values >= '2014-04-01' and < '2014-07-01'
partition 3: values >= '2014-07-01' and < '2014-10-01'
partition 4: values >= '2014-10-01' and < '2015-01-01'
partition 5: values >= '2015-01-01'

The rationale for keeping (at least) the first and last partitions empty is to ensure expensive data movement is not required during partition maintenance of an incremental or sliding window scenario.  If data must be moved to a different partition to accommodate new boundaries during MERGE or SPLIT, the logging is about 4 times greater than normal DDL operations.  This is especially nasty with large tables, as is common with table partitioning.

Data movement is never required when an existing empty partition is split or when 2 adjacent empty partitions are merged.  This is why the best practice is to plan for such, making the maintenance a no-brainer that can easily be automated.

That said, one can split a non-empty partition without overhead in some cases.  For example, you could split the function to add the new '2015-04-01' boundary without data movement as long no existing rows are >= '2015-04-01'.  But to also avoid an expensive scan during the offline operation, you need an index with the partitioning column as the high-order key column so that SQL Server can efficiently validate no data exists beyond the new boundary.  So in your case, you could probably split the last partition in advance of the start of the next quarter without incurring overhead if the partitioning column is indexed (a common design pattern).

Assuming your objective is 4 quarterly partitions of historical data plus the current active quarter in a sliding window, I suggest bounded partitions for each of these 5 quarters plus another bounded one for the future quarter.  This will result in 8 partitions when you include the unbounded first and last ones.

CREATE PARTITION FUNCTION PfDateRange(date)
AS RANGE RIGHT FOR VALUES(
      '2014-01-01'
    , '2014-04-01'
    , '2014-07-01'
    , '2014-10-01'
    , '2015-01-01'
    , '2015-04-01'
    , '2015-07-01');

The reason one might want additional future empty partitions is to provide time to react in case partition maintenance is missed for some reason.  Multiple future partitions are more important with more frequent maintenance (e.g. daily), and less so for quarterly maintenance.

After the start of the '2015-04-01' quarter, the partition maintenance script can:

switch '2014-01-01' to a and empty staging table and truncate staging
merge the '2014-01-01' partition to remove it
spilt at '2015-10-01' for the next future quarter

You may want to do a sanity check before the maintenance in case you have bad data outside of the expected range (less than '2014-01-01' or greater than '2015-07-01').  Alternatively, maintain a check constraint for the expected range so that only valid data can be inserted.


Implement Log Shipping in prod env We have a 3.5 TB DB which is already involved in log shipping to two different servers, as one will be in restore sate only and one will be in stand by mode which helps for reporting needs. All three instances are SQL Server 2005.

Now we want to implement log shipping to third server (SQL Server 2008) without downtime. We restored a full backup of primary db on the 2008 server and it took 2 hours to restore and later on we took a differential back up to of primary (since we have too many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server. How can we implement log shipping in this scenario? Is it possible to implement with out any downtime.

We have a 3.5 TB DB which is already involved in log shipping to two different server, as one will be in restore sate only and one will be in stand by mode which helps for reporting need. all source and destinations are sql 2005.

Make sure you have Instant File Initialization ON. This helps to drastically cut down the backup/restore times.

  we took a differential backup to of primary (since we have to many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

A differential or FULL backup does not break the log chain. Also, logshipping would not skip the files. There has to be something that might be taking ad-hoc log backup and then breaking logshipping.

I have set up a 3 way logshipping (dont know what to call .. so calling 3 way - one primary ships to 3 other standby servers) and it works without any issues.

  Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server.

As I mentioned earlier, Instant File Initialization will help with the restoring of full backup - cutting down the restore time.

Setting up log-shipping won't involve a downtime on your primary server. It will only on your secondary server - if you are doing reporting and this is your case. It is by design, that during the restore - you cannot access the database.


Change Location of VALIDATE RECOVERY FILES work area Is there a way to tell the VALIDATE RECOVERY FILES command to use a different location with more available disk space?
When I run the command in RMAN, it fails with the following error stack:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of validate command on ORA_DISK_1 channel at 06/05/2012 12:02:42
ORA-19502: write error on file "/opt/app/oracle/product/11.2.0/dbhome_1/dbs/...
ORA-27072: File I/O error
Additional information: 4
Additional information: 1071488
Additional information: 286720
ORA-19502: write error on file "/opt/app/oracle/product/11.2.0/dbhome_1/dbs/...

This appears to be due to the fact that the validation is using the database home for it's work area and there isn't enough disk space there.  I can actually see the 8GB free there drop to zero before the command fails and the space is returned.
This location is not affected by setting the location in the format when allocating the channel for the device.
DB_RECOVERY_FILE_DEST is set to '+FRA', but currently most of the backups are in /u01/back rather than ASM.  '+FRA' does have some control file backups.
Update:
I haven't found a way to do this, but I also haven't found anything definitive saying it is not possible.  There is an Oracle community topic discussing this, but no resolution.  Interestingly RESTORE ARCHIVELOG ALL VALIDATE and RESTORE VALIDATE DATABASE CHECK LOGICAL do not exhibit this behavior, which may be why more people haven't run into it.
Update 2:
I opened a SR with Oracle support to see if they can shed light on this question.
Update 3:
Oracle support was able to reproduce this issue in house and they are treating it as a bug.

Oracle Support closed bug 14226856 as "not a bug" and said it will be fixed in 12c.


Speed up a MySQL BETWEEN query SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo

I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind,
dateFrom, dateTo and a combination of dateFrom and dateTo.

But nothing changed, with or without indexes the query time is the same. I know BETWEEN doesn't use indexes, is it true?

As  M Khalid Junaid suggested, this is the EXPLAIN PLAN of the above query:

+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
| id | select_type | table        | type | possible_keys                  | key  | key_len | ref  | rows  | Extra                                          |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+
|  1 | SIMPLE      | table1       | ALL  | dateToFind                     | NULL | NULL    | NULL | 32027 |                                                |
|  1 | SIMPLE      | table2       | ALL  | dateFrom,dateTo,dateFrom_dateTo| NULL | NULL    | NULL |  6951 | Range checked for each record (index map: 0xE) |
+----+-------------+--------------+------+--------------------------------+------+---------+------+-------+------------------------------------------------+

Any suggestion?

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx .

Add:
Here is the explain for the test table

explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1;
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+-------+-------------------+---------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | t2    | index | from1,to1,from_to | from_to | 12      | NULL |    2 | Using index                                    |
|  1 | SIMPLE      | t1    | ALL   | idx               | NULL    | NULL    | NULL |    5 | Range checked for each record (index map: 0x1) |


How to prevent big performance impact during index creation I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is there a way to prevent or limit this? After all I am creating the index to increase performance. Or is this a onetime hit I have to endure for things to be better in the future?

There are many possible explanations for this. It may be that you have to accept it as the cost of making an improvement but it may be highlighting issues elsewhere in the system. Possible causes:

Fragmentation of the table.
Fragmentation of the physical database files.
Poor choice of clustered index key.
Large table with low percentage of its data in the buffer pool, requiring lots of physical IO to read.
Lack of spare memory/io/cpu capacity to deal with this one off task on top of the normal workload.
Index creation requiring tempdb for sorting but tempdb on same drive as data and or log files.

Your question is a little sparse on the details necessary to provide a conclusive answer. To start with, can you add the table DDL (including clustered index definition) to your question along with row counts and database size. Also, a description of the server spec (memory, cpu, drive configuration) and whether or not tempdb is on a separate array.

Next, the following will give us an indication of how your IO subsystem is performing.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

The following will tell us how badly fragmented your existing indexes are. You may want to run this at a quiet time as it can impact on performance.

SELECT
    DB_NAME(database_id) AS [Database Name]
  , OBJECT_NAME(ps.OBJECT_ID) AS [Object Name]
  , i.name AS [Index Name]
  , ps.index_id
  , index_type_desc
  , avg_fragmentation_in_percent
  , fragment_count
  , page_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
INNER JOIN
    sys.indexes AS i
ON  ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE
    database_id = DB_ID()
AND page_count > 500
ORDER BY
    avg_fragmentation_in_percent DESC ;


Why does MATCH() give Score 0, if there are less rows in the database? I followed this example from the Mysql Documentation:

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     );

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

SELECT *, MATCH ( title, body ) AGAINST ( 'database' ) AS Score
    FROM articles
    WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------+------------+
| id | title             | body                         | Score      |
+----+-------------------+------------------------------+------------+
|  5 | MySQL vs. YourSQL | In the following database... | 0.662664...|
|  1 | MySQL Tutorial    | DBMS stands for DataBase ... | 0.655458...|
+----+-------------------+------------------------------+------------+

this works, but if i delete all rows, but one:

DELETE FROM `articles` WHERE id >1

then the select gives Score 0:

SELECT * , MATCH ( title, body ) AGAINST ( 'database' ) AS Score FROM articles

With only one line left all occurances are 100% of the search, and everything above 50% is considered a stop word. My guess is that a stop word gets scored 0. There's also a stop word list (the word 'database' is not in it though) found here: http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

From http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html you find:

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match.


Postgres - autovacuum on several big tables I have an issue with autovacuum - I have several big tables (over 10 million records) which are updated very frequently (500 updates per second). Everything works OK until bloat happen :)

As I can see, some of the tables are not autovacuumed for more than 10 minutes, and in that period, a lot of dead tuples are generated which then kill performance.

So, my question is - what is the flow of autovacuum process?
E.g. If I have 10 tables that should be vacuumed (autovacuum threshold is reached on those tables), will Postgres vacuum tables one-by-one, or will it vacuum all tables in parallel? I couldn't find that info in official documentation. Another question - Postgres will spawn single autovacuum worker per database? If I have only one database, then I do not have to change "autovacuum_max_workers" parameter?

I have changed the following autovacuum config parameters:

autovacuum_vacuum_cost_limit = 1000
autovacuum_max_workers = 6
autovacuum_vacuum_scale_factor = 0.01
maintenance_work_mem = 512MB

Beside that, I have configured autovacuum threshold on big tables (because 1% od 10 million is 100k):

ALTER TABLE mytable SET (autovacuum_vacuum_threshold = 15000);
ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0);

If only one database needs any attention, then upto autovacuum_max_workers will all get launched in that one database.  But it will take a few passages of autovacuum_naptime to get all of them launched.  Each one will take a different table to work on at a given time.

This might not have the effect you want, however, as the IO throttling threshold will get divided up between them.  That means that while multiple tables are vacuumed in parallel, increasing the number autovacuum_max_workers does not increase the overall speed of vacuuming when you have IO throttling turned on.  The purpose of using a higher value of autovacuum_max_workers is to ensure that small tables don't get entirely neglected if all the (would-be-lower max number) of workers are individually pinned down by large tables.

If you are worried autovac is not working fast enough, I would just go for the big guns and set autovacuum_vacuum_cost_delay = 0 in order to turn off IO throttling, and set log_autovacuum_min_duration = 0 so you can see the what is going on.  This should quickly give you a definitive answer.  As opposed to incrementally changing autovacuum_vacuum_cost_limit, which will slowly give you ambiguous answers.


How to remove files and filesgroup from dis that have data in it? I have created files and filegroup under partitioning process. These secondary files have data in them. I want to remove these files as I have deleted the table from the database but I am finding it difficult to remove them. how can I remove them?

You need to remove all allocations from that filegroup. That includes indexes, tables and partitions that resides on that filegroup. Then you can remove the files and finally the filegroup.

For just a file and to keep other files for the same filegroup (not what I think you're asking about), then you can shrink that file using the EMPTYFILE option and then remove that file.


Reusing query for duplicate tables I have Database A & Database B. Both have a User table.

I need to write one stored procedure that will retrieve the data from the correct table depending on a @Parameter passed in.

The idea of the query (data needing returned) in the stored proc needed:

SELECT * FROM dbA.User || OR || SELECT * FROM dbB.User

I have found the following ways to accomplish this but I want to minimize duplicate code nor add performance slowness/overhead.

Possibilities on my list:

Dynamic SQL - harder to maintain, adds overhead in that my
understanding cannot define execution plans
Create a duplicate proc or If/Else inside proc - duplicate code
never good
Table value function - not sure of a way to do this using it as I
can't use dynamic SQL here or If/Else to tell which query to run.

At this point I am leaning towards Option #2, duplicate code, with the understanding the database setup needs to be better if we want to avoid duplicating code.

I am not saying this is a good database setup, but it is what I am faced with. I have no control over combining database A & B or changing the structure to only have one User table.

Is there any other way to take care of this issue? Or thoughts on my approach?

Let's assume the value of @Parameter is dbA or dbB

SELECT * FROM dbA.User where @Parameter = 'dbA'
UNION ALL
SELECT * FROM dbB.User where @Parameter = 'dbB'


NULL rows and IS NULL I've been writing some queries that look like this:

SELECT
  ...
FROM table_a a
LEFT JOIN table_b b
  ON b.id = a.id
LEFT JOIN table_c c
  ON c.id = a.id

In these queries only one of the joins can be successful. In other words, a row from table_a is matched by either a row from table_b or a row from table_c.

In the select list, there is some logic based on which join succeeds. I can test this by doing the following:

SELECT
  CASE
    WHEN b IS NULL THEN
      -- do something
    ELSE NULL
  END
FROM table_a a
LEFT JOIN table_b b
  ON b.id = a.id
LEFT JOIN table_c c
  ON c.id = a.id

When testing if a row is NULL you can use the table alias in an IS NULL expression, but it does not work as expected in an IS NOT NULL expression. If there are columns in table_b that are not NULL, the expression table_b IS NOT NULL will return FALSE.

However, the expression NOT (table_b IS NULL) will return TRUE as expected.

Why this is the case?

This is a sad story.

When the SQL standard committee defined the NULL-ness of composite values, they must have taken the wrong drugs.

Anyway, they ruled that a composite IS NULL if all elements are NULL, and that a composite IS NOT NULL if all elements are NOT NULL.

In other words, there are composites that are neither NULL nor NOT NULL.

PostgreSQL implements that, but treats composites as NOT NULL in most other respects. There have been discussions about this, but in the face of the standard's definitions, nobody could come up with a consistent solution.

This is a mess, and you are well advised not to think too hard about this if you want to keep your peace of mind.


Permission for business logic layer & application I'm very new into db development and currently working on my first production app.

I learned that I would need a business logic layer (BLL) to authenticate and authorize users, for example:
John can only query the database while Andrew can insert new records.

Following are my questions that required clarification:

Does it mean the BLL would have to connect to the database with greatest privilege necessary, instead of least privilege needed for each user?
Will the BLL need INSERT permission to provide service to Andrew, which is more than enough for John?
Can we solve this potential flaw (except by securing BLL better, which I would of course do)?

For example, implement authorization in database layer (as described here)?

Database engines have no standardized access layer with enough resolution to allow direct use of the database from client apps.

The databases locking mechanism and indexing also have to be taken into account. For example a report query on SQL server or db2 will lock tables and delay all other queries on the same tables.

The purpose of the business layer is to mediate the access to the database and sharp those edges. Some people do that as stored procedures but that makes the application too tied on a specific vendor. I offer no criticism about that. But many others here will.

On my web applications, I put the logic in the server side and access the database directly from the PHP/j2ee webservers. My philosophy is the database has to have the constraints and triggers to ensure the data quality otherwise an rookie developer can break the data model easily.

But yes, mostly the end user will authenticate against the web layer while the servers will authenticate against the database using power users. The database users are focused on which tables, views and functions the server side app can use.


Log shipping setup across domains I'm running SQL Server 2008 R2 on a Windows machine.

I've been tasked with setting up log shipping between our production site (managed by a service provider) and our QA environment which is hosted on our local network.

My research has only mentioned that the accounts on the two different domains must be the same for network passthrough.  I'm not sure how this works since the domains are different and the accounts referenced would be domain accounts. How does network passthrough work with SQL Server?  What do I need to do to enable it?

Pass-through is a feature of Windows  authentication. It's not related to SQL Server at all, and it's "on" by default (I'm not sure if there's a way to turn it off, now that I think about it).

Which accounts you use depends on the environment, and how the permissions are configured on the backup share. I'm assuming in this scenario that the copy job will be a "pull" from production to QA (in other words, the backup share exists within the production domain).

If domain trusts are configured such that a principal on the QA domain can authenticate against a production resource, the backup share (folder) permissions can be directly modified to give read access to whichever QA domain account/group is being used (they don't have to match at all). Some environments allow this kind of configuration, while others don't. If you don't know the trust configuration between the two domains, ask your Windows system administrator.

If there are no trusts configured between the two domains, then you'll need to use pass-through authentication. This is set up by creating identical AD accounts (matching user name + password) on both domains. Assuming appropriate permissions are configured on the backup share (folder) for the production account, the Windows authentication system will automatically handle the rest of the process.

See these two TechNet articles for more information about domain trusts and pass-through authentication.