How to compare and aggregate data in a table Is there a way to evaluate data in a table to derive if in a set of ProductIDs a PRODUCTID passed or failed?  Here is an example of my data:

DECLARE @example TABLE (ID INT NOT NULL, PRODUCTID INT, PASSED BIT, LEVEL INT, CREATEDATE DATETIME, PRIMARY KEY (ID))
INSERT INTO @example VALUES
 (  1, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  2, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  3, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  4, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  5, 101, 1, 1, '2016-01-10 07:35:02.123' )
,(  6, 102, 0, 1, '2016-01-10 07:35:02.123' )
,(  7, 102, 1, 2, '2016-01-10 07:35:02.123' )
,(  8, 103, 1, 1, '2016-01-10 10:35:02.123' )
,(  9, 103, 1, 2, '2016-01-10 09:35:02.123' )
,( 10, 103, 1, 2, '2016-01-10 08:35:02.123' )
,( 11, 103, 1, 3, '2016-01-10 07:35:02.123' )

Is it possible to structure a query that returns:

PRODUCTID    ISPASSED
100          0
101          0
102          0
103          1

The conditions are there needs to be 2 PASSED for 2 different LEVELS for a given PRODUCTID.

100 is a 0 because all 4 records are for LEVEL 1
101 is a 0 because there is only 1 PASS and for only 1 LEVEL
102 is a 0 because one record PASSED and the other failed
103 is a 1 because there are 2 PASSED records for different LEVELS.

Thanks so much!

EDIT:

I have come up with this query, but it comes up short because I am only able to qualify first part of the requirements:

WITH cte(PRODUCTID, ISPASSED) AS
(
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) = 2 THEN 1 ELSE 0 END AS ISPASSED
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC), LEVEL, PASSED
    FROM @example
    GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
  WHERE RowNumber <=2
  GROUP BY PRODUCTID
)
select * from cte

Since you want to count differing values in a set that may contain identical values, I would say COUNT(DISTINCT ...) is the perfect tool to use. For instance, the following will count distinct levels per product:

SELECT
  PRODUCTID,
  COUNT(DISTINCT LEVEL)
FROM
  @example
GROUP BY
  PRODUCTID
;

This will be the output for your example:

PRODUCTID
---------  ---
100        1
101        1
102        2
103        3

Now, how can you count only the distinct values matching a specific condition? The answer is, by using conditional aggregation, i.e. one that uses a CASE expression as the aggregate function's argument. So, to count only distinct passed levels, you can use a query like this:

SELECT
  PRODUCTID,
  COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END)
FROM
  @example
GROUP BY
  PRODUCTID
;

And the results will now be

PRODUCTID
---------  ---
100        1
101        1
102        1
103        3

The figures seem correct now, so how can you turn those 1, 3 into 0, 1? Why, by using another CASE expression, of course:

SELECT
  PRODUCTID,
  ISPASSED = CASE
                WHEN COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END) >= 2
                THEN 1
                ELSE 0
              END
FROM
  @example
GROUP BY
  PRODUCTID
;

Now you can enjoy the expected output of

PRODUCTID  ISPASSED
---------  --------
100        0
101        0
102        0
103        1


Dodgy causes of ORA-12549? I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error.

Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - has resources, hasn't reached maximum files to open or processes and have space on all tablespaces.

I start to wonder if this error could have been thrown by client. However it is a Java application using JDBC to connect and the heap size is of 16gb, also not nearly filled up according to the GC logs.

The process itself is rather simple: make a massive select into Oracle, apply some logic over the data and commit is periodically to another table. I have used commits from 5million lines to mere 100k (divided in 100 batches of 1k lines). The error is always about the same point.

Can someone give me some insight on what is happening? Where should I look for clues?

