How to check values for records from different rows I have a view I want to select those records whose (Letter, pro, record, and education) column values are not null.

For details, I want to check record MI13900454 that his (Letter, pro, record, and education) should not be null. Note: if we select ... from xxx where record_id is not null and letter_id is not null ..... it is not correct because values are not in same row - they are in different rows and columns.

Plaintext table:

|        ID | LETTER_ID | MEDICAL_ID | SICKNESS_ID |  PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID |
|-----------|-----------|------------|-------------|---------|--------|-------------|-----------|--------|
| MI1390454 |           |            |             |         |        |       47584 |           |        |
| MI1390454 |           |            |             | 4673735 |        |             |           |        |
| MI1390033 |           |            |             |  105419 |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |    299080 |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         | 659652 |             |           |        |
| MI1390033 |           |     309609 |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         | 659650 |             |           |        |
| MI1390033 |           |            |             |         | 659654 |             |           |        |
| MI1390033 |           |            |             |         |        |             |           | 161077 |
| MI1390033 |           |     309608 |             |         |        |             |           |        |
| MI1390033 |           |            |             |         | 659651 |             |           |        |
| MI1390454 |           |            |             |         |        |             |    154745 |        |
| MI1390033 |           |            |             |         | 659653 |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |           |        |
| MI1390033 |           |            |             |         |        |             |     94272 |        |
| MI1390033 |           |            |             |         |        |       44917 |           |        |
| MI1390033 |           |            |             |  105418 |        |             |           |        |

I guess that what you would like is not exactly what you are asking for... so, correct me if I missinterpreted you.

Assuming this is your data (I represent it with a table, but it can be a view):

 CREATE TABLE v
 (
     ID varchar(9),
     LETTER_ID int,
     MEDICAL_ID int,
     SICKNESS_ID int,
     PRO_ID int,
     EDU_ID int,
     TRAINING_ID int,
     RECORD_ID int,
     NID int
 ) ;

 INSERT INTO v
     (ID,         LETTER_ID, MEDICAL_ID, SICKNESS_ID, PRO_ID, EDU_ID, TRAINING_ID, RECORD_ID, NID)
 VALUES
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, 47584, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, 4673735, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105419, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', 299080,   NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659652, NULL, NULL, NULL),
     ('MI1390033', NULL,     309609, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659650, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659654, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, 161077),
     ('MI1390033', NULL,     309608, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659651, NULL, NULL, NULL),
     ('MI1390454', NULL,     NULL, NULL, NULL, NULL, NULL, 154745, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, 659653, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, NULL, 94272, NULL),
     ('MI1390033', NULL,     NULL, NULL, NULL, NULL, 44917, NULL, NULL),
     ('MI1390033', NULL,     NULL, NULL, 105418, NULL, NULL, NULL, NULL)
 ;

What you actually want is to flatten these data first.

That is, GROUP all rows with the same ID, and take the only non null value of the other columns (or, if there are more than one, the one value that actually is of interest to you).

I have chosen to do this using the MAX aggregate function. You may want another one, such as MIN, or a MIN || MAX or something like a string_agg or group_concat (which MS-SQL Server doesn't yet provide):

 SELECT
     ID,
     max(LETTER_ID) AS LETTER_ID,
     max(MEDICAL_ID) AS MEDICAL_ID,
     max(SICKNESS_ID) AS SICKNESS_ID,
     max(PRO_ID) AS PRO_ID,
     max(EDU_ID) AS EDU_ID,
     max(TRAINING_ID) AS TRAINING_ID,
     max(RECORD_ID) AS RECORD_ID,
     max(NID) AS NID
 FROM
     v
 GROUP BY
     ID ;

This would be the intermediate result:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID |  PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | ------: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null |  105419 | 659654 |       44917 |     94272 | 161077
 MI1390454 |      null | null       |        null | 4673735 |   null |       47584 |    154745 |   null

At this point, you can select only the rows with (Letter, pro, record, education) not being null:

 SELECT
    *
 FROM
    (SELECT
         ID,
         max(LETTER_ID) AS LETTER_ID,
         max(MEDICAL_ID) AS MEDICAL_ID,
         max(SICKNESS_ID) AS SICKNESS_ID,
         max(PRO_ID) AS PRO_ID,
         max(EDU_ID) AS EDU_ID,
         max(TRAINING_ID) AS TRAINING_ID,
         max(RECORD_ID) AS RECORD_ID,
         max(NID) AS NID
     FROM
         v
     GROUP BY
         ID
    ) AS flatten
 WHERE
        LETTER_ID is not null
    and PRO_ID    is not null
    and RECORD_ID is not null
    and EDU_ID    is not null ;

You will get:

 ID        | LETTER_ID | MEDICAL_ID | SICKNESS_ID | PRO_ID | EDU_ID | TRAINING_ID | RECORD_ID |    NID
 :-------- | --------: | ---------: | ----------: | -----: | -----: | ----------: | --------: | -----:
 MI1390033 |    299080 |     309609 |        null | 105419 | 659654 |       44917 |     94272 | 161077

dbfiddle here

If you're only interested in the ID value, but not the rest, you can use a different approach:

SELECT DISTINCT
    ID
FROM
    v AS v0
WHERE
        EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND LETTER_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND PRO_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND RECORD_ID is not null)
   AND  EXISTS (SELECT * FROM v AS v1 WHERE v1.ID = v0.ID AND EDU_ID is not null) ;

dbfiddle here

WHERE [ThisColumn] IS NOT NULL OR [ThatColumn] IS NOT NULL

Don't expect good performance, because that table design does not adhere to a relational database's normalized forms, other than 1NF.


Unexpected gaps in IDENTITY column I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:

CREATE TABLE [dbo].[PONumbers]
(
  [PONumberPK] [int] IDENTITY(1,1) NOT NULL,
  [NewPONo] [bit] NOT NULL,
  [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)
);

And a stored procedure created using this script:

CREATE PROCEDURE [dbo].[GetPONumber]
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);
    SELECT SCOPE_IDENTITY() AS PONumber;
END

At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.

The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.

See results below:

You can see that the number jumped from 8 to 1002!

Why is this happening?
How do I ensure that numbers aren't skipped like that?
All I need is for SQL to generate numbers that are:

a) Guaranteed unique.
b) increment by the desired amount.

I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does?  Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were actually issued. This is documented here. There is a trace flag that changes this behavior such that every IDENTITY assignment is logged*, preventing those specific gaps (but not gaps from rollbacks or deletes); however, it is important to note that this can be quite costly in terms of performance, so I'm not even going to mention the specific trace flag here.

* (Personally, I think this is a technical problem that could be solved differently, but since I don't write the engine, I can't change that.)

To be clear about how IDENTITY and SEQUENCE work:

Neither is guaranteed to be unique (you need to enforce that at the table level, using a primary key or unique constraint)
Neither is guaranteed to be gapless (any rollback or delete, for example, will produce a gap, this specific problem notwithstanding)

Uniqueness is easy to enforce. Avoiding gaps is not. You need to determine how important it is for you to avoid these gaps (in theory, you should not care about gaps at all, since IDENTITY/SEQUENCE values should be meaningless surrogate keys). If it is very important, then you should not be using either implementation, but rather roll your own serializable sequence generator (see some ideas here, here and here) - just note that it will kill concurrency.

Lots of background on this "problem":

SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry
identity increment is jumping in sql server database
Identity column value suddenly jumps to 1001 in sql server

This is problem of SQL Server. All you can do is reseed the column.

delete the entries with wrong column id.
Reseed the column identity.
And then the next entry has proper ID to it.

Reseed Identity using following sql command :
DBCC CHECKIDENT ('YOUR_TABLE_NAME', RESEED, 9) -- 9 is last correct Id


Restore Error event id 17066 and 3624 while restoring diff backup I am getting the following error while restoring the Diff back up after restoring the Full backup in Norecovery mode.

I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Error:

  Msg 5052, Level 16, State 1, Line 4

  ALTER DATABASE is not permitted while a database is in the Restoring
  state.

  Msg 5069, Level 16, State 1, Line 4

  ALTER DATABASE statement failed.

  Location:     filesystem.cpp:526

  Expression:   newTree.IsInArchive ()

  SPID:         98

  Process ID:   1956

  Msg 3013, Level 16, State 1, Line 22

  RESTORE DATABASE is terminating abnormally.

  Msg 3624, Level 20, State 1, Line 22

  A system assertion check has failed. Check the SQL Server error log
  for details.

  Typically, an assertion failure is caused by a software bug or data
  corruption. To check for database corruption, consider running DBCC
  CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini
  dump will be sent to Microsoft. An update might be available from
  Microsoft in the latest Service Pack or in a QFE from Technical
  Support.

Help me in resolving this issue.

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB

This error can be caused by transient, timing-related errors, or by in-memory or on-disk data corruption.

This message also says that the backup set which you are trying to restore as differential backup is actually not consistent and SQL Server cannot bring database to a logical point if you try to apply this backup. The assertion checks are either SQL Server bugs or something corrupt in database.

You can bring database online and can run regular dbcc checkdb command and post the output

You need to also check your differential backup is consistent and you can do that by below command

restore verifyonly from disk='backup_location\backup.bak'

run above command and paste the result in question

  I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Since database is in recovery mode you cannot run any command on it that is why every command you run on it fails

Lastly can you also add output of

sp_readerrorlog

You can simply restore the Database without any database file.

Try this:

RESTORE DATABASE [yourDatabase] WITH RECOVERY

