Cannot Connect to Oracle Database running on Windows 8 Hyper V Virtual Machine I have oracle server (standard edition) installed on a guest VM (Windows 8 Hyper V).  I have mapped a host entry to the VM as "brettvm".  I am able to log into the VM and connect to the default instance using sqlplus.  So far so good.

EDIT: Here's the listner.ora file on the server.  I'm starting to suspect the trouble may lie here:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = c:\oracle

The tnsnames.ora file on the server looks like this:

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

The Problem:
When I attempt to connect from the HOST machine, sqlplus prompts for a password and then hangs.  Here is my tnsnames.ora on the host machine:

orcl.brettvm =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Some things I have tried:

Verifed that "brettvm" is in my hosts (pingable and browseable)
Verified connectivity between the host and VM over port 80 (browser / telnet)
Verified connectivity between the host and vm over port 1521 (telnet).  Note that I opened this port up on the VM through windows firewall.
Verified the database is up and running via sqlplus on the host (see above).
Turned off windows firewall on the VM

I'm going to start digging through the installation guide...

Any other advice?  Thanks.

Ultimately, this came down to a networking issue -- configuring the guest VM's network adapter (internal switch) with a static IP (this was the hard part), assigning a hosts file entry for that IP in both the Host and the Guest machines and then altering both tnsnames.ora files (host and guest) and listner.ora (guest) to use the mapped host name.

Then everything just worked.

I've pasted my internal switch config for both the host and guest and the listener and tnsnames.ora files below.

STEPS TO CONFIGURE THE GUEST VM / HOST:

Assuming that you've already created a guest VM, with a Windows Server OS, here are the networking steps...

Using Hyper-V manager, use the virtual switch manager add an "Internal" type switch
Using Hyper-V manager, open the settings dialog for the guest VM (right click on the VM).  Add a network adapter using the internal switch you just created.
Here are my internal switch adapter settings for the host.  Note that there is a baked in assumption here that you're outside the range of IP's that would otherwise appear on your network.  I know that my home network uses this range (192.168.x.x), and I know I'm ok.  Also, I know that my workplace uses 10.x.x.x for wireless, so I'm good there, too.

4 . And here's how I configured the guest VM:

5 . I added hosts file aliases for the guest VM on both the host and the guest:

# internal vm's
192.168.0.120    brettvm

6 . Here's the listner.ora on the guest vm (database server):

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = c:\oracle

7 .  Here's the tnsnames.ora on the guest VM:

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

8 . Finally, here's the tnsnames on the client (host machine):

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = brettvm)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Bon appetit!

--Brett


Cannot update certain rows in innodb tables I found the following on http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlock-detection.html

  When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

My questions are:

What kind of situation is "a single SQL statement is rolled back as a result of an error" referring to? Does autocommit=1 count? Any example?
I could imagine that if this happens, it leaves a lock to the rows affected and make any subsequent update to these rows fail, until a mysql restart or something that clears the lock, is that so?

Thanks for any input!

EDIT

OK I realized that maybe I wasn't asking the right question. Here is my situation:

I'm experiencing a weird locking issue with my innodb tables (MySQL version 5.1.61 shipped with latest Debian Squeeze). The symptom is that after running for a few days, certain rows suddenly cannot be updated. When observing locks with innotop, I can see that every update to those rows is waiting for X lock, but nobody is actually holding the lock, so all those updates wait till timeout. And the symptom could last for a very very long time (actually I haven't seen it to an end). Sometimes a flush table xxx could solve the problem, but other times I had to flush all tables or this wouldn't go away. The only clue I had was that before this happened, there were almost always deadlock about certain stored procedure (wrapped in transaction) and an auto-commit enabled statement in innodb status.

The stored procedure was like:

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET var_is_error = 1;
  DECLARE var_cur CURSOR FOR SELECT items from item_table;
  SELECT some stuff;
  START TRANSACTION;
  REPEAT
    FETCH var_cur INTO item:
      UPDATE stuff set number = number + 1;
      ...MORE SELECTS AND UPDATES;
  Until some condition END REPEAT
  IF var_is_error = 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
END

and the autocommit = 1 statement

UPDATE `inventory` SET locked=IF(locked-2>0,locked-2,0),ordered=ordered+2 WHERE (`sku` = 10103)

According to innodb status, the auto-committed statement was holding the lock. I don't see a "WE ROLLED BACK TRANSACTION XX" normally seen in innodb status after a deadlock and there are just many lines of hex dumps about record locks, I guess more than innodb status could hold.

That's why I posted the original questions (and now I realize that I misunderstood that paragraph). I was actually trying to ask that is there a possibility that somehow row locks could be left behind after transaction rollbacks, that could affect the system for a very long time? Or am I looking at the wrong direction? Anyone else had a similar problem?

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)


Database Table design Help I just want to understand and avoid mistakes ..
I have this input table , so I designed this database to manage it ,
my question is : Is the design of StudentMarks good or bad in Normalization ?
I need help please .

It's not normalized. StudentMarks has one value for homework. That does not sound ideal.

Makes more sense, to me, to have a another table GRADES, or the like. CREATE TABLE grades ( category text, grade float ). Then you can insert into it VALUES ('homework', 100) keep all those grades and then later at query time avg() them.


Change column from NOT LOGGED to LOGGED I have a database DAT1 running on DB2 version 10.5.
I want to enable logging for all CLOB and BLOB columns of my table TAB1.
The table was initially created with those columns as NOT LOGGED but now I need to change it to LOGGED.

I know there is the procedure ALTOBJ

that should be able to change this attribute. However I learned form the documentation that this procedure do the following:

  The procedure backs up the data of the table being altered, then drops the original table and creates a new version using the DDL statement; the final step loads the stored data back into the new table.

I need to modify many tables and the size of each table normally reaches about 10GB or even more. This means that this operation may require a very large amount of free disk space plus the time needed to create the new table and copy back the data.

I'm wondering if this operation can be achieved also with a simple ALTER TABLE or another command which doesn't involve creating or copying new data.

You cannot alter the LOGGED attribute of a LOB column. However, you can alter the table by adding a new LOGGED LOB column, update the new column from the existing column, then drop the original column. It may require less wasted disk space but is unlikely to be faster than using ALTOBJ.


Log implications of copying data from one table to another I need to move 10's of millions of rows from one table to another. Are there different ways I can do this that will minimize impact on log files? Clients sometimes choose Simple Recovery over the default Full. But even with simple recovery, bulk copy operations seem to consume huge amounts of log space. We can suggest a different recovery model if that will help.

INSERT INTO Table2 (<columns>)
SELECT <columns>
FROM Table1
WHERE <condition>;

DELETE FROM Table1
WHERE <condition>;

COMMIT;

Many thanks in advance

Even with Simple Recovery Model, you still have to log every part of the insert in case it fails and you need to rollback. If you are moving all of the rows (or almost all of them) as a one off process you may be better off just renaming the table and then altering the columns to be what you need.

If this is a process that runs over and over again (E.g. an end of month process) then you might want to look at partition switching.

If neither of those work, then you'll probably be better off batching your inserts. Michael Swart has a good blog on how to do that. See also Take Care When Scripting Batches.


What are the disadvantages if indexes are added on all columns? I know, that it is not a good design to set too many indexes, but just for understanding the theory:
What exactly are the disadvantages if you add an index on all columns in a table in MySQL?

Are the queries getting slower if I have indexes on columns, that i
rarely use?
Are the inserts somewhat slower?
Is MySQL able to pick out the best index that speeds up the query?

Insert/Updates/Delete speed will degrade. The index has to be updated on data modification.
I don't know mysql in fine details but it also means more time spent by the query planner to determine the indexes to use.
More disk space.

These are the key disadvantages.


How can I remove a channel from replication slave? Today my question is about MySQL replication cleanup.

I used mysqldump with the --master-info --all-databases tag and restored it to a new host to be used as a replication slave.

After restore, I see some artifacts of slave information from the master. This is the third host in a replication chain.

I issued reset slave for channel 'xxxxx'; which returned Query OK, 0 rows affected (0.00 sec). When I later query using show slave status for channel 'xxxxx';, I still see information for this replication channel appearing.

How can I cleanup this replication channel such that it never accidentally starts, as well as cleanup the output of show slave status \G to only show the intended replication channel?


Do I need a separate server for Quorum? We are working on setting up AlwaysOn Availability Group for SQL Server 2014.

There will be 2 nodes: Primary Replica and Secondary Replica.

The question is, do we need a separate server for Quorum?

All the Windows Servers are in Azure.
As of now nothing is in place.
The vendor stated that they will add a server for maintaining quorums.
All servers for the complete system will be in same domain.

Couple of points of clarification. Nodes and replicas are not the same thing. Nodes refer to the Windows Failover cluster and replicas to AvailabilityGroup instances. So assuming you are running a 2 node WSFC, each hosting a SQL instance in an AvailabilityGroup, then yes, you will need another server to achieve quorum and avoid split brain behavior. The easiest way to achieve this is simply set up a quorum file on another server outside of the cluster, known as Node and File Share majority. Read more on WSFC Quorum Modes.

You don't necessarily need to commission a new server for this.  Any server outside of the server will work.


MASSIVE Identity column value jump in SQL Server 2014 So in the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables. We are unaware of any server blips or attempted bulk operations, but DBAs are looking into logs.
The gaps are not the typical 1,000 or 10,000 seen with server restarts and such.
The gap for Application_NO is 10,410,345 for a table with 2,320 rows
Transaction_Payment_NO jumped an astonishing 1,712,149,313 for a table with 685 records.
Any ideas on what could be causing such large and seemingly arbitrary jumps?

