SQL Query is getting progressively slower We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can.
We have a webserver and database server through Rackspace.  They are running Windows Server and Sql Server 2008 r2 express version for the database. The problem only began to happen when we moved our database to a remote server.  There is no problem when both are on the same box.
Symptoms
When we make multiple SQL calls from code (PHP, ASP) to database server, the results will get returned at a slower, but consistent rate. The SQL call generally has to be the same call, and after the first two or three calls we see a slow down to 1 second, 3 seconds, 8 seconds, 25 seconds, timeout.  When it slows down, the times are very consistent(1,3,8,25,timeout).  And we can almost always cause the slow down, but it isn't always consistent.
It seems that a single selected result set is the hardest to reproduce the slowdown, but we can reproduce it.  Just not as consistently as a more complex call with multiple result sets.
What we know.

It's not a code problem, because we can reproduce using bare bones calls and from different languages.
It's not the sql, because we can reproduce with extremely simple calls on tables with 10 rows. And the rows are only small integers. But it also happens on big tables.
If we run procedures in SQL Management studio, there is never a problem. Only across the network.
It also doesn't matter if we call raw sql from code (PHP, ASP), or call stored procedures to get result sets.
We don't see any event log errors on the server, or at least we don't know where to look.

We're not sure what would cause this, or even where to continue our search. Any info would be great.
Edit
We added SSMS to another server, but there was no slow down to any of the queries.
The Sql Server version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (Intel X86)   Mar 19 2015 13:34:46   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)
Just to be clear, this is not a single query.  We are seeing this happen on a very wide variety of queries.  From selecting from a very simple table, with like 3 columns, 30 rows, and indexing. To a select with many joins across tables with 300,000 records. But when the slow down starts to happen, it happens consistently no matter how complex the sql.
Edit 2
The ARITHABORT ON did not solve the problem.
I've linked to one of the EXECUTION PLANS. This procedure probably causes the most consistent slow down.  I just added it to a jsfiddle, html section. I'm assuming that would be ok.
Edit 3
Just some more information.  The server is a dedicated server.
Also, we spun up a server on Amazon and made some calls to the database server(on Rackspace), and we had the same problem. Same slow down.
Update
I believe the problem is/was with Rackspace servers.  We upgraded to a General Purpose server from a Standard.  With 4x more CPU's, 2x more RAM, and 4x more bandwidth. But when we point to our old Standard server the slowdown issue goes away. Thanks for everyone's help.

Try SET ARITHABORT ON in your client application...

Why would SET ARITHABORT ON dramatically speed up a query?

I have almost always seen this to be differences between the ADO(usually OFF) and SSMS(ON) for the ARITHABORT setting. This setting can dramatically impact the Query Plan that SQL Server uses for certain queries. Here is a very detailed article (slow queries but SSMS works fine) and how to perform parameter sniffing troubleshooting to get to the bottom of it: http://www.sommarskog.se/query-plan-mysteries.html

One way to test this would be to set it OFF in SSMS (it is on by default in SSMS and off in client apps generally) and run the queries to see if they slow down:

From the MSDN article:

  The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.


Is this product table schema good or worth editing? I haven’t created such structures before and decided to ask the experts. Is this a good structure?
I am concerned about the following questions:

I created the "product_size" table separately, because some products do not have dimensions.
The "product_prefix" table is a boolean value for displaying icons on the product card.
Table "product_gallery", is it correct or worth transferring these values to the table "products"?
Table "product_characteristics", I haven’t come up with anything else how to store an array with json data of the form
[{"key": "value"}]

After examining the type of database relationships (one-to-one, one-to-many, many-to-many) I made such a database structure. If something can be corrected here, correct me.


SQL Server - why are window functions not permitted in update statements? When running an update statement, such as the one below, I get an error telling me that

  Windowed functions can only appear in the SELECT or ORDER BY clauses.

UPDATE dbo.Dim_Chart_of_Account
SET Account_Order = LAG([Account_Order]) OVER (ORDER BY [Account_SKey])

I know that this can be easily worked around using an updatable cte, like below

 WITH my_cte AS (
     SELECT [Account_Order], LAG([Account_Order]) OVER (ORDER BY [Account_SKey]) AS acc_order_lag
     FROM Dim_Chart_of_Account
)
UPDATE my_cte
SET [Account_Order] = acc_order_lag

My question is, are there any reasons why this is not permitted in an update statement, should I avoid using an updatable cte as a workaround?

My concern is that there are issues when using window functions with update statements and therefore I'd like to understand if this is an acceptable method or should be avoided.

Window functions aren't permitted in UPDATE statements because UPDATE isn't compatible with SELECT or ORDER BY.

Window functions are like scoped SELECT statements that re-examine the relevant rows and apply conditions like PARTITION BY and ORDER BY. In addition, many window functions require an ORDER BY clause (ROW_NUMBER, LAG, and FIRST_VALUE, for example).

UPDATE statements use SET instead of SELECT, so SELECT is not allowed anywhere in the same query level. Any SELECT appearing with UPDATE must be contained in a subquery.

Disallowing the ORDER BY makes sense considering an UPDATE statement is indifferent to the order in which it updates rows.

There's no inherent downside to using a CTE or other subquery as a workaround to get an UPDATE to use a window function. That's the common practice advocated by T-SQL experts like Itzik Ben-Gan. (See page 29 of his book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions where he covers this exact scenario.)


MySQL Performance Monitoring I already have system availability monitoring for all my MySQL instances in Nagios.  I'm looking to get some trending monitoring going for activity. It'd be nice to see how all the information_schema stats trend over time, to help identify sources of problems etc.

I've seen Cacti exists and seems pretty popular.

What have your experiences with Cacti been (pros/cons)?

Are there any other common solutions I should investigate?

If  you're looking for graphs, Cacti (or Munin) will work. I've personally only used Munin.

However, I wouldn't put much stock in graphs to troubleshoot problems that arise. If it's showing in the graphs, it's been going on too long.

I would get familiar with the maatkit or percona tools (I think percona is a fork of maatkit) for trouble shooting problems.


What am I doing wrong with the Database Tuning Advisor? My background: I'm a dev/architect, not a DBA. Sorry!

So we have a ~400 table 75GB database. I ran Profiler for ~24 hours ("Tuning" template minus LoginName) and have ~7GB of usage recorded in a trace file. I then ran the Database Engine Tuning Advisor on this trace file (no partitioning, keep clustered indexes, PDS Recommend: Indexes) against our production database (after hours). I gave it ~4 hours to analyze. And here are the summary results I got:

The things that stood out here to me that seem "wrong" are:

It only tuned ~9k out of ~530k queries
It recommended I drop a ton of indexes (in fact, most of them)
It recommended I create 0 indexes

Does this sound right to you guys? I expected it to drop most of my indexes but then to create a ton of new indexes. Also, if it takes 4 hours to analyze 9k queries, is it even feasible for me to get this to consider a normal day's worth of usage? Compared to most large databases, ours is fairly light on consumption (~50 users total).

I think I'm either misunderstanding something or am simply doing something wrong.

I wouldn't bother with DTA. It's a whole lot of work for results that are sketchy at best. Everyone that gets into index tuning eventually realizes it's both a science and an art, and there's a lot to it. You can use tools to get recommendations, but implementing those recommendations blindly almost always results in issues down the road.

Instead, I'd give BlitzIndex a try. It'll take advantage of the statistics SQL Server already maintains to determine where you can improve your indexing (and does so within seconds). It also provides very accessible documentation on how to use the results. Every result includes a link to the pertinent doc. This is how I maintain my indexes these days.


MySQL Workbench Automatically Capitalized and Reformatted Section of SQL Script I was editing a SQL script that I was using in MySQL Workbench 6.3.  To my surprise, I noticed that one section of the code had transformed into all uppercase.  I normally type in mostly lowercase with my own habits for tabs and capitalization.  Somehow, a section of the code was automatically reformatted.

I am not familiar with what option caused this.  Perhaps I made some sort of error in keyboarding while flipping and scrolling through a lot of code.  Is there an autoformatting feature in MySQL Workbench?

How can this type of formatting be done or undone?

A sample of my code before and after follows:

Before:

    select * from ReportQA_TEMPA;
    select * from ReportQA_TEMPB;
    select * from ReportQA_TEMPC;
    select * from ReportQA_TEMPD;

After:

  SELECT
    *
FROM
    ReportQA_TEMPA;
SELECT
    *
FROM
    ReportQA_TEMPB;
SELECT
    *
FROM
    ReportQA_TEMPC;
SELECT
    *
FROM
    ReportQA_TEMPD;

Have you looked at

Edit -> Preferences -> SQL Editor -> Query Editor?

There are a couple of "beautifier" and "capitalise" options there.


MySql: Delete all rows greater than n entries ordered by datetime Can someone help me with a MySql query to delete all rows greater than n entries ordered by date?

I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve rows only up to the first 200.

If I have only 199 rows of data, then I need to preserve them all.

This will keep the first 200 rows (and possibly a few more, with identical timestamps):

DELETE t
FROM
    tableX AS t
  JOIN
    ( SELECT timestampColumn AS ts
      FROM tableX
      ORDER BY ts ASC
      LIMIT 1 OFFSET 199
    ) tlimit
    ON t.timestampColumn > tlimit.ts

Additional question: Keep the first 200 rows for every user (by the uid column):

DELETE tt
FROM
      ( SELECT DISTINCT uid             --- these 3 lines can be replaced
        FROM tableX                     --- with:   UserTable AS du
      ) AS du                           --- a table that you probably have
  JOIN
      tableX AS tt
    ON  tt.uid = du.uid
    AND tt.timestampColumn >
        ( SELECT timestampColumn AS ts
          FROM tableX
          WHERE uid = du.uid
          ORDER BY ts ASC
          LIMIT 1 OFFSET 199
        )