It won't hurt your database. It will just remove the IN RESTORING-State and you can then run your DBCC CHECKDB and whatever you want.


Search for Arabic text ignoring diacritics, alef hamza differences, and kashida in SQL Server and Oracle Well, I have a table with an Arabic column which may contain variant forms of the same word in multiple rows, for example the word "أسمى" might be in the following forms:

with diacritics: "أَسْمَى";
with changing the last letter of "ى" into "ي" so it would be like "أسمي";
with kashida or "ـ" in some part of the word so it would possibly be "أسمــى";
with variant forms of alef hamza (أ - إ - ا - ء), so it might be "اسمى" or "إسمى";
any combination of the former cases, i.e diacritics and kashida.

I'm looking for a way to store these values in the database (actually I need a solution for SQL Server and another one for Oracle), and to retrieve them regardless of these differences.

I found that I should use an Arabic collation like this Arabic_CI_AI but this collation only helped me in sorting out the problems #1 and #2.

In addition I considered using a fulltext index on the column, but this has its drawbacks and it doesn't provide a full solution.

How can I resolve this?

Not sure but based on my understanding, this is related to the language/character set.

I use Oracle database and I do store arabic values within the database.

The character set I use is UTF8, I have also tried storing the different examples provided for the same word and I see it working fine.

I am sure SQL Server also supports such character set.


Full text search over multiple related tables: indices and performance We have the following database structure

CREATE TABLE objects (
    id       int   PRIMARY KEY,
    name     text,
    address  text
);

CREATE TABLE tasks (
    id int           PRIMARY KEY,
    object_id int    NOT NULL,
    actor_id int     NOT NULL,
    description text
);

CREATE TABLE actors (
    id   int  PRIMARY KEY,
    name text
);

The user enters a whitespace-separated list of words (search terms, basically) and we have to search for tasks, that satisfy the following: the task is a "match" if each search term occurs at least once in concatenation of task's description, the name and address of its associated object and the name of its associated actor.

Now, if we are not concerned about performance, we can just do something like this (given query "foo bar"):

SELECT t.id, t.description
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id
WHERE to_tsvector(concat_ws(' ', t.description, o.name, o.address, a.name)) @@
    plainto_tsquery('foo bar');

Unfortunately, we are concerned about performance. The dataset will, probably, be as follows (and it is expected to grow):

about 10000 objects
about 1000 actors
about 100000 tasks evenly distributed between objects

What I've considered:

Make a denormalized table like this:

CREATE TABLE task_documents (
    id int PRIMARY KEY,
    doc tsvector
)

The field "doc" will contain the concatenation of task's description, associated object's name and address and actor's name. We will have to create an index over this field and it will be used in full text search queries. This table will be updated in update / insert triggers on tasks, actors, objects.

Drawbacks: tons of duplicated data (this one I am not quite concerned about), and updates to the master tables will become unpredictable in terms of number of rows updated (say, you update a name of some object and now suddenly you must update thousands of rows in task_documents).

Honestly, I don't have any more (good) ideas. It is obviously impossible to create an index, spanning 3 tables, so that it will be used for the WHERE clause in the original query.

UPD

Here's a sqlfiddle with DB schema and some data. I had to make it up, because we have no real data at the moment.

Optimization

You're going down the right track.

You either need to

Denormalize
Cache

Caching the results

What you probably want is a MATERIALIZED VIEW. This is easy and works reasonably well.

CREATE MATERIALIZED VIEW foo
AS
SELECT t.id, to_tsvector(concat_ws(' ',a.name, o.address, t.description, a.name)) AS tsv
FROM tasks AS t
INNER JOIN actors AS a ON t.actor_id = a.id
INNER JOIN objects AS o ON t.object_id = o.id
;

Then just

SELECT * FROM foo WHERE tsv @@ plainto_tsquery('foo bar');

Denormalizing the table

This can take a lot of forms, you've got this right though..

Redesign

Searching everything in a fuzzy fashion like this is a losing game. Even this knock off of Dungeon and Dragons meets Yahoo Answers has rules.

It becomes a lot easier to generate a query when you introduce syntax likes [text] for tagging, and is:answer to search just answers, rather than rebuilding Google and normalized indexes.

Are you using the right technology?
No, for example a Solr/Lucene index will do it much better. If you need, you could do that on the database side (user supplied index)

And you have not thought about query and data imperfections, for example return "house", "houe", "gouse" and similar misspellings, let alone synonyms or using  hierarchies of search terms for queries. Soon your users will ask for faceted search...

Going down here is a dangerous and unsustainable route. If you can, you should avoid it.


Insert into select into multiple related tables using INSERT_IDENTITY Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source.
So for every row in DataTable I want a row in Table1 and Table2, and Table2 needs to have the inserted id (PK) from Table1...

If I were to do this...

INSERT INTO Table1 SELECT A, B, C FROM MyTable
INSERT INTO Table2 SELECT IDENTITY_INSERT(), D, E, F FROM MyTable

I'd get the ID of the last inserted record into Table1.

Is a CURSOR or WHILE loop the only ways to do this?

A solution that might work for you is using the OUTPUT clause, which spits out all the inserted rows, so you can re-insert them into a different table. However, this puts limitations on foreign key constraints on Table2, if memory serves.

Anyway, the solution would look something like this:

MERGE INTO Table1 AS t1
USING MyTable ON 1=0 -- always generates "not matched by target"

WHEN NOT MATCHED BY TARGET THEN
    -- INSERT into Table1:
    INSERT (A, B, C) VALUES (t1.A, t1.B, t1.C)

--- .. and INSERT into Table2:
OUTPUT inserted.ID, MyTable.D, MyTable.E, MyTable.F
INTO Table2 (ID, D, E, F);

MERGE, as opposed to the other DML statements, can reference other tables than just inserted and deleted, which is useful for you here.

More: http://sqlsunday.com/2013/08/04/cool-merge-features/

INSERT INTO VouchersOtherDetail (
                                 [VouNo],
                                 [Location],
                                 [VouType],
                                 [VouDate],
                                 [InputDate],
                                 [CrossRefGoodsVouNo],
                                 [Reversed],
                                 [ReversalReference],
                                 [UserID]
                                 )
SELECT
                                [VouNo],
                                [Location],
                                [VouType],
                                [VouDate],
                                [InputDate],
                                [CrossRefGoodsVouNo],
                                [Reversed],
                                [ReversalReference],
                                [UserID]
FROM @InsertTableForVoucherDetail

INSERT INTO VouchersDrCrDetail (
                                [VouID],
                                [AccountCode],
                                [CrossReferAccountCode],
                                [Description],
                                [VouDrAmount],
                                [VouCrAmount],
                                [RunningBalance]
                               )
SELECT  -- IDENT_CURRENT to get the identity of row from previous insert
                                 IDENT_CURRENT('VouchersOtherDetail'),
                                 [AccountCode],
                                 [CrossReferAccountCode],
                                 [Description],
                                 [VouDrAmount],
                                 [VouCrAmount],
                                 [RunningBalance]
FROM @InsertTableForDrAndCR

This thing worked for me, I know its very late reply but may help others. I used IDENT_CURRENT get the identity of row from previous insert, but for me it is always one row.


I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea? Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as the job scheduler in pretty much every case, rather than the native Windows Task Scheduler.

From my perspective, the SQL Agent has a number of advantages over the native Windows Task Scheduler:

Remote (from my workstation) start/stop/monitoring of tasks
Shared schedules (rather than each task on its own)
Multiple steps and control flow
Different types of tasks
Alerts on failure/completion
Can be configured to act as different users
(Moderately) descriptive error messages, rather than just an error code

However, I can't escape the feeling that this is bad practice - the SQL Agent should be reserved for just database-related tasks, and I should leave OS level tasks running in the Windows Task Scheduler, despite my dislike of its usability.

Is it okay to rely on the SQL Agent in this way? If not, should I consider a third-party Windows task scheduler to get some of the functionality I'm looking for?

At my previous job I did exactly this, mostly because the jobs were all run from our central, primary cluster, which was the most visible server. I could see all of our scheduled tasks in one place instead of having to go check command line stuff on a bunch of servers.