Some possible culprits:

the testing process creates many rows in a transaction, and then rolls back the transaction

This seems like the most likely reason, as you mentioned you're currently doing testing.  Perhaps there are some automated tests being run that make changes to those tables, verify the results, and then roll back the changes.
Identity values are not reused after a transaction has been rolled back, which is what causes you to see large gaps in this scenario.

Someone used the RESEED command

You can manually change the current "next identity" value with this command:
DBCC CHECKIDENT ('dbo.Transaction_Payment_NO', RESEED, 1712149313);

This one seems less likely, because you or one of the admins would have to go out of their way to do it.  Similarly...

Someone inserted those values manually

You can insert whatever you like into an IDENTITY column by first running this statement:
SET IDENTITY_INSERT dbo.Transaction_Payment_NO ON;

Failovers and restarts

You mentioned this in the question, but just for completeness - SQL Server caches identity values in order to improve performance.  But those pre-allocated identity values can be lost if the service restarts or an AG failover occurs.  This leads to more predictable gaps though (10,000 on modern versions of SQL Server).


N prefix before string in Transact-SQL query Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this

SELECT * FROM a_table WHERE a_field LIKE '%а_pattern%'

until I change pattern to N'%а_pattern%'. I never had to add this prefix in the past, so I am curious. a_field is defined as nvarchar(255), but I think the reason is something else.

The following articles have some good information on the question. The short answer is just that there's a type mismatch between the unicode column and non-unicode string literal you're using. From the KB article, it looks like omitting the N prefix might still work in some cases, but it would depend on the code page and collation settings of the database. That might explain the change in behavior, if you were previously having success with the no-prefix method.

https://support.microsoft.com/en-us/kb/239530


Concurrent transactions result in race condition with unique constraint on insert I have a web service (http api) which allows a user to restfully create a resource. After authentication and validation I pass off the data to a Postgres function and allow it to check authorisation and create the records in the database.

I found a bug today when two http requests had been made within the same second which caused this function to be called with identical data twice. There is a clause inside the function which makes a select on a table to see if a value exists, if it does exist then I take the ID and use that on my next operation, if it doesn't then I insert the data, get back the ID and then use that on the next operation. Below is a simple example.

select id into articleId from articles where title = 'my new blog';
if articleId is null then
    insert into articles (title, content) values (_title, _content)
    returning id into articleId;
end if;
-- Continue, using articleId to represent the article for next operations...

As you can probably guess, I got a phantom read on the data where both transactions entered the if articleId is null then block and tried to insert onto the table. One succeeded and the other blew up because of a unique constraint on a field.

I've had a look around at how to defend against this and found a few different options but none seem like they fit our needs for a few reasons and I'm struggling to find any alternatives.

insert ... on conflict do nothing/update... I first looked at the on conflict option which looked good however the only option is to do nothing which then doesn't return the ID of the record that caused the collision, and do update won't work as it will cause triggers to be fired off when in reality the data hasn't changed. In some instances this isn't a problem but in many cases this might invalidate sessions user sessions which isn't something we can do.
set transaction isolation level serializable; this seems like the most attractive answer, however even our test suite can cause read/write dependencies where, like the above, we want to insert if something doesn't exist and return it if it does and carry on with further operations. If we have several transactions pending that run the above code it will cause a read/write dependency error as outlined in the transaction-iso of the Postgres docs.

How should is this sort of concurrent read/write transaction handled?

Neither myself or my team claim to be database experts, let alone Postgres experts but feel like this must be a solved problem, or one people have come across in the past. We're open to any suggestions. If the information provided above is not enough, please comment and I'll add more information as needed.

Try the insert first, with on conflict ... do nothing and returning id.
If the value already exists, you will get no result from this statement, so you have then to execute a select to get the ID.

If two transactions try to do this at the same time, one of them will block on the insert (because the database does not yet know if the other transaction will commit or rollback), and continue only after the other transaction has finished.


Is it OK to Change System Locale after MSSQL2012 installation I have a MS SQL Server 2012 instance preinstalled on a Windows 2012 R2 production server. The Windows locale was English(EN-US), and SQL Server Collation is SQL_Latin1_General_CP1_CI_AS. The system handles all Chinese data with nvarchar fields just fine.

Later for some reason (unrelated to sql server) I may have to change default system locale of the Windows 2012 R2 server to Chinese. Will this cause any undesired side effect on the SQL server instance ?

In general you should be ok. The only area that I am aware of that could be affected is SQLCLR, in which case the following conditions must be true:

(
The LCID of the OS does not match the LCID of the default Collation of the Database where the Assembly is loaded,

OR
The default Collation of the Database where the Assembly is loaded is either a binary Collation, or is any combination of the following: case-sensitive, accent-insensitive, width-sensitive, or Kana-type-sensitive
),

AND
custom SQLCLR code is being used
SqlString is being used as an input parameter type
The SqlString parameter is being concatenated inline with a string while not using either the Value property or the ToString() method (i.e. "some string" + SqlStringInputParam;)
Or, possibly using Regular Expressions / Regex.

To be clear, this issue relates to the default Collation of the Database in which the Assembly is loaded, not the Instance- / Server- level Collation, nor even the Collation of any string column or any string literal or variable with a Collation set via the COLLATE keyword.

If your Database has a default Collation of SQL_Latin1_General_CP1_CI_AS because that is what the Instance-level Collation is, then neither # 1 nor # 2 above are true. And in that case, IF there is any SQLCLR code in that Database that matches items 4 - 6 above, then you wouldn't see any problem. BUT, changing the Locale of the OS would make item # 1 above true, in which case if there is any SQLCLR code that matches item 4 - 6 above, then that code will start to throw "Collation mismatch" exceptions.

Admittedly there is a fairly low probability of running into this. But, it has happened, so it's best to be aware of it.


Is UNIQUE(), INDEX() different from UNIQUE INDEX()? Is there any different how mysql treats

UNIQUE (column1, column2),
INDEX (column1)

and

UNIQUE INDEX (column1, column2)

and

UNIQUE (column1, column2)

In the latter, I think mysql will automatically creates index for UNIQUE to avoid duplicate records. Am I right?

UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error

INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does allow duplicate (column1, column2) pairs


Does having a big table that I do not query cause the whole DB to be slower? I have a log table that I use for logging files sent to clients.
I'm not querying that table, just use it for logging (INSERT) and when I need to check when something was sent to clients, usually once a week, maybe less.

Does having this table with BLOB field affects my DB performance? I'm putting PDF files in that table.

In my experience, a big table should not slow down the whole database. it can slow down the queries on same table though but that too will depend on the structure of table and indexes.

You dont have to store the PDF in database, I'd recommend that you eliminate the BLOB column and make it a CHAR. then store the PDF files on disk and their names into database. Here is how I'd have done this. My table would be:

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name CHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX USING BTREE (file_name)
) ENGINE = INNODB;

and then create a SHA256 sum of PDF file and use it as filename.

Some obvious benefits:

This table is a fixed width table (See this), hence faster in all ways.
file_name column is a SHA256 hash of file which is always 64 characters in length and unique
You have option to save PDF files in any directory on disk and reconstruct the path from this data, for example if you store the files at /home/username/html/storage/log_reports/pdf and categorise them by month or week of creation you have the created_at timestamp which you can use.

so splitting the data by days of month in a given year is

/home/username/html/storage/log_reports/pdf
    |
    |-- 2014
    |    |
    |    |-- May
    |        |
    |        |-- 5th
    |             |-- 4355a46b19d348dc2f57c046f8ef63d4538ebb936000f3c9ee954a27460dd865.pdf
    |             |-- 53c234e5e8472b6ac51c1ae1cab3fe06fad053beb8ebfd8977b010655bfdd3c3.pdf

and so on.

Example case:

CREATE TABLE logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_name CHAR(64) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX USING BTREE (file_name)
) ENGINE = INNODB;

Insert some random data

INSERT INTO
  logs (file_name, created_at)
VALUES
  ('4355a46b19d348dc2f57c046f8ef63d4538ebb936000f3c9ee954a27460dd865', TIMESTAMP('2014-5-10')),
  ('53c234e5e8472b6ac51c1ae1cab3fe06fad053beb8ebfd8977b010655bfdd3c3', TIMESTAMP('2014-5-9')),
  ('1121cfccd5913f0a63fec40a6ffd44ea64f9dc135c66634ba001d10bcf4302a2', TIMESTAMP('2014-5-5')),
  ('7de1555df0c2700329e815b93b32c571c3ea54dc967b89e81ab73b9972b72d1d', TIMESTAMP('2014-5-1'));

Recreate the path to file:

SELECT CONCAT(DATE_FORMAT(created_at, '%Y/%D/%M/'), file_name, '.pdf') from logs where id = 3;

output:

2014/5th/May/1121cfccd5913f0a63fec40a6ffd44ea64f9dc135c66634ba001d10bcf4302a2.pdf

Then you can just prepend the path to storage directory and send the file for download or attachment.

Here is an example fiddle on this


When should I use shared lock over exclusive lock? I know the difference between shared lock and exclusive lock.

But I am not able to figure out a use case when I should go for shared lock instead of exclusive lock. Because using exclusive lock and using normal select query select * from wmployee where id=4; serves the purpose of reading data when exclusive lock is applied.

So in which case should I be using shared lock?
Please could someone explain with a MySQL example statement?

In general you should not worry about such.  This pattern covers most common cases adequately.

BEGIN;
SELECT ... FOR UPDATE; -- touch the rows you _might_ update
do some stuff
UPDATE ...;    -- then update them (or not)
COMMIT;

Note that shared locks get 'promoted' in some circumstances for you.