A (uid, timestampColumn) will be useful with a big table.


Can not backup multiple (not all) databases Ola Hallengren This error is quite easy to replicate. So before I send a bug to Ola, I'd like to check if maybe I'm doing it wrong. Version is 2018-06-27 20:44:12

I created 3 databases. DB1, DB2 and DB3. I'd like to backup these 3 databases only in this script independently of what other databases exists. According to Ola's homepage, this is the way I interpret it to be written for the @Databases parameter.

Here is the syntax

EXECUTE [dbo].[DatabaseBackup]
@Databases = DB1, DB2, DB3,
@Directory = N'C:\Temp',
@BackupType = 'FULL',
@Verify = 'N',
--@CleanupTime = 360,
@CheckSum = 'Y',
@LogToTable = 'Y',
@NumberOfFiles = 4

I get error:

  Msg 119, Level 15, State 1, Line 2 Must pass parameter number 2 and
  subsequent parameters as '@name = value'.      After the form '@name =
  value' has been used, all subsequent parameters must be passed in the
  form '@name = value'.

I've tried

@Databases = DB1, @Databases = DB2, @Databases = DB3, but then I get error - Parameter '@Databases' was supplied multiple times.
I've tried using ; instead of , but still error.
Tried [DB1] etc, also error.
Tried N', also error

I'm trying to accomplish line 7 on the picture below from Ola Hallengren

Any Ideas?

If you examine the code for DatabaseBackup.sql, you'll see that the definition for the parameter @Databases is @Databases nvarchar(max) = NULL.  When passing in literals like DB1, DB2, etc., the entire string needs to be surrounded by single quotes - (just like you used for literals for other parameters).

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DB1, DB2, DB3',
@Directory = N'C:\Temp',
@BackupType = 'FULL',
@Verify = 'N',
--@CleanupTime = 360,
@CheckSum = 'Y',
@LogToTable = 'Y',
@NumberOfFiles = 4


Search between dates where all dates appear I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate.

Tables columns are:

id
house_id
date
status

house_id + date are unique

the closest I could get is:

SELECT * FROM (
SELECT *
FROM tests) AS T1
WHERE date BETWEEN '2016-02-01' AND '2016-02-02'
HAVING COUNT(*) = DATEDIFF('2016-02-02', '2016-02-01') < 1

sample data:

CREATE TABLE IF NOT EXISTS `tests` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`),
`house_id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
`status` enum('0','1','2') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tests` (`id`, `house_id`, `date`, `status`) VALUES
(1,1, '2016-02-01', '1'),
(2,1, '2016-02-02', '1'),
(3,1, '2016-02-03', '1'),
(4,1, '2016-02-04', '1'),
(5,1, '2016-02-05', '1'),
(6,1, '2016-02-06', '1'),
(7,2, '2016-02-01', '2'),
(8,2, '2016-02-02', '2'),
(9,2, '2016-02-04', '2'),
(10,2, '2016-02-05', '2'),
(11,2, '2016-02-06', '2'),
(12,2, '2016-02-07', '2'),
(13,2, '2016-02-08', '2')

I believe you want to get house_id where date exists for each day between @startDate and @finalData.

Sample queries with 3 different house_id: SQL Fiddle.

Query:

SET @startDate := CAST('2016-02-01' as date);
SET @finalDate := CAST('2016-02-04' as date);

SELECT house_id
    , COUNT(*)
    , DATEDIFF(@startDate, @finalDate)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id;

Query 1 in SQL Fiddle.

This query GROUP BY house_id and output the COUNT of days between @startDate and @finalData:

house_id    COUNT(*)    DATEDIFF(@startDate, @finalDate)
1           3                       3
2           2                       3
3           3                       3

From this query, it is obvious that only rows where COUNT(*) = DATEDIFF(@startDate, @finalDate) should be returned.

If (house_id, date) is not unique, COUNT(*) must be replaced by COUNT(DISTINCT `date`).

Query with all days

If you only want house_id where COUNT(*) = DATEDIFF(day, @startDate, @finalDate), the query must use the HAVING clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 2 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3
3           3

Query where status = X

Status value can be checked in the WHERE clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
    AND status = '1'
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 3 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3


Whats the difference between float and numeric in Postgresql? Reading the Postgresql docs about the numeric data types
leads me to this question:
why do I get these unexpected results with the data types Float (SQL standard) and Numeric in Postgresql?
For example:
CREATE TEMP TABLE testnum (a numeric, b float);
INSERT INTO testnum VALUES (100,100);
INSERT INTO testnum VALUES (9*9*9,9*9*9);
INSERT INTO testnum VALUES (9^9^9,9^9^9);
SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum;
SELECT (a/5)*5 AS numeric, (b/5) * 5 AS float FROM testnum;

Then run
SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum;

numeric                                     |         float
                        99.9999999999999999 |                    100
                       729.0000000000000000 |                    729

In this test Float looks more accurate than Numeric.
Which one is the appropriate data type for precise numeric values like currency or inventory quantities?

You are falling victim of implicit type casts. When one of the operands is numeric and another one integer, the integer operand will be cast to numeric and the result will be numeric. Since numeric has a strictly defined precision, attempting to assign to it a value with infinitely many digits after the decimal point (which 100/3 produces), without rounding, will result in truncation. The same logic applies to multiplication, to which one operand now is not 33.333... (with an infinite sequence of "3"s) that you seem to expect, but rather a defined precision numeric 33.333...3 (a finite sequence of "3"s), which naturally produces 99.999...9 -- a finite sequence
When one of the operands is float and another one integer, the integer operand will be cast to float and the result will be float. Since float has an approximate precision, it is equipped to deal with 33.333... and 99.999... differently, which eventually results in an approximate value of 100.
As to

Which one is the appropriate data type for precise numeric values like currency or inventory quantities?

the manual gives you the exact answer you need:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

What it does not say is that you should control calculations at each step by using explicit type casting and rounding appropriate for your business rules.


How to create a flexible table schema for storing messages from different chats? Help please solve the following situation:

There are two kinds of API where message history is stored, it's Zopim and Chat2Desc (import into Postman). While these two but can then others appear.

And my database with the users table:

Table users
id , email, phone, ...

In Zopim, users are identified via email, and in Chat2Desc through the phone. For me, these two fields are important, whatever the chat was and how many would not be.

That is, if I receive an email or a user's phone in messages, I make a request to my database (table users) to identify my user.

And in principle, even the structure of chat rooms is not important.I'll somehow choose them.And here's how to properly save them, so much so that I had one structure for everyone.

And that's what I came up with (Something I don't like, especially the chat_clients table):

Explanation:

Table chats (Data for chat):

client_id   - indicates the id of the chat_clients table
duration    - the duration of the chat (120 seconds)
system_type - stores the name of the chat (Zopim, Chat2Desc, ...)
created_at  - creation date

Table  chat_clients (information about users who were in the chat):

is_agent      - 0 | 1: 1 => my user, 0 => not my
user_id       - is the user id. Contains either id from the users table or empty.
assigned_data - those initials under which users were in the chat
bean_module   - it does not matter (information about my user)
unique_col    - There will either be a email (from Zopim) or a phone (from Chat2Desc, Or I think to store the id of the users table). Will guarantee the uniqueness of the values.

The users_id + unique_col bunch is unique (UNIQUE KEY user_id_unique_col_UQ (user_id, unique_col))

Table chat_messages:

text       - the text of the message.
client_id  - indicates the id of the chat_clients table
chat_id    - indicates the id of the table chats
file_id    - indicates the id of the chat_files table
transport  - the value will be for Chat2Desc (Viber, WhatsApp, ...), for Zopim, so it's not empty, Zopim

Table chat_files Information about the transferred files in the chat. Analogical tables may be not to store additional information.

In the future I'm going to select the history of correspondence for each user.

Q: How can I improve the structure of tables to get more flexibility ?

Thank you in advance.

All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. That means you have to use (the soon to be released) MySQL 8, as prior versions do not support recursive CTEs which are required to do this. Any workaround can't permit infinite depth, which is usually required or at the very least nice to have.

Rather than drawing up a schema here, you can see what one looks like in PostgreSQL here, where I answered a similar question. Essentially, each message has id|parent_id the parent_id points to the id on the same table. This creates a hierarchy. We call this implementation of hierarchy "self-referential", or "single-table hierarchy."

Moreover, you may wish to implement an array of tagged users or the like for better indexing.

Ultimately, if you're going to go down this route and you have no prior work, you should be using PostgreSQL -- which supports Recursive CTE's now, and sql-array's for easy tagging.

I'm not saying your schema has to look like those. You may have additional demands, but I wouldn't want to start off with an inferior feature set and the wrong tool.

For clarity this critique is specific to your chat_messages table. The chat sessioning would be done by just creating a seperate table for it, and linking all messages to an entry in that table. For instance,

CREATE TABLE chat_session (
  chat_session_id  int  PRIMARY KEY
  ....
);

Other tables..

CREATE TABLE chat_message (
  ts                             timestamp with time zone,
  chat_message_id                int PRIMARY KEY,
  chat_message_parent_id         int REFERENCES chat_message,
  chat_message_author_user_id    int REFERENCES user,
  chat_message_author_client_id  int REFERENCES client
);

Or the like.


How to create a 'keywords' table from a column's values I have a table with 8 million+ rows in it. It stores jobs and their characteristics. There is a column in it that holds job titles. I am planning to create a keyword table based on the job titles from that column, so that later the keyword table can be used to implement an auto-complete field on the client side.

For example, if the job title is The Java programmer, then I want to store Java and programmer. I want to eliminate all special characters, get rid of some words like a and the and break the string on space. Can you please advise me on what my approach should be? From where should I start? Is this even the right way of creating a table to store keywords?

To query the Title column you may like to investigate Full Text Search.

To create the autocomplete list you could split the Title on space character and remove the small words, which you would keep in a separate pre-defined list, often called a "stop list."


Oplog incremental backup failure I have scheduled oplog incremental dump using mongodump but it fails very often on majority of the servers. I have a shard cluster with around 200GB of oplog size defined and around 30-40 hours of oplog window.
I am thinking it is because of message "WiredTiger record store oplog truncation" just before the dump starts which is common during every failure. Other times, the dump runs perfectly fine. I have analysed the oplog window and size and it is fine. The oplog start data was way older than the timestamp I am using during oplog dump.
Below are some of the logs :

From mongod.log and my backup script :

2020-07-19T16:28:10.472+0000 I STORAGE  [WT RecordStoreThread: local.oplog.rs] WiredTiger record store oplog truncation finished in: 420ms

2020-07-19T16:28:10.502+0000 E QUERY    [conn74791] Plan executor error during find command: DEAD, stats: { stage: "COLLSCAN", filter: { $and: [ { ts: { $lte: Timestamp(1595176086, 2244) } }, { ts: { $gt: Timestamp(1595173520, 25) } } ] }, nReturned: 0, executionTimeMillisEstimate: 2970, works: 356598, advanced: 0, needTime: 356597, needYield: 0, saveState: 2787, restoreState: 2787, isEOF: 0, invalidates: 0, direction: "forward", docsExamined: 356596 }```

oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:06.794+0000 reading password from standard input
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-Enter password:
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:07.153+0000 writing local.oplog.rs to stdout
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:09.795+0000 local.oplog.rs  0
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:10.517+0000 local.oplog.rs  0
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log:2020-07-19T16:28:10.517+0000 Failed: error writing data for collection `local.oplog.rs` to disk: error reading collection: Executor error during find command :: caused by :: errmsg: "CollectionScan died due to position in capped collection being deleted. Last seen record id: RecordId(6850019249918838732)"

You are right, the oplog truncation is killing your cursor.
There are no indexes on the oplog, so dump has to scan from the very beginning to find the documents that match the query.
In that case of this mongodump cursor, it had examined 356596 documents, but still hadn't found any that matched the query.
The failure message reports the last seen record was "RecordId(6850019249918838732)".  That number is a 64-bit value that corresponds to the ts.
You can get the first 32 bits and the corresponding timestamp from bash:
% echo $((6850019249918838732 >> 32))
1594894391

That date is Thu Jul 16 10:13:11 UTC 2020 still over a month prior to the first date of interest.
When those documents were trimmed from the capped collection, the cursor no longer pointed to a valid position, so it was not possible to continue.
When that happens, you'll need to restart the mongodump so it can start scanning from the new beginning of the oplog.


Where does Attunity's CDC services write error data? We use Attunity's CDC services to move data from Oracle to SQL Server.

Using the Oracle CDC Designer snap-in tool, I can see the current status of the service and an error message if a problem has occurred.  I can click 'Collect Diagnostics' in the tool to gather messages for a date range and write to a file.

I want to find out where the diagnostic information is stored so I can read it directly through powershell from a monitoring server.

I have looked through the event logs and I can see some status messages but the diagnostic report has much more data.

I found the data I was looking for.  In each target database there are a series of system tables within the schema cdc.  The error messages I wanted are in the cdc.xdbcdc_trace table.

select * from MyTarget_CT.cdc.xdbcdc_trace

There is a similar table in the MSXDBCDC database, but it is currently empty on my system.

There is also information regarding the connection status to Oracle from SQL Server in a file located, most of which I believe is also found in the trace table mentioned above.

C:\Users\CTServiceName\AppData\Local\Temp\oradiag_XXX\diag\clients\user_XXX\host_XXX\alert\log.xml


Query not returning results if selecting by indexed columns (Mysql 5.6) We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column then we get the expected results.

For example,

SELECT product_id FROM product where product_id = 1;

doesn't return any results.

But if we do this:

SELECT * FROM product where product_id = 1;

We get the expected results.

The table engine used to be MyISAM, however, when we changed it to InnoDB we started having these issues. We had to change it back to MyISAM because a lot of websites were down because of the above issue.

The server specs:

Ubuntu 16.04 LTS
Mysql 5.6
27GB RAM
300 GB Free space while using 100 GB

My.cnf:

innodb_log_file_size = 3G
innodb_buffer_pool_instances = 21
key_buffer_size = 500M
innodb_buffer_pool_size = 22G
sort_buffer_size = 512K
read_rnd_buffer_size = 512K
thread_cache_size = 9
thread_cache_size = 9
innodb_file_per_table = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_at_shutdown = 1

We didn't have any errors on the application side and no error logs in Mysql. The server had 6 GB of Free memory so we weren't running out of memory. The above recommendations were made by mysqltunner.pl

Results from Mysqltunner:

[OK] Slow queries: 0%
[OK] Thread cache hit rate: 93%
[OK] Table cache hit rate: 95%
[OK] Open file limit used: 0%
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 20.0G/21.3G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3G *     2/20G should be equal 25%
[OK] InnoDB buffer pool instances: 21
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95%
[OK] InnoDB Write Log efficiency:  93%
[OK] InnoDB log waits: 0.00% (0 waits / 68 writes)

Edit 1:

Query used to convert MyISAM to InnoDb

ALTER TABLE `product` ENGINE=InnoDB;