While this is largely subjective (and it's going to be hard to derive a "correct" answer in this format), I don't think there's anything inherently wrong with this approach other than it becomes a single point of failure.

That might not be relevant if all of the tasks interact with, or depend on, the database server being up and the SQL Server services running (in our case, it did).

Oh, and you need to add in error handling for cases where the server where the task tries to run is not up - something you wouldn't have to do if the task were set up on that server.

Another option is to create a table for scheduled tasks with a stored procedure to update a message queue table from it.  Then the SQL Server Agent would call the stored procedure every so often to create messages and update the task status.  Other systems would query the message queue table through either a SQL connection or a web API layer as JSON.


Issues in starting MongoDB service I've installed MongoDB 3.0 in RHEL 6. From root user, I'm trying to start the mongo service but it is getting failed and the log file is not getting updated as well.

2015-10-13T09:53:26.133+0530 E NETWORK  [initandlisten] Failed to unlink socket file /tmp/mongodb-27017.sock errno:1 Operation not permitted
2015-10-13T09:53:26.133+0530 I -        [initandlisten] Fatal Assertion 28578
2015-10-13T09:53:26.133+0530 I -        [initandlisten]

***aborting after fassert() failure

The above is the last updated log.

I have another user 'oracle' from which also it is not starting.

I also tried running mongod --repair

It gives:

2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] MongoDB starting : pid=11033 port=27017 dbpath=/data/db 64-bit host=vmrac2
2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2015-10-13T11:02:59.473+0530 I CONTROL  [initandlisten] 2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: You are running on a NUMA machine.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **          We suggest launching mongod like this to avoid performance problems:
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **              numactl --interleave=all mongod [other options]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 16384 processes, 65536 files. Number of processes should be at least 32768 : 0.5 times number of files.
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten]
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] db version v3.0.6
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] git version: 1ef45a23a4c5e3480ac919b28afcba3c615488f2
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] build info: Linux ip-10-67-194-123 2.6.32-220.el6.x86_64 #1 SMP Wed Nov 9 08:03:13 EST 2011 x86_64 BOOST_LIB_VERSION=1_49
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] allocator: tcmalloc
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] options: { repair: true }
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] finished checking dbs
2015-10-13T11:02:59.474+0530 I CONTROL  [initandlisten] now exiting
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2015-10-13T11:02:59.474+0530 I NETWORK  [initandlisten] shutdown: going to close sockets...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: waiting for fs preallocator...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: closing all files...
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] closeAllFiles() finished
2015-10-13T11:02:59.474+0530 I STORAGE  [initandlisten] shutdown: removing fs lock...
2015-10-13T11:02:59.475+0530 I CONTROL  [initandlisten] dbexit:  rc: 0

Things I have tried:

Deleting /tmp/mongodb-27017.sock but it says no file found with that name.
Changing the net.port in the config file to change the .sock file.
Setting unixDomainSocket : enabled: false

run these commands for ubuntu

sudo rm -rf /tmp/mongodb-27017.sock
sudo service mongod start
sudo ps aux | grep mongo
sudo kill


SQL Server Management Studio slow opening new windows After upgrading both SQL Server and Management Studio from 2008 to 2012, Management Studio 2012 is very slow when new query windows and dialog boxes are opened.

Even right clicking on tables is slow. There is typically at least a 5 second delay before I can start doing anything within a window. This happens every time, even if I open the same window twice in a row. Why does this happen and how can I fix it?

Other applications establish connections to the database very fast.

Things I have tried that did not help:

A hit on Google where I'd need to modify my hosts file
Resetting SSMS "user-defined settings"
Updating video drivers, turning off hardware acceleration, disabling DirectX
Disabling the Biometric Authentication Service (I don't have it installed).

My computer should be more than fast enough, and I also have 16GB RAM. My hardware should definitely not be a problem. It looks like SSMS is waiting for something - I can operate other programs smoothly while this is happening.

I don't have the opportunity to install the SQL Server 2012 CU1 update because there seems to be some risk of errors installing hotfixes and I can't risk that right now.

SQL Server Management Studio Startup

When Microsoft's SQL Server Management Studio (SSMS) starts it tries to connect the Certificate Revocation List (CRL) of Microsoft:

http://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl

The underlying .NET components of SSMS are trying to contact the Certificate Revocation List and SSMS is unable to do so. This slows down the overall loading procedure. (15 seconds per certificate apparently)

  Ok so here is what is happening. SSMS has a high percentage of managed code, all of this code is signed when we ship it. At start up (if this setting is checked) the .Net Runtime tries to contact crl.microsoft.com to ensure that the cert is valid(there were some fake certs issued in Microsoft’s name a while back so this is a very valid concern). If there is no internet connection or there is a problem contacting the certificate revocation list server then this will delay SSMS startup.

Reference: FAQ, Why does SSMS take 45s to start up? (MSDN Blog)

  One issue that can cause this problem is that if the server does not have access to the internet, then the .NET framework can’t access the crl.microsoft.com website to verify that the digital signatures used to sign the binaries for managed applications are valid. Each certificate check has a 15 second timeout in the .NET runtime implementation. Depending on what features are installed, this can add up to a minute of startup time for Management Studio.

Reference: SQL Server Management Studio Startup Time (MSDN Blog)

Solutions

You can circumvent part of the issue, by downloading the certificate directly be entering the link into your browser and then importing the certificate to your certificate database

OR -

You can reconfigure your (company's) firewall to allow connections to Microsoft's CRL

OR -

You can reconfigure your personal antivirus/firewall to allow connections to the Microsoft CRL

OR -

You can configure your (company's) firewall to send a timeout faster to your client for requests accessing Microsoft's CRL.

OR -

You can configure IE to no longer "Check publisher's certificate revocation" in the advanced settings.

(See above mentioned blogs 1 and 2 for details)

The cause of this is almost always a missing network drive - e.g. if you have a drive-mapping to your laptop, but your laptop is turned off.

The resolution unfortunately is to turn on your laptop (so the drives reconnect) or to disconnect your drives in Windows Explorer (but then you have to reconnect them later).

See here for more info:
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/64c13a84-3e62-4b0c-a1ce-65fcc78d6787


Column with selective aliases I want to select an alias based on specific column criteria and output the required XML.

Say we have a table named ACCOUNTS:

Name       Age
---------  ---
John Doe   30
Mary Lane  40
Alice Bob  50

Query:

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
SELECT
     CASE WHEN AGE <= 30 THEN 'BUYER' AS [def:GIRL]
          WHEN AGE >=40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH('abc:OilCompany'), TYPE

The output should be:

 <abc:OilCompany>
   <def:GIRL>BUYER</def:GIRL>
   <def:BOY>SELLER</def:BOY>
   <def:BOY>SELLER</def:BOY>
 </abc:OilCompany>

By default the SQL Server does not create a node for NULL values so if you change your query to use two CASE statements and return NULL when there is no match you will get the XML you are looking for.

WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc,
  'uri' AS def
)
SELECT CASE WHEN AGE <= 30 THEN 'BUYER' END AS [def:GIRL],
       CASE WHEN AGE >= 40 THEN 'SELLER' END AS [def:BOY]
FROM ACCOUNTS
FOR XML PATH(''), ROOT('abc:OilCompany'), TYPE;

dbfiddle

;WITH XMLNAMESPACES
(
  'http://www.w3.org/2001/XMLSchema-instance' AS abc, 'uri' AS def
)
cast(replace(cast(SELECT
(
  SELECT 'BUYER'
  FROM
  ACCOUNTS a
  WHERE AGE <=30
  FOR XML PATH ('def:GIRL'),TYPE
) as varchar(1000)),'http://www.w3.org/2001/XMLSchema-instance','') as xml)   ,
( SELECT 'SELLER'
  FROM
  ACCOUNTS
  WHERE AGE  >=40
  FOR XML PATH ('def:BOY'), TYPE
)
FOR XML PATH ('abc:OilCompany'),TYPE


what is the consequence of setting enableQueryTimeouts to FALSE? MySQL Connector-J has a property enableQueryTimeouts which is TRUE by default. However, in the documentation it is mentioned as :

  When enabled, query timeouts set via Statement.setQueryTimeout() use a shared java.util.Timer instance for scheduling. Even if the timeout doesn't expire before the query is processed, there will be memory used by the TimerTask for the given timeout which won't be reclaimed until the time the timeout would have expired if it hadn't been cancelled by the driver. High-load environments might want to consider disabling this functionality

So, I want to disable enableQueryTimeouts to gain high performance. However, if I disable enableQueryTimeouts, then

How a query will timeout?
Will there any consequences ?

Please help

Consequences: if there is a slow query(s) your system will be too slow, hang, or even go out of memory.

if QueryTimeout is disabled  from MySQL or the connector it will stay until its finished (how to enable it from MySQL SET GLOBAL MAX_STATEMENT_TIME=XXX;  -- where XXX=number


Can't deploy SSAS Tabular Model in Visual Studio I am following the SSAS Tabular Model Tutorial by Microsoft using the Adventure Works DW. I've imported the DW in SQL Server Management Studio to my local server. I've connected to the DW from Visual Studio, everything worked fine, completed all the steps of the tutorial until deployment. Building the Model works just fine, however when I try to deploy the Model it throws me the error

  Error Cannot deploy metadata. Reason: An error occurred while connecting to the server. 0

Some other users have also reported the same issue and could solve it with reinstalling SSDT, tried that, did not work for me.

Any ideas would be very much appreciated!

I just found out why I could not deploy my Tabular model to the server:

I tried to deploy it on the SQL Server (where my source DW is located), but obviously the Tabular Model needs to be deployed on the Analysis Server

On the solution properties, identify the server  as a tabularmodel ie ... localhost\tabularmodel. This is what worked for me.


Access Denied error in CREATE DATABASE ... FOR ATTACH I'm attempting to attach a copy of msdb to my workstation SQL Server 2012 Developer Edition on Windows 8.1 in an attempt to mimic the problem detailed in this question where the OP is attempting to recreate missing jobs out of a foreign msdb.

I performed the following actions:

use master;

backup database msdb to disk='C:\sqlserver\test.bak';
go
restore database msdbtest from disk='c:\sqlserver\test.bak'
    with move 'MSDBData' to 'C:\SQLServer\Data\msdbtest_data.mdf'
    , move 'MSDBLog' TO 'C:\SQLServer\Logs\msdbtest_log.ldf';
go

I then stopped the SQL Server Service, made a copy of C:\SQLServer\Data\msdbtest_data.mdf and C:\SQLServer\Logs\msdbtest_log.mdf, and restarted SQL Server.

I then performed the following:

drop database msdbtest;
go
create database msdbtest on (
    name='msdbtest_data'
    ,filename='c:\sqlserver\data\msdbtest_data.mdf'
) log on (
    name='msdbtest_log'
    , filename='c:\sqlserver\log\msdbtest_log.ldf'
) for attach;

This results in:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "c:\sqlserver\data\msdbtest_data.mdf".
Operating system error 5: "5(Access is denied.)".

SQL Server is configured with the default out-of-the box settings for the service account:

Security effective access for the SQL Server service account on c:\sqlserver\data\msdbtest_data.mdf is:

I am at a total loss; I think the error being returned is misleading me.  Perhaps I misunderstand the purpose of CREATE DATABASE ... FOR ATTACH?

As far as I can tell, SQL Server should certainly have access to the file.  The folder C:\SQLServer\Data is used by all the other user databases currently attached to the instance.

SELECT @@VERSION returns:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

When you get this message it can be a few things. Some examples:

1.) Read Only Marked Files

2.) Insufficient permissions to the files for the SQL Server Service account

3.) Insufficient permissions to the folders the files live on.