Client-side, ulimit is 1024 (on Oracle's server is is 65k), could it be related?

This is a resource issue.

The DB server cannot satisfy your queries due to the client host being configured incorrectly as-per the pre-requisites for an Oracle database client installation.

Ask your system administrators to verify that they have set the required number of open files, semaphores, shmax etc etc. Link to the Oracle documentation - I assume Linux, but Solaris docs are a link away from that.


Create previously created MySQL database without .sql file I had a bad server crash in which I was able to restore my MySQL files, but I was unable to do a dump in order to get the .sql file. In other words, I have each individual part that was saved in the database. (i.e "databasename.table")

So, I have all my information, but after resetting the server, I am now unable to put my database back online. When I copy the files over, phpMyAdmin and MySQL Workbench show the table names, but act as if they don't exist.

So the files are there, and the names are read, but the information is not there. (i.e phpMyAdmin says something like, "databasename.table does not exist") I am unsure of what to do and I hope I was able to describe it well enough for you guys to understand it as well. If anyone is able to help me, whether it be describing how I can compile those into a .sql, or moving them over for the new server to read them, I would greatly appreciate it.

Thank you!

Note: I have replaced the hard drive in the server, so I still have everything that was saved on that hard drive, but I am unable to get it to boot, so if you guys find that I need to go through it again and find some other files, that can be done.

What I've learned: Create constant backups of databases, it just makes sense that way...

This will depend a bit on your table type. With MyISAM tables, you can generally just copy the folder and files and everything will work. With other table types, it sometimes doesn't work as well.

The first thing you can try is to completely replace your MySQL data directory with the one from the backup. I would move it out of the way (mv /var/lib/mysql /var/lib/mysql-orig) so that you can easily restore it. Do this with the MySQL server not running and preferably on a testing server so you don't accidentally break something on your production machine.

Since you have the old hard drive data but can't get the drive to boot, it may be relatively painless to start MySQL from that drive (you can do this in a couple of ways; chroot to the old drive and try to start it there, copy the files to a server with a working MySQL instance, stop mysqld, edit the configuration file to point to the old mysql data directory, then restart MySQL, or finally just follow the steps above to completely replace the mysql data directory on the working instance with the one from the old server.

As D. Kasipovic points out, you should check the file permissions on the files you have restored, it could be as simple as a file permission problem.

You should make sure you're connecting as the root user or another user with administrative permissions; otherwise it's likely that a MySQL permissions will keep you from being able to see the databases. Depending what method ends up working for you, you may need to create any users that had permissions on that database.


Sum of all Memory Clerks and Total Server Memory (KB) I am trying to understand the memory management of SQL Server.
I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 MB.
By using the following query I found the list of all memory clerks:
SELECT  [type] AS [ClerkType], SUM(pages_kb) / 1024 AS [SizeMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC

Total sum of all memory clerks was equal to 23523 MB.
Value for MEMORYCLERK_SQLBUFFERPOOL memory clerk is 18387 MB.

I also checked some performance counters:
Total Server Memory (KB) = 32262 MB.
Database Cache Memory (KB) = 18387 MB.

Please correct me if any of the below statements are wrong,
Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.

Total Server Memory (KB) - The committed memory from the Buffer Pool.

Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size.

Above query shows all memory clerks for both Buffer Pool Memory and Non-Buffer Pool Memory.

My question related to this subject:
If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory?
EDIT: Result of select * from sys.dm_os_process_memory

Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.

No, some part of memory allocation for SQL Server 2019 can be done outside of max server memory like ( I would quote from Memory Management Architecture Guide)
Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed queries, automation objects referenced in Transact-SQL statements, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.
So you can see some part of memory is allocated outside max server memory so your equation does not always holds true.

Total Server Memory (KB) - The committed memory from the Buffer Pool.

Yes that is the memory committed using memory manager.

Data Cache Memory (KB) performance counter is equivalent to MEMORYCLERK_SQLBUFFERPOOL memory clerk and both represents data cache used size.

No, buffer pool is much bigger, Data cache is subset of it.

If Total Server Memeory (KB) is related to Buffer Pool memory, then why its size (32262 MB) is bigger than Total sum memory clerks altogether (23523 MB) which represents both Buffer Poll Memory and Non-Buffer Pool Memory?

Total server memory includes basically memory used by SQL Server which is almost 31 GB and this will be bigger than sum of the clerks, which is basically 22 GB, because memory clerks do not capture all memory allocations within SQL Server ( They do most of it however).
Let me know if you have further questions.


What is the best approach to mapping between two tables with many to many relationship? What is the best approach to mapping between two tables with many to many relationship? Is it best to create third table with id of each table as foreign key ?
For example student table and course table each student have many courses.

An intersection table such as you've described is the standard way to implement a many to many relationship in a physical relational database.

The intersection table has foreign keys to each of the parent tables that it's joining.  These may be simple or compound foreign keys.  That doesn't matter.

The primary key of the intersection table is the combination of the two foreign keys to the parent tables.  For this reason, the primary key of an intersection table is always a compound foreign key.


How to determine which tables use a sequence in SQL Server I have a sequence in a SQL Server 2012 database that appears in sys.sequences. How do I determine which tables use this sequence? I do not have sufficient permissions to view the definition of this system view so can't dig around for clues.

A quick test shows that sequence references are tracked via sys.dm_sql_referencing_entities in SQL Server 2014 SP2. I don't have access to SQL Server 2012, but you can use the following query to (have an admin) confirm whether they're tracked in 2012 as well. If so, this might provide a slightly more rigorous way to find the references you are looking for.

-- Find all objects that reference each sequence on the database
SELECT s.object_id AS sequence_object_id,
    s.name AS sequence_name,
    OBJECT_SCHEMA_NAME(o.parent_object_id) + '.'
        + OBJECT_NAME(o.parent_object_id) AS table_name,
    r.*
FROM sys.sequences s
CROSS APPLY sys.dm_sql_referencing_entities(
    OBJECT_SCHEMA_NAME(s.object_id) + '.' + s.name,
    'OBJECT'
) r
JOIN sys.objects o
    ON o.object_id = r.referencing_id


SQL Record Transfer from one db to another instance having same table structure I want to move data from server db table to local db table but need to make sure that the Ids of records won't change while copying from main Server db table.
like

The issue I am having is when I move this to local, it merges the next to last record of the table and starts incrementing from number 20.
I want the Ids to remain concurrent or the same in both Local and Production Db.
Any possible Solutions?

You can use SET IDENTITY_INSERT my_table ON before performing the insertion of the data in you local table and on finish use SET IDENTITY_INSERT my_table OFF and your column will take the following value.

Example:

SET IDENTITY_INSERT [dbo].[table] ON

GO

INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (1 ,'qqq');

INSERT INTO [dbo].[table] ([CustomerID] ,[FirstName]) VALUES (2 ,'eee');

SET IDENTITY_INSERT [dbo].[table] OFF


How to start building your own DBMS (based on PostgreSQL)? I'd like to learn how to make a DBMS (relational), based on PostgreSQL specifically. Not for production purposes to make a commercial product but to learn.

I plan on using C++ but, it's not exactly easy to find information out there on how to build your own (or at least where to start).

I know there are people in this community who are even contributors to the PostgreSQL source code (such as Laurenz Albe) so it made sense to ask this question here.

I've been following this course https://www.youtube.com/playlist?list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi to understand the ins and outs of a DBMS. But as most of it simply explains the theoretical, I don't have a chance to see the practical or how to actually do it.

My advice is to get involved with an open source database project. I would go with PostgreSQL, but there are of course alternatives.

If you go with PostgreSQL:

Use the software. It is impossible to learn about databases without actually using them.
Build PostgreSQL from source.
Subscribe to the mailing lists and read them. There you can read what knowledgeable people have to say, and one can learn a lot from that.
Whenever you run into a problem or don't understand the workings of something, dig into the source. If you get stuck, ask for help.
Read this article.
Tune in on the pgsql-hackers mailing list.
Start reviewing patches and contributing.

Keep your eyes open for the different ways that other databases do it. After a few years, you will have gained some understanding. Then you may be in a position to come up with your own ideas on architecture.


Using Auto Increment and Not Null Is there any point in following up a AUTO INCREMENT with a NOT NULL?
Seeing as the db takes care of this, I assumed a AUTO INCREMENT will always have a value?

Yes, because it is possible to manually insert data into this field. You wouldn't want anyone intentionally or accidentally adding a record that placed a NULL in your identity field.

On the flip side, if you are not using the field as an artificial key but for some other purpose, and having a null value is ok, then sure leave it off.


Attaching database to an existing connection Is attaching the database supported only by SQLite and MS SQL Server?
All other DBMSes can only have one database per connection?
Scenario:
Let's say there is an archived database with some historical data. The application needs to compare with the current data for the same period.
The database in use is not the two mentioned above.
How do people solve that?
EDIT:
The plain SQL supports the syntax of catalog.schema.table which produces fully qualified table name to the query.
However I believe that in order to use that syntax I have to have a connection to the second database.
Its very easy if 2 databases (2 database files) are running on the same server. Then, in case of SQL Server, for example you connect to the server, issue use db1 and then just use the appropriate syntax.
However if those 2 databases are physically located on 2 different servers, I think I need to create a connection to the second database in order to use fully qualified name. Am I right? I think I am because otherwise the DB engine will not know anything about the second DB file(s).
Now, my understanding is that there should be only one connection in order to use the syntax as SELECT catalog1.schema1.table1, catalog2.schema2.table2 FROM catalog1.schema1, catalog2.schema2 WHERE ....;. Am I right? I don't know for sure, as I'm not a DB administrator - just a software developer trying to work with different DBMSes.
Now in the answer below I was told that the way to have this one connection to 2 different DBMSes - for PostgreSQL and mySQL. I'm sure the same mechanism exists for Oracle/SAP/Sybase/DB2.
TIA for confirming or deniying my suspicions.

all rdms support
Db.table

SQL SERVER
[db].[dbo].[Table]

So you can make a query
SELECT a.col1,b.cl2 FROM db1.table1 a INNER JOIN db1.table1 b ON a.id= b.id

That would get you columns from 2 databases, on one connection.
2 connections make the thing more complicted
MYsql can establish federated tables http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html so that you have scenario 1 again. (that is possible for almost every rdms)
For Postgreslq exists something similar see https://www.postgresql.org/docs/9.3/postgres-fdw.html
Else you can only get the data from connection/Server  1 and compare it to the data received by server 2, by means of a variables
(the syntax differs somewaht from rdms to rdms)


min/max performance versus order by limit in sqlite? I know that combining min/max in one query is not performant. (See, for example, https://stackoverflow.com/questions/11515165/sqlite3-select-min-max-together-is-much-slower-than-select-them-separately). I also know that for SQL, the recommended approach is to use min/max. (See https://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit) However, how do these functions compare individually against ordering and limiting in sqlite?

And to be clear, these are the queries in question:

SELECT MIN(column)
FROM table;

SELECT column
FROM table
ORDER BY column
LIMIT 1;

When there is an index, both queries just take the first entry from the index.

When there is no index, MIN() does a single pass through the table, and ORDER BY sorts only as much as is needed for the LIMIT, which ends up the same effort. (Older versions of SQLite did sort everything.)


Nothing is written to log in log_directory() for a PostgreSQL cluster Running PostgresSQL 11.4 on Windows 10

I made a DB cluster in  C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1

I registered that cluster to start on as a windows service on startup:

pg_ctl register -D C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1 -N PostgreSQLdb1 -U WindowsUser -P WindowsPassword  -S a

And the Where to log In C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1\postgresql.conf I changed the log parameter to a absolute location.:

# - Where to Log -
log_destination = 'stderr'      # Valid values are combinations of
          # stderr, csvlog, syslog, and eventlog,
          # depending on platform.  csvlog
          # requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on      # Enable capturing of stderr and csvlog
          # into log files. Required to be on for
          # csvlogs.
          # (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'C:\Users\WindowsUser\AppData\Roaming\postgresql\Db_1\log'
                      # directory where log files are written,
          # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
          # can include strftime() escapes

However, the cluster database does not write anything to log. The main PG database is located in a totally different directory, and that database does write to its log file.

Both databases works just fine, except the log part.

I have have even restarted my PC, but it still does not write to log.

Ok, so I did not get the Log files to work. But instead I got the Windows event log to work.

In postgresql.conf I set these line uncommented with those options:

log_destination = 'eventlog'        # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is only relevant when logging to eventlog (win32):
# (change requires restart)
event_source = 'PostgreSQL'

log_statement = 'all'    # none, ddl, mod, all

To register The Event log to Windows, I followed these instructions
I entered this command: REGSVR32 "C:\Program Files\PostgreSQL\11\lib\pgevent.dll" and a pop-up dialog confirmed that the pgevent.dll was registerd to REGSVR32
Then I opened Windows Event Viewer
On the right hand side, I chose Create custom view and got this pop-up dialog. I just filtered by the event source.

Then i get a neat list of events.


Values larger than 1/3 of a buffer page cannot be indexed I am not very good with DB so please bear with me.

I am trying to put a very long JSON data to a table, this table was created by Django framework.

I am using Postgres on Heroku. So, when I try to put the data I get the following error:

File "/app/.heroku/python/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.OperationalError: index row size 3496 exceeds maximum 2712 for index "editor_contentmodel_content_2192f49c_uniq"
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

My DB and table looks something like this:

gollahalli-me-django-test::DATABASE=> \dt
                      List of relations
 Schema |            Name            | Type  |     Owner
--------+----------------------------+-------+----------------
 public | auth_group                 | table | ffnyjettujyfck
 public | auth_group_permissions     | table | ffnyjettujyfck
 public | auth_permission            | table | ffnyjettujyfck
 public | auth_user                  | table | ffnyjettujyfck
 public | auth_user_groups           | table | ffnyjettujyfck
 public | auth_user_user_permissions | table | ffnyjettujyfck
 public | django_admin_log           | table | ffnyjettujyfck
 public | django_content_type        | table | ffnyjettujyfck
 public | django_migrations          | table | ffnyjettujyfck
 public | django_session             | table | ffnyjettujyfck
 public | editor_contentmodel        | table | ffnyjettujyfck
(11 rows)

gollahalli-me-django-test::DATABASE=> \d+ editor_contentmodel
                            Table "public.editor_contentmodel"
  Column   |           Type           | Modifiers | Storage  | Stats target | Description
-----------+--------------------------+-----------+----------+--------------+-------------
 ref_id    | character varying(120)   | not null  | extended |              |
 content   | text                     | not null  | extended |              |
 timestamp | timestamp with time zone | not null  | plain    |              |
Indexes:
    "editor_contentmodel_pkey" PRIMARY KEY, btree (ref_id)
    "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id)
    "editor_contentmodel_ref_id_8f74b4f3_like" btree (ref_id varchar_pattern_ops)

It looks like I have to change "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id) to take md5(content)

Can anyone help me with this? I have no clue on how to do it.

Update:

JSON content - https://gist.github.com/akshaybabloo/0b3dc1fb4d964b10d09ccd6884fe3a40

Update 2:

I have created the following UNIQUE index, what should I remove in this?

gollahalli_me_django=> create unique index on editor_contentmodel (ref_id, md5(content::text));
CREATE INDEX
gollahalli_me_django=> \d editor_contentmodel;
        Table "public.editor_contentmodel"
  Column   |           Type           | Modifiers
-----------+--------------------------+-----------
 ref_id    | character varying(120)   | not null
 content   | jsonb                    | not null
 timestamp | timestamp with time zone | not null
Indexes:
    "editor_contentmodel_pkey" PRIMARY KEY, btree (ref_id)
    "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id) <---- 1
    "editor_contentmodel_ref_id_md5_idx" UNIQUE, btree (ref_id, md5(content::text))
    "editor_contentmodel_ref_id_8f74b4f3_like" btree (ref_id varchar_pattern_ops) <----2

Should I remove 1 or 2 (See the arrows)?

You have an UNIQUE index on (content, ref_id), called editor_contentmodel_content_2192f49c_uniq

"editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id)

I'm not sure why this there to begin with. So let's step back and address what this does. This makes sure that content, and ref_id are unique. However, in PostgreSQL the UNIQUE constraint is implemented with a btree which makes this a poor solution. Using this method, you're creating a btree with content which essentially duplicates the size of this small table, and makes for a gigantic index. A gigantic index that is still limited by content size though -- as you've found. It raises a few questions

Do you care that content is unique? If you do care that content is unique for ref_id, then what you probably want is to store the hash of the content. Something like..

CREATE TABLE foo ( ref_id int, content text );
CREATE UNIQUE INDEX ON foo (ref_id,md5(content));

This will instead store the md5sum of content on the btree. So long as ref_id has content with a unique md5 over that ref_id, you're good.
If you don't care that content is unique consider removing it entirely.

It may be worth nothing that when you implement a UNIQUE constraint with a btree (as PostgreSQL does), you get an added index for free. Under a normal circumstance this has a fringe benefit.

CREATE TABLE foo ( ref_id int, content text );
CREATE UNIQUE INDEX ON foo (ref_id,content);

Will speed up the query

SELECT *
FROM foo
WHERE ref_id = 5
  AND content = 'This content'

However, when you chance it to use the functional md5() variant there is no longer an index on content, so now to use that index you'll have to

Only query on ref_id,
Add to ref_id a clause that md5(content) = md5('This content')

The whole text = text is over rated. That's almost never what you want. If you're looking to speed up query-time over text the btree is pretty useless. You likely want to look into

pgtrgm
text_pattern_ops
Full text search (FTS)

UPDATE 1

Base on your JSON I would suggest storing it as a jsonb, and then creating the index on md5(content); so perhaps rather than the above instead run this.

ALTER TABLE public.editor_contentmodel
  ALTER COLUMN content
  SET DATA TYPE jsonb
  USING content::jsonb;

CREATE UNIQUE INDEX ON foo (ref_id,md5(content::text));

UPDATE 2

You ask which indexes you should remove

gollahalli_me_django=> create unique index on editor_contentmodel (ref_id, md5(content::text));
CREATE INDEX
gollahalli_me_django=> \d editor_contentmodel;
        Table "public.editor_contentmodel"
  Column   |           Type           | Modifiers
-----------+--------------------------+-----------
 ref_id    | character varying(120)   | not null
 content   | jsonb                    | not null
 timestamp | timestamp with time zone | not null
Indexes:
    "editor_contentmodel_pkey" PRIMARY KEY, btree (ref_id)
    "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id) <---- 1
    "editor_contentmodel_ref_id_md5_idx" UNIQUE, btree (ref_id, md5(content::text))
    "editor_contentmodel_ref_id_8f74b4f3_like" btree (ref_id varchar_pattern_ops) <----2

Here is the surprising answer: you should remove all of them except: editor_contentmodel_pkey which says that all ref_id's need to be unique.

editor_contentmodel_content_2192f49c_uniq this index makes sure that you're UNIQUE on ref_id AND content, but if you can't have a duplicate ref_id you can never have a duplicate content for that ref_id. So you can never violate this index without also violating editor_contentmodel_pkey. That makes it pointless.
editor_contentmodel_ref_id_md5_idx this index is also pointless for the same reason. You can never have a duplicate md5(content::text) over ref_id because regardless of what the value of md5(content::text) is you can never have a duplicate ref_id.
editor_contentmodel_ref_id_8f74b4f3_like is also a bad idea because you're duplicating the index over ref_id. This isn't useless, it's just not-optimal. Instead, if you need a varchar_pattern_ops use it instead over just the content field.

As a last note, we don't much use varchar in PostgreSQL because it's implemented as a varlena with a check constraint. There is no gain to it, and there is nothing lost when you simply use text. So unless there is a concrete reason why ref_id can ever be 120 characters but it can be 119 characters, then I would simply use the text type.

UPDATE 3

Let's go back to your earlier problem..

psycopg2.OperationalError: index row size 3496 exceeds maximum 2712 for index "editor_contentmodel_content_2192f49c_uniq"

This is telling you the problem is specifically with index "editor_contentmodel_content_2192f49c_uniq". You've defined that as

"editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id)

So the problem here is that you're trying to create an index over content. But, again, the index itself stores the actual json content of content, and that's what exceeds the limit. This isn't actually an issue, because even if that limit wasn't in place editor_contentmodel_content_2192f49c_uniq would be totally useless. Why? again you can't add more uniqueness to a row that is already guaranteed to be 100% unique. You don't seem to be getting this. Let's keep it simple.

ref_id | content
1      | 1
1      | 1
1      | 2
2      | 1

In the above a lone unique index/constraint (with no other indexes) over (ref_id, content) makes sense because it would stop the duplication of (1,1). An index over  (ref_id, md5(content)) would also make sense because it would stop the duplication of (1,1) by proxy of stopping the duplication of (1, md5(1)). However all of this works because in the example I've given ref_id is NOT guaranteed to be UNIQUE. Your ref_id is not this ref_id. Your ref_id is a PRIMARY KEY. That means it is guaranteed to be UNIQUE.

That means the duplicate (1,1) and the row of (1,2) could NEVER be inserted. That also means that indexes over anything in addition to ref_id can not guarantee more uniqueness. They would have to be less strict than the index you currently have. So your table could only look like this

ref_id | content
1      | 1
2      | 1


Find common value amongst rows I have the following table structure with example values:

Number      Reference                 Length                                  Quantity
----------- ------------------------- --------------------------------------- ----------
16002983    ASH 14                    383.000000                              4
16002983    ASH 14                    1669.000000                             4
16002983    GLAS-NP                   403.000000                              1
16003002    SZR 16                    1402.000000                             1
16003002    Energy Surcharge F        56556.680000                            1
16003256    SZR 14                    726.000000                              1
16003256    Energy Surcharge F        40706.820000                            1
16003256    7283-ASB                  726.000000                              1
16003308    ASH 14                    740.000000                              4

The table has, as of now, 742 entries and expands every day.

I need to find a Reference that is shared among all Numbers, if there is any.

How? I don't even know if I need JOINs, GROUP BY, something else? I seem to fail at logic here.

Any help appreciated.

Group by reference, count distinct numbers in each group and compare that to the total count of distinct numbers in the table:

SELECT
  Reference
FROM
  dbo.atable
GROUP BY
  Reference
HAVING
  COUNT(DISTINCT Number) = (SELECT COUNT(DISTINCT Number) FROM dbo.atable)
;


Dirty read or phantom row We are having dirty reads or phantom rows in our data warehouse. My question is not how to solve it (it is a long story and I am currently working on it), but I want to know what exactly happens to better understand the behaviour and use the correct terminology. Are these dirty reads or phantom rows?
Let's say we have SourceTableA in the ERP database with a CLUSTERED UNIQUE index on Col1 and Col2. The DWH extraction process that performs the following query:
SELECT Col1, Col2, Col3
FROM ERPdatabase..SourceTableA WITH(NOLOCK);

This is not recommended, because now we can even have dirty reads, but we are forced to do this by the ERP vendor. The vendor knows the implications and uses READ UNCOMMITTED for all their transactions and forces us to use the NOLOCK hint.
Say we have RowA in SourceTableA:
('GreatCompany','WonderfullItem','ColorRed')

Now (I think...) a user session in the ERP system performs an update on RowA while the previous query is running like this:
UPDATE SourceTableA
SET Col3 = 'ColorBlue'
WHERE Col1 = 'GreatCompany'
AND Col2 = 'WonderfullItem'

If I am correct or not, what I see happening is that 2 rows are being read by the extraction process. I am not surprised since we use NOLOCK (although I am unsure if I understand the exact cause, hence this question), but I have 2 questions about this:

Since the row is not moved around in the CLUSTERED index, because the primary key does not change, why is it read a second time? My thinking would be that the extract process is already past that row, so it should not encounter it anymore. Or is that not how it works? The only explanation I can think of is that the extract query is not using the CLUSTERED index to read the data, but maybe a NONCLUSTERED index where the row IS moved around. Or what else am I missing? Or is it just a dirty read? Or is it just 'SQL Server does not guarantee row order unless you use an ORDER BY, DISTINCT or do not use NOLOCK'? Then I am curious why exactly. :-)

Regarding dirty reads, I always thought the behaviour I see were phantom rows, but, to my understanding, for a phantom row to occur we actually need 2 statements within the same transaction that read the same set of data. That is not the case here since it is only 1 statement, so implicitly I think that this should then be a dirty read? On the other hand, for a dirty read to occur we need an update to be rolled back by another transaction. That is not what I assume is happening. Or is that actually the only explanation?

You are correct in your initial assumptions here. If the row don't move (including due to a page split), then a scan/seek won't read it twice. But, as you say, perhaps the query is done through an NC index for which the row index was moved, hence the double read.

Regarding terminology, there are some standard phenomena defined in ANSI SQL, which are then used in conjunction with isolation level to describe the isolation levels. I.e., isolation level A eliminates phenomena X and Y but not Z. Such phenomena include dirty reads (read something that has been modified but later might be rolled back), phantom (read some data, read it again and a new row occurs in that set). Double read isn't among those phenomena defined in ANSI SQL, perhaps since the standard is too posh to bother about physical implementations (like indexes) and in such a world encountering the same row twice isn't even on the table? I.e., what you describe here is what we in the SQL Server community use to call something like "double read", but don't expect to see the terminology formalized for this phenomena.

(I realize that I probably over/misuse the word phenomena here, my bad in that case!)
Missing committed rows or encountering them multiple times due to an allocation-ordered scan over changing data is specific to using read uncommitted isolation (enabled by the NOLOCK hint here). See also Previously committed rows might be missed if NOLOCK hint is used by Microsoft's Lubor Kollar.
As David Browne suggested you could consider reporting off of a database snapshot - if you can’t use a an isolation level that produces correct results.
One can also consider ultra low-tech solutions. I'm thinking backup and restore, and then report off the restored database. You can combine full backups with log backups. This is more feasible when the database isn't too large, of course.


Role or permission to view but not create SQL Agent jobs The Microsoft SQL Server database role SQLAgentReaderRole apparently lets a member create SQL Agent jobs along with viewing jobs, job steps, and results.

We need to allow a user to see all SQL agent jobs, steps, schedules, and results, but not create any jobs.  This can almost be provided using several different views, but the results are messy and hard to read -- I want to be able to see the SQL Agent node in Management Studio and verify things.

Is there any role or permission that would allow this?  Let's assume SQL 2008 through SQL 2016.  Thanks.

Seems like this should work:

  The "reader" name is definitely misleading. This may be stating the
  obvious, but to get around this I've granted the SQLAgentReaderRole
  role and explicitly denied EXECUTE on add job sprocs in msdb for the
  logins with the role.

DENY EXECUTE ON [dbo].[sp_add_job] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobschedule] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobserver] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobstep] TO [YourLogin] GO
DENY EXECUTE ON [dbo].[sp_add_jobstep_internal] TO [YourLogin] GO