Table Schema:

 CREATE TABLE `product` (
  `product_id` int(11) NOT NULL,
  `product_type` int(11) NOT NULL DEFAULT '1',
  `product_quantity` float NOT NULL DEFAULT '0',
  `product_model` varchar(32) DEFAULT NULL,
  `product_sku` varchar(255) DEFAULT NULL,
  `product_label` tinyint(4) NOT NULL,
  `product_flavor` tinyint(4) NOT NULL,
  `product_new` tinyint(1) NOT NULL,
  `product_sale` tinyint(1) NOT NULL,
  `product_image` varchar(64) DEFAULT NULL,
  `product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `product` ADD PRIMARY KEY (`products_id`),

-- Added the FullText key when we changed it to InnoDB
ALTER TABLE `product` ADD FULLTEXT KEY `model_index` (`products_model`);

I wonder what would happen if you ran OPTIMIZE TABLE product; on your MySQL server. OPTIMIZE TABLE will “rebuild” the physical storage of your table, including the indexes.

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html


Avoiding foreign key repetition on related tables in a relational DB I have a simple DB schema describing certain objects that belong to a historical time period of a country and (optionally) a subdivision of that country at that period. The contrived UML class diagram is given below:

The relations are as follows:

a country can have one or more historic time periods (defined by a year interval).
object is an entity that is assigned to a historical period and may optionally be assigned to a region.
region is an optional subdivision of a period (if a country is divided into smaller pieces, i.e. Germany into West/East Germany in 1949-1989).

This schema is flawed by having a data redundancy problem: when an object is assigned to a region, it has two foreign keys pointing to region and period. The pointed region row also has a foreign key that has to be pointing to the same period. Thus, if region is moved to another period, keys have to be replaced in multiple places.

I've thought of two undesirable solutions:

by removing the region—period relationship but then region loses its logical connection to a period (by object—region relation, a region may belong to many periods, which is not desirable);
removing object—period relationship but because object's association to region is optional, object would lose it's connection to country.

Do you have any architectural ideas that would solve the data redundancy problem? I'm using Django ORM for defining my models.

Looks like you can remove the Period FK entirely from the object. When assigned to a Region, it is already assigned to the Period assigned to the Region. Define a special Region -- a NULL Region, "not a Region", Pass-Thru Region, an all inclusive region, whatever -- to act as a placeholder for a Period when you mean "assigned only to Period rather than a Region."

This, I think, would involve the minimum amount of changes to your schema: drop the Period FK field from the Object table.


postgresql archive (old data) My environment:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# rpm -q postgresql
postgresql-9.2.18-1.el7.x86_64
#

I used following Streaming Replication - PostgreSQL wiki to set up replication between master/slave and now after a while I end up with following:

-bash-4.2$ du -h ~/archive/
19G /var/lib/pgsql/archive/
-bash-4.2$ ls -la ~/archive/ | head
total 19169360
drwxr-xr-x.  2 postgres postgres    61440 Jun  6 12:10 .
drwx------. 11 postgres postgres     4096 Jun  6 11:21 ..
-rw-------.  1 postgres postgres 16777216 Feb  6 10:19 0000000100000008000000AE
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000AF
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B0
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B1
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B2
-rw-------.  1 postgres postgres 16777216 Feb 14 20:30 0000000100000008000000B3
-rw-------.  1 postgres postgres 16777216 Feb  6 10:33 0000000100000008000000B4
-bash-4.2$

should I setup a cron job to kill old archives? or there is postgres way of doing this?

You can use pg_archivecleanup.

You will find documentation here : https://www.postgresql.org/docs/current/static/pgarchivecleanup.html


Query against partitioned table accessing more partitions than necessary? I first posted this on SO but got no replies, this is the reason for why I'm trying here now and I hope I'm not breaking any rules in doing so (fingers crossed).
I'm trying to understand how to use table partitioning and partition elimination correctly so that I can decide if I want to use it or not in a new database (sql server 17 express). I'm leaning towards not using it but that's a different story so this question is more that I wan't to know how it works now.
In the following script I create a partitioning function and scheme, a table using the scheme, insert some data and then query it in 3 different ways.

The first query is correctly only accessing the first partition (actual partition count = 1, actual partitions accessed = 1).

I expect the second query to only access the first partition as well given that I've created the partition function as range right on the value 20200101 but it is accessing partition 1 and 2 (actual partition count = 2, actual partitions accessed = 1..2).

I expect the third query to only access partition 2 but it is accessing partition 2 and 3 (actual partition count = 2, actual partitions accessed = 2..3).

I'm aware that I need to have exact type matching for partition elimination to occur but I think I have that here?
What am I missing?
create partition function partfun (datetime2(0))
as range right for values ('20200101', '20210101')

create partition scheme ps
as partition partfun all to ([primary])

create table t
(
    id int not null identity(1,1),
    logdate datetime2(0)
) on ps(logdate)
insert into t values ('20190101'),('20200102')

select * from t where logdate < cast('20191231' as datetime2(0))
select * from t where logdate < cast('20200101' as datetime2(0))
select * from t where logdate >= cast('20200101' as datetime2(0)) and t.logdate < cast('20210101' as datetime2(0))

These trivial queries are automatically parameterized. As such, the literal values are replaced with parameters during compilation. Partitions are eliminated at runtime dynamically using the internal RangePartitonNew function in a range seek predicate. Importantly, the seek predicate start/end range is inclusive so that the same cached plan can be reused for any possible value, not just exact boundary matches as in your queries. Rows are subsequently filtered based on the WHERE clause predicate to return only the rows desired.
Below is the seek predicate from the first 2 queries. At run time, the first query seek is equivilent to partition_number >= 1 AND partition_number <= 1 and the second is partition_number >= 1 AND partition_number <= 2. Partition 1 is hard-coded as the start range due to the less than operator.
Seek Keys[1]:
    Start: PtnId1001 >= Scalar Operator((1)),
    End: PtnId1001 <= Scalar Operator(RangePartitionNew(CONVERT(datetime2(0),[@1],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00'))

The seek predicate from the third query differs only in that the start range also includes RangePartitionNew to eliminate partitions excluded by the greater than or equal to comparison operator. This seek is equivilent to predicate partition_number >= 2 AND partition_number <= 3.
Seek Keys[1]:
    Start: PtnId1001 >= Scalar Operator(RangePartitionNew(CONVERT(datetime2(0),[@1],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00')),
        End: PtnId1001 <= Scalar Operator(RangePartitionNew(CONVERT(datetime2(0),[@2],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00'))

You can avoid simple parameterization with these techniques from Paul's blog article:

OPTION (RECOMPILE)
Add a dummy predicate like WHERE 1 <> 2

If exact boundary values are your only use case, you could add a predicate to evaluate the partition function with a less than comparison operator. This will retain parameterization benefits. For example:
AND $PARTITION.partfun(logdate) < $PARTITION.partfun('20200101')

The resultant parameterized plan seek predicate subtracts one from the end of the boundary range to eliminate the partition excluded by the above predicate:
Seek Keys[1]:
    Start: PtnId1001 >= Scalar Operator((1)),
    End: PtnId1001 <= Scalar Operator(RangePartitionNew(CONVERT_IMPLICIT(datetime2(0),[@2],0),(1),'2020-01-01 00:00:00','2021-01-01 00:00:00')-(1))

Note that an index on the partitioning column will mitigate the need for these work-arounds. Even though an extra partition will be touched with exact boundary values, the overhead is only a couple of additional logical reads.


How to restrict users to select only query through application textbox field? Say that I have a web application that lets users to type in a SQL query (SQL Server 2008+). I want users to be able to run any SELECT query and read whatever is in the database, but I do not want them to be able to insert/update/drop/truncate/exec etc. (in short: only select). Please note that there are no data or even database metadata that are considered confidential in the entire database.

The rules currently in place:

No ";" character. If it is there, it must be exactly one and at the end of the trimmed query string.
Query must begin with select (case ignored, white-spaces are part of regex).
All newlines are removed from the query.

Is this enough? Is there any way to do anything else than just select?

EDIT: Thank you Thomas Stringer for the note about SELECT ...  INSERT. This is exactly the information I was after. I will remove all the newlines from the query string, which should help solve this problem. Is there any other way to get around this and be able to modify the data?

NOTE: CTE is not relevant for this case. Lets assume that query has to begin with SELECT.

Make sure the application connects to the server using a login that has been given only read permissions (give it the db_datareader role in the database to allow reading all tables), and you should be in good shape. The easiest way to prevent changing data is to ensure the user doesn't have permission to change anything.

Be careful about granting execute permissions, as you may inadvertently allow users to change data depending on what the procedures do.


What will happen if you need a fifth echelon? And what if an employee has a role in more than one division?

A smaller, simpler schema should cover your business case and require less work to implement, maintain and extend. For example:

Employee (id, name)

Division (id, name, subdivision_of)
    [subdivision_of -> Division.id]

Position (id, name)

Role (employee_id, division_id, position_id)
    [
        employee_id -> Employee.id,
        division_id -> Division.id,
        position_id -> Position.id
    ]

If you don't want one person to be able to work in more than one division, I suppose you can declare employee_id as unique.

Edit: another problem that I see is a redundancy anomaly in the employee table. It repeats the information about the hierarchy of divisions. If you ever change that structure you have to remember to go over every row in the employees table to make sure that change is reflected there.


What are the "additional actions" available for "Recovery Pending" databases? I've used powershell to run a sql script on several databases on a test server. Disk space was low, so the script failed on 34 out of 75 databases. All fair enough.

In any case, afterwards 10 of my 75 databases ended in a modus where SSMS appends to the database name "(Recovery Pending)". I've solved the disk space issue and waited for a bit, but the databases remain in this state.

The MSDN carefully explains this state (emphasis mine):

  SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.

Great! But what are these "additional actions", and how can I find out which one's for me?

My Google-fu turned up several hazy forum posts with solutions such as:

detach then attach again
take offline then take online again
restore a backup
setting emergency/single_user and back to multi_user
restart the sql server instance

Some of these worked for me, heck: all of them may work. However, these are all "just try this!" suggestions from questionable sources with (side) effects I may not know about or even want.

So, again: what is the appropriate "additional action" msdn speaks of, and above all: why??

Since this is a test server, the best choice would be simply to run RESTORE DATABASE [db_name] WITH RECOVERY; for each database that is in Recovery Pending state.  Check the Microsoft Docs page for RESTORE DATABASE with recovery only for further details.

This code will generate the statements for all databases in recovery pending:

SELECT 'RESTORE DATABASE ' + quotename(d.name) + ' WITH RECOVERY;'
FROM sys.databases d
WHERE d.state_desc = 'RECOVERY_PENDING'
ORDER BY d.name;


Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature? We want to implement the Always Encrypted feature for our application database. There is a table with a varbinary(max) column which is storing binary data of images, pdf documents, etc.

Does the Always Encrypted feature support varbinary(max) column encryption?
If yes, how to do I implement it using SQL Server 2016?

Any support is greatly appreciated.

The limitations are listed here and do not mention VARBINARY(MAX).

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine

The way to do this when the feature first came out was:

https://justdaveinfo.wordpress.com/category/always-encrypted/

  Disclosure
  I am the author of the above blog

It is easier now though either via:

SSMS https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio
Powershell https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-powershell


Extracting entity from attribute in entity-relationship diagram I am developing a database model where I need to capture information about rent contracts for parking places within a parking. I have thus defined an entity called PARKING CONTRACT.

Requirements (r.) say, that each PARKING CONTRACT should be signed for exactly 1 car, which is, in turn, owned by exactly 1 car owner.

To fully define a PARKING CONTRACT, r. say, I need to record various information about car owner and his car. For each car owner: full name, address, passport number, etc. For each car: technical passport number, registration number, brand, etc.

According to the r. (they do not go into such details, here I am judging on the basis of attributes that need to be defined!), a car owner can be uniquely identified by their passport number; a car can be identified by registration number.

I first thought it would be good to model CAR OWNER and CAR as separate entities, and came to this:

.-----------------.                      .-------.                 .------------.
| PARKING CONTRACT|     Is signed for    |  CAR  |   Is owned by   |  CAR OWNER |
|                 |O_____________________|       |O________________|            |
'-----------------' 1                   1'-------' 1..*           1'------------'

But then I realized that with this design we may store as much CAR OWNERs as we like without any of them having any PARKING CONTRACTs for any of their CARs. I think it is not good - why a database should be able to store information about people which never had any PARKING CONTRACTs.

Another option I see here is to not extract CARs and CAR OWNERs into separate entities and to keep all information about them in the PARKING CONTRACT table, for each PARKING CONTRACT.

But this option doesn't seem to be too nice as well, as the table for PARKING CONTRACT, imho, becomes a chimera which tries to handle too much different information.

Also, I have read this brochure: https://support.ca.com/cadocs/0/CA%20ERwin%20Data%20Modeler%20r8-ENU/Bookshelf_Files/PDF/ERwin_Methods.pdf

and on the page 38, it tells that when you end up with attribute names like parking-contract-signer-passport-number, then that is a sign of a design problem.

The question is: What is the commonest way to handle situations like this one? Is any of these two approaches preferred? Or, maybe, there's a whole different third approach? And why is that approach preferred? Thanks!

Unless you have a good reason not to, you should normalize your database schema.  When you normalize your database schema you are avoiding potential issues with data consistency that can occur when you have duplicated data.

Google the terms: insert anomaly, update anomaly, deletion anomaly.  You will see lots of examples of the kinds of problems that an unnormalized database can cause.

Regarding modeling CONTRACT separately from CAR:  I have found that one of the best places to start with a data model is to treat each tangible (or significant intangible) thing that your system cares about as its own table.

The hard reality is that business rules change, but the things that your business cares about don't change - at least not at nearly the same pace.  Therefore, you want your data to reflect the reality of the things your business cares about.

People are not contracts.  Sure, today your boss says one contract per person per car.  What happens when your boss buys (or builds) a new parking garage and that car has a contract for both?  What happens when one person sells a car that has a contract?  The contract is signed by a person, not by the car?  Does the contract go with the car or does it stay with the owner?  What happens when your boss tells you he wants to start tracking future-dated contracts so that a car can have both its current contract and its contract for next year?

You don't have to normalize.  You could try to get away with one big table for everything.  You could just write everything down in a spreadsheet or on a piece of paper.  However, if you want to build a system that is flexible for the future, then start by normalizing.

Regarding IDs for your tables:  Never use anything external as the ID for something, unless you are totally positive that it will never change.  A vehicle VIN is OK as an identifier.  Nothing causes a VIN to change and it is guaranteed to be unique.  A passport number is a terrible ID because (a) it will definitely change as time goes on and (b) you can't be sure that it will be unique.  When primary keys change it causes all kinds of headaches.  This is why a lot of people assign internal, meaningless surrogate keys to their tables.

EDIT: Something Else to Consider:

Something else you should consider is that you should NOT try to model (and maintain data for) things which aren't important to your system.

That is just making work for yourself now and down the road.  Your system will be more complicated to build, maintain, upgrade and use.

Consider the following ERD:

The relationship between CAR and OWNER is not important to your system!  At first this seems a little bit counterintuitive, but think about this:  Does your boss care who actually owns a car?  No!  What they care about is who (PERSON) signed a contract to pay to have which CAR parked in his garage.  Therefore the relationships that are actually important are between CONTRACT and CAR, and CONTRACT and OWNER.  The relationship between CAR and OWNER is not directly significant to your system, unless you have a business rule somewhere that says something about how contracts stay with cars, not owners when people sell their cars.  That is probably dicey from a legal perspective.


Merge Replication between two Embedded Databases? I have an application that uses SQL Server CE 3.5.  I am trying to expand the database to have a remote database accessible from various locations.  I will need both the local (which might or might not have consistent internet connections) and the remote databases to be able to sync with one another and to be able to send and receive updates from the other database upon internet connection.

Is it possible to replicate an embedded database such as SQL Server CE with another embedded database or do you have to set up a server for this functionality?  If not, what would be a recommended server database to use (preferably to be set up on a cloud server)?

Thanks,
Stuart

My understanding (I haven't worked with this in over a year) is that "merge replication" requires a sql server instance to act as a publisher. If you want to only have an SQL CE database at each end, you need to write your own code with sync framework (if you see articles on "sync services" they are using the sync framework).


Is it possible to restore a SQL Server 2012 Standard backup to a SQL Server 2012 Web Edition instance? If I take a backup from a SQL Server 2012 Standard edition instance can I restore it to a SQL Server 2012 Web edition instance?

Yes, you can restore the backup of Standard edition to web edition. Just make sure to use restore database .. with move if the file locations are different between the servers.

See the comparison matrix for more details.


Syntax error, Hello World example. Begin ... End I borrow an example hello world function for mysql, but it throws me an error, I don't know where is the syntax error in my query.

DROP FUNCTION IF EXISTS helloWorld;

CREATE FUNCTION helloWorld( s char(20))
RETURNS VARCHAR(50)
BEGIN
    RETURN CONCAT("Hello ", s);
END;

SELECT helloWorld("Albert");

MYSQL Version: 5.7.21

  ERROR: [ERROR en la consulta 2] You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the
  right syntax to use near '' at line 4

If I do the query without BEGIN ... END It works, but I can't do the query without more than 1 line of body.

It seems like mysql is confused about the delimeter.

The delimeter for RETURN CONCAT("Hello ", s); is ";".

The delimeter for the query is ";"

I had to change the delimeter to "//" get a good query and change it again to ";".

DROP FUNCTION IF EXISTS helloWorld;

DELIMITER //

CREATE FUNCTION helloWorld(s char(20))
   RETURNS CHAR(50)
   BEGIN

     RETURN CONCAT("Hello ", s);
   END; //

DELIMITER ;

SELECT helloWorld("Albert");

I don't know why, but this is happening only in my DB.


PostgreSQL insert record by trigger create table tab1(id int,name text);
create table tab2(id int,name text);

  create or replace function t_tab() returns trigger
as
$$
begin
insert into tab2
values (new.id, new.name)
return new;
end;
$$
language plpgsql;

create trigger t_tab
after insert on tab1
for each row
execute procedure t_tab();

The statement:

insert into tab1(1,'king');

inserts data in both tables tab1 and tab2.

Now my question is: I want same thing in tab2 table. When I insert data in tab2 table automatically insert same data in tab1. If I create same trigger function in tab2 then the execution unlimited. Is there any solution to fix this?

assuming ID is a primary key.

on conflict do nothing:

create or replace function t2_tab() returns trigger
as
$$declare r record;
begin
insert into tab2
select new.*
on conflict do nothing
returning * into r;
return r;
end;
$$ language plpgsql;


How can I rebuild an index on table with 100 million rows without a wait time out? I have the following table with ~100 million rows:

CREATE TABLE Result (
    MetricId int NOT NULL,
    PeriodId int NOT NULL,
    Value decimal (18, 2) NOT NULL,
    CONSTRAINT PK_Result PRIMARY KEY CLUSTERED (MetricId ASC,PeriodId ASC)
)

CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC)

My analysis of this situation (and I'm by no means a DBA) is:

The NC index should have Value as an included column. We are
seeing queries that filter by PeriodId use the NC index but then
need a Key Lookup on the PK to get the value. I think this will give
us much better query performance.
The NC Index does not need MetricId in the key after PeriodId
because SQL Server implicitly includes any columns in the clustered
PK index.
As an aside: the main filter we use in queries is PeriodId therefore it would
be best to have the PK around the other way (ie. PeriodId,MetricId).
I also believe PeriodId is more selective (?) in that it would
make sense to filter first by PeriodId to get rid of the most
rows during a query.

My question is then - how can I create or rebuild the following index?

CREATE NONCLUSTERED INDEX IX_Result_PeriodId ON Result (PeriodId ASC) INCLUDE (Value)

I have tried editing the existing index through Management Studio which requires a rebuild, and that times out after 5-10 minutes. It's a 'Wait Timeout' I believe.

I have tried creating a brand new index through Management Studio with the same result.

I have tried putting the database in Single User mode and creating the new index. Same result.

Upgrading to Enterprise is not an option for me.

How can I create this index on such a large table?

Community Wiki answer for the solution originally provided in a question comment:

  Martin Smith: Run the script through a new query window in SSMS. Not through the visual designer.


Can I change the profile of oracle sys user? Are there side effects of changing the profile of sys to any other than default?
Our monitoring keeps telling us the password of sys has expired. The "Default“ profile has a standard limit of 180 days. Even this does not apply – the password of sys is marked as expired.

Our monitoring keeps telling us the password of sys has expired. The "Default“ profile has a standard limit of 180 days.

You should not try to change the Profile for internal, Oracle accounts (indeed, there is one, very oddly named, account that you cannot changed in any way, shape or form!).
You should not change the behaviour of the DEFAULT profile.
The "default" behaviours can change from version to version of Oracle Database and so may be unexpectedly "reset" on you, during an upgrade.
If you want to tie down these behaviours, define your own Profiles and configure them as you need them.
CREATE PROFILE APPLICATION_ACCOUNTS
    LIMIT
    PASSWORD_LIFETIME UNLIMITED
    ;
ALTER USER APP_1_ACCOUNT PROFILE APPLICATION_ACCOUNTS ;
ALTER USER APP_2_ACCOUNT PROFILE APPLICATION_ACCOUNTS ;

CREATE PROFILE INTERACTIVE_USER
    LIMIT
    PASSWORD_LIFETIME 180
    . . .
    ;
ALTER USER FRED   PROFILE INTERACTIVE_USER ;
ALTER USER BARNEY PROFILE INTERACTIVE_USER ;

Anything you don't specify in your custom profiles will, of course, be "inherited" from the DEFAULT one.


How to implement if (t1.ID=t2.ID) returned nothing do the stuff I have two tables one table1 containing ID, NAME another table2 is only ID.

I want to have all IDs in table2 and return ID in table2 and NAME in table1 but I also want IDs in table2 which is not exist in table1 and just print ID, - - -.

How do I implement if (table1.ID=table2.ID) returned nothing do the stuff as well and print ID with null NAME column.

From what I understand you want a LEFT JOIN (it really helps if definitions of tables, sample data and expected results is included in the question):

SELECT t2.id, coalesce(t1.name, '---') as name
     , CASE WHEN t2.id = t1.id THEN 'EQUAL' ELSE 'NOT EQUAL' END
FROM t2
LEFT JOIN t1
    ON t2.id = t1.id

coalesce returns the leftmost column that is not null, in this case t1.name if it's not null and --- otherwise. If you want to avoid a join (not sure why) you can use a sub select:

SELECT t2.id, coalesce((SELECT t1.name
                        FROM t1
                        WHERE t2.id = t1.id), '---') as name
FROM t2;


Service vs Listener in oracle I'm new to oracle environment. I want to know the difference between service and listener in oracle?

I'm just doing my assignment on accessing an oracle database over a network.

The Oracle Net Listener

  The Oracle Net Listener (the listener) is a server-side process that
  listens for incoming client connection requests and manages traffic to
  the database.

Service Names

  A service name is a logical representation of a service used for
  client connections.

  When a client connects to a listener, it requests a connection to a
  service. When a database instance starts, it registers itself with a
  listener as providing one or more services by name. Thus, the listener
  acts as a mediator between the client and instances and routes the
  connection request to the right place.

  A single service, as known by a listener, can identify one or more
  database instances. Also, a single database instance can register one
  or more services with a listener. Clients connecting to a service need
  not specify which instance they require.


Production server Log file full due to replication I'm getting the error below, log file full due to replication
I have hot dbcc sqlperf(logspace) it is showing 100 and it is in Simple recovery model.
I am not able to shrink it.
I am not able to add space.
I am change the recovery model to Full.

  Error: The transaction log for database XXXXX is full due to
  'REPLICATION'.

I have tried all the possible ways please help, in this regards.

Per the error message, it is holding the data in the log because your database is configured to replicate those changes (via transactional replication) to another database, and for some reason the log reader job hasn't run recently or is experiencing an error.

If you don't know anything about replication, don't touch it, and talk to whoever in your company set this up.

If that person is supposed to be you, I can't teach a whole replication course in a SE comment, but in general your solution will fall into one of the following categories:

Fix replication (and let it run). Enable and run the "log reader" replication job, this should be set up to run continuously. This may take a while to clear while it works through all the records, but is the "correct" method. Hopefully the only problem is that the log reader job needs restarted; if it doesn't work or errors out, you have a bigger problem.
Re-initialize replication (this will clear the log immediately, but you will need to push a full snapshot to get replication working again). This is a quick fix to get your publisher database working again, but will leave your subscriber databases orphaned (static) until you push a new full snapshot. This might break your reporting server or wherever you are replicating to, and pushing a new snapshot could take a very long time, depending on the size of your database. Don't do this if you don't know what you're doing, or if you don't understand the business impact (and get approval from the stakeholders in the business).
Remove replication. If you are positive that replication shouldn't be set up on this box, then remove replication altogether and the log will clear. Again, don't do this if you don't know what you're doing, or if you don't know what server you are replicating to, or if you don't understand the business impact (and get approval from the stakeholders in the business).


Compare dates problems I created a table with the following columns:

CREATE TABLE INDICADOR_DADOS
(

  ID       VARCHAR2(10 CHAR)   NOT NULL,
  DATE_START       DATE        NOT NULL,
  DATA_END         DATE        NOT NULL,
)

Now wanted before adding, checks if there are dates between which intend to add the following query:

 select COUNT(*) as count from indicador_dados
        where id='23' and TO_DATE(data_inicio,'yyyy/mm/dd')
            between to_date('2013/05/15','yyyy/mm/dd')
            and to_date('2013/05/16','yyyy/mm/dd')

The problem is that this query is not to be returned to 1, and because they must have in the database 05/03/2013 (date_start) and 17/05/2013(date_end). Someone can help me?

maybe duplicate: oracle-datetime-in-where-clause but not it helped me to resolve

I see two problems with your date filter:

You're applying TO_DATE on a DATE datatype. It is not only superfluous, but it actually causes unexpected behaviour.

Here's how Oracle analyses the expression TO_DATE(data_inicio,'yyyy/mm/dd'):

Oracle searches for a function named TO_DATE that has two arguments (DATE, VARCHAR2)
Since no such function exists, Oracle looks for related functions that are accessible with implicit conversions.
(Un)fortunately, a single TO_DATE function exists, so there is no ambiguity here, the function choosen is TO_DATE(VARCHAR2, VARCHAR2).
Your date is converted to a VARCHAR2 with the current session date format, which means that the behaviour is unpredictable. The expression is equivalent to:

TO_DATE(TO_CHAR(data_inicio, 'current NLS_DATE_FORMAT'), 'yyyy/mm/dd')

In this case you will lose the time component of the date if your NLS_DATE_FORMAT is YYYY/MM/DD.

You should never rely on implicit conversions because the behaviour is context dependent.
Your interval filter is wrong: two intervals [A,B] and [C,D] will intersect if and only if:

A <= D
and B >= C

Your query should look like:

SELECT COUNT(*) AS COUNT
  FROM indicador_dados
 WHERE id = '23'
   AND date_start  <= to_date('2013/05/16', 'yyyy/mm/dd')
   AND date_end >= to_date('2013/05/15', 'yyyy/mm/dd')


SQL Server : Memory pressure,Spiked user connections and CPU usage I have the below sql server environment.

SQL Server 2016 standard edition.
128 GB  RAM
96 GB alllocated to SQL Server
8 tempdb's with 1 GB each.
Have a major database with 2.5 TB size.
Reporting also done from the same sql instance.Reporting is minimum

There is memory pressure as i see that my plan cache is getting created periodically.Also i have noticed the flag which shows memory pressure when i queries ring buffer.
Also i see tempdb memory spill,huge memory grant issues when i see the execution plan of slow query at times.
Most of the read operations coming to the database has complex queries and to help with performance,the read operations are run in parallel with the below settings.

MAX DOP : 8
Cost Threshold for Parallelism = 20
40 Logical CPUs

I have been trying to improve performance and i was able to achieve it to some extend.But some days server becomes slow and the transactions(read mostly) take like 30 sec or even 1 minute to finish.Since most of the read transactions are running in parallel,i see the CXCONSUMER wait time and for the transactions and also this is making other queries also to wait for the resources.

I am confused where i should start to get things under control.
Do i need more memory to support to 20 times bigger data here.?

Additional info:
I use the monitoring tool grafana from TICK stack,to see the CPU usage and other performance counters.Sometimes,i see the COU usage going really high and along with it number of user connections going really high.The fact is those are not real users(when i checked with the team) and some other process is causing this.
I am not sure,if some locking mechanism can cause this kind of issues were user connections spike up,also CPU usage.

The threshold for parallelism should be higher than 20 for very large database.

Memory pressure can be avoided by tuning query/index, rebuilding fragmented index, enabling TF 2371 at database level to fire auto update statistics frequently.

As a DBA you can add multiple file group to your very large database, each containing separate mdf file (located on different disk drive) and move larger table on newly added file group to get higher disk IO.


PostgreSQL pg_rewind use cases Let's say that I configured streaming replication between Server A (master) and Server B (slave). If server A goes down and Server B is promoted, would I be able to use pg_rewind, where Server B is the source and Server A is the target, to bring Server A back up up to speed with Server B? According to the documentation regarding pg_rewind:

  The result is equivalent to replacing the target data directory with the source one.

I assume that the rewind wouldn't "rewind" anything, but since:

  When the target server is started for the first time after running pg_rewind, it will go into recovery mode and replay all WAL generated in the source server after the point of divergence.

wouldn't I just be able to place server A in recovery mode and recover all the WAL segments generated by Server B, even though they are on different timelines?

Thanks!

You often need pg_rewind because the standby server that got promoted might have been a few transactions behind the broken primary server. This can even happen with synchronous replication.

pg_rewind undoes these few transactions by replacing all affected blocks with blocks from the promoted (source) server. That is also why recovery will start from the last common checkpoint of both servers.


Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server? For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems.

To my understanding this does not apply for the Microsoft SQL Server, but how does the SQL Server handles this? Is there some internal freeze to keep the db consistent?

Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file. Also assuming you have a multicore machine, for example 16 cores, or at least a significant larger number than one.

From my personal experience I never had issues when taking backups, neither locking nor overhead issues, but my experience is limited.
That's why I always recommend turning backup compression on in the server properties.

So what happens when a backup job is running? And also are there significant differences for the different versions? for example 2008,2012 and 2014 (not the licenses).

Your all points are covered in backup myths - by Paul Randal

30-01) backup operations cause blocking

No. Backup operations do not take locks on user objects. Backups do cause a really heavy read load on the I/O subsystem so it might look like the workload is being blocked, but it isn't really. It's just being slowed down. There's a special case where a backup that has to pick up bulk-logged extents will take a file lock which could block a checkpoint operation – but DML is never blocked.

  Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file.

A backup when done to a single file or device will use 1 writer thread. So if you are backing up to Multiple files /devices (be that multiple .bak files) will have one writer thread per file/device.

  The easiest way to improve backup performance is to allow the backup operation to parallelize, which is known as backup striping. By default, there’s a single data reader thread for each drive letter or mount point being read from and a single data writer thread for each backup device being written to.

Check

SQL Server 2008 Microsoft Certified Master (MCM) Readiness videos especially Backup Internals.
A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - By: Jonathan Kehayias
A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 2)- By: Jonathan Kehayias


Calculating RAM requirements for an in-memory database In general, how much RAM does an in-memory database require? I would assume it requires enough RAM for:

Operating system and general system services, plus
DBMS, plus
the entire database (same amount as stored on disk), plus
temporary database tables
some slack

Considering that the largest component of this would normally be the size of the entire database, then it would require the normal size of RAM required to run a regular on-disk database (e.g. 16 GB) plus the on-disk size of the database (let's say 50 GB for a fairly large DB); in this example it would be at least 76 GB plus room for gradual growth of the database.

I know that the answer would be different for different DBMSs (e.g. SAP Hana or Oracle TimesTen), but I am asking about the general components of the calculation.

Is this analysis accurate, or am I missing some important components of the calculation?

Check out these links - the first is for Michael Stonebraker's VoltDB. He is one of the main drivers behind what he terms NewSQL - he believes that OldSQL (i.e. traditional RDBMS systems, your Oracles, MySQLs and SQL Servers) are doing far to much work buffering and latching &c. In his VoltDB (Java based) system, transactions run to completion and don't compete with other transactions for system resources.

The key thing to bear in mind is that it is a shared-nothing in-memory architecture. He believes that this is good for OLTP, but not for OLAP for which he advocates columnar based stores such as the one he founded - Vertica, since bought by HP.

Stonebraker also believes that NoSQL has thrown the baby out with the bathwater in sacrificing SQL and ACID for performance.

Microsoft's Hekaton is based on very similar ideas (1, 2) which is hardly surprising since there was a Microsoft guy as coauthor of some of Stronebraker's work (can't find reference at the minute). The main difference AFAICS is that Hekaton's stored procedures are compiled to native code.

The references I have given are kind of vague and general, but the first Microsoft link says that your memory should be twice your data. AIUI, this rule is not cast in stone, but will depend on your app's functionality, but it's a good place to start :-). This is a fascinating area and worth reading up on (did an essay on it last year) and there's a lot of stuff out there.

Check out these also (1, 2, 3).


Two corrupted indexes found by DBCC CheckTable I'm wondering if this problem is solvable by dropping/recreating the two indexes.  Both are rarely used, nonclustered, non PK, non FK indexes.

Here is the dbcc output:

  Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

  Msg 8955, Level 16, State 1, Line 1

  Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

  Msg 8951, Level 16, State 1, Line 1

  Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_ITEMS_ADVFLAG_PARETN_PRICE_STOCK' (ID 70). Possible missing or invalid keys for the index row matching:

  Msg 8955, Level 16, State 1, Line 1

  Data row (1:10834009:15) identified by (Id = 8885719) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 9.30 and NSFPQtyInStock = 0 and Id = 8885719'.

Non-clustered indexes can always be recreated from the base table. No need to drop them just rebuild them:

alter index IX_Advertise_BrandCopy_Price_Stock on BrandCopy rebuild;

Of course, you must figure out how did they end up corrupted. Is Windows reporting IO errors? Does your drive firmware report errors? You need to investigate to figure out what is causing the corruptions and eliminate the problem (likely faulty hardware, disk or memory).


Oracle 11g database archive log got increased suddenly in production server My production db archivelog got increased suddenly one night. Around 200 MB of archivelog were generating in FRA within a minute. It got into normal condition after 15 hours from the start of this issue. We haven't done any unusual transactions during this time period.

There was no messages in alert or trace of db regarding this enormous increase of archivelog.

What could be the reason for this unusual behaviour of Oracle database?

Is this an issue with our database or Oracle ADF application or related to our servers?

Please let me if you require more inputs on this.

Any help will be appreciated.

If what you say is true (no unusal transactions), then I say this is typically caused by Segment(Compression) Advisor going rogue.

Compression Advisor still Generates Redo Log on 11.2.0.2 (Doc ID 1324598.1)

Huge Redo Generation by Compression Advisor (Doc ID 1969765.1)

Sometimes it copies your tables to check if you could reclaim space by reorganizing them, in the maintenance window (nights, weekends). This may generate a high amount of redo and archivelogs depending on the size of the table.

This is the reason I always disable the maintenance task for it (also because I find it useless, and I can run my own diagnosis whenever I need to):

begin
  dbms_auto_task_admin.disable
  (
    client_name => 'auto space advisor',
    operation => null,
    window_name => null
  );
end;
/

You can create an AWR/Statspack report of the problematic time period, it is quite easy to recognize if it was the culprit (CREATE TABLE statements with weird/specific names that was not executed by the DBA/application/regular jobs).


How to find the active transactions in VLF? Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs?

I am performing tlog backups on every 15 minutes, but sometimes the following query returns ACTIVE_TRANSACTION or LOG_BACKUP (even such is made):

SELECT [name]
      ,[recovery_model_desc]
      ,[log_reuse_wait_desc]
FROM [sys].[databases]

If I run DBCC Loginfo I can see that the firsts and the lasts rows have status 0.

LOG_REUSE_WAIT_DESC will show LOG_BACKUP any time there has been at least one transaction that has not been backed up (when the database is in Full Recovery mode).

You can see active sessions, requests, and transaction count for each session, by looking at the results of this query:

SELECT SessionID = s.session_id
    , HostName = s.host_name
    , ProgramName = s.program_name
    , SessionElapsedTime = s.total_elapsed_time
    , RequestCommand = r.command
    , DatabaseName = d.name
    , StatementExecuting = case when r.statement_end_offset > 0 THEN
            SUBSTRING(t.text, r.statement_start_offset, r.statement_end_offset -
            r.statement_start_offset)
            ELSE t.text END
    , tst.open_transaction_count
FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
    LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
    LEFT JOIN sys.databases d ON r.database_id = d.database_id
    LEFT JOIN sys.dm_tran_session_transactions tst ON s.session_id = tst.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

If I wrap that query in a transaction, you can see the open_transaction_count field shows 1, which is expected since I have no other activity ongoing on my Tablet.


Any pointers on reducing my privileges in production but not making my job excessively difficult Running SQL Server 2005 and 2008 on Windows 2008 R2.

We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to production and elevating when required.

My primary goal would be to eliminate stupid mistakes - made by DBAs, devopers will have read access in production at the most.  We like to act like we are superheroes that cannot make a mistake, but not having production rights all the time makes sense and it a best practice, recommended by some.

What's the best approach?  What will be least painful to use day to day and during installations?

We currently have a windows group for DBAs that has rights to all of our servers and databases.

I'd also be interested in lowering OS / remote login permissions - but I'm most concerned with DB rights.

I'm guessing we'd need elevated privs to run traces as sa, and possibly to some ownership cleanup before we take away our old login's SA rights.  What other problems might we expect?

Thanks for your advice and sharing your experiences!

At first, I suggest you do all the privilege play in a development or QA environment where there is no problem if the access is removed for some time. You will need to see if the applications won't have any issues with security.

I'll tell you our internal approach:

all the applications use a single domain user that is granted necessary permissions on a database (usually db_owner database role).
for occasional data read we use a SQL login. For that user we assign the database role - db_datareader. This is where it ends the access for developers on the main database cluster. For any other idea they would have, they will use the reporting server databases which are copies (done using Log Shipping) of the main server databases done at midnight. In order not to kill the reporting server with killer ad hoc queries we use the resource groups allocations for memory and cpu.
for the DBA team we have a domain group which has all privileges on the machine and the server (admin on the windows machine and sysadmin on the sql server)
for installations we have a SQL user that is db_owner on the databases that we use when running the updates - we use DDL triggers to monitor schema changes and we should see which changes were done during installation or as separate change
there are some occasional exceptions for experienced developers, but after their need is fulfilled we remove their access - they receive permissions based on their domain login, so we can monitor connections in traces/ddl views and any possible changes with the ddl triggers.

As for the way of doing all that work with the logins and users - in Management Studio in the server security folder you create all the needed logins, and then, you associate them with your databases and give them the roles they need. If you script the action you will see that initially it will be created a server login, then a database user connected to that login, then assigned a database role for that user. You can keep the script in your script set, so you can verify every time which users should be live and kicking and which should not be.


Merge two XML outputs to one from SQL query I have following code developing 2 XML files but I would like them to be in one file itself with multiple Organization tag.

SELECT
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

SELECT
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from
(
    Select 'Coll' AS 'orgRole'
    UNION ALL
    Select 'Lega' AS 'orgRole'
) a
FOR XML PATH(''), TYPE, ELEMENTS
)
from  cpt c1
where cptleagname like '%abc%'
and cptcod IN (select cptcod from pf_map
where pf IN ('abc','abc-jp')
and stat = 'a')

FOR XML PATH('organisation'), root ('collection')

When I run this It produces 2 XML files as output as below.

<collection>
 <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
  </externalCodes>
</organisation>

And

<collection>
 <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

While I want to add it under single file to produce results as Collection as root and merge these files in organization tag as follows.

 <collection>
  <organisation>
   <orgHeader>
    <orgCode>abc</orgCode>
    <longName>abc</longName>
    <shortName>abc</shortName>
   </orgHeader>
   <orgRole>Branch</orgRole>
   <externalCodes>
    <externalCode>
      <system>a</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>b</system>
      <extCode>abc</extCode>
    </externalCode>
    <externalCode>
      <system>Manual</system>
      <extCode>abc</extCode>
    </externalCode>
   </externalCodes>
  </organisation>
  <organisation>
   <orgHeader>
     <orgCode>abc.</orgCode>
     <longName>abc.</longName>
     <shortName>abc.</shortName>
   </orgHeader>
   <orgRole>Coll</orgRole>
   <orgRole>Leg</orgRole>
 </organisation>
</collection>

Please comment below if any other information is required. Thanks.

Something close to your desired output:

;with cpt
AS
(
       Select 'abc' as cptname
)
,sys
AS
(
      Select 'a' as "system"
      union all Select 'b' as "system"
      union all Select 'Manual' as "system"
)
,org
AS
(
      Select 'Coll' AS "orgRole"
      union all Select 'Lega'
)

SELECT
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
s."externalCodes"
FROM
   cpt as c1
   CROSS APPLY
   (SELECT
        s."system" as "externalCode/system",
        LTRIM(RTRIM(c1.cptname)) as "externalCode/extCode"
    FROM sys as s
    FOR XML PATH(''), TYPE, ELEMENTS
    )s(externalCodes)

UNION ALL

SELECT
LTRIM(RTRIM(c1.cptname)) +'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) +'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) +'.' as "corgHeader/shortName",
o.orgRole as "orgRole",
null
FROM
   cpt as c1
   CROSS APPLY
   (SELECT org.orgRole
     FROM org
     FOR XML PATH(''), TYPE, ELEMENTS
   ) as o(orgRole)

FOR XML PATH('organisation'), root ('collection')

output for it:

<collection>
   <organisation>
      <orgHeader>
         <orgCode>abc</orgCode>
         <longName>abc</longName>
         <shortName>abc</shortName>
      </orgHeader>
      <orgRole>Branch</orgRole>
      <externalCodes>
         <externalCode>
            <system>a</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>b</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>Manual</system>
            <extCode>abc</extCode>
         </externalCode>
      </externalCodes>
   </organisation>
   <organisation>
      <orgHeader>
         <orgCode>abc.</orgCode>
         <longName>abc.</longName>
         <shortName>abc.</shortName>
      </orgHeader>
      <orgRole>
         <orgRole>Coll</orgRole>
         <orgRole>Lega</orgRole>
      </orgRole>
   </organisation>
</collection>

You said that the parent tag orgRole should be removed. We are close to the final solution , but not yet there.
I have an idea with FOR XML EXPLICIT

;with cpt
AS
(
       Select 'abc' as cptname
)
,sys
AS
(
      Select 'a' as "system"
      union all Select 'b' as "system"
      union all Select 'Manual' as "system"
)
,org
AS
(
      Select 'Coll' AS "orgRole"
      union all Select 'Lega'
)
,cte_source
AS
(
SELECT LTRIM(RTRIM(c1.cptname)) as cptname,
'Branch' as orgRole,
s.system,
s.extcode
FROM
   cpt as c1
   CROSS APPLY
   (SELECT
        s.system ,
        LTRIM(RTRIM(c1.cptname)) as extCode
    FROM sys as s
    )s(system,extCode)

UNION ALL

SELECT
LTRIM(RTRIM(c1.cptname)) +'.' ,
o.orgRole,
null,
null
FROM
   cpt as c1
   CROSS APPLY
   (SELECT org.orgRole
     FROM org
   ) as o(orgRole)
)

--select * from cte_source

SELECT 1 as Tag
    ,NULL as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,NULL as [orgHeader!3!orgCode!ELEMENT]
    ,NULL as [orgHeader!3!longName!ELEMENT]
    ,NULL as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]

UNION ALL

-- for organisation
SELECT DISTINCT
    2 as Tag
    ,1 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for orgHeader
SELECT DISTINCT
    3 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for orgRole
SELECT DISTINCT
    4 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,c.orgRole as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for externalCodes
SELECT DISTINCT
    5 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname  as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for externalCode
SELECT DISTINCT
    6 as Tag
    ,5 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,c.system as [externalCode!6!system!ELEMENT]
    ,c.extCode as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

ORDER BY
    [organisation!2!]
    ,[orgHeader!3!orgCode!ELEMENT]
    --,[orgRole!4!]
    ,[externalCode!6!system!ELEMENT]
    ,[externalCode!6!extCode!ELEMENT]

FOR XML EXPLICIT

the output for it:

<collection>
  <organisation>
    <orgHeader>
      <orgCode>abc</orgCode>
      <longName>abc</longName>
      <shortName>abc</shortName>
    </orgHeader>
    <orgRole>Branch</orgRole>
    <externalCodes>
      <externalCode>
        <system>a</system>
        <extCode>abc</extCode>
      </externalCode>
      <externalCode>
        <system>b</system>
        <extCode>abc</extCode>
      </externalCode>
      <externalCode>
        <system>Manual</system>
        <extCode>abc</extCode>
      </externalCode>
    </externalCodes>
  </organisation>
  <organisation>
    <orgHeader>
      <orgCode>abc.</orgCode>
      <longName>abc.</longName>
      <shortName>abc.</shortName>
    </orgHeader>
    <orgRole>Coll</orgRole>
    <orgRole>Lega</orgRole>
    <externalCodes>
      <externalCode />
    </externalCodes>
  </organisation>
</collection>

a small remark : externalCodes/externalCode still appears (with null) when we have null values in the corespondent columns.

You can use two variables : one to store the first part of the xml and one to store the second part; then add as prefix <collection> and a suffix `'

declare @cpt table
(
    cptname varchar(10)
)
insert into @cpt(cptname)
values('abc')

declare @sys table
(
    system varchar(10)
)
insert into @sys(system)
values('a'),('b'),('Manual')

declare @org table
(
    orgRole varchar(10)
)
insert into @org(orgRole)
values('Coll'),('Lega')

declare @nvc_o1 nvarchar(max)=N''
     ,@nvc_o2 nvarchar(max)=N''

SET @nvc_o1 = (

SELECT
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  @cpt as c1
FOR XML PATH(''), root ('organisation')
)

SET @nvc_o2 = (
SELECT
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from @org
FOR XML PATH(''), TYPE, ELEMENTS
)
from  @cpt as c1
FOR XML PATH(''), root ('organisation')
)