You indicated in our chat that this was not read only. You also verified permissions looked like full access. That ruled out these three items.

The fact that, from our chat conversation, you were not experiencing this issue when you properly detach a database using sp_detach_db - which actually changes permissions around a bit on the files led me to suggest trying to run SSMS as administrator.

Why? Well this article gets into a lot more nitty gritty about some of the impersonation issues here - but my understanding is:

1.) When you just stop the SQL Server Service and move the file around. The owner of the file is the service account that created it.

2.) When you detach the database - you'll potentially notice a permissions change. The owner has changed basically and the file is not really associated with that SQL Server service account the same way.

3.) So it works when you try to attach because the permissions can get assigned, because the security of the file allows it..

But if you just stop the service the security hasn't changed, and you can't attach that if you have UAC enabled unless you are impersonating administrative access.

I've probably made the file ownership and access more confusing here but the article I shared probably does a better job :-)

But one slight moral is - do a detach next time ;-) Or just be prepared to run SSMS as administrator from time to time when interacting with the OS in various ways.

Stop the SQL Server services and change the "log on as" to Local System

Restart the SQL services and check. It started working fine in my case.


MySql Vs. Postgresql for a blog application I am designing a blog application. Which of these fits better?
In which use cases will mysql be good and for what use cases will postgresql be good?

MySQL's home territory is in CMS.  It will be good enough.  I like PostgreSQL as much as the next guy but a blog will work on either, and MyISAM tables might actually be really nice there.

As for where PostgreSQL does better than MySQL, here is my take. There are two big areas:

Advanced data modelling and
Multi-application databases

The first means that PostgreSQL is far more mature at doing complex data modelling than MySQL is.  It has a very mature extensible type system, a wide range of procedural languages, and a great deal of flexibility in how these languages can be plugged into existing queries.

If that wasn't enough, the fact is that you can essentially build your data model in PostgreSQL based not only on what information you are storing but what information is commonly derived from what you are storing.  This makes things like not-first-normal-form actually sane to use where they are needed.  Add collections and multiple inheritance in table structure and you have a very sophisticated data modelling platform.  For examples, see my recent blog entries.

On to the other side.  MySQL developers tend to see the application as the bottom tier of the application stack, while PostgreSQL developers tend to see the application as the center of the data environment.  Besides the content management system market, MySQL's other major market is in applications where data is not expected to be exposed to more than one writing application at a time.  This leads to a significant difference in handling data validation, etc.

In PostgreSQL validation is always equally strict.  If the app expects special error treatment it had better call functions or casts to handle this explicitly.  For example, if I want to store up to a maximum of 144 characters in a text field, I can:  insert into mytable values (?::varchar(144)); and absent such, PostgreSQL will throw an error if it isn't crystal clear what the application wants, and whether it will fit into the data schema as defined.

MySQL however places the application in charge of defining the data validation rules.  The sql_mode setting (which includes such things as whether '0000-00-00' is a valid date or whether '2008-02-30' can be stored in a date field) is something any application can set without restriction, and the DBA has no way of restricting this.  So while PostgreSQL allows the relational and object-relational interface to serve as a public API, it is essentially intended largely to be a private API for applications in MySQL.  This is a huge difference and not readily understood by many people trying to make the choice.  This leads to major differences in application design.

MySQL is a data storage and reporting solution for your application.  PostgreSQL is a data centralization, modelling, and reporting solution for your organization.  The two are remarkably different.

Are you writing the blog application just for fun for yourself, or are you planning to make it available for everyone to download (think WordPress) or sell it as a product?

If it's going to be something that anyone can download and install, then it's a no-brainer:
It must work with MySQL if you want it to succeed...because practically every web hosting provider out there offers MySQL databases, but not PostgreSQL (at least I never saw a provider with PostgreSQL).

No matter if MySQL or PostgreSQL is better from a pure technical point of view - if you want people to use your app, it must work with what's available at their web hosters.


SQL Server 2016 vs 2012 insert performance I have two SQL Server instances on the same server:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition
(64-bit)
Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) -
13.0.4451.0 (X64) Enterprise Edition (64-bit)

sp_configure results are the same on both instances (except of new 2016 options).
I have created new databases on both instances on the same disk folder. Autogrowth parameters are the same.
Autocreate and autoupdate statistics options are turned off.
Then I have made a test with 10000 inserts into a heap:

set nocount on
go

create table dbo.TestInsert ( i int not null, s varchar(50) not null )

declare @d1 datetime, @d2 datetime, @i int
set @d1 = getdate()
set @i = 1

while @i <= 10000
begin
  insert into dbo.TestInsert ( i, s ) select @i, replicate( 'a', 50 )
  set @i = @i + 1
end

set @d2 = getdate()
select datediff(ms, @d1, @d2)
drop table dbo.TestInsert

Result 1

The average test time

2012 - 530 ms
2016 - 600 ms

So, 2016 is about 11% slower.

Then I have made an SQL Profiler trace with results saved to the table to see the single insert duration in microseconds.

Result 2

The histogram of a single insert duration 2012 vs 2016:

Transaction logs' growth from sys.dm_io_virtual_file_stats is:

2012 - 5174784 bytes
2016 - 5171200 bytes

During these tests both instances are started. But one test is running only at the one instance each time. I have allocated 8Gb RAM per instance. The query plans are the same. It would be interesting to run each instance on its own box. But probably the one machine is better, because here we have no hardware and enviromnent hidden differences.

Questions

Why is 2016 slower?
May someone reproduce this test?

Obviously it is very difficult the coincidence of having exactly the same versions in the same server but... I hope my results will help you.
I have two different machines configured both Windows Server 2012 R2 Standard. Unfortunately they don't have the same hardware but similar:

Machine 1 (SQL Server 2016)