If you would like to explain your situation we can be more specific.


Composite Primary Key efficiency as a Foreign Key I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the keys in this table as foreign keys.

My question is which approach is more efficient for lookup speeds:

1) Do I create the new table including all 4 columns and reference them all in a foreign key.

or

2) Do I create a new identity column in the Primary Key table and use this as a foreign key in the new table.

This database is expected to hold a very large amount of data, so I have built it up until now with a view to minimising the amount of data held in each table. With this in mind, option 2 would be the best approach since I will save 2 int columns and a datetime column for every row, but I want to avoid increasing the lookup time if unnecessary.

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

As you point out, you'll have a much simpler FK relationship.
A small PK makes for small (and fast) indices.  Your total table space will probably be made less by adding such a column.
If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK.  If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.


computed column as foreign key allow null I have two tables.

TABLE A:

One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them full_id.

Actually its a two parted indetificator, first byte of which is type of identificator. Lets call it type_id It can have values, which are divided in two groups:

group named '0x0f group': [0x01, 0x02, 0x03, 0x04].
group named '0xf0 group': [0x10, 0x20].

The last 3 bytes of full_id is arbitraty number and goes by name actual_id

So, it given that the full_ids are unique.
acutual_ids are also unique, but only in scope of 0x0f group of type_id values: there cant be equal actual_id coupled with any of those type_ids.

On the other hand - actual_ids are not unique in a scope of 0xf0 group of type_ids values and in practice there is plenty of duplicated values of actual_id coupled with those type_ids

in SQL table A goes like so:

CREATE TABLE A (
    full_id INTEGER NOT NULL PRIMARY KEY,
    actual_id  AS (
        CASE
            WHEN full_id & 0x0F000000 <> 0 THEN
                full_id & 0x00FFFFFF
            ELSE
                NULL
        END
    ) PERSISTED
)

TABLE B:

The second table contains only actual_ids which is supposed to have type_id within 0x0f group. This is caused by the data stream, from which the second table is populated and this is something I cant change.

in SQL table B goes like so:

CREATE TABLE B (
    actual_id INTEGER NOT NULL
)

I want to:

I want to apply a foreign key:

ALTER TABLE B WITH CHECK
ADD CONSTRAINT fk_a_actual_id FOREIGN KEY(actual_id) REFERENCES A(actual_id)

But I cant, because actual_id in table A must have unique constraint. And I cant apply unique constraint on calculated column. How can I overcome this situation?

My DBMS is Microsoft SQL Server 2012

You cannot create the contraint on A.actual_id because it may contains NULLs and there is no PK or unique index.
I didn't touch B because you said you cannot change it.

  Here is what I did. It may work for you:

Create A_data where <> 0, Create A_null when = 0 and your Table B

Create Table dbo.A_data (
    full_id INTEGER NOT NULL PRIMARY KEY,
    actual_id  AS (CASE WHEN (full_id & 0x0F000000) <> 0 THEN full_id &  0x00FFFFFF ELSE NULL END) PERSISTED
)
Create Table dbo.A_null (full_id INTEGER NOT NULL PRIMARY KEY)
Create Table dbo.B(actual_id int not null)

I can now create a unique index on A_data and a contraints on B which only contains not null value

Create UNIQUE INDEX idx_A_data On dbo.A_data(actual_id)
Go
ALTER Table B WITH CHECK ADD CONSTRAINT fk_a_actual_id FOREIGN KEY(actual_id) REFERENCES A_data(actual_id)
Go

I then create a view A which look like your table A

Create View A with schemabinding as
    Select full_id, actual_id From dbo.A_data
    union all
    Select full_id, null From dbo.A_null

I finally create a instead of trigger on the view. It will give you the same insert possibilities than your old table A

Create Trigger triggerA on A Instead Of Insert
As
    Insert Into A_data(full_id)
    select full_id from inserted where (full_id & 0x0F000000) <> 0

    Insert Into A_null(full_id)
    select full_id from inserted where (full_id & 0x0F000000) = 0

You may also have to create Instead Of Update and Instead Of Delete triggers if required. It is better to keep them separated (1 trigger for each task: insert, delete, update)

  Test

     insert into A(full_id)values(0),(1),(2),(286331153)

0, 1 and 2 go to A_null because (full_id & 0x0F000000) = 0 and 286331153 goes to A_data

A contains:

286331153 / 1118481
0 / NULL
1 / NULL
2 / NULL


Why does the same query run faster the second time I run it? I noticed when running a SQL query it is faster the second time than the first. So my question is, in Oracle SQL Developer where can you see the statistics that explain this behaviour?

What you are experiencing is called caching. The database doesn't have to go to disk the 2nd time because it can either get the data from its own buffer cache, or the operating system/disk array can also provide the data faster from its own cache.

In order to see whether Oracle fetched the data from disk, or used its cache you can enable autotrace in SQL Developer.

You'll get something like the following:

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
    538  bytes sent via SQL*Net to client
    543  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Physical reads are pulled from disk.

Another factor that makes the initial query slower is the fact that Oracle has to hard parse a query the first time it sees it - this involves finding the optimum (as far as the Oracle optimiser is concerned) execution plan for the query & is quite computationally (CPU) expensive, thus takes time.


MySQL Troubleshooting workflow In a couple of interviews i have had recently, i have been asked to explain my workflow when troubleshooting or resolving an incident with MySQL. Here are ways they have asked the question:

Explain the steps or measures you take to respond to, troubleshoot and resolve a database issue.
Do you use monitoring and graphing tools? Explain in what circumstances you use them?

Personally, if there is an incident, like too many threads or whatever issue is with our database that is having an impact on performance or service (not a db crash), the first thing i do is to check the running processes on that DB using show processlist or using mytop. That normally gives me an idea of the activities going on on that db server. Then i would also look at our xymon monitoring system to see if it detects any issues with that server, e.g., disk issue, Raid issue, memory issue (e.g., swapping), high CPU usage etc. So this is somewhat my workflow when dealing with my first question.

I would like to get some good ideas from other DBAs on their workflows on dealing with database incidents in their production environments. So please help answer my two questions above with your own workflows.

For discovering performance issues and getting started on them, my favorite tool is the SlowLog.  By keeping it turned on and a low value for long_query_time, I can look back in time to see what might have caused a slowdown in the past.  Plus it will point to the specific query (or queries) that should be investigated.

I feel that most non-crash issues will be reflected in some way in the slowlog.

Sometimes there are things that are challenging to investigate.  One Slave was running significantly slower than other Slaves, even though nothing indicated any spike in traffic, etc.  Even the slowlog was more confusing than helpful.  After a while, the problem went away.  Several people were looking into it.  The problem turned out to be the RAID controller 'cycling' its battery, during which it disabled the its "Battery Backed Write Cache".

Monyog and MEB are a tools ($$) that provide a lot of continual monitoring of a lot of metrics.  Then the problem becomes knowing what to look at.

Many times, I have encountered clients that happily make lots of connections to MySQL.  (Eg: Apache with a high value of MaxClients.)  Usually this is not a problem because MySQL finishes things rapidly.  But sometimes MySQL gets overwhelmed.  The cure is (in my opinion) to throttle how many connections the client will make to MySQL.  After only a few dozen active connections, MySQL starts stumbling over itself (locks, mutexes, I/O, CPU, etc).  The solution may be to speed up the queries (see SlowLog), or it may be to throttle the clients.  Threads_running (at the moment) and Max_used_connections (after the fact, but with no time given) are the clues that this scenario may be happening.

In that case, the SlowLog would show slow queries that are normally fast.  And it would show a lot of slow queries at a specific time -- coming from a lot of different connections (clients).

I have a couple hundred formulas (of STATUS and VARIABLES) with recommended ranges.  This comes in handy once in a while.  Some of them say whether caches (Query, Buffer_pool, Key_buffer, Table_open, etc) are being effective or inefficient.  Unfortunately, they rarely say "change xx to yy".

One project had spikes at the start of the hour.  Well, the obvious answer was 'cron'.  Sure enough, there were over a hundred cron jobs, each hitting at the top of the your, and possibly at other times.  Solution:  Instead of

*/5 * * * * do_something.sh

do

(3-59)/5 * * * * do_something.sh

(and change the '3' to various values for different cron jobs.)  MySQL EVENTs can also be a spike-maker.

Many monitoring tools can discover such an hourly spike.  Even an alert human can notice such.  But few, if any, monitoring tools will tell you what to do.

The real problem is that the tools give you clues, but not answers.  That is where a human needs to step in.  That human needs a knowledge of the 'system', the tunables, the queries, the schema, how to create good indexes, etc.


Index creation in extremely slow in Mysql Aurora 5.7 I have a table in mysql Aurora 5.7 version .

The db instance has very good configuration 488 GB ram (db.r4.16xlarge).

Data base is static data base no application is running on this .

I have 1.5 Billions record approx 2TB size in the table .

The table does not have any index except primary key .

Table has 35 Columns .

This table does not have any partition

Now i need to create Index on nine other columns and here i hit hard .
It takes 5 Hours to create one index and when i run command to create all 9 index at same time it took almost 10 hours and then i cancelled .

Is there any way i can make this index creation faster ?
Is creating 10 partition will help creating Index faster ?

Why i need to create Index on 1.5 Billions records is because i have migrated my table from Orcale Source to Mysql using AWS DMS service .

Before migration i need to drop index at target and then have to migrate so that migration will be faster  and it is actually very fast .

Without Index migration took 10 hours where as with Index it is taking 25 Hours and some time it fails as well .

You have a lot of rows., every row has to be inserted into the index, that takes time, live with it.

You can try to use only one index for all columns

