What is Heap Fragmentation sans Indexes? When you talk about fragmentation in a heap that has no indexes, there can't be logical order fragmentation, so does fragmentation in this instance only refer to non-contiguous pages? Edit: no.

Is the same thing happening in a case where a heap has an index that has no fragmentation but the heap itself does have fragmentation?

Why does SQL Server create forwarded records at all, instead of just writing to a new page, and backfilling when it makes sense?

Imagine you have a heap and a record needs to be updated which causes it to grow.  The new record size cannot fit on the DB page anymore.  As a result a 'forwarding' record is created which is a pointer to which DB page the real record is.  This is now fragmentation in your heap.

Thus, when you do a bookmark lookup on a heap with indexes on it you will still hit the forwarders which will cause more IO, and your indexes need to store the long RID.  Removing that fragmentation based on the table will help reclaim size but almost always the internal storage engine prefers to work with a clustered index.

Background

Just for background there are a few kinds of fragmentation.  The heap table itself might not be ordered, but it might have non clustered indexes on it which then need to reference the heap through a physical record ID (RID) which is a combination of file id, row id, and a few others.  Basically where the record exists in the database page.  This is not a logical RID.  A logical RID would be like a unique clustered index and if your clustered index is not unique, it adds a uniqueifier similar to the RID.  The DB engine would use this unique "ID" to find the record logically.

This is one reason a heaps row is sometimes wider than a table with a unique clustered index as it adds that info per record in your indexes.


Sorting on compound Indexes This may be a novice question but could it be possible query here.

If I have a compound Index as

{ a: 1, b: 1, c: 1, d: 1 }

Is this query possible?

db.data.find( { a: {$gte:1,$lt:2}, b: { $gt: 3} },c:"test",d:"yes" ).sort( { b: 1 } )

I mean can I use sorting on field b with this compound index and the specified query or do I need to create a separate index on b?

Compound indexes are great for static values

Flip the WHERE components so that static values are first

db.data.find( { c:"test", d:"yes", a: {$gte:1,$lt:2}, b: {$gt: 3} }).sort( { b: 1 } )

Create a different compound index

db.data.ensureIndex({c:1},{d:1},{b:1},{a:1})

How does it help ?

Static c value
Static d value
Sorted Range on b

One more thing

{$gte:1,$lt:2} is really {$eq:1}

I have discussed covering indexes and how the order of columns can changes things. In these posts, I talked about it from the MySQL perspective:

Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
Aug 11, 2013 : How to index for a query with independent range conditions?
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?


Storing dictionaries in mongo Is there any reliable and fast database architecture that allows to store a dictionary(containing up to hundred thousand words) using mongodb?

For optimal performance, MongoDB schema design must be custom-tailored for each application's unique access patterns. It's better to study general MongoDB design techniques than specific architectures (which may not match your specific application).

Having said that, Wordnik is a great case-study:

Similar application: real-time dictionary
20 billion records stored in 3.5 terabytes of MongoDB
Many online presentations sharing their MongoDB knowledge/experiences

Inside Wordnik's Architecture gives a more "macro" view of Wordnik's
architecture.
Lots of other slides

Here is how Wordnik apparently modeled their words:

source: MongoDB Schema Design


Performance Issues on MSSQL Server Web Edition I am facing some performance issues on my MSSQL Server which is a Web Edition. My database size is around 3 TB and there are continuous inserts that happen through various processes. We also have heavy select queries running. I have 128GB RAM on the server and since SQL is a Web Edition we only get to use 64GB RAM and due to this the CPU utilization sometimes goes up to 100%. My question is if we upgrade to Enterprise edition and then reduce 4 cores (to save on cost) will we be able to get better performance? As we are also planning to increase the RAM to 256 GB. The other option is to go for Standard edition and keep 16 cores. As we know with Standard edition the max RAM that I can get for SQL is 128 GB.

Please help me as to what will be the best solution to increase my performance.

These types of decisions should be backed up by a lot more investigation and testing. However, from the information you have provided I can give you a few pointers to put you in the right direction to make the right decision.

Since you are already spiking CPU to 100%, reducing cores to save cost and get more memory will only make things worse.
Also, check what is causing CPU spikes on the server, maybe some performance tuning for the code can help the situation.
Why add 128GB RAM when SQL Server can only make use of 64GB on the server? Do you have any other applications on the server? If you do, move them to a separate machine, it will help with the CPU spike issue.
Even if you do plan to upgrade to Standard Edition why add 256GB RAM, your SQL Server will only be able to use 128GB RAM.
Collect some query stats on the server during peak hours to check if more memory will actually help the situation.
Also, look into moving "old" data into a separate environment (data purging), to keep the application DB size small. (not an easy thing to do but is something should be on your todo list).
Setup a test environment, to test your conclusion drawn from the above investigation to find out whether your suggested fix will actually work. Also, make sure you test your new solution in the proper test environment (Like prod) and not with 10 rows in the testing environment.

Once you have done all the above, the chances are you will make the right decision or what ever you do will not be very far off the right solution.

Also, if possible convince your business to buy the enterprise license, present them the case, educate them and let them make the decision.


cannot execute as the database principal because the principal does not exist - DB Restoration I have received one database backup from one of my client and now I'm trying to restore it into my system.

I tried to search this issue on google and tried most of the suggested solutions but none of them is working. Can anyone please suggest some specific solution for this error after restoration of database?

Screenshot of the Error

Thanks in advance!

When you restore a database it contains database users. These users are transported with the database, whenever you perform a backup and a restore to a different SQL Server.

On your source and target SQL Servers you will have SQL Server Logins. These logins are either SQL Server accounts (Native SQL Server logins) or they will be Windows Server/Domain accounts (Windows Accounts).

SQL Server Logins

SQL Server Logins (Native SQL Logins or Windows Accounts) are stored in the master database and have a unique SID assigned to each SQL Login. You can retrieve a list of SQL Server logins when you query the system catalog view sys.server_principals.

select * from sys.server_principals

You will receive a list of SQL Server logins for your SQL Server instance.

Note: These are the SQL Server Logins and not the database users.

Database Users

Database Users can be queried by querying the system catalog view sys.database_principles of each user database by issuing the following query:

USE [<your_db>]
GO
SELECT * FROM sys.database_principals

You will receive a list of database users that have database privileges assigned to them.

Note: These are the Database Users and not SQL Server Logins.

Normal Behaviour

When you create a database and assign a "user" to the database with certain privileges you are in fact doing the following (partly in the background):

Creating SQL Server Login with password (sys.server_principal)
Creating a database user in the database (sys.database_principal)
Granting permissions to the database user (in various tables, depending on the privileges)

In this case the SID of the SQL Login (sys.server_principal) will match the SID of the database user (sys.database_principal).

Restore Behaviour

When you restore a database from a source SQL Server the database users (Native SQL Server Logins and local Windows Accounts only) in the source database will have different SIDs than the SQL Server Logins on the target server. This is because the SID is unique for the source and target Native SQL Server Login or Local Windows Account.

The SQL Server Logins that are based on Windows Domain Accounts will always have the same SID, because SQL Server will retrieve these values from Active Directory.

When you restore the database from the source to the target SQL Server the SIDs of the Native SQL Server Logins will be mismatched, even though a user might be listed in the sys.server_principals system management catalog of the SQL Server instance and in the sys.database_principals system management catalog of the restored database.

Solution

To rectify this and allow you to navigate the "SQL Server Login | permissions" and/or the "Database Properties | Permissions" you can relink these orphaned database users to the SQL Server Login.

Switch to your user database and query the orphaned database users:

USE [your_db]
GO
sp_change_users_login 'Report'

If a user is reported as orphaned you can relink the SQL Server Login with the Database User by issuing the following command:

USE [your_db]
GO
sp_change_users_lgoin 'Update_one', '<database_user>', '<sql_server_login>'

This will relink the (Native) SQL Server Login (sys.server_principal) on your target instance, with the Database User (sys.database_principal) of the restored database.

Alternative

Seeing as sp_change_users_login is deprecated, you could achieve the same with the ALTER USER statement:

USE [<your_db>]
GO
ALTER USER <database_user>
    WITH LOGIN = <sql_server_login>

In some cases

...when you receive a database backup from a client, you might not have a corresponding SQL Server Login to link to the Database User. In that case you can create a SQL Server Login without assigning permissions to the restored database and then link the newly create SQL Server Login with the Database User with the above mentioned statements.


How to run EM of Oracle 11g, if i haven't emctl, netmgr, dbca, OracleDBConsole? I don't know what is wrong with my Oracle 11g, but it is missing a lot of things. After reinstallation it stays the same. My overall path is like C:\oraclexe\app\oracle and after it there are folders like admin, diag, fast_recovery_area, oradata, product. Also, I don't have dbca, netmgr, emctl and service called OracleDBConsole<SID>. How to fix it?

Nothing wrong with it, you installed 11g Express Edition, which is limited and does not include most features.

You can download other editions from here:

https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html


Showing data in a table horizontally I have a table that stores sensor data (in SQLite) and looks like this;

TABLE Timestream:
idTimestream PK autoincrementing integer,
time int not null,
value float not null,
idSensor integer not null FK

Some but not all sensors have matching times, but I'll only be considering those that do. What I want to do is rearrange the table into the following format,based on a set of sensors listed in the query, not whole set from the table:

Time Sensor1 Sensor2 etc.

I was thinking of Creating a temporary table and then inserting time and the first column, then doing a join on the time for subsequent queries, and finally selecting the whole lot. This doesn't sound too efficient though and I was wondering if there was a better way?

It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements:

test data:

create table timestream(
  idTimestream integer primary key autoincrement
, time int not null
, value float not null
, idSensor integer not null);

insert into timestream(time, value, idSensor) values(1,0.1,100);

insert into timestream(time, value, idSensor) values(1,0.2,101);

insert into timestream(time, value, idSensor) values(1,0.3,102);

insert into timestream(time, value, idSensor) values(2,0.4,101);

query:

select time, sum(case idSensor when 100 then value end) as s100,
             sum(case idSensor when 101 then value end) as s101,
             sum(case idSensor when 102 then value end) as s102
from timestream
group by time;

time | s100 | s101 | s102
:--- | :--- | :--- | :---
1    | 0.1  | 0.2  | 0.3
2    | null | 0.4  | null

dbfiddle here


Can SSDT deploy SQL server service broker objects? Can SQL Server Data Tools (SSDT) deploy sql server broker objects? (So contacts, message types, services, queues and so on.)

It would appear so. I have not tried these, but the following is the list of items that can be added to an SSDT SQL Server Database Project:

Broker Priority
Contract
Endpoint (ServiceBroker over TCP)
Endpoint (SOAP over HTTP)
Event Notification
Message Type
Queue
Remote Service Binding
Route
Service


Making a field greater than the limit unique Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome.

I am currently designing a set of database tables for a project I am working on. A couple of the tables such as the following (simplified table):