Processor: Intel(R) Xeon(r) CPU X5650 @2.67GHz
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016    23:23:58   Copyright (c) Microsoft Corporation  Standard Edition     (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Machine 2 (SQL Server 2012)

Processor: Intel(R) Xeon(R) CPU E5-2667 0 @ 2.9 GHz
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

And I run 5 times in both machines the same script you have provided and obtained the following average:

2012: 9961
2016: 8971

What is almost the opposite results that you obtain. Anyway, as you have seen, my 2012 machine has better processor but the hard disk that normally is what shows the difference is the same. So, even if 2012 has better resources is a bit slower for my case.

(Sorry, please, check again, my first version had some important erratums)

Can you add (TABLOCK)  hint to enable minimally logging ?
What is the difference after applying this hint?

INSERT INTO t_heap WITH (TABLOCK)
SELECT *
FROM t_source


Delete data before INSERT I have this table which is used to generate second table with the result:

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

But I need to delete the old data before I make insert. Is there a way with one SQL to delete first the table data starting from some date and them to insert a new one?

Yes, you can issue a DELETE statement before your INSERT. If you do it inside a transaction, they will be essentially one operation, they will either fail or succeed together (assuming that the table uses a transactional engine, like InnoDB):

START TRANSACTION ;

    DELETE FROM
        payment_transactions_daily_facts
    WHERE
        date BETWEEN '2018-11-11' AND '2018-11-14' ;

    INSERT INTO
        payment_transactions_daily_facts
        (count, volume, date, ...)
    SELECT
         ...  ;

COMMIT ;

Use stored procedure. Here is a tutorial for MySQL stored procedures http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Alternatively google "MySQL stored procedures" for plenty of other readings.


Is a REINDEX required after CLUSTER? I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications that a REINDEX is required after a CLUSTER. I've found other references that indicate that CLUSTER does a REINDEX. The Official Documentation says nothing at all about REINDEX being part of CLUSTER or required (Although it does suggest running ANALYZE after the CLUSTER)

Can anyone definitively (i.e. with some sort of reference to official docs) say whether or not a REINDEX is required after a CLUSTER?

You do not need to reindex, because CLUSTER effectively does it for you.

More specifically, CLUSTER locks the source table then creates a new copy of it ordered according to the target index. It creates indexes on the new copy then replaces the old table and indexes with the new ones.

Note that this is also true of VACUUM FULL in 9.0+.

If you've been seeing discussion suggesting that CLUSTER bloats indexes it could be people who're assuming that CLUSTER works like pre-9.0 VACUUM FULL. You might also be seeing and misreading discussions that mention index bloat caused by the old VACUUM FULL implementation and suggesting CLUSTER as an alternative.

This is implied in the documentation:

  a temporary copy of the table is created that contains the table data
  in the index order. Temporary copies of each index on the table are
  created as well. Therefore, you need free space on disk at least equal
  to the sum of the table size and the index sizes

What it doesn't say, but should, is that those temporary copies then replace the original table. (Bold mine).

Analysing all the answers, in my opinion the right way to do that is to reindex BEFORE cluster.
As the documentation doesn't tell if cluster do or not a reindex, and only a copy of the index, ordered or not, I think that an indexed index will result in a better clustered table.
After that an analyse will finish the job.
A vacuum full before all seems to be useless, unless cluster and/or reindex do not free dead tuples


need information on a deleted SSRS report In SSRS, once a report is deleted, the ItemPath no longer appears in the ExecutionLog because the Catalog no longer has the report information.  I can track down the ReportID, but I can't find anywhere with the history of the report name.

Is there anywhere I could find the name and path of the deleted report?  If not, any suggestions on how to best stash the information moving forward?

If you need to get the information restore a backup taken prior to removing the report into a different environment and get it from there.

To see what SQL Server knows about your report you can try:

select * from Reportserver.dbo.Catalog where name='xxx'

select * from Reportserver.dbo.Catalog where name='project name'


SQL Server: Explaining to C-Level Executives the impact of using Primary Key of VARCHAR(50) in many tables throughout a database? Suppose a global, mid-sized company is considering a document control management solution that is backed by a SQL Server database and allows developers to create document form types using a form creation tool.  Each form type created using the tool is represented in the database by a table with a primary key of datatype VARCHAR(50).

What impact would the requirement to use VARCHAR(50) primary keys have on system performance vs. a system using a more conventional design approach, e.g., this?  Assume the core audience for the answer is mostly non-technical, i.e., C-Level Executives making a go or no-go decision on the solution.

Notes:

The document control management system needs to perform typical document control management system tasks for a global mid-sized company for many years (at least two decades).
This is a canned solution and the primary key data type cannot be altered.
The VARCHAR(50) data type maps to the company's serial number for products.  The serial number format is alphanumeric and can contain dashes.
The system is web based and uses SQL Server 2014 and the .NET Framework (C#) technology stack.
Questions researched before creating this question, all of which don't align with the technology stack and/or have answers too in the weeds for a non-technical audience:

VARCHAR primary key - MySQL
Should I replace my varchar primary key with an integer primary key?
Can I use VARCHAR as the PRIMARY KEY?
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

The document control management system needs to perform typical document control management system tasks...for many years (at least two decades).

The end of extended support for SQL Server 2014 Service Pack 3 is 7/9/2024.  That's 5 years from now.  So I think your first step should be to see if / when the vendor intends to get certified on a more modern version of SQL Server.  This is more important if you are subject to compliance rules that require software to recieve vendor support and security updates.

As far as other things to share with executive stakeholders, it's hard to make general statements, because you certainly won't be comparing systems that have the exact same schema with the exception of the primary key data type.

If you are comparing this product to a similar one that uses surrogate (integer) keys, assuming the varchar(50) primary keys are also clustered indexes (the default for SQL Server), issues you might raise would be:

You will likely need to pay for increased disk space for the database

the varchar(50) key will take up more space on disk than the integer key
this space increase is multiplied by any nonlclustered indexes on these tables, since the clustering key is included in them automatically
it's common to say "disk is cheap," but some very smart folks have disagreed with that assessment: Disk Is Cheap! ORLY?

Because of the above, you will also spend more on disk space for backups
You will likely need to pay for more RAM

since these tables and indexes are bigger on disk, they will be bigger in RAM too

Some reports and screens might be slower to load

index scans and seeks will have to consume more pages, and would take longer than they equivalent scan of an integer-keyed-table

Some reports and screens might be faster to load

since the text of this serial number would be stored in any table that references it as a foreign key, you might be able to avoid joins sometimes

Notice all of the hedge words above like "might" and "likely" - like I said, I don't think you can make a lot of useful general statements about this situation.

If the company's serial number contains characters other than digits, varchar seems to be the correct data type.

If it contains only digits, an integral data type would be better, since it probably saves space and makes comparisons faster.


Tempdb is getting full very quickly in microsoft sql server 2008 I am developing a log analyzer tool and After queering it and
generating reports Tempdb is getting full very quickly .

Is there any solution to clean the tempdb file in microsoft sql server
2008 on a timely basis other than restarting the server ?

Thank you so much in Advance

Tempdb is growing like this because something is using it.  No attempt to fix, clean or manipulate tempdb is going to work as long as this is going on.

To address this problem, you need to find out what is causing it to grow like this and then fix that problem first.  This alone may resolve the whole issue and remove your need to do anything else with tempdb.  In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).

If you have a nightly maintenance window for the DB the simplest method is to change your database from FULL logging to SIMPLE and then back to FULL again.

All other methods, e.g. http://support.microsoft.com/kb/307487
are overly complicated but you may be forced to consider them nonetheless.


MySQL issue - startup I have Percona Server 5.6 running in linux platform.. recently I faced an issue where mysql instance was continuously restarting for no reason.. mysql logs had this error:

[ERROR] InnoDB: File ./TestBAMC/s_snippet.ibd: 'Linux aio' returned OS error 0. Cannot continue operation

Instance has binary logging enabled and has a master-slave configured. This issue is with the master. Does the above error cause the instance to reboot continuously?

Oracle say: This is most probably due to table corruption. See the error number is 0, which indicates success, not actual error. So, this means that read from file was successful and OS returned 0, but actual read data length is different from expected data length. This is unlikely to be a problem on MySQL, but most likely to be a problem on OS including file system.

InnoDB writes data to data file per page. So, size of data file is always multiple of page size. In general, writes against a file is atomic. So, data file will never be non-multiple of page size, if OS works properly. If it's not multiple of page size, InnoDB will read incomplete page at the end of data file, then it results in this error.

Recommended solutions are:
Take following actions to mend or prevent this problem:

Run fsck and check if the file system is flawless.
Replace suspicious disk.
Ensure that the filesystem is transactional. (such as NTFS and ext4)
Check if journaling setting of filesystem is fine.
Change block size of filesystem to same as InnoDB page size (16KB by default)
Upgrade OS to the latest version.

Perhaps you are hitting full disk conditions? This bug could be related:

https://bugs.mysql.com/bug.php?id=68895


Why is it considered that a set unnormalizes a database absolutely? In order for a relationship to be on 1NF it needs to have all values as atomic, if there's a set it's not even in that first normal form:

But intuitively I think that a table with that set would be more normalized than one that is not as long as the values of that set are just used as attributes of the entity.

For example, let's imagine this table about paintings:

Painting_name, Author, Used technique, Used colors

Now if we get to use a set of colors like {blue, Green, yellow, black, White, purple} we get a table that is not even in 1NF.

If we pass the table to 1NF, then we need to have 6 rows each one repeating the Painting_name, Author, and Used technique.

This looks way more unnormalized than the table that is not even in 1NF, and I don't see why having a set there would harm any possible normalization, as that sets would only be used in that table.

What is then the reason that atomic values are needed in order to have a normalized table?

The reference for this post is an amazing book called Database System Concepts 6th Edition which I recommend you read.

In the book, page 328, it states:

  A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in first normal form (1NF) if
  the domains of all attributes of R are atomic.

You might be wondering "but why!?", it's best explained using a practical example.

Let's look at your example with colors. Lets say we have 2 scenarios, 1.) where the table is not in 1NF, 2.) where the table is in 1NF.

1.)

Id          Painting_name                  Author                         Used_colors
----------- ------------------------------ ------------------------------ ---------------
1           Some_Painting                  John                           Blue, red, Yellow
2           Monalisa                       Leonardo da Vinci              orange, black, White, red, Yellow

Although this might seem intuitive to you, consider what happens when you want to query this table. One, you have inconsistent upper and lower case (which you would have to check with your query for both cases) two, if used_colors isn't an array, you would either have to convert it to an array or use extra steps just to check the data you need (like using a string_split function in SQL Server 2014 and later).

This causes performance issues and ends up being a hassle every time you want to check for something. If you are wondering what stops a person from typing in black_white_yellow instead? That question is answered in 2NF and 3NF, foreign key constraints etc...

2.)

Id          Painting_name                  Author                         Used_colors
----------- ------------------------------ ------------------------------ ---------------
3           Some_Painting                  John                           Blue
4           Some_Painting                  John                           red
5           Some_Painting                  John                           Yellow
6           Monalisa                       Leonardo da Vinci              orange
7           Monalisa                       Leonardo da Vinci              black
8           Monalisa                       Leonardo da Vinci              White
9           Monalisa                       Leonardo da Vinci              red
10          Monalisa                       Leonardo da Vinci              Yellow

In this case, every single row is atomic and unique. What did we gain by doing this? You don't have to think about handling arrays, since every single row now is atomic you can clearly check for what you need quickly and efficiently.

And just to give you an idea of how big of a hassle it could become, here are some posts related to the issue of searching for values in a comma separated column:

How can I check whether a number is contained in a comma separated
list
Using Comma Separated String Value in a Select Statement

There are also many different approaches, none of which are really elegant to say the least (at least pertaining to the problem of 1NF). So, basically by using the 1NF you go from using code like the ones mentioned in those posts above to simple stuff like:

SELECT * FROM Paintings WHERE Used_colors LIKE 'BLUE'

Which helps in both readability and performance.

There is one thing you need to keep in mind, 1NF is only the starting point of the normalization process. The 1NF alone is practically never used in any DB out there, after the 1NF comes the 2NF where you would have to split this table into two separate tables. Used_colors will be made into its own table called Colors. At this point you will reach the cardinality issue which is also covered in the book mentioned above.