forcing innodb recovery of a corrupted database A poorly designed script selected records from two existing tables and added records to a newly created table for 24 hours until I killed it by terminating the MySQL 5.6 command line client and restarting the server.  I estimate that the script inserted over 70 million rows into the new table.  The result is that service.msc will not start the MySQL service.  I took various steps to address this problem, including deleting the log files and the myservername.err file, then restarting the server.  The new myservername.err file contains a long stream of repetitive warnings which state that I need to force an innodb recovery because the database is corrupted.  How can I accomplish this?

I want to:

1.) start the corrupted database, and then
2.) do a rollback to the status of the database before the script was run.

I could either select a time point, or simply delete the newly created table because the only activity in the database since things worked well has been the running of the poorly designed script.  What steps do I need to take to restart the corrupted database and then rollback the database to its precorrupted state?

I uploaded the error log to a file sharing site.  You can read it by clicking on this link.

What I imagine is that I need to add the following line to my.ini, immediately after the [mysqld] line:

[mysqld]
innodb_force_recovery=?

But what number do I use instead of ??  And then what code do I use to do the rollback?  Do I simply write drop table new_table_name?  Or do I do a rollback command to a specific date?  (I moved the log files to a different folder, so would I need to reimport the log files?)  What would this look like in code?

EDIT:

I finished mysqldump, then moved the contents of the /data directory into a backup location.  I then moved /data/MySQL subdirectory contents back into the /mysql subdirectory of the otherwise empty /data directory.  But when I try to start the MySQL windows server, I get error 1067.  I uploaded the contents of the newly created myservername.err file to a file sharing site.  Here is the link to view the error log.

InnoDB complains about wrong LSN because ib_logfile-s were deleted. You shouldn't have deleted those. But MySQL can survive after that. Next time InnoDB updates a page it will overwrite its LSN in the header and the error message will go away. Just wait till it starts (the error log shows neither successful nor unsuccessful start).

To get 100% clean tablespace you need to start MySQL with innodb_force_recovery=4, take mysqldump and restore it on a fresh instance of InnoDB (by fresh I mean you have to delete ibdata1, and all databases directories).

UPDATE:

At this point MySQL is started with innodb_force_recovery=x (x != 0)

Take dump of all databases:

# mysqldump --skip-lock-tables -A > alldb.sql

Check where MySQL keeps its files(in my case it's /var/lib/mysql/):

# mysql -NBe "SELECT @@datadir"
/var/lib/mysql/

Stop MySQL

# mysqladmin shut

Move old MySQL files to safe place

# mv /var/lib/mysql /var/lib/mysql.old

Create new system database

# mkdir /var/lib/mysql
# mysql_install_db

Start MySQL

# /etc/init.d/mysql start

Restore the dump

# mysql < alldb.sql

Restore may take long time if the database is big.
Another trick may work in that case. Run ALTER TABLE ... ENGINE INNODB on each InnoDB table. It will rebuild all InnoDB indexes and thus the errors will go away.


What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time? According to the docs:

  CONCURRENTLY
  Refresh the materialized view without locking out
  concurrent selects on the materialized view. (...)

  ... OTHER CONTENTS ...

  Even with this option only one REFRESH at a time may run against any
  one materialized view.

I had a function that checked the last refresh time for a MATERIALIZED VIEW and, if more than 60 seconds had passed, it would to refresh it.

However, what would happen if I try to refresh a materialized view from two separate processes at the same time? would they queue or would they raise an error?

Is there a way to detect when a MATERIALIZED VIEW is being refreshed and therefore avoid touching it?

Currently, I have resorted to populate a table record before refreshing (setting refreshing to true) and then setting it to false when the process has finished.

EXECUTE 'INSERT INTO refresh_status (last_update, refreshing)
         VALUES (clock_timestamp(), true) RETURNING id') INTO refresh_id;
EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY my_mat_view';
EXECUTE 'UPDATE refresh_status SET refreshing=false WHERE id=$1' USING refresh_id;

Then, whenever I call this procedure, I check the most recent last_update and its refreshing value. If refreshing is true, then don't try to refresh the materialized view.

EXECUTE 'SELECT
           extract(epoch FROM now() - (last_update))::integer,
           refreshing
         FROM refresh_status
         ORDER BY last_update DESC
         LIMIT 1' INTO update_seconds_ago, refreshing;

IF(updated_seconds_ago > 60 AND refreshing = FALSE) THEN
  -- the refresh block above
END IF;

However, I'm not sure the refreshing flag is being updated synchronously (I mean, it really waits for the refresh to actually be complete)

Is this approach rational or am I missing something here?

As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE conflicts with ... EXCLUSIVE", meaning that another REFRESH MATERIALIZED VIEW CONCURRENTLY statement, which requests the same EXCLUSIVE lock, will have to wait until the earlier EXCLUSIVE lock is released.

If you want to avoid waiting for this lock for an undefined period, you may want to set the session variable lock_timeout to a sensible value.


Changing to compatibility level 150 makes SQL Server consume all available memory We're moving an existing DB to a new SQL Server 2019 instance.

If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available memory and all queries time out with an error that says

  There is insufficient system memory in resource pool internal to run this query

The RAM is completely consumed until no query can be run (all end with the previous error I've mentioned). We've tried with 16, 32 and 64GB of RAM.

Can anyone give any tips on how to debug this?

Notice that it's not a query issue...just changing the compat level is enough to make SQL start exhausting the memory.

Even running a simple scalar function (which internally uses several Substring and cast calls - no table or view selects) is enough to make it consume all the available memory.

You're encountering a known bug with scalar UDF inlining.  You can disable inlining using one of the methods here (or by using a lower compat level, as you've discovered yourself):

Disabling Scalar UDF Inlining without changing the compatibility level

Or install CU2 for the permanent fix.

  ...even running a simple scalar function (which internally uses several Substring and cast calls - no table or view selects) is enough to make it consume all the available memory...

Reading this part of your issue reminded me of this really interesting blog post from sqL_handLe:

SQL Server 2019 Scalar UDF inlining - OOM in some cases

He demonstrates a scalar UDF that doesn't do any data access, but uses up all the memory on the box (even one with almost a TB of RAM):

  Not just crashing itself.  Crashing any other query on the instance that was trying to allocate memory (eg stealing against a query memory grant).

This should be fixed in SQL Server 2019 CU2, it looks like this is the KB for it:

FIX: Scalar UDF Inlining issues in SQL Server 2019

  This cumulative update includes several fixes across the following areas for scenarios where a query that uses Scalar UDF Inlining may return an error or unexpected results:

  ...
  Out of memory conditions and memory leaks due to very large scalar UDFs
  ...

If you're unable to install CU2 for some reason, you can use the newer compatibility level as long as you disable scalar UDF inling at the database level like this:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Or at the query level, as described in the docs article I linked to earlier.


show con_name not working in SQLDevelopper I typed show con_name; in sqlDeveloper but the result is the following error : SP2-0158: unknown SHOW option "con_name" ... Other commands like Show user;  work perfectly. I tried in sqlplus and no problem there.

Any idea of what's happening here ?

ps : I'm using Oracle 12c on Linux

SHOW is a SQL*Plus command, but SQL Developer does support all of the SQL*Plus commands

you run them through the 'script engine' which is done using F5 or the 2nd execution button on the toolbar

you will need to be on a newer version of SQL Developer as we've vastly increased the amount of SQL*Plus support we offer


Pre Caching Index on a large table in PostgrSQL I have a table with about 10mln rows in it with a primary key and an index defined on it:

create table test.test_table(
    date_info date not null,
    string_data varchar(64) not null,
    data bigint
    primary key(date_info, string_data));
    create index test_table_idx
    on test.test_table(string_data);

I have a query that makes the use of the test_table_idx:

select distinct date_info from test.test_table where string_data = 'some_val';

The issue is that first time around it could take up to 20 seconds to run the query and < 2 seconds on any subsequent runs.

Is there a way to pull load the entire index into memory rather then have DB load information on first access?

You could use the additional module pg_prewarm. Has to be installed once per database. See:

PostgreSQL: Force data into memory

It can "prewarm" tables as well as indexes. To do it for your index:

SELECT pg_prewarm('test.test_table_idx');

Unless you get index-only scans (which you do not with the index at hand), you might want to prewarm the table as well:

SELECT pg_prewarm('test.test_table');

There are more parameters to narrow down what and how to prewarm. Follow the links.

This is costly and the system might be better off using the cache for something else. If you know the exact query ahead of time and it's a SELECT without side effects, you might just run the query to "prewarm" relevant data pages of index and table.

Aside, you might be better off rearranging PK and index like this:

    ...
    primary key(string_data, date_info);
    create index test_table_idx on test.test_table(date_info);

Now, the PK index can give you index-only scans for the query at hand. Might make a substantial difference. See:

Is a composite index also good for queries on the first field?
Can Postgres use an index-only scan for this query with joined tables?


MySQL Finding index size for compound unique indexes I'm using following query to get index and data sizes for tables in database. But unfortunately it doesn't show index size for compound unique indexes. How can i get index sizes for compound unique indexes?

Here is my query;

SELECT TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows",
CONCAT((FORMAT((DATA_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Data Size",
CONCAT((FORMAT((INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Index Size",
CONCAT((FORMAT((DATA_LENGTH+ INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Total Size",
TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = "my_database_name_here"

For MySQL (not Percona), see if this table gives you what you want:

mysql.INNODB_INDEX_STATS

Sorry, but it is not easy to tease out the specific data you asked about.


Can I open a SQL Server 2008 R2 database in SQL Server 2008 R2 Express? This question has probably been asked a dozen times but I couldn't find them here or at SO.

Can I attach/restore a SQL Server 2008 R2 database to my local SQL 2008 R2 Express instance?

Not if you used any persisted Enterprise features. If you used any of such there will be an entry in sys.dm_db_persisted_sku_features and when trying to open/attach/restore this database on any edition lower than Enterprise you will gen an exception and the database won't open. The list possible of persisted SKU features are:

  Compression. Indicates that at least one table or index uses data
  compression or the vardecimal storage format. To enable a database to
  be moved to an edition of SQL Server other than Enterprise or
  Developer, use the ALTER TABLE or ALTER INDEX statement to remove data
  compression. To remove vardecimal storage format, use the
  sp_tableoption statement.

  Partitioning. Indicates that the database
  contains partitioned tables, partitioned indexes, partition schemes,
  or partition functions. To enable a database to be moved to an edition
  of SQL Server other than Enterprise or Developer, it is insufficient
  to modify the table to be on a single partition. You must remove the
  partitioned table. If the table contains data, use SWITCH PARTITION to
  convert each partition into a nonpartitioned table. Then delete the
  partitioned table, the partition scheme, and the partition function.

  TransparentDataEncryption. Indicates that a database is encrypted by
  using transparent data encryption. To remove transparent data
  encryption, use the ALTER DATABASE statement. For more information,
  see Transparent Data Encryption (TDE).

  ChangeCapture. Indicates that a
  database has change data capture enabled. To remove change data
  capture, use the sys.sp_cdc_disable_db stored procedure.

In addition, for Express edition specifically, the database must be smaller than the maximum database size supported by Express Editions (10Gb). There could be complications if you used any of the service pack specific options like sp_db_increased_partitions but I doubt that's the case.

If your database is clean of all of the above then Express will open it w/o problems. If the source is a Standard Edition then for sure it won't have any of the persisted Enterprise features and the only issue is size.

You need to make sure the SQL Server is the same major release (SQL Server 2008R2, not SQL Server 2008). The patching level (service pack and cumulative updates applied) does not matter as the database format cannot change between minor releases, but it would be recommended to have the Express patched to the latest SP and have the latest CU applied.


one to many without foreign key I know this is kind of a beginner question.  I am maintaining legacy code and data and just have to deal with the way things are  But if you were to design a new database from the start - is there any legitimate reason to create two tables with a one-to-many relationship in Microsoft Sql and not use a Foreign Key?

We currently are not in some tables and the reasons to not implement them now make sense , but none of the reasons are really sufficient to explain why the keys were not implemented originally

There are several legitimate reasons not to actually create a FOREIGN KEY constraint on a foreign key relationship:

In some cases, you may not be able to - for example, if the other table is in another database.
If your database was migrated from another environment, you may already be maintaining the relationship via triggers. In the best of all possible worlds, you'd switch to an actual foreign key constraint; however, when you have something that's already working, making changes that don't add anything of immediate and obvious benefit is a hard sell with your bosses.
Due to complex business rules, you already have to maintain the relationship with a trigger. You might be able to have the foreign key relationship defined as well, but having it defined in part in two different places is not necessarily a good idea.
Extremely frequent data load tasks that can be done faster/easier without foreign keys being on. It's possible to add code to a script to drop and recreate your foreign keys (or disable and enable, whichever), but if this is happening on a daily basis, and is the source for most of your data, there may not be much of a point in actually having the foreign keys.

Note: In all the above cases, I would have something in place (usually triggers) to maintain the foreign key relationship properly.

There are also less legitimate reasons not to have actual foreign key constraints in place:

The database was migrated from an older system that did not have foreign keys, or where using them was impractical;
The system's developer didn't know what they were;
The system's developer did know what they were, but didn't understand how to implement them properly, and found that they caused errors (because the system didn't bother to create ways to add new values to the other table, etc.).

In the first of these cases, other mechanisms (triggers, application code, etc.) may be in place already to maintain relational integrity, and adding the foreign key could again leave you with two different mechanisms in use at once, which can make things very messy.

In the latter two cases, the biggest impediment to adding the foreign key constraints immediately is that you will start having errors until you've ferreted out and fixed all the places where the original developer may have been ignoring nor bypassing the relational integrity.


Postgres RIGHT JOIN with custom array I'm using Postgres 9.1 and want to get a result with some blanks where there is no data.  My query looks like the following:

SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)

The ... is not important to this question, it's just important that it returns a result with the institution_ids in the array (1, 3, 4, 5, 7, 9) and it includes those institutions with no data. Here is an example of the current output:

days    treatments    institution_id
266    6996    4
265    5310    1
267    3361    5
260    2809    3
264    5249    7

An example of the output I want is:

days    treatments    institution_id
266    6996    4
265    5310    1
267    3361    5
260    2809    3
264    5249    7
               9

I know I can achieve this by using the following query:

SELECT *
FROM (
       SELECT institution_id
       FROM ...
       WHERE institution_id IN (1, 3, 4, 5, 7, 9)
     )
RIGHT JOIN generate_series(1,9) ON generate_series = institution_id
WHERE generate_series IN (1, 3, 4, 5, 7, 9)

However, this is extra work because generate_series(1,9) creates institution_ids I'm not interested in, it requires that I know the max institution_id a priori, and it introduces an unnecessary WHERE clause.  Ideally I'd like a query like the following:

SELECT *
FROM (
      SELECT institution_id
      FROM ...
      WHERE institution_id IN (1, 3, 4, 5, 7, 9)
     )
RIGHT JOIN (1, 3, 4, 5, 7, 9) ON generate_series = institution_id

Where (1, 3, 4, 5, 7, 9) is just an array that Postgres will use for the JOIN command.  I've also already tried [1, 3, 4, 5, 7, 9] and {1, 3, 4, 5, 7, 9} both to no avail.

Any ideas?

You may do an OUTER JOIN directly with a list of values, like this:

SELECT v.id,tablename.* FROM tablename RIGHT JOIN (values (1),(3),...,(9)) as v(id)
  ON (tablename.field=v.id)


Moving large databases I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space).

Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need to know the safest way of copying the databases over to a new server.

All advice appreciated!

You can use following steps

On old Server

1.Stop mysql server

2.Copy contents of datadir to another location on disk as ... mysqlbackup

3.Start mysql server again

4.Compress the data (tar -czvf mysqlbackup.tar.gz mysqlbackup)

5.Copy the compressed file to new server

On New Server

1.Install MySQL [MySQL version should be same as of old server](don't start MySQL server)

2.Unzip compressed file (tar -xzvf mysqlbackup.tar.gz)

3.Move contents of mysqlbackup to the datadir.

4.Make sure that permissions of datadir are correct

5.Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (MySQL will generate these)

6.Start MySQL.

You can also look at How can I move a database from one server to another?


Affect scoping in SQL stored function The condition staff.staffId = staffId in the following stored function apparently always evaluates to true because staffId is scoped to the (inner) column in table staff instead of the (outer) argument of the function endpoint_organizations.

CREATE OR REPLACE FUNCTION endpoint_organizations (staffId INTEGER)
RETURNS TABLE (organizationId INTEGER, name TEXT)
AS $$
  SELECT organizations.organizationId, name
  FROM organizations
  INNER JOIN staff
  ON staff.organizationId = organizations.organizationId
  WHERE staff.staffId = staffId
$$ LANGUAGE SQL;

Is there are way for affecting the scoping in a concise way that does not require changing the name of the argument e.g. into staffId1. I've tried changing the condition into (staff.staffId AS staffId1) = staffId, but that does not seem to work.

Quote from the manual

  You can also set the behavior on a function-by-function basis, by inserting one of these special commands at the start of the function text:

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

But for readability (and maintainability) I would strongly suggest to rename the variable to something different.

Many people simply use a prefix for the parameters (e.g. p_staff_id) and also prefixes for local variables (e.g. l_some_var) to make sure they never clash with each other or column names (this is also the de-facto standard in the Oracle world)


how to change default language of SQL server management studio 2008R2? I tried to change default language to french by using the below query.

USE ssidps;
GO
EXEC sp_configure 'default language', 2 ;
GO
RECONFIGURE ;
GO

but SELECT @@language gives "us_english" always.

My aim is to change my onshore system's default language from korean to english. All error/warning messages are coming in korean when query failed. And i couldn't catch that message as i developed in English.

Its working in the current session by the query "set language french" but not for the database.
I asked the same in stackexchange but i was referred to this site.
https://stackoverflow.com/questions/19654101/how-to-change-default-language-of-sql-server-management-studio

Using SSMS :

To do so, open SQL Server Management Studio (SSMS) > Right click Server in Object Explorer > Properties > Advanced > Check the ‘Default Language’ property and make sure it is set to the one you want.

using TSQL:

Look up the sys.messages catalog view to check if SQL Server supports a message in your local language.

SELECT msg.language_id, lang.langid, alias
FROM
sys.messages AS msg
JOIN
syslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY  msg.language_id, lang.langid, alias

then once you know the langid use below tsql

EXEC sp_configure "default language", n  -- n = `langid` that you want to set
RECONFIGURE WITH OVERRIDE;

note: you have to restart SQL server and  this is an instance wide setting.


How to copy data from table to another and automatically change the values to match the other's data type msql I want to copy data from table column to another and automatically change the values to match the other's data type.

That is; in one table, a column is "Gender" and the values are Male & Female for every row. Now, while transferring it to the other table, I want "Male" to change to "1", "Female" to "2". I can't do it manually cause it's a huge table.

Based on the information and SQL supplied, you would need to amend the statement as follows:

insert into table2 (
        name, state, dob, gender)
select
    name, state, dob,
    case gender
        when 'Male' then 1
        when 'Female' then 2
     end
from table1

Remember that NULL values will obviously be ignored.

MySQL CASE syntax

Since you will always only have one of two values in this query, you can also of course use the IF operator, depending of course on how you want to handle NULL values.

select name, state, dob, if(gender='Male',1,2)
from table1

or

select name, state, dob, if(gender='Male',1,if(gender='Female',2))
from table1


How to determine how much storage to store backups? I was thinking about this question ( Here where I work, we have a lot of space so backups are dayli ok ), and i'm thinking about if I was hired in another company, and see that they don't have backups, because there's no space.

How can I say " So, we need xTB of storage for the backups".?

There are some questions here and stackoverflow saying to use sp_spaceused to get an approach. But I think this is wrong because I made 3 tests, and all of them are wrongs. ( In one test, My database has 257MB of size, and reserved space on sp_usedspaceis 109.064 KB. When I create a backup, the backup has 24.729kb).

I believe in order to get the calculations correct to somewhat close to an appropriate figure would be collecting a data or a trend analysis as mentioned by Kin. There are various methods available to gather such data:

One approach would be analyzing a trend for the size of backup that has happened with its history being retained in msdb backup set from below query:

SELECT
[database_name] AS "Database",
DATEPART(day,[backup_start_date]) AS "Day",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'Databasename'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(dd,[backup_start_date]);

As you mentioned normal full backups, above should be OK, but it could be tedious if you have quite a large no of databases. But i believe you can come up with query on same with help of sp_foreachdb. Also, make calculations based on the factor of retention period for the backups on that drive accordingly.

But the one which i have been using is this excellent article from Chad miller on Database Space Capacity Planning which with help of a Powershell and SQL script might do the trick by gathering the data say for next 15 days or a month to help you in deciding or predicting the Vol space for that particular drive ( in my case i have a dedicated backup drive, so this works good for me)

As said

  You can use the process described in the article to create a
  consolidated space forecasting report, which focuses on a "days
  remaining" metric. In addition, the use of PowerShell to collect data
  and load into a SQL table as demonstrated in this article, provides a
  solution you can easily adapt to many database administration problems


Combining nullif while checking if field is an integer with a case statement As part of an ETL process, I need to copy a varchar field in table1 (staging table) which is meant to contain an integer value to a tinyint field in table2 (production table). However I am having some issues when trying to combine the following two business requirements:

If the value of the source varchar field is blank, then it will be set as NULL in the target tinyint field. For that I would normally use NULLIF.
If the field contains anything that could not be cast as a tinyint value (e.g. punctuation, letters, or a value higher than 255) then the value will be set as 255 in the target tinyint field. The rationale behind this is that the tinyint field would never have a 255 value unless there is an error -- and it wouldn't be appropriate to have errors set as NULL or 0 because these are perfectly valid values and we wouldn't be able to differentiate between a valid entry and a mistake.

The problem I have is trying to combine NULLIF with some sort of case statement. I think that perhaps this is not the right approach. Any thoughts on this?

I believe Azure supports TRY_CAST.

  (Returns a value cast to the specified data type if the cast succeeds;
  otherwise, returns null.)

Here are three examples that seem to demonstrate what you're after:

--Column is blank, then NULL
DECLARE @YourCol1 VARCHAR(10) = ' '

SELECT CASE
        WHEN @YourCol1 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol1 AS TINYINT), 255)
        END AS YourCol1

--Column won't successfully CAST to TinyInt, then 255
DECLARE @YourCol2 VARCHAR(10) = 'abc'

SELECT CASE
        WHEN @YourCol2 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol2 AS TINYINT), 255)
        END AS YourCol2

--Column successfully CASTS to TinyInt, then actual value
DECLARE @YourCol3 VARCHAR(10) = '21'

SELECT CASE
        WHEN @YourCol3 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol3 AS TINYINT), 255)
        END AS YourCol3


check slave status innodb cluster On InnoDB cluster, I have tried to execute command 'show slave status' to get status of a slave.
But the system returns 0 row. So now I want to check the status of a slave, check which bin log file is reading on the slave for purging bin log on the primary server.
So how do I check the status of slave and delete applied bin log on primary safely?

MySQL InnoDB cluster relies on Group Replication, which provides the mechanism to replicate data within InnoDB clusters with built-in failover, and not on the asynchronous replication. However, whenever you join an instance to a cluster the distributed recovery process kicks-in, and this process uses the asynchronous master-slave replication until the instance finishes joining the group.

As stated on the manual: "Group Replication distributed recovery can be summarized as the process through which a server gets missing transactions from the group so that it can then join the group having processed the same set of transactions as the other group members. During distributed recovery, the server joining the group buffers any transactions and membership events that happen while the server joining the group is receiving the transactions required from the group. Once the server joining the group has received all of the group's transactions, it applies the transactions that were buffered during the recovery process. At the end of this process the server then joins the group as an online member.".

Concluding, as soon as the distributed recovery process is complete you can (if you really need) delete the bin logs on the primary safely.

For further information, please take a look a the InnoDB cluster userguide.

Cheers,

Miguel


Is it possible to upgrade Oracle 8i to 12c? We have an old server running Oracle 8i.

Is it going to be possible to directly update it to something newer like Oracle 12c, or would that require a complete uninstall and re-install?

If we had to completely uninstall and re-install, is it then a case of importing the database dump from Oracle 8i and it will be converted, or is that also a manual task?

Update

Thanks for everyones input. I have checked with our application vendor (the ones who originally installed Oracle 8i for us around 10 years ago) and their application that we use does work with Oracle 12c but they are not willing to do the update for us.

They have basically said that we should do the update, preferably on a new server and at that point they would be willing to get involved to check their application out using the new database version etc.

Is there some way to find Oracle experts capable of doing this job (preferably UK based)? I'm pretty new to Oracle (past experience is mainly Microsoft SQL Server), day to day management/security, scripting etc. is ok but I'd rather have someone in who has done this (many times?) before.

Thanks again!

There is no in-place upgrade possible but the existing exp utility is still supported for importing dmp files.

Some of the issues you need to research are:

I have taken an export from an 8i database and imported it into an 11g with no issues. Importing into 12 should not be a problem but planning is everything!
will you use the same character set?  Often organizations start with western only and need to use UTF16 for more compatibility with other languages.
do yourself a favour and do an install on a new machine with new hardware.  12 requires more of everything, memory, hard drive.
many 8i parameters are not supported so you need to establish if there are any custom settings that you need equivalents for

Of course this would need to be tested.


Query across schemas in PostgreSQL In PostgreSQL, I have a table which, due to sharding, exists across many schemas.  In sub-optimal cases, I need to execute a query on all of these schemas (due to an exception which violates the sharding interface.. but that's another story).  What is an efficient way to do this?  Assuming the tables have an index across the data that is interesting to this particular query (that is going across all schemas.)

A construct like the following should work for you:

select *
from (
  select *
  from schemaA.tableA
  union all
  select *
  from schemaB.tableB
  union all
  ...
)
where columnA = "value";

You'll have to check the execution plans to see if the optimizer is smart enough to push the where clause into the subquery for each table.

The other thing to remember is that you can generate your queries by querying the metadata for a list of schema and table names.


When Master is being auto vacuumed, Is slave gettting exclusive lock on its table to apply WAL? I've met the very curious pattern that triggers a lot of wait queue on slaves.
And I think I finally find it's because of auto vacuum. However, I've never got such description on official document nor any of blogs of PostgreSQL.
So I would really appreciate if someone knows this question.

I have a big table(I'll say, A table) on Master, and I run periodically a batch script on slaves and this batch calls a lot of SELECT queries on the A table.

One day, I found that on slave there are a lot of wait queues, and there were waiting on the SharedAccessLock on the A table. However, these queries are also SELECT query and so these queries should never wait any SELECT queries.

So I've searched around and I found that when slaves are being got lots of SELECT queries on the A table, and at the same moment Master is executing autovacuum on the A table. I stopped autovacuum on Master and kill the autovacuum process, then restart all the queries to slaves. After that wait queues disappeared.

Error logs say, these SELECT queries on slaves wait SharedAccessLock, however I am guessing that these SELECT queries waits the WAL apply. I know it's very odd to say like this thing. My PostgreSQL is 9.6ver.

If autovacuum finds a bunch of empty pages at the end of the table, it will try to truncate those pages away to return the space to the OS.  It will need to obtain an Access Exclusive lock to do this, and that lock is recorded in WAL and is replayed by and held by the replica server for the duration of the truncation (and replay of the truncation WAL).  hot_standby_feedback will not directly prevent this lock from being taken, although it may delay the pages from becoming empty (as the tuples won't be removed if they are still needed on the standby) but sooner or later they will need to go.

If you are truncating away a large amount of empty pages, this can take a while (but that performance was improved substantially in v10).  If the truncation on the master detects that it is blocking someone, it will stop the truncation before completion, leaving the rest of the empty pages to be truncated by some future vacuum.  But it cannot detect that it is blocking queries on the replica, so it cannot abort the truncation.  Instead of killing the autovac worker, you could have just opened a session on the master and taken a dummy lock on the table to force the autovac worker to yield gracefully.  The dummy lock does not need to be held for a prolonged period, it can be dropped as soon as it is acquired, and it can be a weak lock, even just Access Share.

As far as I know, this is the only situation in which autovacuum will take an Access Exclusive lock.


dropping SYNONYM is blocking in sql server? I have synonyms which points to a TestDB1, by the next refresh synonyms point to TestDB2. while re-pointing from TestDB1 to TestDB2 it will drop all synonyms and re-creates them for TestDB2.

Now my problems is: while users querying from synonyms which points to TestDB1 is still running it won't let me drop synonyms. It is blocking the process synonyms re-point to TestDB2.

It only let me re-point after queries complete. Is there any other process to overcome this problem. I want to re-point synonyms while users querying?

Note: Both databases contains same tables but data will be change

No, dropping a synonym requires a Sch-M lock, and also IX locks on sysschobjs/sysobjvalues, at least if it is on the same server (I haven't tested what happens when the synonym uses a 4-part name).

What exactly would it mean if you could change a synonym in the middle of a query or transaction? If you have a query plan operator or a cursor which needs to go back to the table, or you have multiple steps in a transaction that reference the table, it would violate all kinds of principles if the referenced table were different. I think it is much better that you can't change it until a point where nobody is referencing it.


Convert SQL Server 2012 SSIS package to 2008 I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.

Is there a way to convert the 2012 packages to a 2008 version?

If not, what are my alternatives? Start over?

Yes,  you'll need to recreate the package in 2008. 2005/2008 packages can be upgraded to 2012, but downgrading doesn't work.

There is a codeplex project/utility that can move 2008 packages to 2005 by changing version numbers, and making small changes to the XML. Unfortunately this approach doesn't work with 2012, as the package format went through some big changes.


Failed to allocate BUFs during DBCC CHECKDB I am writing to see if anyone can help me out with a problem that I just can't seem to figure out. This one is going to be a doosey and I am going to try and list out as much of the important info that I can but if I miss anything please let me know and I would be happy to provide whatever info you might need to be able to assist if you're willing?
The symptom that I am experiencing is that I have dbcc checkdb locking up when running against a VLDB (approx 1TB) as one of the tasks in a Maintenance Plan and the error log is reporting the error: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 7 (sometimes 8) and then  the error log starts filling up with memory charts (best way I can describe it, see attached screenshot) about physical and virtual memory.
So here is the scenario. We are currently beginning testing of our NEWSERVER before we migrate off our OLDSERVER. Everything is working as expected on our OLDSERVER. The problem is occurring on our NEWSERVER in the PROD instance during our nightly Maintenance Plan routine. There are multiple db's present in the instance but the one that we are concerned with is DB1. DB1 is made up of 2 data files and 1 log file. On the OLDSERVER the .mdf (519 GB) is located on H:, the .ndf (200 GB) is located on E:, and the .ldf (313 GB) on D:. On the NEWSERVER both data files are on E: and the log file is on D:. Note: I was not involved in the configuration of the database having 2 data files or their location, or the setup/configuration of either of the servers.
On the OLDSERVER the maintenance plan (consisting of a Check Database Integrity task, Full Database Backup, and a Maintenance Cleanup Task and is configured to run against DB1 only) completes nightly with no issues. On the NEWSERVER the maintenance plan (set up exactly the same way) will sometimes complete but is mostly slowing to a snail's crawl (or find something even slower than a snail) and will eventually fail during the Check Database Integrity task .
I can run the DBCC CHECKDB manually and sometimes it will complete in a very timely fashion but other times even the manual run will exhibit the same behavior. Not that I know if either of these settings are directly applicable, but I have tried with Lock Pages in Memory both turned on and off, no difference, and I have tried with Instant File Initialization turned both on and off, no difference.
Here are the details of the PHYSICAL server that we'll call NEWSERVER.

OS: Windows Server 2016 Standard - 6.3 (14393)
Processor Count: 32
Memory: 384 GB (382 GB usable)
Drives (All SSD configured with) : OS (C:) -181 GB free of 243 GB | Logs (D:) - 117 GB free of 488 GB | Data (E:) - 2.86 TB free of
3.81 TB | Backup (F:) - 1.44 TB free of 1.9 TB
SQL Server 2016 Standard - SP2 CU3 (13.0.5216.0)
Number of instances: 4 (PROD, DEV, TEST, TRAIN)
Max Memory Configuration per Instance: PROD (131072 MB) | DEV (65536 MB) | TEST (65536 MB) | TRAIN (32768 MB)

Here are the details of the PHYSICAL server that we'll call OLDSERVER

OS: Windows Server 2016 Standard - 6.3 (14393)
Processor Count: 24
Memory: 384 GB
Drives (Spindle & SSD Mix): OS (C: SSD) -163 GB free of 249 GB | Logs (D: 15k Spindle) - 197 GB free of 557 GB | ProdData (E: SSD) - 604 GB free of 865 GB | Backup (F: 10k Spindle) - 1.46 TB free of 2.18 TB | NonProdData (G: SSD) - 591 GB free of 1.08 TB | ProdData2 (H: SSD) - 231 GB free of 743 GB
SQL Server 2016 Standard - SP2 CU2 (13.0.5153.0)
Number of instances: 5 (PROD, DEV, TEST, TRAIN, PROD2)
Max Memory Configuration per Instance: PROD (131072 MB) | DEV (65536 MB) | TEST (65536 MB) | TRAIN (32768 MB) | PROD2 (32768 MB)

Attached are the first (almost 50) lines of the error log (I can provide more if needed.

Any help or ideas are much appreciated!

While I can't tell from the OLDSERVER data, it looks like NEWSERVER has at least 2 NUMA Nodes whereas I'm not sure if OLDSERVER has multiple NUMA Nodes or a single node.
Assuming, for the moment, that the OLDERSERVER only had a single NUMA Node, it does seem you're hitting a known issue which was fixed in SQL Server 2016 SP2 CU5, whereas you're currently on SQL server 2016 SP2 CU3.

An "Out of Memory" error can occur when a Database Node Memory (KB) becomes less than 2 percent of the target size, and it cannot discard database pages on the node anymore to get free pages.

If you look at the MEMORYSTATUS output, it does fall into this with about 1.59% of pages available, at least on NUMA Node 0.
I'd apply, at a minimum, SP2 CU5 and run the load again.


Create Trigger MySql update or insert in another table I am trying to learn more about triggers in mysql, so bear with me. I have two tables,

TestTable
 (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Date date, Value REAL)

TestTable2
 (id INTEGER PRIMARY KEY AUTO_INCREMENT, ItemId INTEGER,Year INTEGER, Month INTEGER, Open REAL, Close REAL, Increase REAL)

Where the table TestTable is measurements on a certain item on a specific date. TestTable2 contains measurements for a specific month,year and the increase during this period.

I want to create a trigger which updates or insert values into TestTable2 as I insert values into TestTable.

This is what the trigger i tried to create looked like,

CREATE TRIGGER 'monthUpdateTrigger' AFTER INSERT ON TestTable
BEGIN
IF NOT (EXISTS (SELECT 1 FROM TestTable2 WHERE
(ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date))))
THEN
    INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES
    (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0);
ELSE
    UPDATE TestTable2 SET Close=NEW.Close AND Increase=(NEW.Close/(SELECT
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date))
END IF;
END;

But this does not seem to work, I get the error,

  You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use
  near 'BEGIN IF NOT (EXISTS (SELECT 1 FROM TestTable WHERE
  (ItemId=NEW.ItemId AND Yea' at line 1

Can someone please point out the error/errors. Or offer a better solution to this problem.

First, you need to specified when do you want to run the trigger inside the table. I've edited your trigger and use AFTER UPDATE and AFTER INSERT:

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0);
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.


Live Partial Table / column replication I want to know if there is a way to achieve the following with SQL Server 2012 Enterprise Edition?

I have Table A: on Server 1 with say 10 columns.

Table A:
| col1 | col2 | col3 | coln | col10 |

on Server 2, I need a live copy of Table A in Table B on Server 2. However I only actually need 3 of the columns replicated, as that's all I use.

Table B:
| col1 | col3 | col10 |

I mentioned "live" above, By this I mean that when I insert a new value or update a new value in Table A that change will be propagated as soon as it can to Table B.

Table B is a read only table. And Table A gets data written to it. So A will be the publisher and B the subscriber.

How can I achieve this sort of setup?  In other words do I need to use some sort of replication? or can I achieve this with Always ON High Availability.

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick users out temporarily as you restore new logs. If log shipping the entire database is a concern, then you could instead use a trigger to populate another database on the same instance - which only has this table and the required columns populated - and then log ship that database to the other instance:

I have a blog post on making log shipping work much more like a set of readable secondaries:L

Readable Secondaries on a Budget

You can also look at these posts for an approach I've used to do something a lot like replication but with more control, less impact, and none of the troubleshooting nightmares:

Schema Switch-A-Roo
Schema Switch-A-Roo : Part 2

I also talk about some ways to provide read-only copies of the data for reporting at the end of this post:

Bad habits : Putting NOLOCK everywhere


SQL Server users and application users I have develop a web application in MVC4 it is a application about credit and financing. Application is used by 40 to 50 users at a time. Application reporting is very heavy takes 2 to 3 minutes in generating.

I am using single db user 'sa'

Now I am thinking to create all application users in server 2008 also.
Each application user will connect with own db user not with 'sa' user
my question is that is It right decision?

It will increase my application performance?  If it is not right then how i can increase its performance? as 40 users taking reports with single 'sa' user may be take more time instead off each user taking with its own database user.

First of all: DO NOT USE 'sa' account.

Create database roles for the application with appropriate permissions, reader, writer, executor etc, with only permissions the application requires.

Secondly, is there any reason this server is not on the Windows Domain? It is preferable to handle permissions via AD Security Groups and service accounts (and managed service accounts) rather than SQL accounts. The benefit is simplified management and increased security - Kerberos vs plain text passwords.

EDIT : as I have not answered your question. In your case the number of users is irreleveant as they transalate to number of requests and spawned workers/threads. You are asking database to serve 40 reports, no matter if they all come from 1 user, or 40 users asks for 1 report, the end result is still 40. In extreme situations it may be preferable to have less users to save resources and time on creating new connections as each takes tiny amount of memory but that is a whole different conversation.

Hope this helps.


SQL Server Express 2014 Installation directory I face recently a problem when starting SQL Server Management Studio. As it can't start, I uninstall it. When trying to install it again, I am asked to select the installation folder of SQL Server.

Unfortunately, nor the MSSQL folder, nor its Binn child solved my problem. Can somebody help get the right folder ?

Edit :
I've succeeded installing SSMS again, but I still can not run it. It is showing this message :

Finally, I cannot guess the source of the problem.

That dialog asks you for the installation media, pay attention. You need to specify the location from where you've run the original install. That is not an Express uninstall, you are uninstalling a licensed SSMS, so you should provide your original, licensed, CDs.


Deleting 200M+ rows from InnoDB table Basically, we have a table with the following structure:

CREATE TABLE `reqs` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `reqid` BIGINT(20) NOT NULL,
    `from` BIGINT(20) NOT NULL,
    `to` BIGINT(20) NOT NULL,
    `rdate` DATETIME NOT NULL,
    `state` ENUM('sent','approved','accepted','rejected') NOT NULL,
    `type` INT(11) NOT NULL,
    `subtype` INT(11) NOT NULL,
    `clusterid` INT(11) NOT NULL,
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `to_state_clusterid` (`to`, `state`, `clusterid`),
    INDEX `from_state_clusterid` (`from`, `state`, `clusterid`),
    INDEX `reqid_to` (`reqid`, `to`),
    INDEX `from_rdate_type` (`from`, `rdate`, `type`),
    INDEX `type` (`type`),
    INDEX `from_state_type_clusterid` (`from`, `state`, `type`, `clusterid`),
    INDEX `to_state_type_clusterid` (`to`, `state`, `type`, `clusterid`),
    INDEX `state` (`state`),
    INDEX `from_to_state_type_clusterid` (`from`, `to`, `state`, `type`, `clusterid`),
    INDEX `from_rdate_type_clusterid` (`from`, `rdate`, `type`, `clusterid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

It's currently around ~250M rows and we'd like to delete 200M+ of them. This is a production database and this table in particular gets dozens of INSERT/UPDATE/SELECT queries every second.

Finally, I don't know if it changes anything, but all SELECTs to this table have USE INDEX statements in them.

What would be the best way to do this with minimal impact to our application?

EDIT: There are 2 DELETE statements to be executed:

DELETE FROM `reqs` WHERE `state` = 'accepted' or `state` = 'rejected';
DELETE FROM `reqs` WHERE `rdate` < '2013-01-01 00:00:00';

These queries are expected to delete ~70M and ~130M rows respectively.

Any data change action of this size if executed at once will cause a significant logging overhead and will require a lot of additional log space. That in turn will slow down the execution causing an even bigger impact on the rest of the system.

The best way to handle this is to break the delete down into smaller chunks. If you delete between 1000 and 10000 rows at a time and then wait for a few seconds the impact on other transactions should be small. Make sure each delete runs in a separate transaction so that all locks are released during the wait time. However, keep in mind that this will stretch out the delete over several hours or even days.

Also, even though it might sound counter intuitive, make sure there is an index supporting each delete so that MySQL does not have to execute a full table scan each time.


ERROR: column "FLAT" does not exit. Line 1. SELECT UPPER(FLAT). Kindly fix the trigger CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp()
RETURNS trigger
AS
$BODY$
BEGIN
  n.FLAT := UPPER(n.FLAT);
  RETURN NEW;
end
$BODY$
 LANGUAGE plpgsql
 SECURITY DEFINER;

CREATE TRIGGER tri_sv_tab_temp
    BEFORE INSERT OR UPDATE ON sv_tab_temp FOR EACH ROW
EXECUTE PROCEDURE trigger_fct_tri_sv_tab_temp();

The updated or inserted row is referenced using the new record:
CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp()
RETURNS trigger
AS
$BODY$
BEGIN
  new.FLAT := UPPER(new.FLAT);
  RETURN NEW;
end
$BODY$
 LANGUAGE plpgsql
 SECURITY DEFINER;


unistall sql server 2005 I hope someone can help me . I 've Unistall all components sql server 2005 in the control panel but the configuration tools still there.
I try to open HKEY_LOCAL_MACHINE > Software > Wow6432Node > Microsoft > Microsoft SQL Server > 90 but did not find it .
how the solution of my problem ?

Not sure if it will solve your issue but you can try something like this:

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

See more at: https://technet.microsoft.com/en-us/library/ms144259(v=sql.90).aspx


Speeding up "duplicate" checks We have an events table that is reasonably large (~35 million rows)

The original table is like this,

CREATE TABLE [dbo].[Events](
[EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[EventTypeID] int,
[SourceID] int,
[EventTitle] [varchar](max) NOT NULL,
[EventContent] [varchar](max) NULL,
[EventDate] [datetime] NOT NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
([EventID] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and it has an index that looks like

CREATE NONCLUSTERED INDEX [ix_EventDate] ON [dbo].[Events]
(
    [EventTypeID] ASC,
    [SourceID] ASC,
    [EventDate] ASC
)
INCLUDE (EventTitle, EventContent)

When adding new events, there is a business requirement to check to see if there's a "duplicate" event filed within the last 7 days (duplicate defined as having the same title and content).  The above index is used in the check query, but it is still very slow and only rarely finds any duplicates.

The query looks like

SELECT *
FROM #NewEvents
WHERE EXISTS (SELECT *
    FROM [dbo].[Events] e
    WHERE
    DATEDIFF(dd, #NewEvents.EventXML.value('(/Event/EventDate)[1]', 'DATETIME'), e.EventDate) BETWEEN -7 AND 0
    AND e.EventTitle = #NewEvents.EventXML.value('(/Event/EventTitle)[1]', 'VARCHAR(MAX)')
    AND e.EventContent = #NewEvents.EventXML.value('(/Event/EventContent)[1]', 'VARCHAR(MAX)')
)

I had thought maybe a couple of computed columns with hashes on the title and content might speed the check (by making the indexes much more compact).  Not wanting to alter the underlying table on a theory (possibly mucking with the existing replication setup), I tried to create an indexed view with those computed columns.

I created an index on the view like the one above INCLUDING the computed columns, but for whatever reason I can't get the query to use that index on the view; it always defaults back to the base table index, and to make matters worse doesn't use the INCLUDE values for the computed values; it joins with the PK to fetch the original columns.

The view index looked like this (where TitleHash and ContentHash are HASHBYTES of the full column text)

CREATE NONCLUSTERED INDEX [ix_viewEvents_EventDate] ON [dbo].[viewEvents]
(
    [EventTypeID] ASC,
    [SourceID] ASC,
    [EventDate] ASC
)
INCLUDE (TitleHash, ContentHash)

Anyway, I was wondering if anyone had any suggestions to speed up the query given the business constraints.

EDIT:  The first thing I tried was to change that DATEDIFF expression on the idea that it might have been blocking the use of the old index, but the plan for the old query showed that it was doing an Index Seek (not scan).

As Josh pointed out below, yeah the old query was doing an Index Seek - but only on the 2 leading columns.  The date was an output column and that filtering was done later.  But that stays that way no matter if I phrase the date filter as DATEDIFF() or "e.EventDate BETWEEN DATEADD(DAY, -7, #NewEvents.EventXML.value('(/Event/EventDate)1', 'DATETIME')) AND  #NewEvents.EventXML.value('(/Event/EventDate)1', 'DATETIME')"

EDIT 2: Using WITH (NOEXPAND) did, indeed, get sql server to use the new view and the new index, though to my disappointment running the new view-based query and the old query in the same batch came back with the Actual Plan saying the view was 99% of the 2 query batch.  So the experiment doesn't appear to have been successful.  Changing the phrasing of the date filtering here, though, does seem to be reflected in the Index Seek node.

Improving the Original Query

The query as written will use the nonclustered index that you created, but it won't seek into it - the whole index gets scanned.

I reproduced the query and data locally - the DATEDIFF version has a plan that looks like this:

Table 'Events'. Scan count 1, logical reads 28323

 SQL Server Execution Times:
   CPU time = 1891 ms,  elapsed time = 1943 ms.

This plan will scan the Events table once for each row in your #NewEvents temp table.  It filters rows at the Nested Loops join operator after they've already been read.  This is not ideal.

To get a seek, you could write your query like this:

SELECT *
FROM #NewEvents
WHERE EXISTS (SELECT *
    FROM [dbo].[Events] e
    WHERE
    e.EventDate >= DATEADD(dd, -7, #NewEvents.EventDate)
    AND e.EventDate <= #NewEvents.EventDate
    AND e.EventTitle = #NewEvents.EventTitle
    AND e.EventContent = #NewEvents.EventContent
);

That plans looks like this:

Table 'Events'. Scan count 1, logical reads 7

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 149 ms.

For each row in the temp table, there is a seek into your nonclustered index to just get the events that are within 7 days of the new event.  A filter is applied to qualifying rows to check if the title and content are equal.

The nice thing about this seek is that will, at most, read all the events within a 7 day span.  Which should be a fairly small number.

Thoughts about the Indexed View

  I created an index on the view like the one above INCLUDING the computed columns, but for whatever reason I can't get the query to use that index on the view

I don't think the view is really necessary, if the change mentioned above provides reasonable performance.  But you could try adding a WITH (NOEXPAND) hint after the view name in the query to see if you can force the view to be used.