select CAST(N'<collection>' AS NVARCHAR(MAX))
+ @nvc_o1
+ @nvc_o2
+ CAST(N'</collection>' AS NVARCHAR(MAX))

output of this:

<collection>
   <organisation>
      <orgHeader>
         <orgCode>abc</orgCode>
         <longName>abc</longName>
         <shortName>abc</shortName>
      </orgHeader>
      <orgRole>Branch</orgRole>
      <externalCodes>
         <externalCode>
            <system>a</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>b</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>Manual</system>
            <extCode>abc</extCode>
         </externalCode>
      </externalCodes>
   </organisation>
   <organisation>
      <orgHeader>
         <orgCode>abc.</orgCode>
         <longName>abc.</longName>
         <shortName>abc.</shortName>
      </orgHeader>
      <orgRole>Coll</orgRole>
      <orgRole>Lega</orgRole>
   </organisation>
</collection>

dbfiddle here

LATER ADDED

to export with bcp:
[test].[dbo].[test_xml] it is a stored procedure where you will place your code inside.

declare @sql nvarchar(4000)
select @sql  = N'bcp.exe "EXEC [test].[dbo].[test_xml]" queryout "d:\csv\comm.txt" -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql


A table with many null values VS many tables Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge.

So my question here is let say I have a table to record substances in water:

Here I am only showing 7 columns, it could be more than 100 columns. As you can see here, those empty fields represent the researchers are not conducting tests on those. The researches will choose a few of them so basically this design will leave a lots of null fields and takes up a lot of space.

Should I normalize the large table into smaller ones like these which only have an entry when there is data being inputted? This action is called normalization, am I right? However, this way will need me to join so many tables if I want to display the data in a report.

I am using PostgreSQL and I couldn't find the maximum limit of table can a query joins online. But I have a feeling that joining too many table is a slow operation. Should I keep the full table with nulls in it or should I break it down into smaller tables? Or is there a better way to do this or how would you do it?

Edited:

Edited 2:

As I understand you now, a "water" is kind of a recipe.

For such a recipe you want to record a) what additives where added and b) what the outcome was.

That's two entities, recipe and additives. Both have a many to many relationship with a relationship attribute, which is the amount. The outcome of a recipe is of course an attribute of the recipe and not one of a relation between a recipe and an additive. It's the whole mixture that counts.

A typical and normalized solution are three tables.

One for the additives.

CREATE TABLE additive
             (id serial,
              name text,
              PRIMARY KEY (id));

Another one for the recipe.

CREATE TABLE recipe
             (id serial,
              name text,
              drinkable boolean,
              dangerous boolean,
              PRIMARY KEY (id),
              CHECK (NOT drinkable
                      OR NOT dangerous));