One last thing, there are many cases where you're going to come across databases that break the 1NF on one or more tables while adhering to the rules of 2NF, 3NF and even 4NF. For example PostgreSQL has json data type which immediately breaks the 1NF rule (as you can save multiple keys and values in json). The general rule of thumb goes like this: Always follow the normal forms unless you really know what you're doing. Since the moment you introduce such variables you could cause inconsistencies throughout the database and you will most likely lose on performance.

Also, as Paul said in the comment below, there is another point of view which Christopher. J. Date supports(who is a famous researcher and writer on relational DB theory as well as being one of the people with Ted Codd who helped push the relational model). This outlook stabs at the idea of atomic values in 1NF, saying that the whole term "atomic" is ambiguous. The idea behind this is simple, you can say that almost no data type is atomic under the current definition of 1NF. To explain this, lets take a look at an example:

Lets say you have a string Hello World. You have functions in every DBMS that decomposes this string into smaller chunks (like the SUBSTRING or LEFT / RIGHT functions) which means that string isn't really an atomic value as everything can be decomposed. This outlook is similar to data types such as json, you can decompose both string and json, so why is one considered atomic while the other not? That's why C. J. Date considers the current definition of 1NF as ambiguous, since almost all data types can be decomposed in one way or another. If you access json or xml data types as a whole unit without decomposing them, there is nothing to say that json or xml data type isn't atomic.

You can find an interesting paper on The Third Manifesto, they (C. J. Date and Hugh Darwin) made their paper on Databases, Types and the Relational Model The Third Manifesto publicly available. It's also an interesting read which will send you to interesting other articles and topics in general.

If we pass the table to 1NF, then we need to have 6 rows each one repeating the Painting_name, Author, and Used technique.

No, you can also add a Painting_ID, that whay  the tables that refer to a painting can use this to reference the painting.

Likewise you should not store Author, but Author_ID. If the name of this author changes (because you made a typo), you will only have to change 1 record (in the Authors-table). Aslo if you want to add more info about this author, like i.e. birthdate than every painting by this author will have this info too.


Permissions required for running extended events I've found this link: Changes to Extended Events Permission Requirements in SQL Server 2012 that mentions the need of CONTROL SERVER permission for using extended events on SQL Server 2008. I want to grant access to extended events to developers, is not any other way to do it? We are running on SQL Server 2008R2.

As Jonathan stated and the link shows CONTROL SERVER is required prior to SQL Server 2012 to create an Extended Event session. The ALTER ANY EVENT SESSION permission prior to SQL Server 2012 only allowed a user to start/stop a session, or change a current session; it will not allow them to create a session. CONTROL SERVER is required for that ability.

If this is a production system it is based on your company's policies but I would not provide a developer that level of access to anything in production. I would likely create a few different sessions that they might need for troubleshooting code in production, and then grant them ALTER ANY EVENT SESSION so they could run that as needed. They would also have the ability to then change the session as they see fit. However, be aware that even though Extended Events has less overhead than Profiler it still has overhead depending on what you are trying to capture. I would hope the developers have an understanding on what events can cause additional overhead.

Check the details in the Create an Extended Events Session Using Query Editor MSDN article. It's for SQL Server 2012, but it should be valid for 2008 too; the ALTER ANY EVENT SESSION permission should be granted.


Will subquery be executed first here with full scan? I have below query

select *
    from employee employee,
    (select address1.id
        from address1 add1, address2 add2
        where add1.address2_id = add2.id ) address_data
    where emp.empId = 100
    and emp.address_id = address_data.id
// index exists  on both columns i.e. address_id and id

I was expecting first subquery(address_data) will be executed internally and there will be full scan on address1 and address2 table. Then outer
query will be executed.

Revised understanding after looking at explain plain

But when I looked at explain plain looks like it is not happening. I found there was not any full scan on address1 and address2 table. Looks like
first outer query executed(which returned only record) and then only relevant records were fetched from address1 and address2 table(just fyi address tabl also returned 1 row).

This strategy looks logical to me as there is no need to do full access of address tables in this case.

My question - Is my revised understanding correct ? But I was under impression that sub query will always be executed first. Look like  I was wrong here ?

PLAN

Your understanding of the Plan seems correct.

Common Misunderstanding

People often forget that SQL is a declarative language, not a procedural language.

You should never assume sub-queries run in a certain order.

Why would the subquery always be executed first?

Example:

select
  s.column1,
  (select count(*) from huge_table h where h.column1 = s.column2)
from
  any_table s
where
  s.id < 0
;

Let's say no s.id is less than 0. Because of s.id < 0 condition, the outer query returns 0 rows. Why would the database scan the huge_table without any filtering, just to find out after it in an instant that it was all for nothing, because the outer query returns nothing.


Database cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery I have received a database file and the instructions for loading it is to install SQL Server 2005 and then attach it using SQL Server Management Studio.

After installing everything, I tried to attach the MDF file but then it tells me:

  the directory lookup for the file "D:{folderName}{filename}.LDF" failed with the operating system error 21 (error not found)

An LDF file did not come with the database, so presumably it should be generated automatically.

Now, D drive is where my CD drive is, so it's not going to find anything there. Nor is it going to have any luck trying to create anything there.

Why is it trying to look for a log file at a specific path? Why not where the database file is?
How can I attach this database?

I realized that when I select a database to attach, three entries appear under "database details". an MDF, NDF, and LDF. The LDF's "current file path" points to the D drive path above, so I removed it.

This time, when I hit "OK", I get a different error message:

  Database cannot be upgraded because it is read-only or has read-only files.
  Make the database or files writeable, and rerun recovery.

  File activation Failure. The physical name D:{folder}{file}.LDF may be incorrect.
  New log file "..." was created. (Microsoft SQL Server, Error: 3415)

So now it creates a new log file in the same folder as the database file, which is great, but it seems like there are security issues.

Additional information:

The instructions require me to use the login name "sa", which appears to be the sysadmin account. I am connected to my SQL Server instance using that login.
I have checked the file properties that it is not read-only. The directory is not read-only either. All ACLs are allowed.
I am unable to attach the database. When I try to attach it, it throws an error message with "attach database failed".
Closing SSMS and reopening it as an Administrator made no difference.
select SERVERPROPERTY('ProductVersion') returns 9.00.4035.00. There's another file that comes with the database called "dbdata.ini" which says "IsSql2000=1" so presumably it is meant to be loaded in SQL Server 2000. I'll see if I can get it working on 2000.

I just found a post that answered this question:

This may not work in all cases, but I was trying to install the Semantic Search Database and got the same error.

You have to run SQL Server Management Studio as an administrator, and it works.

1) Open Sql Server Configuration Manager

2) Select Sql Server Services(Left pane)

3) Doubleclick your SqlServer(right Pane)

4) Select Log On tab

5) Select built-in radio button and change local system or network service in combobox.

6) Click Ok.

7) Attach mdf file in sql server manager.


How to change MongoDB slowOpThresholdMs in mongo shell I need to decrease the slowms trash hold to 50, can't find the way of doing that without restarting the mongod process.

The slowOpThresholdMs setting defines what constitutes a “slow” operation. To set the threshold which the profiler considers operations “slow” (and thus, included in the level 1 profiling data), you can configure slowOpThresholdMs at runtime as an argument to the db.setProfilingLevel() operation.

For Ref setProfilingLevel and
slowOpThresholdMs

Note:  The threshold for slow operations applies to the entire mongod instance. When you change the threshold, you change it for all databases on the instance.

For Example:

{ profile: 1, slowms: 200 }

Database profiling can impact database performance. Enable this option only after careful consideration


Updating rows from other rows in a huge table I have a huge table with items

item-id | name | type | extra

Many of them are "old versions" of type 0.

I have to update the extra field in all new items (same item-id, but type > 0) with the extra data we have for the old version.

Since this is a very large table with millions of rows, I have to be very careful with joining the table with itself.

Basically I want to do this

update mytable t1 set extra = (select extra from mytable where t1.itemid = mytable.itemid and type=0) where type > 0;

But MySQL doesn't accept t1 in the sub-select.

Recently read an article that might help you, see: https://dzone.com/articles/specifying-target-tables-in-mysql

Still think a self join would be faster than a correlated sub-query. Especially on MySQL 5.5 and less, where these type of sub queries are badly optimized.

Self join like:

UPDATE mytable t1
  JOIN mytable t2
    ON t1.item-id = t2.item-id
SET t1.extra = t2.extra
WHERE t1.type > 0;

Your syntax is wrong
Try this:

Update TableName
Set columnName = Value
From TableName T1


SELECT
  y.Trade_date,x.Scrip,
  (((y.Open_Interest - x.Open_Interest)/x.Open_Interest)*100)
     AS Change_in_OI from stock_future x
JOIN stock_future y
  ON y.Scrip = x.Scrip
  AND y.Trade_date = '2020-03-17'
WHERE x.Trade_date = '2020-03-16';


How can I allow an Oracle database to be accessed remotely? I have an Oracle database installed on one machine, and I am trying to accessing the same from another machine.

I tried via Oracle SQL developer eclipse, and even via JDBC program, butnothing works. I am not sure if it is firewall issue (I have ruled out this because both the machines are in same domain) or something to do with Oracle database setting. I know in MySQL you need to grant permission to connect to database remotely. Do we need to set something similar on oracle SID also?