CREATE TABLE members (
  id int(11) NOT NULL AUTO_INCREMENT
  email varchar(255) NOT NULL
  PRIMARY KEY (id)
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

I would like to ensure that the email remains unique however I am unable to use the unique constraint due to the length of the field and the fact I am using the utf8mb4 character set (trying to do so results in an error sch as "Specified key was too long; max key length is 767 bytes").

To overcome this I appear to have the following choices:

Modify the character set to 1 that will store the data in with a smaller footprint
Reduce the length of the field so it fits within the limit
Split the email into 2 sections (username, domain) and use these as unique values
Check for uniqueness in the code utilizing the database
Create a hash of the email field and store this in another field. Use this for unique constraint
Use a before insert and before update trigger to enforce the uniqueness

I am not overly keen on options 1 (or am I simply, in this case, using the wrong character set?), 2 (even this field is actually too short for a valid email address according to the RFC) and 3 (the domain would still be too long if following the rules of the RFC's) as I do not believe the structure of the database should be compromised to satisfy a simple constraint.

4 relies on the code to ensure the database is conforming. I am not overly keen on this as it can cause the data stored to lose integrity is someone forgets to check.

5 would require extraneous data to be stored in the database. This could cause collisions but dependent upon the hashing algorithm it may not be a problem.

6 would be implemented something like:

DELIMITER $$

CREATE
DEFINER = 'user'@'localhost'
TRIGGER MemberBeforeInsertTrigger
BEFORE INSERT
ON members
FOR EACH ROW
BEGIN
  DECLARE userCount int;

  SELECT
    COUNT(email) INTO userCount
  FROM members
  WHERE email = new.email;

  -- Ensure Email is not in use.
  IF userCount > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email address already in use';
  END IF;

END
$$

DELIMITER ;

Would using a trigger such as this cause any negative affects in the performance of the database (compared with above options) or am I simply overthinking this?

A realistic email limit could have a length of 64
email addresses are never utf8, only ANSI
innodb_large_prefix can give you a bigger index.
generated column
with a HASH as a unique index is just as good, like:

ALTER TABLE members ADD email_hash VARBINARY(56) AS (SHA2(email, 224)) UNIQUE KEY


How to perform a 3 table join with sparse lookup table I have 3 tables companies, products, and companies_products.

companies (id, name)
products (id, name)
companies_products(company_id, product_id)

companies_products is sparse, in that the only entries in it are for products that a company has subscribed to - there is no entry for non-subscribed products). I am doing this to prevent having to update every company in the event that a new product is to be added.

Unfortunately this makes getting list of what a company is and isn't subscribed to rather difficult for me to figure out

Is there a way to query for a result from this data:

company

(id: 1, name: "Test Company")

products

(id: 1, name: "Product 1")
(id: 2, name: "Product 2")
(id: 3, name: "Product 3")

company_products

(company_id: 1, product_id: 1)
(company_id: 1, product_id: 2)

to get a result such as this?

(company_id: 1, product_id: 1, status: "enabled")
(company_id: 1, product_id: 2, status: "enabled")
(company_id: 1, product_id: 3, status: "disabled")

First part returns the Cartesian product of companies and products.

from       companies c
cross join products p

Then for every combination of Company - Product it checks if exists a record in the companies_products table.

select     c.id,
           c.name,
           p.id,
           p.name,
           if(cp.company_id is null, 'status: enabled', 'status: disabled') as status
from       companies c
cross join products p
left join  companies_products cp
on         cp.company_id = c.id
and        cp.product_id = p.id
;

id | name         | id | name      | status
-: | :----------- | -: | :-------- | :---------------
 1 | Test Company |  1 | Product 1 | status: disabled
 1 | Test Company |  2 | Product 2 | status: disabled
 1 | Test Company |  3 | Product 3 | status: enabled

dbfiddle here

Or you can use EXISTS to check if there is a record that match both identifiers.

select     c.id,
           c.name,
           p.id,
           p.name,
           if (exists (select 1
                       from   companies_products cp
                       where  cp.company_id = c.id
                       and    cp.product_id = p.id)
                      ,'status: enabled', 'status: disabled') as status
from       companies c
cross join products p
;

id | name         | id | name      | status
-: | :----------- | -: | :-------- | :---------------
 1 | Test Company |  1 | Product 1 | status: enabled
 1 | Test Company |  2 | Product 2 | status: enabled
 1 | Test Company |  3 | Product 3 | status: disabled

dbfiddle here


How to peer to a new domain? How do I peer to a new domain? I'm using NuoDB on MacOS
http://dev.nuodb.com/download-nuodb/request/download

There are times when you may want to take any number of hosts from Domain A and Peer to Domain B. The most common example is moving hosts between development environments. By default our Durable Domain state will prevent this from happening because the new host is a known entity in the currently defined domain.

The typical error message that is received in the agent log on the peering host is:

SEVERE PeerService$EntryListener.messageArrived (serv-socket5-thread-2)
Failed to set ServerStateUUID
java.lang.IllegalArgumentException: UUID was already set

To resolve this execute the following:

Ensure that your host's default.properties files is updated with the new peer value (IP or DNS).

Stop / Start the nuoagent service with the --reset-broker-state flag.
It's important that you do this only once otherwise you will be resetting your domain state with each restart.

$ sudo service nuoagent stop

$ sudo service nuoagent start --reset-broker-state


Comparing nullable column values using "IS NULL" versus "= NULL" I'm dealing with nullable values in a column of a table, i'm wondering why this:

with cte as (
    select 'c1'::text as c1,null::text as c2
    union
    select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 is null

returns a result set with the two "rows" having a null value in c2; comparting to this query:

with cte as (
    select 'c1'::text as c1,null::text as c2
    union
    select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 = null

that returns an empty result set!!

so it looks like that field is null is actually different from field = null? the question is... is there "null-compatible operator" to use? so i can write the comparision like: column_name = $1 and argument $1 can be a not null or null value. IF $1 is a null value then it will semantically equals to write as IS NULL, ELSE (if $1 is not a null value) then it would be semantically equals to = $1.

P.D: This question also applies to the case is NOT NULL versus <> NULL

As documented in the manual it's not possible to use = to test for NULL values:

  Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL.

is there "null-compatible operator" to use - Yes, right below the above quote it states:

  When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM predicates

So you can use

where c2 is not distinct from $1

One drawback of the IS DISTINCT or IS NOT DISTINCT operator is however, that they can't use an index.


SQLCMD :connect directive - how can I specify connection protocol? In a script I have the directive

:connect DbServer

and it seems that if I run this script in SQLCMD (like this:

sqlcmd -i script.sql

then sqlcmd attempts to connect using Named Pipes (which doesn't work). I get error messages saying that Named Pipes provider could not connect.
However, if I try to connect to the same server by specifying its name in the -S parameter, it works fine.

Is there a way to let the :connect directive know that I am trying to connect by TCP/IP?

I tried using :connect tcp:DbServer to no avail. Also tried to go by IP address, same results. On this machine I don't have the SQL Server Configuration Manager to specify connectino aliases or manipulate protocols. Maybe that's the next thing to do, but I'd like to know if somebody knows a way to contain the connection specification inside the script.

sqlcmd.exe -S tcp:myserver or sqlcmd.exe -S tcp:myserver\instance should do the trick.  If you must specify the port, do so like this:  sqlcmd.exe -S tcp:myserver,1433

By the way, documentation for SQLCMD is available directly from Microsoft, where it states the -S parameter syntax as:

  -S [protocol:]server[instance_name][,port]

SQLCMD Mode in SQL Server Management Studio accepts precisely the same syntax.

So:

:CONNECT [protocol:]servername[instance_name][,port]


Why is this seemingly simple insert trigger function causing deadlocks? This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens.

The situation...
I have a tweet table and one of the columns receives an array[]::text[] of urls.
There is a trigger function on the table that inserts the urls into a url_starting table.

The url_starting table looks like this.

CREATE TABLE public.url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

And tweet table the trigger looks like this.

CREATE OR REPLACE FUNCTION public.create_tweet_relationships()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO url_starting (url)
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING;

    RETURN NULL;
END
$function$;

Sometimes I get deadlock errors like this.

deadlock detected
DETAIL:  Process 4540 waits for ShareLock on transaction 4709; blocked by process 4531.
Process 4531 waits for ShareLock on transaction 4710; blocked by process 4540.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (2314,101) in relation "url_starting"
SQL statement "INSERT INTO url_starting (url)
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

Shots in the dark... ♂

Could this be caused by UNNEST? Am I doing something wrong in the syntax?

Why does the error say in relation "url_starting" when there is no relationship in the table?

There are thousands of tweets committing to the database simultaneously. It shouldn't matter if set up correctly, should it?

Under heavy concurrent write access, the defense against deadlocks is to process rows in consistent order in all writing queries.

You may have sorted rows properly in all your writing queries to the table tweet- did you? (I know about that table from your related question.)

And you are obviously sorting rows to be "upserted" in url_starting in the trigger function.

That should do it for transactions inserting a single row in tweet with multiple URLs to be upserted in url_starting.

But while inserting multiple rows to tweet, each with an arbitrary array of URLs, rows to be upserted in url_starting are still in inconsistent order for the transaction. URLs are only sorted per row in tweet, not for the whole transaction (or even command). You would need to unnest all URLs of the same INSERT, sort them consistently, and then upsert url_starting. This cannot be achieved with a trigger FOR EACH ROW. I don't think you can solve this at all with your current approach while inserting multiple rows with arbitrary arrays of URLs. Concurrent write access to both tables is inherently conflicting.

Single-row inserts to tweet should be fine (each in its own transaction) - though possibly substantially more expensive. Maybe you can get rid of the trigger completely and reorganize the workflow (with data-modifying CTEs): write a sorted list of URLs to url_starting, then write to tweet ...

Arrays are often problematic in a relational table design. Full normalization might be another approach - replacing the array column with a related table. Not sure if that's the way ...

Or you keep your transactions as short and fast as possible, making deadlocks the rare exception, and prepare your application to retry in case of an error.


Select row from non-empty node value in xml column using xml query I am trying to select the rows from the table by putting the condition on the xml column. The condition like if there is date value on the node ‘Completedon’ in the xml column then select that row.
For this above requirement I wrote one query, that is

SELECT [Tasks].* FROM [TaskManager].dbo.[Tasks] WHERE ( [Work details].exist('for $x in /Root/Row where  ( (empty(xs:date($x/Completedon[1])) or ($x/Completedon[1])="")  )  return $x')>0 )

But in this above query, it selects all the rows which has the ‘Completedon’ node as well as the row without this node.
Please help me, where did I go wrong? And please help me to correct the query..
Thank you in advance…

I have inferred some XML structure from your query but it is best if you supply some sample XML.  Does this work for you?

SELECT *
FROM dbo.Tasks
WHERE [Work details].exist('Root/Row/Completedon/text()') = 1

My full repro script:

CREATE TABLE dbo.Tasks
(
    taskId          INT IDENTITY PRIMARY KEY,
    [Work details]  XML
)
GO

INSERT INTO dbo.Tasks ( [Work details] )
VALUES ( '<Root>
    <Row>
        <Completedon>01/04/2016</Completedon>
    </Row>
</Root>' ),
( '<Root>
    <Row>
        <Completedon></Completedon>
    </Row>
</Root>' )
,
( '<Root>
    <Row>
        <Completedon/>
    </Row>
</Root>' )

GO

-- Original query; returns all records
SELECT [Tasks].*
FROM dbo.[Tasks]
WHERE ( [Work details].exist('for $x in /Root/Row where  ( (empty(xs:date($x/Completedon[1])) or ($x/Completedon[1])="")  )  return $x')>0 )

-- Revised query
SELECT *
FROM dbo.Tasks
WHERE [Work details].exist('Root/Row/Completedon/text()') = 1

And test results:


Redshift table level security I have a common scenario where I need to drop and create an Amazon Redshift table or view (more often with views). There are situations where an ALTER is not going to work, which would preserve privileges.

What query can I use to identify all of the privileges on a particular table or view so that I can script out the reapplication of those privileges, after I perform a drop and create operation?

we have a bunch of utility scripts and views for this kind of thing on GitHub. This one is for table privileges: v_get_tbl_priv_by_user .

A couple of things to keep in mind to simplify your admin tasks:

Use late binding views to minimize drop-recreate scenarios: WITH NO SCHEMA BINDING
Use ALTER APPEND to move a large volume of rows between tables. Again avoiding expensive drop-rename or insert scenarios.


Progress of mongodump operation jams at a random point We are executing remote mongodumps to backup collections. For some time now sporadically one of these dumps stops to progress any further and remains to log out the same amount of documents processed over and over:

2016-06-27T06:35:06.428+0000    [##############.......]  col  1140198/1853120  (61.5%)
2016-06-27T06:35:09.428+0000    [##############.......]  col  1140198/1853120  (61.5%)
2016-06-27T06:35:12.428+0000    [##############.......]  col  1140198/1853120  (61.5%)

This time the dump is stuck at this point for about four hours and more than 5000 log lines showing 61.5% of progress.

I can still access the MongoDB Server from the backup machine, mongodb answers to queries, no annomalies in the logs.

MongoDB version 2.6.12

Seems that some other folks had this issue (here or here) without any clear resolution. Someone suggests adding --quiet to the mongodump command.

But maybe mongodump is not the correct way of doing it. From the documentation:

  mongodump and mongorestore are simple and efficient tools for backing
  up and restoring small MongoDB deployments, but are not ideal for
  capturing backups of larger systems

Also:

  When connected to a MongoDB instance, mongodump can adversely affect
  mongod performance. If your data is larger than system memory, the
  queries will push the working set out of memory, causing page faults

Some other methods of creating backups are detailed in the documentation. Maybe „Filesystem Snapshots” or „cp or rsync” are more suitable to your use case.


postgreSQL - Checking if one date interval does not overlap a date interval in array of intervals for each row PostgreSQL(9.6), I am trying to check whether a date interval

('2018-11-18 12:00','2018-11-20 12:00')

does not overlap any of the following date intervals in the array

{('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00')}

The elements in the array are composite:

CREATE TYPE reservation AS (
checkIn TIMESTAMP WITHOUT TIME ZONE,
checkOut TIMESTAMP WITHOUT TIME ZONE
)

Basically I am trying to check whether a "room" is available for a selected time interval by checking if this interval does not overlap with any existing reservation date intervals, example: ('2018-11-21 12:00','2018-11-23 12:00'),('2018-11-19 12:00','2018-11-20 12:00').

Here is the rooms table:

CREATE TABLE rooms (
rId roomId PRIMARY KEY,
hRef hotelId NOT NULL,
rNo roomNo NOT NULL,
rType roomType,
numPeople INTEGER,
rBedOptions roomBed[],
reservations reservation[],
priceNight FLOAT,
FOREIGN KEY (hRef) REFERENCES hotels(hId) ON UPDATE CASCADE ON DELETE SET NULL
)

INSERT INTO rooms VALUES
('R001','H001','101','one-bedroom',1,
ARRAY[row('1 twin')::roomBed],
ARRAY[
      row('2018-11-21 12:00','2018-11-23 12:00')::reservation,
      row('2018-11-19 12:00','2018-11-20 12:00')::reservation],
450.5);

So far I have been successful in checking the first element of the array for each row by writing the following query:

SELECT * FROM rooms R
WHERE R.reservations[1] IS null
OR NOT (('2018-11-18 12:00','2018-11-20 12:00')
OVERLAPS (R.reservations[1].checkIn, R.reservations[1].checkOut))
ORDER BY rid;

The problem is that I don't know how to check all elements in the array if there is more than one. Any ideas or suggestions? Thanks

Why don’t you use second table “rooms_reservations” with period of data reservation? It’s more easy and faster with big data.


How does mydumper's ''--use-savepoints" option reduce metadata locking MyDumper 0.6.1 add a new option --use-savepoints. From the manual, it means:

  Use savepoints to reduce metadata locking issues, needs SUPER privilege

I don't understand. How does it "reduce metadata locking issues" and why does it need "SUPER privilege"? As I think, metadata is essential to prevent other DDL modifying table structure.

I had to do a little digging in the source code of mydumper to find the answer to this question. If you look at the actual source code of mydumper:

You will find the following starting on line 415 as part of the process queue:

if(use_savepoints && mysql_query(thrconn, "SET SQL_LOG_BIN = 0")){
    g_critical("Failed to disable binlog for the thread: %s",mysql_error(thrconn));
    exit(EXIT_FAILURE);
}

This shows using --use-savepoints in mydumper requires the ability to turn off the MySQL bin log. I attempted this on my own MariaDB server which still uses MySQL as the base DBMS and got the following error when using a non administrator account:

  MariaDB [(none)]> SET SQL_LOG_BIN = 0;
  ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

From what I was reading of the actual code and testing this condition in my own MySQL server my understanding is you need "SUPER privilege" as mydumper will be disabling logging to the binlog while it runs. This is part of "the enabling or disabling logging" power of a SUPER mentioned in the DOCS.

More specific info on the binlog is here:

http://dev.mysql.com/doc/refman/5.6/en/set-sql-log-bin.html

As far as savepoints go:

http://dev.mysql.com/doc/refman/5.6/en/savepoint.html

After reading through the manual and this bug report it looks like if savepoints are released they will release locks on the table being worked on which can prevent locking issues which have been seen on mysqldump as recently as MySQL 5.5.

I hope this gives a little more insight into the mydumper tool.


How to modify a stored procedure using SQLCMD for MS SQL? Suppose a stored procedure as follows have been created using the Microsoft SQL Management Studio.

use testbase
go
create procedure testtable_pricesmaller
    @pricelimit money
as
select * from testtable where price = @pricelimit;
go

Is it possible to modify a stored procedure using SQLCMD for MS SQL?

Assuming that you have the permission to do so then there should be no reason why you could not. The following example assumes that you are using Windows Authentication:

sqlcmd -E -S computer\instance -d database

alter procedure testtable_pricesmaller
@pricelimit money
as
select * from testtable where price = @pricelimit;
go

Alternatively if you have an existing script file that has the command you want to run you could use the -i option to run that file:

sqlcmd -E -S computer\instance -d database -i file.sql

Here is some more information on the sqlcmd utility that you should find helpful:

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

I hope this helps you.


Upgrade SQL Server 2012 Developer Edition to SQL Server 2014? I have several x64 SQL 2012 Developer servers with SP2 and CU4/CU5. In your experience, with this SP/CU mix, is the upgrade to 2014 Developer supported ? I can only find information on the supported path from 2012 SP1 to 2014.

From Microsoft BOL 'Supported Version and Edition Upgrades'

  SQL Server 2014 supports upgrade from the following versions of SQL
  Server:
  • SQL Server 2005 SP4 or later
  • SQL Server 2008 SP3 or later
  • SQL Server 2008 R2 SP2 or later
  • SQL Server 2012 SP1 or later

So, as you have 'later' than 2012 SP1, then answer is yes.

The actual 'Upgrades from Earlier Versions to SQL Server 2014' section of the linked article just looks like it is out of date and requires addition of 2012 SP2.


SQL Server : scheduled JOB issue I have one issue in my live server, I created a job to shrink the log file and I have scheduled it to run every day with sa user

When I run this job manually from Management Studio, it is run successfully and shrinks the log file of database.

I have also checked the history for this job and it is running every day as per the scheduled time, but the issue is that that when it is run as per schedule that time it is run successfully but doesn't shrink the log file, the log file size stays the same size.

Can anyone help me to find what is the issue and how I can solve it?

The answer is you cannot shrink log file if there is no free space available.

Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

Can you run DBCC LOGINFO(DB_name) and see what is last value in status column. If value is 2 you cannot shrink if value is 0 you can because 0 means VLF is truncated and no active transaction is using it. You can read how shrinking of Transaction Log works

A log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended. It wont give you failure message but wont even shrink. I also think there is no free space in log file which can be freed and you are just forcing it to free it.
What is the query you are using to shrink database log file can you please show it.

You must read This BOL article on shrinking a log file. What is output of below query

Use Db_name
GO
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Next question would be why are you shrinking Log file every day is it really causing issue. Shrinking of log file is also a evil operation because when log file grows process running has to wait for it to grow and since instant file initialization is not there for user database log files every time log file grows it has to Zero out space and write the the information there. This could be a time consuming on busy system where disk I/O is heavily utilized so moral is don't shrink log files. by shrinking you are just allowing it to grow again and it will grow again because SQL Server has to write information in log files related to queries running on system

You can pre-size it and provide necessary space to log files so as to avoid autogrowth events.

Make sure you keep proper value for Autogrowth setting don't keep it in percentage as it will cause unnecessary growth. More about autogrowth can be read from this link. See by how much SQL Server is growing and you would allocate that much space for a month to database log file so as to avoid and autogrowth events.


MySQL usage suddenly hitting through the roof We have a site with MySQL DB running properly for more than 2 years.

However, 2 weeks ago we noticed a sudden spike in load. We started the MySQL service and load normalized. We assumed the issue was one off and forgot about it.

But for last 1 week are seeing the MySQL load hitting high almost on a daily basis. Sometimes just a few minutes after restart. It has rendered the site almost unussable.

A check on the log does not show any query that would cause a spike. Majority of the queries (about 90%) take less than 10ms. A few (about 9%) takes between 10 and 20ms. There are still some that take between 20 and 30ms. But this are are negligible, accounting for about 1%. Apart from 2 long-running queries that runs when the app starts, I haven't seen any regular query that takes more than 30ms.

$ uname -a
Linux 3.10.0-229.7.2.el7.x86_64 #1 SMP Tue Jun 23 22:06:11 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using  EditLine wrapper

Any reason why MySQL would suddenly become unstable?

If, for some reason (s), we have just hit limitation for MySQL (probably an InnoDB issue) would it help if we moved to MariaDB or Persona Server (that uses XtraDB)?

Edit 1: Additional information

Something I have noticed is at the time when performance degrades there are several SELECT queries. They seem not any big resource intensive queries (the longest takes about 4ms) but they occur in bursts, and probably this could be the problem.

The `'burst queries' looks valid, and not as a result of DOS attacks or XSS.

**Edit 2: MySQL conf*

$ cat /etc/my.cnf
[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/mysqltmp

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

open_files_limit=50000
query_cache_size=2048M
max_connections=100
max_user_connections=25
wait_timeout=20
tmp_table_size=6144M
max_heap_table_size=6144M
thread_cache_size=64
key_buffer_size=8192M
max_allowed_packet=268435456
#table_cache=32768
table_open_cache=32768
table_definition_cache=32768

delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair)

query_cache_limit=32M # leave at default unless there is a good reason
query_cache_type=1
join_buffer=32M # leave at default unless there is a good reason
sort_buffer_size=32M # leave at default unless there is a good reason
read_rnd_buffer_size=16M # leave at default unless there is a good reason
read_buffer_size=32M # leave at default unless there is a good reason

collation_server=utf8_unicode_ci
character_set_server=utf8

general_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
innodb_file_per_table=1
innodb_buffer_pool_size=34406M
innodb_additional_mem_pool_size=7424M
innodb_log_buffer_size=7424M
innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
innodb_change_buffering=all
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
innodb_doublewrite = 0

default-storage-engine=MyISAM

[mysqld_safe]
#malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4
malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

my.cnf shows default-storage-engine=MyISAM but all tables I have created are InnoDB.

Environment

How much RAM? 48GB

Using InnoDB? Yes

How many cores? 8

This is a common issue as you scale to some point.  There could be many causes; let's first search for the cause; then the solution.

long_query_time=0
turn on the slowlog
run for a few hours
turn off the slowlog (or raise the cutoff to 1)
run pt-query-digest to find the 'worst' couple of queries.

Those queries may be zillions of 10ms queries, or it may be a few 1-minute queries.  In either case, look carefully at whether

you need them that often,
they have optimal indexes,
they can be combined in some more efficient way
they can be rewritten
etc.

Check some tunables

How much RAM do you have?
Are you using InnoDB?
What is the value of innodb_buffer_pool_size?
If you provide me with SHOW GLOBAL STATUS; and SHOW VARIABLES;, I will search for other issues.

Other likely causes

UUIDs are terrible as keys; as soon as the data exceeds cache, you are in deep dodo.
EAV schema has problems
How many cores?  What is max_connections, Max_used_connections?  What version?  (You could have the "thundering herd" or "too many people in the grocery store" syndrone.)

Tuning Analysis

Observations:

Version: 5.6.27
48 GB of RAM
Uptime = 03:06:22; Please rerun SHOW GLOBAL STATUS after several hours.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

Since you are not(?) using MyISAM, decrease key_buffer_size to only 10M.

innodb_log_buffer_size is over 7G, 15% of RAM -- far too big; decrease to 100M.

innodb_additional_mem_pool_size is no longer used; remove it from my.cnf

It is terribly dangerous to set tmp_table_size and max_heap_table_size too high.  Recommend 1% of RAM size, not 12%.

query_cache_type = ON and query_cache_size = 2G -- This may be the worst performance hit.  No more than 100M for the size.  When it is 2G, each write spends a lot of time going through Query Cache to purge entries for the modified table.  Furthermore, the metrics on the QC say it is not very effective.  So even consider turning it OFF.

Several STATUS metrics indicate lack of indexes and/or poorly written queries.  Let's take a look at a couple of them.

innodb_doublewrite = OFF -- this may (rarely) lead to "torn pages" after a crash.

There seems to be no MyISAM activity; this is unexpected because some system tables are MyISAM.  What gives?  Also puzzling:  default-storage-engine=MyISAM.

Details and other observations

Memory allocation

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (8589934592 - 1.2 * 0 * 1024) / 49152M = 16.7% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 8589934592 = 0 -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( innodb_log_buffer_size ) = 7G -- far too big; decrease to 100M

( join_buffer_size ) = 32M -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- Use the default.

( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 6442450944, 6442450944 ) / 49152M = 12.5% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping.
-- Decrease tmp_table_size and max_heap_table_size to, say, 1% of ram.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 5,245 / (5245 + 19126) = 21.5% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( tmp_table_size ) = 6,442,450,944 = 6144MB -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Handler_read_rnd_next ) = 23,608,688,071 / 11182 = 2111311 /sec -- High if lots of table scans
-- possibly inadequate keys

( Handler_read_rnd_next / Com_select ) = 23,608,688,071 / 116779 = 202,165 -- Avg rows scanned per SELECT. (approx)
-- Consider raising read_buffer_size

( Select_scan ) = 33,170 / 11182 = 3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 33,170 / 116779 = 28.4% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log / max_connections ) = 70 / 100 = 70.0%

( Connections ) = 28,080 / 11182 = 2.5 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 64 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- 0 is good for Windows
0 is inefficient for non-Windows; 10 is probably fine

( thread_cache_size / max_connections ) = 64 / 100 = 64.0%
-- (0 for Windows)

( Com_show_collations ) = 30/minute.  What's up?


create a backup table with million records I want to move old records from TABLEA to TABLEA_AUDIT. TABLEA have around  1.5 million records and it have two nested tables.

No of Records : 1557951
Size :  1024 MB

I tried it by,

1.using Create table as select * from

CREATE TABLE
TABLEA_AUDIT
COLUMN TABLEA_STAGE NOT SUBSTITUTABLE AT ALL LEVELS
NESTED TABLE TABLEA_STAGES STORE AS AUDIT_TABLEA_STAGES,
NESTED TABLE TABLEA_MODELS STORE AS AUDIT_TABLEA_MODELS
AS
SELECT *
FROM
TABLEA
WHERE COULMN1 IS NOT NULL
AND TRUNC(UPDATED_DT) < '01-MAR-18';

Result : 2hours of waiting and didnt get any result

2.Tried CREATE TABLE AS with no logging by follwoing below blog.
http://www.dba-oracle.com/t_fast_copy_data_oracle_table.htm

Result : 2hours of waiting and didnt get any result

3.Developed a new procedure to copy the records and created a new DBMS_JOB

EXECUTE IMMEDIATE 'CREATE TABLE TABLEA_AUDIT AS SELECT * FROM TABLEA';

Result : Job is running for more than 2hours and no result.

4.Created the table and developed a procedure to insert bulk records

set serveroutput on size unlimited
set timing on
declare
type audit_type is table of TABLEA%rowtype;
v_type audit_type;
CURSOR temp_cur is
select  *
FROM TABLEA a
WHERE COLUMN1 IS NOT NULL
AND TRUNC(UPDATED_DT) < '01-MAR-18';
BEGIN

    OPEN  temp_cur;
    / collect data in the collection /
    FETCH temp_cur BULK COLLECT INTO v_type;
    / close the pointer /
    CLOSE temp_cur;

    FORALL i in v_type.first .. v_type.last
    INSERT INTO TABLEA_AUDIT VALUES v_type(i);

    FORALL i in v_type.first .. v_type.last
    DELETE  FROM TABLEA WHERE PRIMARY_KEY_COL = v_type(i).PRIMARY_KEY_COL;

    COMMIT;
END;
/

Ps: We have tried the same with out any indexes in new AUDIT (TABLEA) table.
Result :  Block running for more than a hour.

  Without Nested table I can copy 7,716,204 records in less than a
  minute.  Size : 960.28125 MB

Thanks.

In case you won't need the data immediately, you can try extracting table structure, then rename it to backup, and create a new table with the same structure and name.

ALTER TABLE table_name RENAME TO new_table_name;

This will show the table structure. you can use the result to create new table.

>describe table_name

many suggestions are out there to copy by the select statement onstackoverflow. one is:

create table table_name as select * from table_name_oldd where 1=0;

It creates structure but won't copy any value because the condition is false.


Unix timestamp conversion How can I output my Unix timestamps to human readable GMT?

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      ,timecreated
      ,timemodified
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      ,.timecreated
      ,.timemodified
ORDER BY myorder ASC

Note: The query has been changed for security reasons.

MySQL support a FROM_UNIXTIME(...) function :-)

select FROM_UNIXTIME(1433433155)

And the other way around, UNIX_TIMESTAMP

Applying it to your example, assuming timecreated and timemodified are those Unix timestamps:

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
ORDER BY myorder ASC


Primary index is being used, not the one which is on the field present in the where condition I have 2 tables, one is category table and has below coulmns and has a primary index on cat_id column.

cat_id (primary key),  acc_id, islive

Another table is cat_brand table which has below fields and has the composite primary key on fields cat_id and brand_id and fk relation on cat_id column.

cat_id brand_id acc_id

Indexes on cat_brand are(cat_id,brand_id) which is primary index and another index on acc_id column, which I created for below SQL query.

select distinct cat_brand.brand_id
FROM  category cat
inner join  cat_brand on cat.cat_id = cat_brand.cat_id
where cat_brand.acc_id=1
  and cat.islive in ('true' )
  and cat.acc_id=1;

Using explain query shows me, that above query uses the primary index of table cat_brand which is cat_id,brand_id while where condition in the SQL query is cat_brand.acc_id=1 and cat_brand has an index on the same field, then why it doesn't use the index on acc_id field?

Possibly these composite indexes will speed it up:

cat: (acc_id, islive_in, cat_id)
cat_brand: (acc_id, cat_id, brand_id)

SHOW CREATE TABLE may give more clues as to why the Optimizer picked what it did.


Accessing Database without using SQL This might be some ambiguous but I have not much info about this so I need some start point;

As a general idea; in conventional RDBMS', there are a DB engine and storage of data. We use the language 'SQL' to access these datas on the DB. To access datas, is there other ways different from using SQL queries? Can we 'fire' events of DB engine without using SQL and get the datas in the storage? If yes, how?

You can also use your favorite hex editor :). This can even be a good learning lesson to see how things work under the hood. For example, you could have a look at those posts/videos:

How does sql server store data by Brent Ozar;
Corrupting Databases for Dummies- Hex Editor Edition by Kendra Little.

But using a high level language like SQL or your vendors implementation (tsql, pl/sql...) makes things so much easier.


before update trigger on sql server I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and password. I would like to create a trigger on users table so if I insert pairs of values in users table for name and password fields and after I update the value for password field where name = some of the inserts I have made in users table, trigger fires and inserts old value of name and password in formerpasswords table. How could I do that in SQL Server? I know that this can be done easily in MySQL using a before update trigger but in SQL Server that doesn't exist so I look for a way to do this.

Thank you so much in advance.

Here is a functional (albeit basic) example for you.  Triggers in SQL Server can be either AFTER or INSTEAD and can apply to any DML (INSERT, UPDATE or DELETE), even at the same time.  Inside the scope of each trigger is a virtual table called:

INSERTED - what is intended to be.
DELETED - what the old values were.

These virtual tables contain every column from the base table and make modification to either the base table or other tables.

CAVEATS (or basic reminders):

MAKE SURE that the statement inside your trigger can handle multiple rows properly.  A common mistake that I see is people making the logic inside the trigger only work for one row at a time.
These can kill performance, use them sparingly and keep the logic as simple as possible.
Bulk Inserts do not typically fire triggers, but can be configured to do so.
AVOID cursors inside triggers.
You cannot try/catch inside a trigger.  Any exception that happens in there WILL bubble out to the caller.

FINALLY

Take care storing the passwords using a proper cryptographic library.  I used VARCHAR in my example below to better illustrate the trigger logic.

DROP TABLE IF EXISTS dbo.FormerPasswords
DROP TABLE IF EXISTS dbo.Users

GO

CREATE TABLE dbo.Users
    (
    UsersID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UserName NVARCHAR(200) NOT NULL
    , CurrentPassword VARCHAR(64) NOT NULL
    )

CREATE TABLE dbo.FormerPasswords
    (
    FormerPasswords INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UsersID INT NOT NULL REFERENCES dbo.Users (UsersID)
    , PriorPasswords VARCHAR(64) NOT NULL
    , DateLastUsed_UTC DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME())
    )

GO

CREATE OR ALTER TRIGGER trgUsers
    ON dbo.Users
    AFTER UPDATE
AS
BEGIN

    INSERT INTO dbo.FormerPasswords
    (UsersID, PriorPasswords)
    SELECT D.UsersID
        , D.CurrentPassword
    FROM DELETED AS D

END

GO

INSERT INTO dbo.Users
(UserName, CurrentPassword)
VALUES ('jsmith', 'original 1')
    , ('jdoe', 'original 2')

UPDATE dbo.Users
SET CurrentPassword = 'updated 1'
WHERE UserName = 'jsmith'

SELECT * FROM dbo.Users
SELECT * FROM dbo.FormerPasswords


Oracle SQL: How to give user the permissions to SELECT from a table? I'm not very familiar with Oracle SQL at all, but using our admin account I ran the following query on our Oracle database server: GRANT SELECT ON Table1 TO OtherUser. I then committed this change (not sure if you need to even commit this?)

I'm using Oracle SQL Developer to connect to our Oracle database and after I ran the above GRANT statement from our admin account, I would've thought I would see Table1 listed under the Table objects node when I connect as OtherUser, but I do not see it and cannot select from it when I try.

Is there any other permissions I need to grant, or anything else I need to do that I missed?

Edit: When I query DBA_TAB_PRIVS I do see Table1 listed with the SELECT privilege for grantee OtherUser, even though from OtherUser's perspective I can't see Table1 under the Tables node in Oracle SQL Developer.

Objects owned by other users are not listed under your objects in SQL Developer.

Expand Other Users, Owner (username), Tables, and you will find the table there.


SQL Server 2008 Authentication mode changed on its own This morning we experienced an issue where our production server seemed to decide to change from Mixed authentication mode to Windows only authentication of its own volition. I was curious to know if any of you other DBAs out there had ever experienced this or might be able to point me in a good direction to try and make sure this doesn't happen to us again. The only other thing that seemed to be going on database wise, beyond typical use, was that the transaction log had just completed a backup.

The chances that the Authentication Mode changed on it's own is pretty near zero. More likely, it got changed by accident.

This is a change that requires a restart of the SQL Server service in order for it to take effect. What likely happened is that the Authentication Mode was changed sometime since the last restart (which could have been months ago)--then, when the instance restarted, the change "suddenly" took effect seemingly on it's own.

This particular configuration is stored in the registry, so if you have OS backups of your server, you could do some research to go back in time to try to determine when it changed.


Oracle DB | Issue with agent and OMS connection We performed a "P2V" migration to a local VMware host last night to get an old physical Oracle server over to a new virtual home (we covered all the licensing aspects, we're OK there).  Prior to starting the virtualization process we brought all services and instances offline.  Coming up on the virtual side went very smooth, with everything being operational expect an error we're seeing in Enterprise Manager.  The error we're seeing is:

  Agent is unable to communicate with the OMS. (REASON = Agent is
  Unreachable (REASON : Agent to OMS Communication is broken OMS
  platform services are not available)

We've done the normal troubleshooting, and running EMCTL STATUS AGENT returns:

  emctl status agent
  Oracle Enterprise Manager Cloud Control 13c Release 1 Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
  --------------------------------------------------------------- Agent Version          : 13.1.0.0.0 OMS Version            : 13.1.0.0.0
  Protocol Version       : 12.1.0.1.0 Agent Home             :
  /oracle/product/agent/agent_inst Agent Log Directory    :
  /oracle/product/agent/agent_inst/sysman/log Agent Binaries         :
  /oracle/product/agent/agent_13.1.0.0.0 Core JAR Location      :
  /oracle/product/agent/agent_13.1.0.0.0/jlib Agent Process ID       :
  32622 Parent Process ID      : 32476 Agent URL              :
  https://xxxx:3872/emd/main/ Local Agent URL in NAT :
  https://xxxx:3872/emd/main/ Repository URL         :
  https://xxxx:4903/empbs/upload Started at             : 2020-01-25
  09:52:01 Started by user        : oracle Operating System       :
  Linux version 3.0.101-0.46-default (amd64) Number of Targets      : 75
  Last Reload            : 2020-01-25 10:29:21 Last successful upload
  : 2020-01-25 10:59:32 Last attempted upload                        :
  2020-01-25 10:59:32 Total Megabytes of XML files uploaded so far :
  10.25 Number of XML files pending upload           : 0 Size of XML files pending upload(MB)         : 0 Available disk space on upload
  filesystem    : 46.30% Collection Status                            :
  Collections enabled Heartbeat Status                             : Ok
  Last attempted heartbeat to OMS              : 2020-01-25 10:59:25
  Last successful heartbeat to OMS             : 2020-01-25 10:59:25
  Next scheduled heartbeat to OMS              : 2020-01-25 11:00:25

We're at a loss here.  Any advice on how to fix this?  Thanks in advance.

As they say, time heals all wounds.  After investigating for around two hours, the issue cleared on its own.


Why is it faster to create index and run query than run query without it? I have table with few million rows. It contains logs from external service, so I've decided not to index it (lot of inserts, sparse reads).
When I run query that reads from the table without index, it takes (unsurprisingly) very long time.
However, when I create index and run the query and then drop the index, it is considerably faster (even with creating and dropping index).
Why is it faster to create index ad hoc, instead of letting SQL Server do it's thing? It seems unintuitive (Why wouldn't SQL Server create the index itself?).
Are there any downsides to this approach?

The query in question looks something like this, but I do not think it is necessarily relevant, as I've seen similar behavior elsewhere as well.
    UPDATE Device
    SET Col1 = l.Col1
        ,Col2 = l.Col2
        ,Col3 = l.Col3
    FROM dbo.Device
        OUTER APPLY (
            SELECT MAX(Id) AS [Id]
            FROM dbo.Logs
            WHERE Logs.Device_FK = Device.Id
            GROUP BY Logs.Device_FK
        ) lastLog
        OUTER APPLY (
            SELECT Col1, Col2, FORMAT(Col3) AS "Col3"
            FROM dbo.Logs
            WHERE Logs.Id = lastLog.Id
        ) l

The reason is the you repeatedly scan the Logs table. And you even have two cross apply in that query. The repeated scanning of this table is evidently more expensive than building the index and then using that index.
Nothing strange or unexpected here.
SQL Server could possibly do an index spool so it can used that for each visit to the Logs table. Perhaps the optimizer evaluated that strategy and discarded it since its estimates showed that it wouldn't be beneficial (incorrectly, perhaps). One very first step would be to study the execution plan, the estimates compared to actual values and take it from there.


Spatial Loader DB2 giving error while importing shape file While I am trying to import a shapefile using db2se import_shape file utility,

db2se import_shape <dbname> -userId <userid> -pw <password> -fileName U:\ShapeFiles\ShapeFilesMarch2014\SubLimits\Africa_Criteria_Data\AF-CIV.shp -inputAttrColumns N(FIPS_CNTRY) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\AF-CIV.txt -client 1

GSE0206N  A SELECT statement failed.  SQLERROR = "SQL0805N  Package    "DB2GSE.GSEIEIMP 0X4141414141644459" was not found.  SQLSTATE=51002".

Any idea why this error happens? I tried in all forums and not able to debug it.

I used db2level to check the client version and found that the client version was the mismatch.

Not working client version
--------------------------
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09070" with
level identifier "08010107". Informational tokens are "DB2 v9.7.0.441", "s090521", "NT3297", and Fix Pack "0".

Working Client version
----------------------
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09075" with
level identifier "08060107". Informational tokens are "DB2 v9.7.500.702", "s111017", "IP23286", and Fix Pack "5".


Complex dto based result sets with EF and MS SQL server ok lets say I have a collection of these ...

public class ReferenceLookup
{
    public string InvoiceNumber { get; set; }
    public CompanyReference Buyer { get; set; }
    public CompanyReference Supplier { get; set; }
}

I need a collection of these as results where any of the pieces exist ...

public class ReferenceLookupResult
{
    public SIHead SIHead { get; set; }
    public Company Buyer { get; set; }
    public Company Supplier { get; set; }
}

SIHead == invoice

Companies have related tables that contain external references for the company
I somehow (if possible) want to write a single linq query that can do this in a way that exposes me a queryable set of  ReferenceLookupResult if possible,
how I would get these pieces is quite complex though

for example here's how I currently get a collection of companies by reference ...

public IQueryable<Company> ByReferences(CompanyReference[] refs)
{
    var refStrings = refs.Select(r => r.System + "|" + r.Ident);
    return GetAll().Where(c => c.CompanyTaxIdentifiers.Any(cti => refStrings.Contains(cti.ExternalReferenceSystem.DisplayName + "|" + cti.Reference)));
}

... i think i need multiple collections of companies and a collection of invoices where one from each set makes up 1 result dto

here's how I get invoices by reference ...

public IQueryable<SIHead> GetAllByRefs(ICollection<InvoiceReference> refs)
{
    var keys = GetAll().Select(i => new { Id = i.SIHeadId, Number = i.InvoiceNumber, Supplier = i.SupplierCompanyAux.CompanyId }).ToList();
    var resultItemIds = keys.Where(k => refs.Any(r => r.InvoiceNumber == k.Number && r.SupplierId == k.Supplier)).Select(k => k.Id).ToArray();
    return GetAll(resultItemIds);
}

an invoice reference is an invoice number and a supplier reference
all invoices are at their root generated by suppliers.

Options considered so far

Create a sproc and call that (not queryable though I would think)
Create a view and add that to the context (EF Code First treats this like a normal entity set)

I'm thinking the latter might be the way to go, but in the past each time I've had a view for something I've ended up replacing it longer term with something LINQ based.

Using a view to produce the complex result set seems to be the best option in my case of the options presented in the question however ...

I found that with a slight architectural change in the business process I wouldn't end up in this situation at all, resulting 2 or 3 simpler requests rather than 1 complex one requiring a "special case" like this to be handled in the call handling logic.


Advice on T-SQL script, change to use dynamic SQL The below script will query Ola's Maintenance Solution results table (CommandLog) and come up with a comparison on the bi-weekly reindex exec duration, for the last 4 weeks.

The way it looks right now, it's a quick hack to help me tune the maintenance windows. But I would like to remove the date hardcoding, so that I don't have to manually add every future weekend as a new JOIN.

Please provide a complete rewrite (dynamic SQL?) or just some hints on how to achieve this or some other useful features to be included. Ok to change the sproc and add extra output to the table. Using SQL 2016. If there's already a script out there that serves the purpose, happy to go with that one.

WITH t0 AS
(
SELECT ObjectName, IndexName, IndexType
FROM Tools.dbo.CommandLog
WHERE 1=1
AND DatabaseName = 'testdb'
AND CommandType = 'ALTER_INDEX'
GROUP BY ObjectName, IndexName, IndexType
)

SELECT
 t0.ObjectName
,t0.IndexName
,t0.IndexType
,DATEDIFF(ss,t1.StartTime,t1.EndTime) as '20-40 01-06'
,DATEDIFF(ss,t2.StartTime,t2.EndTime) as '5-40 01-07'
,DATEDIFF(ss,t3.StartTime,t3.EndTime) as '20-40 01-13'
,DATEDIFF(ss,t4.StartTime,t4.EndTime) as '5-40 01-14'
,DATEDIFF(ss,t5.StartTime,t5.EndTime) as '20-40 01-20'
,DATEDIFF(ss,t6.StartTime,t6.EndTime) as '5-40 01-21'
,DATEDIFF(ss,t7.StartTime,t7.EndTime) as '20-40 01-27'
,DATEDIFF(ss,t8.StartTime,t8.EndTime) as '5-40 01-28'
FROM t0
LEFT JOIN Tools.dbo.CommandLog as t1 ON t0.ObjectName = t1.ObjectName AND t0.IndexName = t1.IndexName AND t1.StartTime BETWEEN '2018-01-06 00:30:00' AND '2018-01-06 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t2 ON t0.ObjectName = t2.ObjectName AND t0.IndexName = t2.IndexName AND t2.StartTime BETWEEN '2018-01-07 00:30:00' AND '2018-01-07 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t3 ON t0.ObjectName = t3.ObjectName AND t0.IndexName = t3.IndexName AND t3.StartTime BETWEEN '2018-01-13 00:30:00' AND '2018-01-13 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t4 ON t0.ObjectName = t4.ObjectName AND t0.IndexName = t4.IndexName AND t4.StartTime BETWEEN '2018-01-14 00:30:00' AND '2018-01-14 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t5 ON t0.ObjectName = t5.ObjectName AND t0.IndexName = t5.IndexName AND t5.StartTime BETWEEN '2018-01-20 00:30:00' AND '2018-01-20 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t6 ON t0.ObjectName = t6.ObjectName AND t0.IndexName = t6.IndexName AND t6.StartTime BETWEEN '2018-01-21 00:30:00' AND '2018-01-21 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t7 ON t0.ObjectName = t7.ObjectName AND t0.IndexName = t7.IndexName AND t7.StartTime BETWEEN '2018-01-27 00:30:00' AND '2018-01-27 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t8 ON t0.ObjectName = t8.ObjectName AND t0.IndexName = t8.IndexName AND t8.StartTime BETWEEN '2018-01-28 00:30:00' AND '2018-01-28 23:59:59'
WHERE 1=1
ORDER BY
 t0.ObjectName
,t0.IndexName
,t0.IndexType

Calendar table

As LowlyDBA and Anti-weakpasswords have pointed out in their comments you can find more than one method to generate a calendar table:

Bones of SQL - The Calendar Table
Calendar Tables in T-SQL
Creating a date dimension or calendar table in SQL Server

For the sake of the question I'll generate a (basic) calendar table just to show you how to use it with your data, you'll find examples on-line that use more detailed fields (seconds, day of year, quarters, etc)

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date,
                                 DAY(@date),
                                 DATEPART(weekday, @date),
                                 DATENAME(weekday, @date));

    SET @date = DATEADD(day, 1, @date);
END

Accordingly with your query, it seems that you're interested on get results for Saturday and Sunday only. In this case you should filter Calendar table by day name or by day of week. Have a look at SET DATEFIRST if you want to filter by day of week.

SELECT * FROM Calendar WHERE cDayName IN ('Saturday', 'Sunday');
GO

cDate               | cDay | cDayOfWeek | cDayName
:------------------ | ---: | ---------: | :-------
06/01/2018 00:00:00 |    6 |          7 | Saturday
07/01/2018 00:00:00 |    7 |          1 | Sunday
13/01/2018 00:00:00 |   13 |          7 | Saturday
14/01/2018 00:00:00 |   14 |          1 | Sunday
20/01/2018 00:00:00 |   20 |          7 | Saturday
21/01/2018 00:00:00 |   21 |          1 | Sunday
27/01/2018 00:00:00 |   27 |          7 | Saturday
28/01/2018 00:00:00 |   28 |          1 | Sunday

Populating CommandLog table with random data

Now let me simulate a CommandLog table and populate it with 1000 random dates between Jan-01 and Jan-31.

CREATE TABLE CommandLog
(
    ObjectName varchar(10),
    IndexName varchar(10),
    IndexType int,
    StartTime datetime,
    EndTime datetime
);

DECLARE @step int = 1;
DECLARE @startDate datetime;

WHILE @step <= 1000
BEGIN
    SET @startDate = DATEADD(minute, RAND() * 59,
                             DATEADD(hour, RAND() * 23,
                                     DATEADD(day, RAND() * 31, '20180101')))

    INSERT INTO CommandLog VALUES('OBJ1',
                                  'INDEX1',
                                  1,
                                  @startDate,
                                  DATEADD(second, RAND() * 59,@startDate));
    SET @step += 1;
END

/* just to check first 10 records */
SELECT TOP 10 * FROM CommandLog;
GO

ObjectName | IndexName | IndexType | StartTime           | EndTime
:--------- | :-------- | --------: | :------------------ | :------------------
OBJ1       | INDEX1    |         1 | 16/01/2018 04:36:00 | 16/01/2018 04:36:43
OBJ1       | INDEX1    |         1 | 06/01/2018 09:11:00 | 06/01/2018 09:11:33
OBJ1       | INDEX1    |         1 | 05/01/2018 14:23:00 | 05/01/2018 14:23:43
OBJ1       | INDEX1    |         1 | 10/01/2018 19:53:00 | 10/01/2018 19:53:11
OBJ1       | INDEX1    |         1 | 14/01/2018 14:31:00 | 14/01/2018 14:31:53
OBJ1       | INDEX1    |         1 | 06/01/2018 18:43:00 | 06/01/2018 18:43:07
OBJ1       | INDEX1    |         1 | 21/01/2018 21:52:00 | 21/01/2018 21:52:41
OBJ1       | INDEX1    |         1 | 28/01/2018 06:51:00 | 28/01/2018 06:51:03
OBJ1       | INDEX1    |         1 | 19/01/2018 08:39:00 | 19/01/2018 08:39:58
OBJ1       | INDEX1    |         1 | 30/01/2018 19:57:00 | 30/01/2018 19:57:50

Formatting data

Ok, so far so good, I've chosen a PIVOT solution in this case, but first I need to format source data to make it possible. Records can be filtered or grouped using the Calendar table.

ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)

/* check first 10 records again */
SELECT     TOP 10
           cl.ObjectName, cl.IndexName, cl.IndexType, cDayName,
           CASE cDayName
                WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
           END FormatDate,
           DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GO

ObjectName | IndexName | IndexType | cDayName | FormatDate    | DiffSeconds
:--------- | :-------- | --------: | :------- | :------------ | ----------:
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           7
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          25
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          40
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |           0
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          33
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          13
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          52
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          50
OBJ1       | INDEX1    |         1 | Saturday | [20-40 06-01] |          15

PIVOT query

Well, this is the PIVOT query I'm trying to simulate. Keep in mind that PIVOT queries require a well-know number of columns and you often must generate it dynamically.

SELECT ObjectName, IndexName, IndexType,
      [20-40 06-01],[5-40 07-01],[20-40 13-01],[5-40 14-01],
      [20-40 20-01],[5-40 21-01],[20-40 27-01],[5-40 28-01]
FROM
(
    SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
               CASE cDayName WHEN 'Saturday' THEN '20-40 ' + FORMAT(cDate, 'dd-MM')
                             WHEN 'Sunday'   THEN '5-40 ' + FORMAT(cDate, 'dd-MM')
                             END FormatDate,
               DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
    FROM       CommandLog cl
    INNER JOIN Calendar c
    ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
    AND        cl.StartTime < DATEADD(day, 1, c.cDate)
    WHERE      cDayName IN ('Saturday', 'Sunday')
)src
PIVOT
(
    SUM(DiffSeconds) FOR FormatDate IN ([20-40 06-01],[5-40 07-01],[20-40 13-01],
                                        [5-40 14-01],[20-40 20-01],[5-40 21-01],
                                        [20-40 27-01],[5-40 28-01])
) pvt;
GO

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

Generating dynamic query

You can use STUFF function to dynamically generate columns names. And then simply concatenate strings.

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ',' +
                   CASE cDayName
                     WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                     WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                   END
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =
    'SELECT ObjectName, IndexName, IndexType, ' + @cols
 + ' FROM
     (
       SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
                  CASE cDayName
                       WHEN ''Saturday'' THEN ''20-40 '' + FORMAT(cDate, ''dd-MM'')
                       WHEN ''Sunday''   THEN ''5-40 '' + FORMAT(cDate, ''dd-MM'')
                  END FormatDate,
                  DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
       FROM       CommandLog cl
       INNER JOIN Calendar c
       ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
       AND        cl.StartTime < DATEADD(day, 1, c.cDate)
       WHERE      cDayName IN (''Saturday'', ''Sunday'')
     )src
    PIVOT
    (
      SUM(DiffSeconds) FOR FormatDate IN (' + @cols + ')'
 + ') pvt';

EXEC sp_executesql @cmd;
GO

Final result

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

GROUP BY solution

Ok, it's Saturday afternoon and I have nothing else to do than read some interesting book. Having a look at your query I think you could replace all this table joins by a single GROUP BY + SUM(CASE... query using the Calendar table.

Similar to this one:

SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
           SUM(CASE WHEN cDay = 6 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 06-01],
           SUM(CASE WHEN cDay = 7 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 07-01],
           SUM(CASE WHEN cDay = 13 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 13-01],
           SUM(CASE WHEN cDay = 14 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 14-01],
           SUM(CASE WHEN cDay = 20 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 20-01],
           SUM(CASE WHEN cDay = 21 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 21-01],
           SUM(CASE WHEN cDay = 27 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 27-01],
           SUM(CASE WHEN cDay = 28 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 28-01]
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType;
GO

ObjectName | IndexName | IndexType | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:--------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1       | INDEX1    |         1 |        1196 |       1480 |         747 |       1010 |        1197 |       1031 |         901 |        605

This query can be generated dynamically too:

DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ', ' + ('SUM(CASE WHEN cDay = '
                                 + FORMAT(cDay, 'D')
                                 + ' THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS '
                                 + CASE cDayName
                                        WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                                        WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                                   END
                                 + ' ')
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =   'SELECT     cl.ObjectName, cl.IndexName, cl.IndexType, '
           + @cols
           + 'FROM       CommandLog cl
              INNER JOIN Calendar c
              ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
              AND        cl.StartTime < DATEADD(day, 1, c.cDate)
              WHERE      cDayName IN (''Saturday'', ''Sunday'')
              GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType';

/* REMOVE Warning: Null value is eliminated by an aggregate or other SET operation. */
SET ANSI_WARNINGS OFF;

EXEC sp_executesql @cmd;
GO

dbfiddle here


Removing apostrophe in result set create view ENTRP_SERVS.V_IRS_BULK as
Select
  Atr.Ssn_Tx
,Names.First_Na
,Names.Middle_Na
,Names.Last_Na
,Reff.Ref_Cd_Desc_Tx Suffix_Na
,MBR.PRSN_MBRSH_ID,
T_PRSN_MBRSH_TAX.FILTER_TYPE_CD

 From Hix.T_Enrt En Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                                                   where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID

Left Outer Join

(SELECT (CASE WHEN FILLING_STATUS_CD IN (2061,2062,2063) OR  (FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='Y') THEN 'PRIMARY'
             WHEN FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='N' THEN 'SPOUSE'
             WHEN FILLING_STATUS_CD IN (2066, 2068) THEN 'DEPENDENT'
             ELSE NULL
             END) AS FILTER_TYPE_CD,PRSN_MBRSH_ID FROM HIX.T_PRSN_MBRSH_TAX  WHERE TAX_FL_YR_NB=YEAR(CURRENT DATE)-2)T_PRSN_MBRSH_TAX
    ON PE.PRSN_MBRSH_ID=T_PRSN_MBRSH_TAX.PRSN_MBRSH_ID WHERE T_PRSN_MBRSH_TAX.FILTER_TYPE_CD IS NOT NULL

I have this view and I want to remove the apostrophe (') from the FIRST_NA, MIDDLE_NA and LAST_NA columns. How can I do that?

Use the REPLACE function, e.g.

select replace(yourfield,'''','') from yourtable where ...

Here we are replacing a single quote character with an empty string. Since SQL character literals are themselves enclosed in single quotes, we escape the single quote character in the literal by doubling it.

More information about the use of the REPLACE function can be found in the manual, as is often the case.


Do I need to enumerate the columns used in a materialized view when creating the MV log? I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from various tables. One of our fact tables contains about 15 columns that will be used in a materialized view.

Is it necessary to enumerate all of the columns in the base table that will be required in the MV when creating the MV Log?

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A
  WITH ROWID, PRIMARY KEY, SEQUENCE (COL1, COL2, COL3, ..., COL48)
  INCLUDING NEW VALUES;

The above is an example of how I'm currently creating the MV Log. I'll admit that the above is a result of trial and error, without a thorough understanding of how each component in the statement works (I understand most).

Under what circumstances is it necessary for me to define which columns should be included in the MV log?

Some materialized views will just be a join across multiple tables. Others will include aggregations, groupings, sums, etc.

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log.  In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed.

The concept of a materialized view log is to store the rowid or primary key of the data that changed.  The refresh can then look up the entire record from the table.  Adding specific columns to the WITH clause of the log explicitly records the data in the materialized view log itself.  If your materialized view query is filtering on these columns or joining on them it could speed up the refresh.

Theoretically if all the columns used in the MV are in the log, then it should be able to refresh without referencing the table.  The documentation does not indicate that this is being done.  It would be interesting to trace this to see.  Even if it does, the additional storage requirements may not make this route worth the trouble.

You should probably just create the materialized view logs without specifying a column list, like this:

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A
   WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

More Information:

SQL Snippets
Oracle® Database SQL Language Reference
Oracle® Database Data Warehousing Guide


Possible benefits of storing multiple values in one field of one row instead of as separate rows During our last weekly meeting, a person that has no background experience in Database Administration brought up this question:

  "Would there be a scenario that justifies storing data in-line (string) instead of several lines?"

Let us assume a table called countryStates where we want to store the states of a country; I'll use USA for this example and will not list all the States for the sake of laziness.

There we would have two columns; one called Country and the other called States. As discussed here, and proposed by @srutzky's answer, the PK will be the code defined by ISO 3166-1 alpha-3.

Our table would look like this:

+---------+-----------------------+-------------------------------------------------------+
| Country | States                | StateName                                             |
+---------+-----------------------+-------------------------------------------------------+
| USA     | AL, CA, FL,OH, NY, WY | Alabama, California, Florida, Ohio, New York, Wyoming |
+---------+-----------------------+-------------------------------------------------------+

When asking this same question to a friend developer, he said that from the data traffic size point of view, this might be useful, but not if we need to manipulate this data. In this case there would have to be an intelligence on the application code which could transform this string in a list (let's say that the software that has access to this table needs to create a combo box).

We concluded that this model is not very useful, but I got suspicious that there might be a way to make this useful.

What I'd like to ask is if any of you already saw, heard or done something like this in a way that really works.

To start with, the current Question title referring to "storing data as string instead of columns" is a little confusing. When speaking of storing data as strings instead of something else, that usually refers to serializing everything to a string format instead of a proper / strong datatype (e.g. INT or DATETIME). But if asking about storing data as multiple values in a single field as opposed to separate rows, that is a bit different. And to be fair, while concatenating values is most easily done with strings, it can also be done with INT and BINARY types as well, either by bit-masking or similarly reserving certain positions to have different meanings. Since the second interpretation is what is actually being asked about, based on the text of the Question, let's address that.

In a word: No. If you are storing actual data points then it will only bring pain (in terms of code and performance) as it is unnecessary complication. If it is a value that will only ever be stored as a single unit, updated as a single unit, and never disassembled within the database, then that could be ok as it is roughly analogous to storing an image or PDF. Otherwise, any attempt to parse the data will invalidate using any indexes (e.g. using LIKE '%something%', or CHARINDEX, or PATINDEX, or SUBSTRING, etc).

If you need to store separate values in a single field of a single row then there are more appropriate means of doing that: XML or JSON. These are parseable formats (XML / JSON) and XML can even be indexed. But ideally this data would be stored in properly-typed fields so that it can be truly useful.

And please do not forgot that the purpose of an RDBMS is to store data such that it can be retrieved and manipulated as efficiently as possible, within the constraints imposed by being ACID-compliant. Retrieving concatenated values is bad enough due to the need to parse the values first, and that is not indexable. But manipulating often means replacing the entire blob just to update a part of it (assuming that no pattern exists to use with a REPLACE function). The XML datatype at least allows for XML DML for simplistic updates, though those are still not as fast as a simple update of properly modeled data.

Also, given a scenario such as what is shown in the Question above, by concatenating all of the StateCodes together, you would be unable to Foreign Key (in either direction) those values.

And what if the business requirements change over time and you need to track additional properties of these items? In terms of "states", what about the capitals, or population, or a sort-order, or anything else? Stored properly as rows you can add more columns for additional properties. Sure, you can have multiple levels of parsable data, such as |StateCode,Capital,Population
|StateCode,Capital,Populate|... but hopefully anyone can see the problem growing exponentially out of control. Of course, this particular issue is rather easily dealt with the XML and JSON formats, and that is their value as mentioned above. But you would still need a very good reason for using either of those as an initial means of modeling as neither will ever be as efficient as using discrete fields in separate rows.


pgAdmin 9.5 not showing all databases I have a problem with my database. I installed PostgreSQL 9.5 on my Ubuntu server. I changed the postgresql.conf file to allow binding the PostgreSQL server to the localhost. This allows me to run pgAdmin and connect to my database by forwarding also the port 5432, where I run my PostgreSQL.

The problem I am experiencing is that I only see the default table 'postgres', but not my newly created one 'games'. I created this table by running postgres=# create database games; with the postgres user connected to the server. I know the database was created, because if I run postgres=# \l+ while connected to the server, I get a list of databases, including my 'games'.

In the second picture is my screenshot of the pgAdmin 9.5 application with all the property values I use to connect to my server. So I manage to connect to the server, but I am unable to see the database 'games'.

I also went a step further and granted all permissions to the postgres user by executing postgres=# grant all privileges on database games to postgres;. You can see in my third screenshot, that the access privileges changed. But this change did not do help me to solve the problem. I am still not able to see 'games' in pgAdmin. I saw someone had a similar problem and run the right click on the server and clicked 'New database'.

This seemed to created a new database, because as you can see from the pgAdmin, the application was able to find the score table I create inside pgAdmin. The reason I believe this is the case is, because running the same SQL connected to the server games=# select * from score; results in ERROR: relation "score" does not exist LINE 1: select * from score;.

Any other ideas where I went wrong. Would appreciate any help I can get.

I can log in to my database. When I run psql games postgres, this will log me into my games database with the postgres user. And this is also how I logged in and executed the select statement:

games=# select * from score;

  ERROR:  relation "score" does not exist
  LINE 1: select * from score;

I went a step further I printed out the tables we have in 'games':

games=# \dt

  No relations found.

I managed to find the problems.

One was I had an installed PostgreSQL server on my machine that I was unaware of. It seems I installed it with my pgAdmin install. So every time I connected to my server, I would establish a connection to my localhost server and not my remote server. I just uninstalled the server and installed only the pgAdmin client.

The second problem I had was that the file /etc/postgresql/9.5/main/pg_hba.conf had to be changed. So I ran:

sudo vi /etc/postgresql/9.5/main/pg_hba.conf

and changed the line

# Database administrative login by Unix domain socket
local    all    postgres    peer

to

# Database administrative login by Unix domain socket
local   all     postgres     md5

Once that was changed, I had two restart the configuration by executing:

sudo /etc/init.d/postgresql reload

I would also point out that it is important to have the postgres user as a unix and DB user with same passwords. I found all this information here.


How to check if several tables exist in database, and how to show it in Mysql I would like to check if several tables exist in a database, and return a easy to understand return, I know you can check if a single table exists by running

show tables like "foo";

but is there something that could check more than one, and could return something that is not a list of tables that exists since there are more than a dozen that needs to be checked.

Perhaps counting the found tables?

Perhaps this is better solution for you:

SELECT COUNT(*) AS tables_found_count
FROM `information_schema`.`tables`
WHERE `TABLE_SCHEMA` = 'database_name' AND
`TABLE_NAME` IN ('table1', 'table2')

It returns the count of found tables. Please list the tables in brackets and specify your database name instead of database_name.