Note: I added a check constraint, that a receipt deemed dangerous cannot be marked as drinkable.

And a table linking the additives for a recipe.

CREATE TABLE recipe_additive
             (recipe integer,
              additive integer,
              amount integer NOT NULL,
              PRIMARY KEY (recipe,
                           additive),
              FOREIGN KEY (recipe)
                          REFERENCES recipe
                                     (id),
              FOREIGN KEY (additive)
                          REFERENCES additive
                                     (id));


How can I restore SQL Server database to a custom location, if the backup file was renamed? Suppose I have an SQL Server database called bubu and the respective backup file is bubu.bak.

If I want to restore it with the backing store in a custom location, I use the following T-SQL statement:

RESTORE DATABASE "bubu2" FROM DISK = N'C:\bubu.bak' WITH REPLACE, STATS = 10,
    MOVE N'bubu' TO N'C:\custom\bubu.mdf', MOVE N'bubu_log' TO N'C:\custom\bubu.LDF'

And so I have a new database bubu2, which happens to be the backup copy of bubu. The important thing here is that I assume that the backup contains the logical files bubu and bubu_log, because the backup file is named bubu.bak.

Now, suppose someone renames bubu.bak to mumu.bak. Doing so, breaks my logic, since now I will try this T-SQL statement:

RESTORE DATABASE "mumu2" FROM DISK = N'C:\mumu.bak' WITH REPLACE, STATS = 10,
    MOVE N'mumu' TO N'C:\custom\mumu.mdf', MOVE N'mumu_log' TO N'C:\custom\mumu.LDF'