The error message I get:

  Io Exception: The Network Adapter could not establish the connection

Oracle is an enterprise database server so you shouldnt need any strange setup to get it working, just the correct configuration on the DB server or client will be enough.

When you get connection errors, i suggest first ensuring that the client machine can ping the database server where  is the name or ip address - try both as it's possible the name may not be configured in the hosts file of the client machine. (Note that ping may be disabled due to security but if you get a reply back, you know the connection is good.A non reply may not mean the network is missing but you can try to ssh or telnet to the dbhost from the client machine as an alternative)

 unix> ping <dbhost>

Once you have confirmed the client machine can see the db server, you can check the configuration on the DB server.

On the DB server, if you connect with the following, you are connecting directly to the database without using TNS.

 unix> sqlplus user/pass

If you connect instead with

 unix> sqlplus user/pass@ORACLE_SID

where ORACLE_SID is the identifier of the database, you will be using TNS, the same as your client machine.  If this doesnt work locally, then it's also not going to work on the remote machine.

Check the listener is running

 unix> lsnrctl status

and if it's not, start it as the oracle user

 unix> lsnrctl start

and try again to connect using user/pass@ORACLE_SID

If this connects OK, then check your JDBC configuration to ensure you have specified the connection string correctly.

see http://javaeesupportpatterns.blogspot.de/2011/08/network-adapter-could-not-establish.html for assistance with the correct jdbc connection string.

I know it's an old thread, but I had the same issue and did not find answer anywhere.
Here is the reason: Oracle installs it's own Firewall. You can check if it's running on your system (execute as root):
~> systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
  Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
  Active: active (running) (thawing) since Fri 2021-01-01 00:00:00 UTC; 0min ago
  ...

Or
~> /bin/ps -ef | grep fire
root        1234       1  0 00:00 ?        00:00:00 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid

If it is running - that's what's blocking the connection.
Issue commands:
~> firewall-cmd --zone=public --add-port=1521/tcp --add-port=5500/tcp --add-port=5520/tcp --add-port=3938/tcp --permanent
success
~> firewall-cmd --reload
success

And try it. It should be fine now.


how to get list of months between two dates in db2 I have two dates start and end date i need find list of months between two dates.for example, 01-12-2015 as start date and 01-02-2015 as end date i need result to be as , January. I am using IBM db2. another thing i need to find these using select query i have no access to create function or procedures.

Your date format is ambiguous but given your expected answer is January, I'll assume your start date is 2015-01-02 and end date is 2015-01-12. The following recursive query should do what you need:

WITH months(i, d) AS (
    VALUES (1, DATE('2015-01-02'))

    UNION ALL

    SELECT i + 1, d + 1 MONTH
      FROM months
     WHERE i < 10000
       AND d + 1 MONTH <= DATE('2015-01-12')
)
SELECT monthname(d)
  FROM months

The "i" term in the recursive element is simply there to suppress the "possibly infinite" warning that occurs otherwise. If you're not bothered by the warning, you could just remove it.

This will work on DB2 for LUW and I think it'll work on DB2 for z/OS but that'll depend on whether they've finally added support for VALUES or not; if they haven't just use SELECT foo FROM SYSIBM.SYSDUMMY1 in the base case instead).

case when year(End_Date) = year(Start_Date)
     then month(End_Date) - month(Start_Date)
     else month(End_Date) - month(Start_Date) + 12 * (year(End_Date) - year(Start_Date))
end Mon,


Is unique index combined with a "not null" constraint equivalent to a PK constraint? I have these legacy table definitions (from here):