CREATE INDEX name
ON yourtable(column1,column2,column3,column4...colmn9);

That takes also long, but should be faster


Get a list of orders by excluding the recents one in the same category? I have a table of the following structure (simplified):

Table orders

id
created
name
executed
category_id

I need a list of n orders from distinct categories (if n is 5, I should have at most 5 different categories, and never two same categories). I successfully done this one using the following request :

SELECT   name
FROM     orders
WHERE    executed IS NULL
GROUP BY category_id LIMIT 5;

But I'd need to exclude the categories that has already been processed less than an hour ago.

For that, I was able to do the following query :

SELECT   name, category_id
FROM     orders
WHERE    executed IS NULL
AND      category_id NOT IN (SELECT   category_id
                             FROM     orders
                             WHERE    executed > (UTC_TIMESTAMP() - INTERVAL 60 MINUTE)
                             GROUP BY category_id)
GROUP BY category_id
LIMIT 5;

But I was wondering if there was a faster way to get the results.

I've made an SQLFiddle to show my example, but I find it quite slow and I was not able to get a successful result from them.

Here's an alternative version with a LEFT JOIN I tried but it doesn't work:

SELECT  u1.some, u1.columns
    FROM  orders AS u1
    LEFT JOIN  orders AS u2  ON u1.category_id = u2.category_id
      AND  u2.executed > (UTC_TIMESTAMP() - INTERVAL 5 SECOND)
    WHERE  u1.created < (UTC_TIMESTAMP() - INTERVAL 60 SECOND)
      AND  (u1.executed IS NULL
              OR  u1.executed < (UTC_DATE() - INTERVAL 1 MONTH)
           )
      AND  u2.category_id IS NOT NULL
      AND  u1.category_id NOT IN u2.category_id
    LIMIT  10;

You can speed up the query by adding an index as follows:

ALTER TABLE `orders` ADD KEY ix_executed(executed,category_id);

Create table orders as follows:

CREATE TABLE `orders` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(250) NOT NULL,
  `executed` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `category_id` tinyint(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_executed` (`executed`,`category_id`)
) ENGINE=InnoDB;

Rest of your query seems good to me.


How to handle huge table We have a problem with a few tables that are very very big slowing down the system. so my question is if that size is normal for the amount of rows in the table, and also what could be done to make it smaller and lighter it shouldn't slow down everything.

CREATE TABLE [dbo].[TransactionEntry](
    [TransactionEntryID] [uniqueidentifier] NOT NULL,
    [TransactionID] [uniqueidentifier] NULL,
    [ItemStoreID] [uniqueidentifier] NULL,
    [Sort] [int] NULL,
    [TransactionEntryType] [int] NOT NULL,
    [Taxable] [bit] NULL,
    [Qty] [decimal](19, 3) NULL,
    [UOMPrice] [money] NULL,
    [UOMType] [money] NULL,
    [UOMQty] [decimal](19, 3) NULL,
    [Total] [money] NULL,
    [RegUnitPrice] [money] NULL,
    [DiscountPerc] [decimal](19, 3) NULL,
    [DiscountAmount] [money] NULL,
    [SaleCode] [nvarchar](50) NULL,
    [PriceExplanation] [nvarchar](50) NULL,
    [ParentTransactionEntry] [uniqueidentifier] NULL,
    [AVGCost] [money] NULL,
    [Cost] [money] NULL,
    [ReturnReason] [int] NULL,
    [Note] [nvarchar](50) NULL,
    [DepartmentID] [uniqueidentifier] NULL,
    [DiscountOnTotal] [decimal](19, 3) NULL,
    [Status] [smallint] NULL,
    [DateCreated] [datetime] NULL,
    [UserCreated] [uniqueidentifier] NULL,
    [DateModified] [datetime] NULL,
    [UserModified] [uniqueidentifier] NULL,
    [TotalAfterDiscount] [decimal](18, 3) NULL,
    [TaxID] [uniqueidentifier] NULL,
    [TaxRate] [decimal](18, 4) NULL,
 CONSTRAINT [PK_TransactionEntry] PRIMARY KEY CLUSTERED
(
    [TransactionEntryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

That is not a large table, SQL-Server should be able to handle that easily assuming you are running on server-grade hardware. If your hardware is severely underpowered then nothing we tell you can solve the problem entirely.

You may have performance issues relating to using GUIDs. Your PK is clustered and is a GUID, this means it is continually moving rows around on insert to keep the clustered index correct. If you are not using newsequentialid for the default value of your GUIDs, then you have a problem right there. Of course joins are faster using int keys but it is probably too late to completely redesign around that.

You may need to experiment with indexes - you want ones that will work with your queries but too many will slow down inserts considerably.

When was the last time you updated statistics or reindexed the table?

You could look at partitioning the table by date. That might speed things up. If you don't have Enterprise Edition and the right hardware to partition, you could archive old records and only use them for reporting by unioning the active records in the main table with the inactive records in the archive table. Then you would have many fewer records to handle for the main bulk of your work.

But clearly what you need most urgently is a good solid DBA who knows how to performance tune. Someone who won't let you make design mistakes in the future and who has the skill and ability to refactor your system to make it work. Performance tuning is not the sort of thing you can learn by asking questions on a board like this. It is a complex subject that takes a good amount of expertise learned over years and by working with many databases. If you have no DBA, then hire one. For later projects, you probably should hire one at design time; databases are hard to refactor and they need to be designed for performance from the start.


changing from inner joins to left joins to include null values? I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date in the range). Do I need to change the cross join to an outer join as well?

SELECT EmployeeNumber,
   JobCode,
   [Date],
   SUM(Hours) as [Hours]
FROM
(
SELECT        tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]

FROM          AdjustUserRoleRequestStatusLookup
CROSS JOIN    tServiceJobCodes
INNER JOIN    tServiceHours
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode
INNER JOIN    tServiceReports
ON            tServiceHours.ReportNo = tServiceReports.ReportNo
INNER JOIN    tServiceReps
ON            tservicereports.employee = tservicereps.repid
INNER JOIN    tRegions
ON            tServiceReports.Region = tRegions.RegionCode

WHERE         Date between '2017-01-01' and '2017-01-05'

GROUP BY      tServiceReps.EmployeeNumber,
              tServiceHours.JobCode,
              tServiceReports.[Date],
              tServiceHours.[Hours]
) ctHours
GROUP BY EmployeeNumber, JobCode, [Date];

Output for inner select query

EmployeeNumber  JobCode      Date               Hours
10203             82    2017-01-03 00:00:00.000 3.00
10203             82    2017-01-04 00:00:00.000 3.50
10203             82    2017-01-05 00:00:00.000 3.00
10203             86    2017-01-02 00:00:00.000 8.00
10203            210    2017-01-03 00:00:00.000 5.00
10203            210    2017-01-05 00:00:00.000 5.00
10203            215    2017-01-04 00:00:00.000 4.50

@DominiqueBoucher  i understand your confusion. this was built by non-dba admins. servicehours and servicereports both contain the ReportNo column. servicehours also contains the jobcode column.

The query works where there is data to report against, the issue is, I need to see Null where no data exist.

an example of this, would be if I pull from 2018/01/05, no records should exist, but I need to see:

 employee#     jobcode      date             Hours
 12345                1              01/05/2018        0
 12345                2              01/05/2018        0

As I don't have the table definition and values Inside them, here's the code I used to do something "similar".

You can use it as inspiration... You may be able to adapt it to your situation.

 /*------------------------------------------------------------------ */
--create table tservicehours ([hours] float,jobcode int, reportno int);
--create table tServiceJobCodes (Servicejobcode int);
--create table tServiceReports (ReportNo int, [date] date, employee int);

--insert into tServiceReports values (1, '2017-01-03',10204), (2, '2017-01-04',10203), (3, '2017-01-05',10203), (4, '2017-01-02',10203), (5, '2017-01-03',10203), (6, '2017-01-05',10203), (7, '2017-01-04',10203);
--insert into tServiceJobCodes values (82),(86),(210),(215);
--insert into tservicehours values (3,82,1),(3.5,82,2),(3,82,3),(8,86,4),(5,210,5),(5,210,6),(4.5,215,7);

create table #date ([date] date);
insert into #date values ('2017-01-01'),('2017-01-02'),('2017-01-03'),('2017-01-04'),('2017-01-05');

select distinct #date.[date],tServiceJobCodes.Servicejobcode, tServiceReports.employee  into #temp from #date cross apply tServiceJobCodes cross apply tServiceReports;

with datas as (
select  date, [hours],  jobcode, employee
from tServiceReports r
join tservicehours h on h.reportno = r.reportno)

select distinct #temp.employee, #temp.[date], #temp.servicejobcode, datas.[hours] from #temp
left join datas on jobcode = #temp.Servicejobcode and datas.[date] = #temp.[date] and #temp.employee=datas.employee
where #temp.[date] between '2017-01-01' and '2017-01-05'
order by employee, servicejobcode;

drop table #date;
drop table #temp;
/* ----------------------------------------------------------------- */

The general idea here is to create a temp table containing each day for every jobcode/employee and then use that table to join on the actual data.

I hope it will help.


What is the difference between these 2 queries? We have these 2 queries:

select jsh.jobid, max(jsh.Audit_CreatedDate)
from JobStatusHistories jsh
join Jobs j on j.JobId = jsh.JobId
where j.JobStatusId = 11
and jsh.Audit_CreatedBy <> 'Archiver'
and jsh.Audit_CreatedDate < '5/9/2015'
group by jsh.jobid

and

select jsh.jobid
from JobStatusHistories jsh
join Jobs j on j.JobId = jsh.JobId
where j.JobStatusId = 11
and jsh.Audit_CreatedBy <> 'Archiver'
group by jsh.jobid
having MAX(jsh.Audit_CreatedDate) < '5/9/2015'

The first returns 5.3 million records and the second returns 4.3 million.

The goal is to get a list of jobids where the job status is 11 and has been modified over 5 months ago (old) but they may have been modified recently by the Archiver, so we want to ignore those.

Simplified data might look like:

JobId    Audit_CreatedDate    Audit_CreatedBy
234      3/25/2015            Importer
234      9/25/2015            Archiver
456      8/25/2015            Importer
456      9/25/2015            Archiver
789      3/25/2015            Importer
789      4/25/2015            Archiver

We would expect to get back:

JobId    Audit_CreatedDate
234      3/25/2015
789      3/25/2015

Unfortunately, both queries return so many results, it's difficult to see which is doing what we want.

The difference is that the first query will include a job like this:

jobId    Audit_CreatedDate
-------  -----------------
1        2015-04-01
1        2015-06-01

The first row would be included in the query, but the second row won't be included. This is because at least one row from that job met the where clause of < May 9th (or is that September 5th? Please use non-regional, unambiguous date literals).

This same job would be excluded from the second query altogether, since the filter is effectively applied on the aggregate, not on individual rows. MAX(date) = June 1st in that case, and so the job is thrown out as a whole, rather than just the rows > May 9.

Which query is right? Who knows? You'll need to give more details about your actual requirements - your current word problem is not explicit enough for me to understand. Not that any user or client could consume 4.3 or 5.3 million rows anyway.


Generate Schema and Data to SQL script and Run it on another DB programmatically I can manually generate a schema with data via
Table Name > Tasks > Generate Scripts...

But it is possible to create a schema+data single sql file backup of a database and run it on another database programmatically ?

Maybe some SQL Script which will be scheduled by Job or by another approach, i dont know.

Thank you

It is possible to generate DDL and Data from SQL Server Management Studio (SSMS):

Object Explorer > yourServer > yourDatabase > right-click > Tasks > Generate Scripts ...

Within the 'Advanced' options change 'Types of data to script' to 'Schema and Data.

Also, here is a simple example of using Powershell to script data for tables I did a few years ago:

Example: Scripting Data with SMO and Powershell

So it's possible, and you will find many Powershell examples for scripting tables.  However backup and restore would be much more straightforward!  If the tables are empty, then it's inconsequential if you move them over right?


How to delete stored procedures from a database project and have that reflect on the server? I have a Visual Studio database project with several stored procedures. I deleted one of them and then published the project. But the SP was still present on the published database and I had to delete manually.

I would want the VS project to be the point of truth for the database. But at the same time, I'm publishing to a production database that has data.

Is there a way to delete files in a VS database project so that those changes reflect on the published database?

The easiest way is using "Schema compare", where you would manually select objects to be dropped/ignored/added. to do this:

Right click the database in the solution explorer (choose schema compare)
Connect to your database
Click the 'compare' button
select/deselect differences changes that will be made
click the 'update' button
*note: if you want to NOT block on possible data loss, you need to configure that BEFORE comparing schemas. Do this by clicking the little settings icon

If you wish to DELETE (DROP) database objects in production database, that do not exist in your Database Project, you should successfully Build and Publish the project on production database using default settings.

If you have performed the Build successfully, proceed like this:

  Check to see if this checkbox is deselected:

  In Solution Explorer, right-click your database and select "Publish".
  "Publish Database" window should open
  Click the "Advanced..." button.
  "Advanced Publish Settings" window should open
  Switch to "Drop" tab
  Deselect the checkbox "Do not drop stored procedures".
  Click OK and continue with publish.


Is there way to join every row of TableA to a row of the smaller TableB by repeating TableB however many times are needed? Sorry for the confusing title, I wasn't sure what to write there.

I have a table of a few hundred records. I need to assign each record of this table to a much smaller dynamic table of users, and the users should alternate as to what records they get assigned.

For example, if TableA is

Row_Number()    Id
1               1
2               2
3               3
4               4
5               5
6               6
7               7
8               8
9               9
10              10

and TableB is

Row_Number()    Id
1               1
2               2
3               3

I need an end result set that is

UserId          RecordId
1               1
2               2
3               3
1               4
2               5
3               6
1               7
2               8
3               9
1               10

I've managed to do something a bit messily using the mod operator, but I was curious if this same query could be run without the temp table and the variable.

The temp table is used because TableA is actually a User Defined Function that converts a comma-delimited string to a table, and I need the Count of the objects from the UDF.

-- Converts a comma-delimited string into a table
SELECT Num as [UserId], Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
INTO #tmpTest
FROM dbo.StringToNumSet('2,3,1', ',')

DECLARE @test int
SELECT @test = Count(*) FROM #tmpTest

SELECT *
FROM #tmpTest as T1
INNER JOIN (
    SELECT Top 10 Id, Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
    FROM TableA WITH (NOLOCK)
) as T2 ON T1.RowNo = (T2.RowNo % @test) + 1

Its important that the UserIds alternate too. I cannot assign the top 1/3 of the records to User1, second 1/3 of the records to User2, and 3rd 1/3 of the records to User3.

Also, the UserIds need to maintain the order in which they were originally entered in, which is why I have a Row_Number() OVER (ORDER BY (SELECT 1)) in the User's table

Is there a way of joining these tables in a single query so I won't need to use a temp table and variable?

I'm using SQL Server 2005

Another way to avoid temp tables would be this:

;WITH tmpTest AS
(
    SELECT  Num as [UserId]
            , Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo]
            , COUNT(*) OVER() AS Quant
    FROM dbo.StringToNumSet('2,3,1', ',')
)
SELECT *
FROM tmpTest as T1
INNER JOIN
    (
        SELECT Top 10 Id
            , Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo]
        FROM TableA WITH (NOLOCK)
    ) as T2 ON T1.RowNo = (T2.RowNo % Quant) + 1;


Active Directory domain being shutdown-- Do I have to update my DBO accounts? I manage a SQL Server machine (with SQL Server 2000 and 2005 installed) that was first created on an Active Directory domain that we are transitioning away from and are about to shut down.  All of my databases have File Owner's from the old domain.

New accounts have been created that have the correct permissions to run the SQL Server services and execute all necessary SQL Server processes.  My question is, what benefit is there to updating the associated File's Owner property on each database?

Do I need to assign a new AD account to each database, or can I leave it as it is since there are already accounts setup that have full admin rights for all of my databases?

Yes, you need to change the databse owners to valid principals. There are several features that require the database owner principal to be valid, most notably the EXECUTE AS infrasturcture. You may be using these feature whithouth your knowledge, for example an application may be using SqlDependency which requires Service Broker which uses EXECUTE AS. Rather than face long nights of troubleshooting cryptic error messages, do yourself a favour and change all owners on all databases to sa:

alter authorization on database::<dbname> to [sa];

And no, I did not specify sa by mistake.


Troubleshooting vanishing fulltext index in SQL Server 2012 I am trying to get to the bottom of some weirdness. Yesterday I enabled a fulltext index on a table in SQL Server 2012 running on one of our LAN servers. I used the Wizard and set the index to update automatically. I had put some test data into the table and ran some CONTAINS queries and they worked fine.

Today I added some more data to the table and ran some CONTAINS() queries, and they failed with this error message:

  Cannot use a CONTAINS or FREETEXT predicate on table or indexed view
  'foo' because it is not full-text indexed.

What could make the fulltext index ephemeral? How can I get the status of fulltext indexing in this database, including which tables have been indexed?

When I go back in to the Fulltext Wizard to recreate the indexes, the catalog that I created is still present.

EDIT:  I did confirm that there were data in the table. Test queries were working. Nothing was deleted, but there was indeed some kind of anomaly overnight that caused the server to shut down.  Is the definition of the fulltext index stored in the Master database?

Catalog and index are separate. What do you see in sys.fulltext_indexes? Did you confirm data there after the index was created?

You could check for recent Object:Deleted events in the default trace:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
-- AND StartTime > 'yyyymmdd' -- rough date/time you created the index
ORDER BY StartTime DESC;

Now, your default trace may be very busy with a lot of relevant events, in which case the drop could have rolled off the current set of trace files, but this is a rare scenario.

Another case where it might not show up in the default trace is if the database was restored to an earlier point in time - you may find some evidence of this in SQL Server's error log (though this only keeps so much history too - if you've restarted SQL Server six times, for example, any information from before that is gone).


SELECT/INSERT Deadlock This instance hosts the SharePoint 2007 databases (SP). We have been experiencing numerous SELECT/INSERT deadlocks against one heavily utilized table within SP content database. I have narrowed down the resources involved, both processes are requiring locks on the non-clustered index.  The INSERT needs an IX lock on the SELECT resource and the SELECT needs a S lock on the INSERT resource. The deadlock graph depicts and three resources, 1.) two from the SELECT (producer/consumer parallel threads), and 2.) the INSERT.  I have attached the deadlock graph for your review. Because this is Microsoft code and table structures we cannot make any changes.  However, I have read, on the MSFT SP site, that they recommend setting MAXDOP Instance level configuration option to 1. Since this instance is shared amongst many other databases/applications this setting cannot be disabled.

Therefore, I decided to try and prevent these SELECT statements from going parallel. I know this is not a solution but more a temporary modification to help with troubleshooting. Therfore, I increased the “Cost Threshold for Parallelism” from our standard 25 to 40 upon doing so, even though the workload has not changed (SELECT/INSERT occurring frequently) the deadlocks have disappeared. My question is why?

SPID 356 INSERT has an IX lock on a page belonging to the non-clustered index
SPID 690 SELECT Execution ID 0 has S lock on a page belonging to the same non clustered index

Now

SPID 356 wants an IX lock on SPID 690 resource but cannot ontain it because SPID 356 is being blocked by SPID 690 Execution ID 0 S lock
SPID 690 Execution ID 1 wants a S lock on SPID 356 resource but cannot obtain it because SPID 690 Execution ID 1 is being blocked by SPID 356 and now we have our deadlock.

Execution Plan can be found on my SkyDrive

Full Deadlock Details can be found here

If someone can help me understand why I would really appreciate it.

EventReceivers Table.
Id  uniqueidentifier    no  16
Name    nvarchar    no  512
SiteId  uniqueidentifier    no  16
WebId   uniqueidentifier    no  16
HostId  uniqueidentifier    no  16
HostType    int no  4
ItemId  int no  4
DirName nvarchar    no  512
LeafName    nvarchar    no  256
Type    int no  4
SequenceNumber  int no  4
Assembly    nvarchar    no  512
Class   nvarchar    no  512
Data    nvarchar    no  512
Filter  nvarchar    no  512
SourceId    tContentTypeId  no  512
SourceType  int no  4
Credential  int no  4
ContextType varbinary   no  16
ContextEventType    varbinary   no  16
ContextId   varbinary   no  16
ContextObjectId varbinary   no  16
ContextCollectionId varbinary   no  16

index_name  index_description   index_keys
EventReceivers_ByContextCollectionId    nonclustered located on PRIMARY SiteId, ContextCollectionId
EventReceivers_ByContextObjectId    nonclustered located on PRIMARY SiteId, ContextObjectId
EventReceivers_ById nonclustered, unique located on PRIMARY SiteId, Id
EventReceivers_ByTarget clustered, unique located on PRIMARY    SiteId, WebId, HostId, HostType, Type, ContextCollectionId, ContextObjectId, ContextId, ContextType, ContextEventType, SequenceNumber, Assembly, Class
EventReceivers_IdUnique nonclustered, unique, unique key located on PRIMARY Id

On the face of it, this looks like a classic lookup deadlock.  The essential ingredients for this deadlock pattern are:

a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index

The SELECT accesses the nonclustered index first, then the clustered index.
The INSERT access the clustered index first, then the nonclustered index.  Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course.

In this case, the SELECT query is:

...and the INSERT query is:

Notice the green highlighted non-clustered indexes maintenance.

We would need to see the serial version of the SELECT plan in case it is very different from the parallel version, but as Jonathan Kehayias notes in his guide to Handling Deadlocks, this particular deadlock pattern is very sensitive to timing and internal query execution implementation details.  This type of deadlock often comes and goes without an obvious external reason.

Given access to the system concerned, and suitable permissions, I am certain we could eventually work out exactly why the deadlock occurs with the parallel plan but not the serial (assuming the same general shape).  Potential lines of enquiry include checking for optimized nested loops and/or prefetching - both of which can internally escalate the isolation level to REPEATABLE READ for the duration of the statement.  It is also possible that some feature of parallel index seek range assignment contributes to the issue.  If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting.

The usual solution for this type of deadlocking is to make the index covering, though the number of columns in this case might make that impractical (and besides, we are not supposed to mess with such things on SharePoint, I am told).  Ultimately, the recommendation for serial-only plans when using SharePoint is there for a reason (though not necessarily a good one, when it comes right down to it).  If the change in cost threshold for parallelism fixes the issue for the moment, this is good.  Longer term, I would probably look to separate the workloads, perhaps using Resource Governor so that SharePoint internal queries get the desired MAXDOP 1 behaviour and the other application is able to use parallelism.

The question of exchanges appearing in the deadlock trace seems a red herring to me; simply a consequence of the independent threads owning resources which technically must appear in the tree.  I cannot see anything to suggest that the exchanges themselves are contributing directly to the deadlocking issue.


Why group_concat and SQL_CALC_FOUND_ROWS do not work with each other? I have following query which contain group_concat and SQL_CALC_FOUND_ROWS together as I want result + total no. of records present in it.

SELECT SQL_CALC_FOUND_ROWS GROUP_CONCAT(ca.id)
FROM notification AS ca
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 '
AND  ca.date!=""
AND  ca.alert_msg_id
NOT IN(26,27) AND ca.from_id != '777'

SELECT FOUND_ROWS();

But this does not return the desired result.

Please suggest.

From the question itself, I see two problems

The return value of the query will be, at most, one row. SQL_CALC_FOUND_ROWS is uesless
You are looking for the number of rows that piled up in the GROUP_CONCAT result

You have to extrapolate the row count from the result of the group_concat. Here is how

If there is a return value from the GROUP_CONCAT, set @found_rows = 1 + the number of commas in the return value.
If no value is returned for the GROUP_CONCAT, set @found_rows = 0

BTW there is a heads you need to be aware of: GROUP_CONCAT's max length is 1024 by default. You should extend it to 10M.

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(ca.id)
INTO @IDList
FROM notification AS ca
LEFT JOIN alert_msg AS an ON an.id=ca.alert_msg_id
WHERE ca.mem_id='777 '
AND  ca.date!=""
AND  ca.alert_msg_id
NOT IN(26,27) AND ca.from_id != '777'

SET @found_rows = IF(
    LOCATE(',',IFNULL(@IDList,''))=0,0,
    LENGTH(@IDList) - LENGTH(REPLACE(@IDList,',','')) + 1
);

SELECT @found_rows,@IDList;

Give it a Try !!!


Help with this query dealing with aggregates I have a database that stores information about artists, bands and albums and I'm trying to select the band names and album titles for bands with more than 1 album.

band_id, name,            year_formed
1,       blink182,        1990
2,       fall out boy,    1997
3,       u2,              1982
4,       coldplay,        1990
5,       zach brown band, 2002

and album

album_id, title,                          band_id
1,        take off your pants and jacket, 1
2,        stars,                          3
3,        fbgm,                           3
4,        reach,                          3
5,        yup,                            4

This doesn't seem too hard of a problem but I cannot figure it out for the life of me.
The problem seems to be with getting both the album title and band name from the select statement.

SELECT b.name, alb.title
FROM band as b, album as alb
WHERE exists (SELECT * FROM album as al WHERE b.bid = alb.bid)

Returns the all band names and their album titles. My guess is to have the subquery handle finding the bands with more than one album but I just am at a complete loss for how to do this. Subqueries have been throwing me for a loop in general, so any nice description of how they work conceptually would be appreciated.

First your query is missing a join on the two tables involved. After that, it can be slightly altered to get you the albums of bands with more than one album, like this:

SELECT b.name, alb.title                     -- show me all bands
FROM band AS b                               -- and their albums
  JOIN album AS alb
    ON alb.band_id = b.band_id
WHERE EXISTS                                 -- where exists at least one
      ( SELECT *
        FROM album AS al                     -- other album
        WHERE al.band_id = alb.band_id       -- of the same band
          AND al.album_id <> alb.album_id    -- (this ensures the "other")
      ) ;


teradata database unable to increase database size in permanent space Anyone with teradata database experience? I'm new to this database and I've been tasked to increase the size of a database. I am using Teradata Administrator to do this. I am using Tools > Modify Database then increased the size in Perm space.

I'm getting the following error message when trying to modify the database to increase the size:

3541: The request to assign new PERMANENT space is invalid

My understanding so far is that key is to have enough space in DBC.
The database that I am using is a child database of the parent (DBC).

I can see from running queries that I should have enough size to increase my database since DBC is big enough.

Example:

select sum(currentperm) c1, sum (maxperm) c2, c2-c1 from dbc.diskspace;

Results:

currentperm         maxperm                 c2-c1
293,696,239,616.00  2,382,684,039,413.76    2,088,987,799,797.76
273.525938          2219.047434             1945.521496

This tells me I have 1.9TB available in DBC. Am I reading this correctly?
I have plenty of space. So why can't I increase my db?

Your query and the results don't match, but you're probably right.

This will return the actual available space (modify it accordingly for TBs or 1024**3):

SELECT DatabaseName,
   CAST(SUM(    MaxPerm                  / 1000**3) AS DEC(9,2)) AS MaxPermGB,
   CAST(SUM(CurrentPerm                  / 1000**3) AS DEC(9,2)) AS CurrentPermGB,
   CAST(MAX(CurrentPerm) * (HASHAMP()+1) / 1000**3  AS DEC(9,2)) AS SkewedPermGB, -- due to skewed tables
   MaxPermGB - SkewedPermGB AS AvailablePermGB -- this space can be assigned to other databases
FROM dbc.DiskSpaceV
WHERE MaxPerm > 0
GROUP BY 1
ORDER BY AvailablePermGB DESC

MODIFY DATABASE will only work if enough perm is available in the parent/immediate owner. If this database is not in the 2nd level of the hierarchy dbc is not the direct owner and then it's more complicated. Now you better use Tools -> Move Space, but this might fail if you don't have appropriate access rights.


Oracle 11gR2 permit NOLOGGING but ensure non-corrupt backups I'd like to stand up a database which offers a single tablespace for NOLOGGING operations (all other perm tablespaces set with FORCE_LOGGING=YES).  A conventional weekend full-backup + daily incremental should guarantee that database is recoverable to time-of-failure, except NOLOGGING tables in FORCE_LOGGING=NO tablespace.  What I'd like to guarantee is that all NOLOGGING tables are recoverable to daily incremental backup (with applications responsible for re-playing any ETL processing between backup and failure).

However, I understand that if NOLOGGING DML operation is running during incremental backup, the backup will have corrupt blocks - and entire NOLOGGING table (or partition thereof) may need to be truncated after recovery.  I'd like to avoid this, but without application & dba scheduling / coordination.

To avoid NOLOGGING operations from running during backup, my idea is that the backup script (full and incremental) is wrapped with FORCE_LOGGING On/Off commands:
1) ALTER DATABASE FORCE LOGGING;
2) Take backup
3) ALTER DATABASE NO FORCE LOGGING;

Questions:
Q1: What will happen if a NOLOGGING DML operation is running when backup script starts and FORCE_LOGGING=YES runs?  Will DML proceed (and backup get NOLOGGING corruption on affected tables)?  Will ALTER DATABASE wait for NOLOGGING transaction to commit (while preventing others from starting)?  Will NOLOGGING DML operation quietly rollback and restart in LOGGING mode?
Q2: For recovery itself, is there special syntax to say, "Restore tablespace A, B, and C to time-of-failure, but only recover tablespace D to last backup?"
Q3: Can NOLOGGING tables be restored to incremental backup, or only full level-0 backups?

The first principle to remember is, do not make a backup when a NOLOGGING operation is occurring. Oracle Database does not currently enforce this rule, so DBAs must schedule the backup jobs and the ETL jobs such that the NOLOGGING operations do not overlap with backup operations.

The above excerpt is from Oracle Documentation.

  Q1: What will happen if a NOLOGGING DML operation is running when backup script starts and FORCE_LOGGING=YES runs? Will DML proceed (and backup get NOLOGGING corruption on affected tables)? Will ALTER DATABASE wait for NOLOGGING transaction to commit (while preventing others from starting)? Will NOLOGGING DML operation quietly rollback and restart in LOGGING mode?

The FORCE_LOGGING=YES will be applied for subsequent operations only not for running operation.

  Q2: For recovery itself, is there special syntax to say, "Restore tablespace A, B, and C to time-of-failure, but only recover tablespace D to last backup?"

No, However you may use Tablespace Point-in-Time Recovery (TSPITR) for tablespace A,B and C.

  Q3: Can NOLOGGING tables be restored to incremental backup, or only full level-0 backups?

Yes(But not tables, for tables you can use Flashback). After restoring full/level 0 backup Oracle will apply incremental backups.

For details about NOLOGGING and Backup Strategy:
Plan for NOLOGGING Operations in Your Backup/Recovery Strategy


Best way to setup master to multi master replication ORIGINAL QUESTION:
I have multiple servers that all need to act as masters, as if one fails the next server kicks in and takes over. I'm just wondering what the best way to replicate is. All servers are in different data centers.

Questions

Would it be ok to set up every server as a master-master with the 'supermaster'?
What sort of conflicts would I have?
Are there better ways to do this?

UPDATED QUESTION:
We have servers all around the world in different data centers and each server needs access to a db usually this is on localhost.

Each server needs to be able to update the database and each server is pretty much a mirror of every other server. These all stay in sync via one server(supermaster) that all can communicate with.

All the servers only communicates with supermaster, they are not aware of any other server. The super master it self is a mirror of the other servers, just with the added service of syncing them all.

Some times the supermaster is offline for various reasons; when this happens the other servers carry on as normal which includes reading and writing data. When the supermaster comes back up, it starts the syncing process and sorts out and resolves conflicts between the other servers, when this is complete all servers have a mirror of the data and are all 'synced'.

So after thinking a bit more on the question, My questions is;

Is there any way to have a single db on each server that are the same
across the board, without having a single point where I can update
the db as in a Star Topology?
Is there a better option for replicating mysql across the board for my situation?

Any help is much appreciated.

There are four(4) options you need to combine
Option 1: MySQL in Star Topology
So as not to reinvent the wheel, please read my past posts about this subject

Mysql Master-Master Replication Topologies on >2 machines
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504
https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444

Option 2: Use DRBD

The supermaster should be a DRBD pair and using a DBVIP
The Star Topology's distribution master should replicate from the DRBD primary
All Slaves should replicate from Star Topology's distribution master
See my past post on using DRBD in Circular Replication between two datacenters (DRBD cannot sync well over geographic distance but can provide disk-level + failover capability per data center)

Option 3: Use Semisynchronous Replication
Any network latency can affect the collecting of binary logs and its shipping of entries over a network could be bottleneck. Using MySQL 5.5's Semisynchronous Replication can allow you to tune the MySQL Heartbeat characteristics so as to minimize replication losing its place due to any significant network latency.
Option 4: Circular Replication
If you have multiple masters, circular replication is a must. There are many products out there for providing circular replication, such as mysql-mmm (mentioned in the comment), which is great within the confines of a single datacenter. This is only one piece of your puzzle in that four(4) things must be accommodated outside of just circular replication:

Network latency
Geogrpahic Distance
Disk-level Redundancy per DataCenter (handled by DRBD)
Automatic Failover of DBVIP backbone of DRBD (via ucarp)

CAVEAT
This may not be your full answer but these options can provide ideas you can try out in different scenarios. For example, when in the star topology, you could do the following:
SCENARIO #1

Have the DB Server at one data center be SuperMaster holding InnoDB data
Have the Distribution Master in the same data center (binary logs only)
Have all Slaves replicate from the Distribution Master

SCENARIO #2

Have the DB Server at one data center be SuperMaster holding no data, which means the supermaster is the Distribution Master (binary logs only)
Have all Slaves replicate from the Distribution Master

SCENARIO #3

Setup a Distribution Master in each Data Center in Circular Replication
Within each Data Center, have MySQL Instance (Local SuperMaster) with InnoDB as the only Storage Engine
From each Local SuperMaster, hang two or more read slaves. Each read slave should use MyISAM as the main Storage Engine. Each MyISAM table should have ROW_FORMAT=FIXED to increase read speed 20-30%

As far as the possibilities go, it's up to your imagination and department budget.
Links for MySQL and DRBD

http://dev.mysql.com/doc/mysql-ha-scalability/en/ha-drbd.html
http://downloads.mysql.com/docs/mysql-ha-drbd-en.a4.pdf

DRBD for Disk Level Redundancy and ucarp for DBVIP Automatic Failover

http://www.drbd.org
http://www.ucarp.org


Which is the best tool to monitor replication on MySQL 5.5.20? I have just installed and setup replication for 3 MySQL 5.5.20 server( 1 Master/ 2 slaves). I intend to add more slaves to the equation.

Which is the best software tool to monitor the replication status ( like SHOW SLAVE STATUS \G;)

Also i want to know, how much is the replication lagging in time and error reports if there is any error in replication.

Thanks in advance

Checkout Percona toolkit. Look at pt-heartbeat, and some of their other tools as well.


Select the MAX(time) grouping by vid and find the corresponding Id.

select t1.id, t1.vid, t1.data, t1.time
from yourTable t1
     join (select vid, max(time) as time
           from yourTable
           group by vid) t2
     on t1.vid = t2.vid
     and t1.time = t2.time;

If you need WHERE or ORDER BY:

select t1.id, t1.vid, t1.data, t1.time
from yourTable t1
     join (select vid, max(time) as time
           from yourTable
           group by vid) t2
     on t1.vid = t2.vid
     and t1.time = t2.time
WHERE t1.SOME_FIELD = SOME_VALUE
ORDER BY t1.SOME_FIELD;


Order by query optimization for multi select data (enum) We previously used to store multi select data (enum) using pipe separated characters, as shown below like this - which made ordering on that enum data very simple.

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ |A|B|C|     ║
║ 2 ║ 2          ║ |B|D|       ║
║ 3 ║ 3          ║ |A|C|D|     ║
║ 4 ║ 4          ║ |A|C|       ║
║ 5 ║ 5          ║ |A|B|D|     ║
╚═══╩════════════╩═════════════╝

This above structure was easier to sort on the stored "Alphabets" values but search was expensive since we had to search using %|A|%.

Now we are using the following structure to store the same above data since searching the data becomes much easier:

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ A           ║
║ 2 ║ 1          ║ B           ║
║ 3 ║ 1          ║ C           ║
║ 4 ║ 2          ║ B           ║
║ 5 ║ 2          ║ D           ║
║ 6 ║ 3          ║ A           ║
║ 7 ║ 3          ║ C           ║
║ 8 ║ 3          ║ D           ║
║ 9 ║ 4          ║ A           ║
║ 10║ 4          ║ C           ║
║ 11║ 5          ║ A           ║
║ 12║ 5          ║ B           ║
║ 13║ 5          ║ D           ║
╚═══╩════════════╩═════════════╝

Searching becomes easy now but when I try to sort the data it gives nightmares.
Can anyone give me a good idea to sort this above new data-structure using sql server so that I get the same sorted results as with the previous data-structure??

Below is an example of how you can return the ordered list using your new structure.  What is missing from the database design is an ID table with one row per ID.  This would then provide a one-to-many relationship in 3NF, and avoid the need to select the distinct ID values from the many side.  You could use that parent table instead of the derived table in this example, which would be more efficient.

CREATE TABLE dbo.Alphabets(
    ID int NOT NULL
    ,Alphabets char(1) NOT NULL
    ,CONSTRAINT PK_Alphabets PRIMARY KEY (ID, Alphabets)
    );

INSERT INTO dbo.Alphabets VALUES
     (1, 'A')
    ,(1, 'B')
    ,(1, 'C')
    ,(2, 'B')
    ,(2, 'D')
    ,(3, 'A')
    ,(3, 'C')
    ,(3, 'D')
    ,(4, 'A')
    ,(4, 'C')
    ,(5, 'A')
    ,(5, 'B')
    ,(5, 'D');

SELECT  ID
        , ( SELECT    '|' + Alphabets
            FROM      dbo.Alphabets AS b
            WHERE     b.ID = a.ID
            ORDER BY  b.Alphabets
        FOR
            XML PATH('')
        ) + '|' AS Alphabets
FROM    ( SELECT DISTINCT
                    ID
            FROM      dbo.Alphabets
        ) AS a
ORDER BY Alphabets DESC;


Is there a way to check when a user was mapped as DB Owner? I have a user that was mapped to a database as DB Owner.

Is there a way to see when this mapping was granted?

Unless you have a DDL trigger (or some monitoring software) capturing that, you are out of luck.


Get dates of the MSSQL Database Transaction Log records I am wondering if there is a way not just to get the transaction log (e.g. using fn_dblog), but also get the date for the start of execution for each of the transactions recorded?

It's okay to get any amount of extra uninteresting data as long as I get the date or timestamp of the transaction execution.

Your question is not very detailed in exactly what you want, but How to read and interpret the SQL Server log has some very good information about interpreting the SQL Server log.

Specifically relating to finding the start of a transaction (from the above post):

  The first think I will do will be to look at the LOP_BEGIN_XACT
  operations. This log record marks the beginning of a transaction and
  is very important in log analysis because is the only log record that
  contains the date and time when the transaction started, and also
  contains the SID of the user that had issued the statement. Here is
  how I query for these operations:

select [Current LSN], [Operation], [Transaction ID], [Parent Transaction ID],
    [Begin Time], [Transaction Name], [Transaction SID]
from fn_dblog(null, null)
where [Operation] = 'LOP_BEGIN_XACT'


Getting data from Postgres through Linked server of SQL I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query
select * from openquery(SALESFORCE,'select closeddate
from case
where closeddate > convert(datetime2, "2020-01-01 00:00:00.0000000")')
OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error: MALFORMED_QUERY:
where closeddate > convert(datetime2, "2020-01-01
^
ERROR at Row:3:Column:28
Bind variables only allowed in Apex code".
Msg 7320, Level 16, State 2, Line 21
Cannot execute the query "select closeddate
from case1
where closeddate > convert(datetime2, "2020-01-01 00:00:00.0000000")" against OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
Completion time: 2021-04-22T03:00:25.5943467-04:00
Where I am getting wrong

I think the problem is datetime2 is not a postgres data type:
convert(datetime2, "2020-01-01 00:00:00.0000000")

The openquery statement exucute in the target database the query between '' so it must be written in the targewt dialect
try this

select * from openquery(SALESFORCE,'select closeddate from case where
closeddate > cast(''2020-01-01'' as date)')


create insertion rule on view returning id I have a table private.products and a view public.products representing that table. And I want to create entries in private.products while inserting into view, it all works right, except that I also what to return the inserted id:

CREATE RULE insert_product
AS ON INSERT TO public.products DO INSTEAD
INSERT INTO private.products (name) VALUES (new.name) RETURNING products.id;
ERROR:  RETURNING list has too few entries

What am I doing wrong?

Without knowing your table and view structure, I created a test case for myself:

CREATE TABLE private_table (
    id serial,
    col1 text
);

CREATE OR REPLACE VIEW public_view AS
    SELECT id, col1
      FROM private;

CREATE OR REPLACE RULE insert_private AS
    ON INSERT TO public_view DO INSTEAD
        INSERT INTO private_table (col1)
        VALUES (new.col1)
        RETURNING id, col1; -- apparently, this does not work with fewer columns
                            -- or different ordering

So far, everything works fine, and there is an answer for your question: you have to add all columns of the view in the RETURNING clause of the rule.

What this does not achieve, though, is turning the INSERT into the view into something that returns the id:

INSERT INTO public_view (col1)
    VALUES ('blah');

INSERT 0 1
-- no id returned!

This absolutely makes sense, and can be avoided by adding the RETURNING clause to the actual INSERT, as usual:

INSERT INTO public_view (col1)
    VALUES ('blah2')
    RETURNING id;

 id
────
  2
(1 row)

INSERT 0 1


Combine various temporal criteria to filter rows I am trying to query a database table with some filters. I have a form through which a user will create query attributes:

When user will click on search it will pass such values:

 {camera_id: "9879"
, from_date: "2015/05/15"
, to_date: "2016/08/15"
, interval: "120"
, schedule: "{"Monday":["3:0-4:0"],"Tuesday":["1:30-3:30"]
             ,"Wed…":["0:0-3:0"],"Saturday":[],"Sunday":["1:0-3:0"]}"}

interval has one of these names and values:

"All", "0"
"1 Frame Every 1 min", "1"
"1 Frame Every 5 min", "5"
...
"1 Frame Every hour", "60"
...
"1 Frame Every 24 hours", "1440"

For example: "1 Frame every 1 min". If 5 rows in snapshots have been taken within 1 min, the query shall only return the first.

schedule is being sent by using the FullCalendar jquery plugin. Meaning:

Day: Monday,    Time: 03:00 AM to 04:00 AM
Day: Tuesday,   Time: 01:30 AM to 03:30 AM
...

Table snapshots:

  snapshot_id integer
, camera_id   integer
, created_at  timestamp

I want to select rows passing the filters. Temporal filters apply to created_at. I want rows between from_date and to_date, considering the schedule, and only get the first row per interval.

A basic solution to retrieve only the first row per interval would be to

Generate time series using from_date, to_dateand interval with generate_series().
Join to (pre-selected) main table thereby forming 1 group per interval.
Use DISTINCT ON to get the first per group.

Select first row in each GROUP BY group?

For simpler parameter handling I wrapped the query in a plpgsql function:

CREATE OR REPLACE FUNCTION f_snapshots(_camera_id int
                                     , _from_date timestamp
                                     , _to_date   timestamp
                                     , _interval  interval = '0 min')
  RETURNS SETOF snapshots AS
$func$
BEGIN
IF _interval > '0 min' THEN
   RETURN QUERY
   SELECT DISTINCT ON (i) s.*
   FROM  (
      SELECT *
      FROM   snapshots
      WHERE  created_at >= _from_date
      AND    created_at <  _to_date    -- exclude upper bound?
      ) s
   JOIN   generate_series(_from_date
                        , _to_date - _interval  -- adjust upper bound
                        , _interval) i ON s.created_at >= i
                                      AND s.created_at <  i + _interval
   ORDER  BY i, created_at;

ELSE  -- no interval, return all rows
   RETURN QUERY
   SELECT *
   FROM   snapshots
   WHERE  created_at >= _from_date
   AND    created_at <  _to_date
   ORDER  BY created_at;
END IF;
END
$func$  LANGUAGE plpgsql STABLE;

Call:

SELECT * FROM f_snapshots(
        _camera_id := '9879'
      , _from_date := '2015-05-15 00:00'
      , _to_date   := '2016-08-15 00:00'
      , _interval  := '120 min');

I ignored the additional filter schedule. Add that yourself. I would extract all intervals from the json document and add OR'ed WHERE conditions. Either use the SQL OVERLAPS operator or the overlap operator && for range types. More:

2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
Find overlapping date ranges in PostgreSQL

Also consider my answer to your previous question:

Filter created_at and count distinct minutes


Cache statistic data in SQL Server Currently my team develop additional web app to let customers analyze user statistics to theirs portals.

So we have a table with all logins. It contains about 100 millions of rows. One user could return to the portal during the day multiple times.

This table structure something like:

| UserId | LoginDate |

Also we have a hierarchy of customers, portals and countries, it looks like:

Users           | Customers      | Portals         | Countries
FK-> CustomerId
                  FK -> PortalId
                                   FK -> CountryId

Base on user activity we are building daily incremental snapshots to prevent using big login history table. And we store there Total Logins, Unique logins etc. This allows us show nice chart to the end users.

But now we are facing on interesting issue with this. Currently we need implement total Unique login for period. So the user select period, and see this total.

And this cause the story goes on. We can't just sum snapshots total Unique logins, it won't be a real unique. And we can't count each time from history table, because of performance. And I don't have idea how to cache totals.

How about an indexed view?

CREATE VIEW dbo.LoginsByDate
WITH SCHEMABINDING
AS
  SELECT
    UserID,
    LoginDate = CONVERT(DATE, LoginDate),
    LoginCount = COUNT_BIG(*)
  FROM dbo.LoginTable
  GROUP BY UserID, CONVERT(DATE, LoginDate);
GO
CREATE UNIQUE CLUSTERED INDEX IX_LoginsByDate
  ON dbo.LoginsByDate(UserID, LoginDate);
GO

What this does is pre-aggregate the login data for you (you pay the cost at write time), and flattens it into one row per UserID per LoginDate. Depending on how many times a user logs in in a given day, and how often you run these reports, this can be substantially more efficient than calculating those at query time (and much less hassle than running your own nightly report that aggregates the data and stores it somewhere).

Now, to get the total login events by day:

SELECT LoginDate, SUM(LoginCount)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

To get the distinct logins by day:

SELECT LoginDate, COUNT(UserID)
  FROM dbo.LoginsByDate
  GROUP BY LoginDate;

Of course if you're looking for individual user activity, you can do that too, and you can easily aggregate the data to get whatever "total unique" really means to you. If UserID = 1 logs in today 4 times and tomorrow twice, is that 1 total unique (unique per entire time frame) or 2 total uniques (unique per day)?

If the former:

SELECT COUNT(DISTINCT UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd';

If the latter:

SELECT SUM(c) FROM
(
  SELECT LoginDate, c = COUNT(UserID)
  FROM dbo.LoginsByDate
  WHERE LoginDate >= 'yyyymmdd'
    AND LoginDate < 'yyyymmdd'
  GROUP BY LoginDate
) AS x;