And of course, I fail, because mumu.bak does not contain the logical files mumu and mumu_log - renaming the backup file does not rename the logical files contained in the backup (of course).

But then, how do I do what I need if the backup file was renamed?

You can use the filelistonly option to retrieve the files names in a backup:

restore filelistonly from disk='c:\mumu.bak'


Getting formatted MDX values using OPENQUERY Is there any way to convert a value in MDX to a string using its FORMAT_STRING?

I have code similar to the following:

SELECT *
FROM OPENQUERY(DWDB001AS, '
WITH MEMBER [Measures].[ROH Total Sold Gross]
    AS [Measures].[ROH Total Sold Dollars] - [Measures].[ROH Total Sold Cost]
        ,FORMAT_STRING=''Currency''
SELECT {
    [Measures].[ROH Total Sold Gross]
    } ON COLUMNS
FROM [ServiceDept]
')

I am trying to get it to return formatted strings, instead of numeric values.  The CSTR function doesn't seem to take the format string into account.

Converting them in SQL isn't an option here, because the rows have different format strings.  It might be possible to convert them based on the titles of the rows, but I'd prefer to have SSAS do the work for me.

I ended up solving this using the Format function, similar to the following:

MEMBER [RO Count] AS Format([Measures].[ROH Count], "General Number")

I'll keep the question open for a bit, in case anyone has a cleaner answer.  I would have liked to pull the format string in from the calculated members, instead of hardcoding it, but I couldn't figure out how.