CREATE TABLE [dbo].[JobItems] (
   [ItemId]            UNIQUEIDENTIFIER NOT NULL,
   -- lots of other columns
   CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
ON [dbo].[JobItems]([ItemId] ASC);

which effectively yields two identical indexes. I want to get rid of one of them to reduce insertion time and free up disk space.

The table is stored in a production SQL Azure database, so I cannot rebuild the table and I cannot drop the clustered index (SQL Azure requires a clustered index for every table).

My requirement is I keep all the data integrity guarantees which were provided by the original definition and get rid of the non-clustered index. It looks like having a NOT NULL constraint and a unique index gets me the same guarantees as a PK constraint. So the PK constraint is redundant and I can drop it and the underlying index.

Can I just drop the PK constraint here without expecting anything to break?

which effectively yields two identical indexes.

The indexes are not identical.  The CI includes all of the other columns, while the PK has only the ItemId key column on the leaf.  So the Clustered Index is probably 100x the size of the non-clustered index.

For instance COUNT(*) and paging queries will be significantly more expensive with only the clustered index, as they will have to use table scans.

Right now, both your unique index and PK uniquely identify each row. This is redundant. The index can be remove and the cluster can be move into the PK.
However it will fail if there are foreign keys on ItemId.

First look for foreign key(s):

Select OBJECT_NAME(constraint_object_id) From sys.foreign_key_columns where referenced_object_id = OBJECT_ID('dbo.JobItems') and COL_NAME(referenced_object_id, referenced_column_id) = 'ItemId'

Then just drop both of them (Index + PK) and recreate a clustered PK:

If Exists(Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.JobItems') and name = 'JobItemsIndex')
Begin
    Drop Index [JobItemsIndex] On [dbo].[JobItems]
End
If Exists(Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.JobItems') and name = 'PrimaryKey_GUID_HERE')
Begin
    Alter Table [dbo].[JobItems] Drop Constraint [PrimaryKey_GUID_HERE]
End

Alter Table [dbo].[JobItems] Add Constraint [PrimaryKey_GUID_HERE] PRIMARY KEY CLUSTERED ([ItemId] ASC)


Is outputting to both a result set and a temporary table possible? I have a stored procedure that I need to both output the result set to the user and use a piece of information in the result set to possibly output other result sets.

Here is a basic schema of what I have

create table FooQueue(FooId int, IsQueued bit)
create table Foo(FooId int primary key, BarId int, FooData varchar(50))

create table BarQueue(BarId int, IsQueued bit)
create table Bar(BarId int primary key, BarData varchar(50)

Here is a very simplified version of the stored procedures.

create procedure GetFoos as
begin
    select FooId, FooData
    from Foo
    where FooId in (select FooId from FooQueue where IsQueued = 1)

    update BarQueue
    set IsQueued = 1
    where BarId in (select BarId
                    from Foo
                    where FooId in (select FooId from FooQueue where  IsQueued = 1))

    exec GetBars

    delete FooQueue where IsQueued = 1
end
GO

create procedure GetBars as
begin
    select BarId, BarData
    from Bar
    where BarId in (select BarId from BarQueue where IsQueued = 1)

    delete Bar where IsQueued = 1
end
GO

And the first procedure is called like the following

update top 10000 Foo set IsQueued = 1
exec GetFoos

I am about to modify a large number of the stored procedures so it does not hit the Foo table twice to something like the following

alter procedure GetFoos as
begin
    select FooId, BarId, FooData
    from Foo
    into #t
    where FooId in (select FooId from FooQueue where IsQueued = 1)

    select FooId, FooData from #t

    update BarQueue
    set IsQueued = 1
    where BarId in (select BarId from #t)

    exec GetBars

    delete FooQueue where IsQueued = 1
end
GO

Would this be the correct approach to doing this or is there a better way to both return the resultset (without BarId included) and update the 2nd table based on the returned rows?

you can alter the column in select or insert list as you need.

    alter procedure GetFoos as
    BEGIN
        CREATE TABLE #t (FooId INT, BarId INT, FooData SYSNAME)

        select FooId, BarId, FooData
        OUTPUT INSERTED.FooId, INSERTED.BarId,INSERTED.FooData INTO #t(FooId, BarId, FooData )
        from Foo
        where FooId in (select FooId from FooQueue where IsQueued = 1)

        update BarQueue
        set IsQueued = 1
        where BarId in (select BarId from #t)

        exec GetBars

        delete FooQueue where IsQueued = 1
    end
    GO

Not looked calmly yet, but depending on the situation, this is a good way to do what you need.

But as a suggestion to make this way will help to optimize the process and review your data model to better serve tasks like this.

alter procedure GetFoos
as

begin

select t1.FooId, t1.BarId, t1.FooData
from Foo t1 , FooQueue t2
into #t
where t1.FooId = t2.FooId
and t2.IsQueued = 1

select FooId, FooData from #t

update BarQueue
set IsQueued = 1
where BarId exists (select BarId from #t)

exec GetBars

delete FooQueue where IsQueued = 1
end
GO


Oracle redo log buffer cache vs redo log file While going through the Oracle architecture I got stuck with the below question. Could someone help me answering this?

Why do we need both redo log buffer cache and redo log files? Why can't we write the data directly to redo log files from database buffer cache?

Redo Log Buffers: A log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in the redo entries. Redo entries contain the information necessary to reconstruct or redo changes made to the database by insert, update, delete, create, alter, or drop operations. Redo entries are primarily used for database recovery as necessary.

Redo Log: The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

for more information about database structure please refer to https://docs.oracle.com/en/

while doing the dml in the database , redo buffers and db buffers are two different activities first 1.redolog buffers and db buffers will allocate after execution plan , the redo log buffers fill and based on commit and threshold values the data parallel moving to redo log files .This is only for recovery . in redo buffers based the instance type of down the redo log buffers will may roll back or roll forward based on commit to recover the instance to bring up the instance . So hear is redo files will have commited data for recovery we need redo buffers information.

Second one :- db duffers are use to store the data to dbf file , It's again depend up on threshold value, it's not depend up on commit .

So for the recovery purpose redo log files will have exact committed data or recoverable data. redo log buffers can have the commit  and non commit data for roll forward and roll backward .


Copy tables selected from sys.tables to new DB I have a DB with thousands of tables.

I want to copy all tables with name like 'TableType1%' to a new DB.

I can easily get a list of the tables:

  select * from sys.tables t where schema_name(t.schema_id) = 'S1' AND
  [name] LIKE 'TableType1%'

But how do I copy them to a new DB?

I CANNOT do this manually, as there are too many tables.

I would like to use

  INSERT INTO

type statement, but don't know how to put it into select statement above.

Something like:

select * from sys.tables t
insert int [NewDB].[S1].[ t.name ] <<< What should go here?
where schema_name(t.schema_id) = 'S1' AND [name] LIKE 'TableType1%'

If you are looking for a quick one-off solution, you can try this.

In the source database, run this command (making the necessary changes for TrgDb for your target database and SrcDb for your source database.  Also change the where clause to pick your tables.

SELECT 'select * into TrgDb.S1.' + t.NAME + ' from  SrcDb.S1.' + t.NAME + ';'
FROM sys.tables t
WHERE NAME LIKE 'blah%';

Take the results and paste the commands into a new SSMS window that points to the target database.

Do you want to copy all of the data inside of them to the new DB, or are you looking to have blank copies of the table in the new DB?

You should be able to do a Tasks -> Export Data to the destination DB if you need the data, otherwise you could use the SSMS Generate Script feature to copy the tables over with only the Schema.


Do frequent DML operations on a table with indexes and huge data affect SELECT query performance? If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where condition on index columns,
Or will the performance remain same as when there are no DML operations?
This Link says UpdateStatistics does block Select queries. So will DML operations cause UpdateStatistics. It says " The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.". So is changing these values the best solution?
Note:
 I will be using isolation level (Read Uncommitted) for the SELECT query which should not be blocked by locks.

First a comment on terminology: DML (Data Manipulation Language) includes SELECT. I have a feeling that you when you say DML refer to INSERT, UPDATE and DELETE. I'll be referring to these as "modification statements".
You also say that you run "default isolation level (Read Uncommitted)". That is not correct. The default isolation level is Read Committed, not Read Uncommitted.
Read Committed is indeed blocked by exclusive locks, which are acquired by data modification statements. That is the default behavior of Read Committed, but you can re-configure the behavior by changing the database setting read committed snapshot to ON (RCSI). With RCSI readers are not blocked by modifiers, they'll get the last committed version of the row instead of waiting. (RCSI is by default ON for Azure SQL Database, btw.)
Don't worry about update statistics. It doesn't block readers. That blog post you refer to INDEX REORGANIZE at the same time as UPDATE STATISTICS. UPDATE STATISTICS in itself doesn't block readers. I just verified this by running update statistics on the stats for an index and I simultaneously ran SELECT using that index/stats, several times without any hint of blocking.

*Note this answer starts out in regards to the default isolation level which is what OP originally asked about, but then goes into the actual isolation level he was curious about.
In general DML queries do cause locks on rows and Tables which will affect SELECT performance, especially in the READ COMMITTED isolation level. It depends on the specific DML operation, the records it's affecting, and the SELECT query and which records it needs to read.
If a certain DML operation only causes a row level lock, then only those specific rows will be locked and SELECTs against those rows will be blocked while waiting for the DML lock to be released. SELECTs against other rows of the same Table will not be blocked. If the DML operation escalates to a Table level lock, then all SELECT queries against that table will be blocked throughout. Bear in mind, most times this is usually very quick either way, and not a concern (unless it's a poorly constructed DML operation).
The previous is true regardless if you have indexes or not, the only difference is some DML operations can be slightly slower, the more indexes that exist on a Table, such as INSERT operations. But conversely other DML operations like UPDATES generally perform better with the appropriate indexes on the Table, since the indexes help the UPDATE operation locate which records to update.

Since OP meant they are using the READ UNCOMMITTED isolation level, direct row and Table level locking does not apply then from writers blocking readers as normally would be in the default isolation level, READ COMMITTED.
BUT a heavy DML operation can still affect performance of a SELECT query by causing contention on the Table or Server. For example, if the table is compressed and a large amount of rows are UPDATEd by a single query, that can use a lot of CPU and I/O, and those server resources would be under contention throughout the operation causing other SELECT queries to potentially run slower on that server.

Sidenote, "huge data" is very subjective in the database world. I'm not sure what that means to you quantifiably, but I've worked in databases with Tables that had 10s of billions of rows, that did multiple DML operations to 1,000s of rows per minute and the SELECT performance was still excellent.


Asked to Not Use Transactions and to Use A Workaround to Simulate One I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have received what I think is an odd suggestion regarding transactions. Don't ever use them. Instead, use a workaround that simulates a transaction. This is coming from our DBA who works in one database with a lot of transactions and subsequently, a lot of blocking. The database I primarily work in does not suffer from this issue and I see transactions have been used in the past.

I understand that blocking is expected with transactions as it's in their nature to do so and if you can get away without using one, by all means do it. But I have many occasions where each statement MUST execute successfully. If one fails they all must fail to commit.

I’ve always kept the scope of my transactions as narrow as possible, always used in conjunction with SET XACT_ABORT ON and always within a TRY/CATCH.

Example:

CREATE SCHEMA someschema;
GO

CREATE TABLE someschema.tableA
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 ColA VARCHAR(10) NOT NULL
);
GO

CREATE TABLE someschema.tableB
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 ColB VARCHAR(10) NOT NULL
);
GO

CREATE PROCEDURE someschema.ProcedureName @ColA VARCHAR(10),
                                          @ColB VARCHAR(10)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);

--Implement error
    SELECT 1/0

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@trancount > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;
    THROW;
    RETURN;
END CATCH;
END;
GO

Here is what they suggested that I do.

GO

CREATE PROCEDURE someschema.ProcedureNameNoTransaction @ColA VARCHAR(10),
                                                       @ColB VARCHAR(10)
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRY
    DECLARE @tableAid INT;
    DECLARE @tableBid INT;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);
    SET @tableAid = SCOPE_IDENTITY();

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);
    SET @tableBid = SCOPE_IDENTITY();

--Implement error
    SELECT 1/0

END TRY
BEGIN CATCH
    DELETE FROM someschema.tableA
    WHERE id = @tableAid;

    DELETE FROM someschema.tableB
    WHERE id = @tableBid;

    THROW;

    RETURN;
END CATCH;
END;
GO

My question to the community is as follows. Does this make sense as a viable workaround for transactions?

My opinion from what I know about transactions and what the solution is proposing is that no, this isn’t a viable solution and introduces many points of failure.

In the suggested workaround, I see four implicit transactions occurring. The two inserts in the try and then two more transactions for the deletes in the catch. It does “undo” the inserts but without rolling back anything so nothing is actually rolled back.

This is a very basic example to demonstrate the concept they are suggesting. Some of the actual stored procedures I have been doing this in make them exhaustively long and difficult to manage because “rolling back” multiple result sets vs two parameter values in this example becomes quite complicated as you could imagine. Since "rolling back" is being done manually now, the opportunity to miss something because real.

Another issue that I think exists is for timeouts or severed connections. Does this still get rolled back? This is my understanding of why SET XACT_ABORT ON should be used so that in these cases, the transaction will roll back.

Thanks for your feedback in advance!

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails).

Transaction simulation suggested by the person who presents him- or herself as your "DBA" fails to ensure three out of four requisite properties of transaction processing, because it addresses only "soft" errors and is not capable of dealing with "hard" errors, such as network disconnects, power outages, disk failures, and so on.

Atomicity: fail. If a "hard" error occurs somewhere in the middle of your pseudo-transaction, the change will be non-atomic.
Consistency: fail. It follows from the above that your data will be in an inconsistent state following a "hard" error.
Isolation: fail. It is possible that a concurrent pseudo-transaction changes some of the data modified by your pseudo-transaction before yours completes.
Durability: success. Changes you make will be durable, the database server will ensure that; this is the only thing your colleague's approach cannot screw up.

Locks are a widely used and empirically successful method to ensure ACIDity of transactions in all sorts or RDBMSes (this site being an example). I find it very unlikely that a random DBA can come up with a better solution to the concurrency problem than hundreds, possibly thousands of computer scientists and engineers who have been building some interesting database systems over the last, what, 50? 60 years? (I realise this is somewhat fallacious as an "appeal to authority" argument, but I'll stick to it regardless.)

In conclusion, ignore your "DBA"'s advice if you can, fight it if you have the spirit, and come back here with specific concurrency problems if they arise.

There is a way around using transactions to a limited extent and that is by changing your data model to be more object oriented.  So rather than storing for example demographic data  about a person in several tables and relating them to each other and requiring transactions, you could have a single JSON document that stores everything you know about that person in a single field.  Of course working out far that domain stretches is a another design challenge, best done by developers not DBAs


ORA-32000: write to SPFILE requested but SPFILE is not modifiable I'm not able to alter the SORT_AREA_SIZE in my database.

Use the command:

create pfile ='loc' from spfile;

To create a pfile named loc. The edit this file and change the parameter in this pfile. Now create a new spfile from it:

create spfile from pfile ='loc';

Delete the SPFILE in

C:\oraclexe\app\oracle\product\11.2.0\server\database\SPFILEXE.ORA

Or where you have it stored