Rename Field And Add Field With Same Name In One I have a stored procedure that creates a table, inserts data into the table, and now I need to alter the table to

  1) Rename an existing field to a new name
  2) Add a new field with the same name as the field we just renamed

I tried this syntax however my SSMS freezes anytime I execute and I have to ctrl+alt+del to close....I feel that is unrelated to the syntax, but wanted to ask

    Create Table TMI (empName varchar(500), userID varchar(100), TV float)

CREATE NONCLUSTERED INDEX eNIndex
ON TMI (empName ASC)

dbo.sp_RENAME '[dbo].TMI.TV' , 'OrigTV', 'COLUMN'

ALTER TABLE [dbo].TMI
ADD TV float;

Couple of things...

The sp_rename procedure is in the sys schema, not dbo.

Second, if you execute a stored procedure in a batch, then you must use the EXEC keyword.

Try changing your code to this:

CREATE Table TMI (empName varchar(500), userID varchar(100), TV float)

CREATE NONCLUSTERED INDEX eNIndex
ON TMI (empName ASC)

EXEC sys.sp_rename '[dbo].TMI.TV' , 'OrigTV', 'COLUMN'

ALTER TABLE [dbo].TMI
ADD TV float;


Fixing table structure to avoid `Error: duplicate key value violates unique constraint` I have a table which is created this way:

--
-- Table: #__content
--
CREATE TABLE "jos_content" (
  "id" serial NOT NULL,
  "asset_id" bigint DEFAULT 0 NOT NULL,
   ...
  "xreference" varchar(50) DEFAULT '' NOT NULL,
  PRIMARY KEY ("id")
);

Later some rows are inserted specifying the id:

INSERT INTO "jos_content" VALUES (1,36,'About',...)

At a later point some records are inserted without id and they fail with the error:
Error: duplicate key value violates unique constraint.

Apparently the id got defined as a sequence:

Each failed insert increases the pointer in the sequence till it increments to a value that no longer exists and the queries succeed.

SELECT nextval('jos_content_id_seq'::regclass)

What is wrong with the table definition? What is the smart way to fix this?

Nothing is wrong with your table definition.
(Except hat I would use jos_content_id or something instead of the non-descriptive column name id.
And I probably would use text instead of varchar(50).

Your INSERT statement is the problem.

With your id column defined as serial, you shouldn't insert manual values for id. Those may collide with the next value from the associated sequence.

Provide an explicit list of target columns (which is almost always a good idea for persisted INSERT statements) and omit serial columns completely.

INSERT INTO jos_content(asset_id, some_column, ...)
VALUES (36,'About',...);

If you need the value(s) of automatically generated column(s) immediately, use the RETURNING clause:

INSERT ...
RETURNING id;  -- possibly more

More details in this related answer on SO:

Increment value of a table ID for each INSERT

If you have manual entries in serial columns that might conflict later, set your sequence to the current maximum id to fix this once:

SELECT setval('jos_content_id_seq', max(id))
FROM   jos_content;

Where jos_content_id_seq is the default name for a sequence owned by jos_content.id, which you already found in the column default. Seems to be xhzt8_content_id_seq in your case;

Update: A similar issue popped up on SO and I came up with a new solution:

Make Postgres choose the next minimal available id


Will DB session terminate when client close DB connection I have several questions about database connection and database sessions. I assume questions below are apply to all databases (RDBMS).

In most of the cases, one database session is on one database connection. If client close the DB connection, will the DB session terminate straight away?
If the DB session contain a row level lock created via select for update query, will closing the DB connection without commit or rollback release the lock?
What is the impact if database statements are not close?

If the DB connection is closed the session will get an error the next time it attempts to query the database. But will work on subsequent tries as it establishes a new connection.
I believe this answer varies by RDBMS, but to answer you question, I believe the general answer is no. You can however manually kill the session, which should release the locks.
You should commit or rollback your transactions, you should not leave them open.


Question when designing a database I have started to design a database for a company which I work for. I am curious how the professionals would design such a database and what advice you might carry.

Information about the database:

The database is a reflection of activities which are all completely different. Basically there are around seven activities which each carry their own set of information. This means that it is hard to standardize the database and simply make a table called 'activities' which suffices for the entire database.

I was thinking of using a seperate table for each activity and link those to a couple of tables with basic information like the geographic information and/or the employee who was/is involved.

If this is the case then I at least know that I am on the right path and if I am not right, then I have learned something important!

If the information provided is not complete, then feel free to ask for more!

Splitting up the tables into different activities sounds like a much better plan than trying to shoe-horn 7 different activities into 1 table.
Different objects belong in different tables - rule of thumb - better to have many smaller tables that are "thin" - i.e. fewer fields than 1 megatable which is "fat" (i.e. many fields, many of which will have to be NULL). These sorts of tables are unwieldy and difficult to programme against.
Check this out to see what happens if this advice is ignored to the point of lunacy :-)

Finally, if this is your first database  project, try a prototype with a couple of activities and see how that goes. Better to make your mistakes on a small project than a big one (and you will make mistakes).

Be sure to have a working, relatively complete, prototype before moving on to the full project.

Come back here if you get stuck on more specific issues - your question is a bit "broad" for this forum. After successfully completing a prototype you should then be ready to move onto a bigger undertaking.


How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider We have a situation where Developers do not have any UPDATE permissions, BUT they work with applications and see connection strings -> they know passwords from some SQL accounts (example SQLLogin1) that have UPDATE permissions. Our operations currently are not perfect, and sometimes production data needs to be modified (no GUI for that yet).

Instead of contacting DBA, and asking him to modify the data, Developer would (improperly) use SQL account SQLLogin1 (that has permission to modify the data),
and connect over SQL Server Management Studio to modify the data himself.

DBA can not change password for SQLLogin1 without Developer seeing the new connection string and new password, since the application connection string that uses SQLLogin1 is maintained by Developer.

Question:

Is there a way to deny access to SQLLogin1 SQL login, but only if it is connecting over SSMS?

At the same time if SQLLogin1 is connecting over .Net SqlClient Data Provider (program_name in the sys.dm_exec_sessions), it must be allowed to login.

This way we want to not let Developer connect over SSMS using SQLLogin1, while the application that is using SQLLogin1, would still be able to connect.

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user.

Here is how to change it within SSMS:

In Connect to Database Object dialog choose Options, open  Additional Connection Parameters and choose any name for Application Name like this:

Now sys.dm_exec_sessions DMV and Program_name() will show you what you passed in your connection string in Application Name parameter:


How can I first sort by color & then in alphabetical order in ssrs? I'm still new to ssrs. In my query the color is determined by location. In my query I did
ORDER BY [Location], Underwriter. Now this works perfectly fine in my query, but when I run it in SSRS it doesn't sort like in the query. I get this

what I would like to do is first group the colors together and & set the underwriters in alphabetical order i.e. Balluka, Bill(light blue)
           Matanane, Brian(green)
           Walsh, Daniel(green)
           Davidson, Christine (pink)
           Hamilton, Marianne (pink)
           Karger, Tierney (pink)
           Marroquin, Melanie (pink)
           Sylvester, Todd (pink)
           Hopp, Robert (white)
           Lohr, Jeff (white)
           Raskovic, Hanna (white)

Now what I tried doing in ssrs was in the design I right clicked underwriter> interactive sorting: tried sorting by underwriter & location but nothing happens when I run it. Please help

The order by clause in your SQL query should have worked.

Since it isn't working that means that something else is interfering with the sort order.

The next place to look is in your Tablix Properties under sorting.

Then look at any Group Properties. Make sure you check each row.

If you still can't find it, right click on the report in Visual studio and view code. You can then Ctr-F and search for "Sort" and find any sort expressions in the .rdl.

If you find an object you want to look at, you can then use search properties to select the object by name.


Will increase in Innodb_buffer_pool_size increases insert query performance? We have 3 identical database boxes(M1<->M2->S1) with Mysql 5.7.17 installed and having memory 96GB RAM. In these boxes nearly 42GB occupied by Index and data length of Innodb Tables and 38G occupied by Myisam index tables. All at a sudden we observed there is increase in insert query execution time in one of the box "M2" causing replication delays. Upon checking Innodb_buffer_pool_size is defined as 10G in M2 but in remaining boxes the value is 128M. So is this impact with increase in the innodb_buffer_pool_size only? why ?

128M is pathetically low.

If you are running a mixture of InnoDB and MyISAM on a MySQL-only box, I recommend

innodb_buffer_pool_size = 1/3 of RAM (32G for your 96GB machines)
key_buffer_size = 1/10 of RAM (10G)

That leaves some room for data caching for MyISAM since it leaves that up to the OS.

Some of the "Why"...

InnoDB uses 16KB blocks for data and indexes; it caches these blocks in (roughly) a "least-recently-used" fashion in the buffer_pool.  How big should the buffer_pool be?  There are several rules:

It does not have to be much bigger than the actual data+index lengths.
If the "working set" is small, then small is good enough.  After all, that is what "caching" is all about.

MyISAM uses its "key_buffer" for caching index blocks, which are 1KB each.  It depends on the OS for buffering/caching the data, which is not "blocked" (from MyISAM's point of view -- it is just a stream).

Since you are using both engines, and the entire dataset is pushing the amount of RAM, you have a juggling game to play.  You might try 50G and 6G for those two settings.

If you are using UUIDs, you are screwed -- InnoDB and MyISAM are likely to blow out their caches and become I/O-bound as the dataset grows.

But... You said that the machine with the bigger buffer_pool became slower than the other boxes?  This does not make sense.  Unless some query (eg, ALTER TABLE) was hogging resources on it.


MYSQL+MyISAM+Need information regarding check table statement The MySQL CHECK TABLE statement checks a table or tables for errors.
Does the statement put an auto lock on a table before checking table for errors?
Does the statement perform a READ ONLY operation?
I have executed the CHECK TABLE command from MYSQL workbench (on same table copy/replication) operation going on,I got
Lost connection to server during query
error message for CHECK TABLE statement, read timeout interval (in seconds):[30]
Can this action can corrupt my copy/replication? (or because of data Copy/replication running on table, table is having auto lock ON so CHECK TABLE statement failed which is not going to impact copy operation). Please provide information so i will understand process better.

Big table?  It should be read-only.  If it finds something, CHECK TABLE suggests that you run REPAIR TABLE -- which will modify the table.
Whenever the server crashes (power failure or whatever), CHECK TABLE is needed for MyISAM tables, but not for InnoDB tables.  I recommend you change Engines.
Since almost every action locks the whole table in MyISAM, I would guess that CHECK does, too.


How to ignore sql errors in stored procedure ? (Not handle) I have a list of records I want to process and the cursor iterates through them from a table. The procedure just exits when it hits an insert statement and it fails. Is there a way I can ignore the errors and continue processing the following records ?

Cursor C1:
for each record:
    insert into table_t1(col1) values ('...');  <-- Insert statement fails for record 'n'
End cursor

I would like for it to not exit out but continue processing from n+1 to the end of cursor.

Any help appreciated.

Found it.

You have to declare a continue handler instead of an exit handler. That way even if there is an exception, it goes into the handler and continues with the next record.

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING

    BEGIN
     ...
     Code that handles the exception/warning
     ...
    END


Filesystem tuning for PostgreSQL I want to install PostgreSQL 9.x version in an Ubuntu 14.04 workstation and I'm looking for a recommendation on how to set it up in order to

get a good read-write (50/50-balance) performance
keep a decent performance for overall conventional system usage.

My idea is to have two main partitions in an SSD drive:

[A] an ext4 for the root system ("/") with OS recommended/default settings.
[B] an ext4 for postgres database files ("/var/lib/pgsql") with a large block size (eg. 1MB) and a large readahead size (how much?) and proper flags (which?).

I have no clue whether separating postgres db files from general system is a good idea. In such a case, any piece of advice on [B] block size, readahead size, filesystem flags?

Furthermore any helpful hint?

Since you're on SSD you're better of disabling readahead. Seek time is not an issue for flash based drives. That's for the spinning era. It's more important to align the filesystem properly.

Postgresql is using 8k pages so best performance generally comes with matching blocksize.

With XFS use nobarrier and make the filesystem with proper agcount and logging. (https://serverfault.com/questions/222305/trying-to-determine-the-correct-number-of-xfs-allocation-groups-for-postgresql-s).

You can also benefit from having compressed file system like ZFS especially if your data cannot fit in cache and has to be read from disk. Usually I get 2-4x for databases which results in 2-4 times less blocks read from disk but that's very use case dependant. If you use ZFS use recordsize=8k, compression=lz4, primarycache=all and check if you need to set ashift for the particular disk (if it is using 512 byte or 4k blocks by running cat /sys/block/[device]/queue/physical_block_size). http://zfsonlinux.org/faq.html#PerformanceConsideration


How to set sql_safe_updates via my.cnf? I'm running

mysql  Ver 15.1 Distrib 10.4.10-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

How can I set the system variable sql_safe_updates via my.cnf? I want it to always be on.

Turns out you can set it in the [mysqld] block. e.g.

[mysqld]
sql_safe_updates        = 1

Test it's set after you connect:

SHOW GLOBAL VARIABLES LIKE 'sql_safe_updates';


Separate table with one variance Using the example of a 'likes' feature, whereby you can like (for example), a blog, comment, etc, is it better to have a separate table for each section (likes_blog, likes_comment, etc) or simply have a special row in table 'likes' (row 'section')?

By better, I am referring to scaling - millions of records, indexing, select, insert, etc.

I would presume it's better to have separate tables for each section as each will be relatively smaller, which might inherently bring with it less issues. Is this a correct assumption?

Will you ever need to show a user's likes of different types all at once? If so, I would keep it in one table. If you run into scale issues, you can always start partitioning by the like type column and basically have separate tables on the back end.


Oracle UPDATE followed by SELECT in LinqPad I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key.  I'm writing these statements in LinqPad.

This syntax should work just fine in SQL Server SQL:

UPDATE schedule_amounts
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
  and SCHEDULE_NO = 2014
  and SCHEDULE_LEVEL = 100
  and SCHEDULE_STEP = 17

SELECT * FROM schedule_amounts

But the driver LinqPad uses is returning ORA-00933: SQL command not properly ended.

Using multiple WHERE conditions is the only way I can specify the exact row I need to update.  I have Googled Oracle's UPDATE syntax but I just can't seem to find an easy-to-understand example of how to do what, to me, is rather simple in SQL Server.

Thank you all for your comments.  You deserve the credit for this answer.  To summarize:

The Oracle driver for LinqPad that I'm using (dotConnect Direct Mode based on OCI 8) wants the statement to be formatted like this (yes, wihout any semicolons):

UPDATE schedule_amounts
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
    and SCHEDULE_NO = 2014
    and SCHEDULE_LEVEL = 100
    and SCHEDULE_STEP = 17
GO
SELECT * FROM schedule_amounts


How can I remove identical (but negative) rows that are also in some days of each other from a SELECT on a table I have a MySQL table with two fields: date and value.  date is of type date, and value is decimal(10,2).
There are "pairs" of rows (non-consecutive) whose values are of equal magnitude but opposite sign, within 7 days of each other.
date        value
...
2020-06-20  123.45
...
2020-06-25  -123.45
...

The above is a "pair" because they are negatives of each other and their dates are within 7 days of each other.  There may be any number of intervening rows.  There may be any number of pairs in the table.  Pairs of differing values may overlap 7 day periods.  For simplicity assume there are no other equal-value rows also with 7 days (ie, no triples).
I'm trying to find a SELECT statement that gives me all the table's rows EXCEPT "pairs".  Could someone please help?

SELECT *
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM TABLE t2
                   WHERE t2.`value` = -t1.`value`
                     AND t2.`date` BETWEEN t1.`date` - INTERVAL 7 DAY
                                       AND t1.`date` + INTERVAL 7 DAY )


How do I design a database to store quotes online? I have a form to generate quotes online.  The form has many products with their respective prices and options.

The users will generate the quote online and I need to store the quotes of the users when they submit the form.

My question is how I do a design a database to store this information?

This is an example of my form:

Description        amount        price x un      total
product1             1             15000         15000
product 2            3             10000         30000
Total                                            45000

I have thought the next options:

id_quote        descriptions          total
1              product1 1 15000       45000
               product2 3 45000

Other option is by date:

id    date                      description     amount         price          total
1    15/03/2012 3_45:45         product 1       1             15000          45000
2    15/03/2012 3_45:45         product 2       3             10000          30000

Your second option is better, though I'm confused: is that a Quotes table? If so, are those two records the same quote or different quotes? Also, there's no need to store total, you can calculate that when you need it. I'd generally say that calculated values should only be stored if the calculations are very complex and long-running.

Here's a simple table structure for the quotes that might help get you started:

Quotes
------
  quote_id (primary key)
  client_id - so you know who the quote belongs to.
  date - when the quote was generated

Quote_Items
-----------
  quote_item_id (primary key)
  quote_id (foreign key to Quotes.id)
  product_id (foreign key to you products)
  single_item_cost  (copy from products so that if the product price changes the quote won't change)
  quantity (multiply by single_item_cost to get total cost for this quote_item)


Does a replica set with only two machines make sense? A lot of MongoDB tutorials, when they talk about replica sets, give examples of two machines: one originally initialized as primary, and another one originally created as secondary.

As I understand it:

If a primary member unexpectedly dies, the secondary one won't elect itself the new primary anyway, since it won't have the majority (which is two in a replica set of two machines).
It is impossible to read the data from the database if the primary member is unavailable, even with read_preference=ReadPreference.SECONDARY.
Recreating the primary member won't work anyway: it cannot be added to the existent replica set, since it lacks the primary member; it cannot rs.initiate(); either, since it will create a different replica set with a different ID.

Therefore, is there a real life situation where having only two members in a replica set would make any sense? Or such situation is only theoretical, and one would expect to see at least three members in every replica set in practice?

If a primary member unexpectedly dies, the secondary one won't elect itself the new primary anyway, since it won't have the majority (which is two in a replica set of two machines).

This is correct. In order to elect (and maintain) a primary, a majority of voting members need to be available.

  It is impossible to read the data from the database if the primary member is unavailable, even with read_preference=ReadPreference.SECONDARY.

This is incorrect. Without a primary you cannot write to a replica set, but reads are still possible using non-primary read preferences such as secondary, primaryPreferred, secondaryPreferred, or nearest. The default read preference is primary, which provides strong consistency (versus eventual consistency when reading from a secondary). The primaryPreferred read preference reads from a primary if available, otherwise from a secondary.

  Recreating the primary member won't work anyway: it cannot be added to the existent replica set, since it lacks the primary member; it cannot rs.initiate(); either, since it will create a different replica set with a different ID.

If you lose one of the members in a two node replica set, you can force reconfigure the surviving member to be a single node replica set and then add new member(s). You only need to run rs.initiate() once in the lifetime of a replica set.

  Therefore, is there a real life situation where having only two members in a replica set would make any sense? Or such situation is only theoretical, and one would expect to see at least three members in every replica set in practice?

In general, most replica set deployments have sufficient members to allow for automatic failover (i.e. minimum of three members). If high availability is not a concern (or you prefer to have manual intervention), a two member replica set is not disallowed. However, a more typical option to allow failover would be to add a third voting-only member (aka an arbiter) to your replica set.

An arbiter is a lightweight voting-only member that will provide the additional vote needed for a majority in the event one of your two data bearing members is unavailable in a three-member configuration. The arbiter helps with availability but is a compromise over a third data-bearing member. When a three member replica set with an arbiter is in a degraded state (i.e. one of your data-bearing members is unavailable) you will still have a primary but no longer have data redundancy or replication. An arbiter will also prevent your application from being able to rely on the majority write concern to ensure that data is committed to a majority of replica set members.

My personal recommendation would be to deploy three data-bearing members as a minimum for a production replica set, however fewer data-bearing members could be considered for a development or non-critical deployment.


How can I disable all unique index and constraints in Oracle? I have been trying to do OracleBulkCopy on .NET, however, Oracle throws me errors. What specifically does OracleBulkCopy do, and how can I optimize its performance? (This was the post helped me alot) However, I do not know how to disable all unique index and constraints in the entire database.

Thanks in advance.

Community Wiki answer generated from question comments by Justin Cave

You can use user_indexes and user_constraints (or all_indexes and all_constraints or dba_indexes and dba_constraints) to iterate through all the indexes and constraints that you own/ have access to/ exist in the database.

I can't imagine that you'd really want to try to disable every constraint in the database.  Maybe all those in your schema.  More likely, you want to disable those that are on a particular set of tables.  As you iterate through them, you could construct the appropriate DDL statement to do whatever you want.

You can't disable an index (well, other than a function-based index).  You could make the index unusable but that's unlikely to be what you want.  You can drop the index but that presumably implies that you're going to save off the definition of the index so you can re-create it.


Upgrade Database from SQL Server 2008 to SQL Server 2008 R2 I've upgrade my database from SQL Server 2008 to 2008 R2 (v10.50.1600.1) on a remote server. Version of my local SQL Server is 10.50.1600.1.

When attempting to restore database from remote server to my system, I get this erro. How can I solve my problem?

In this case, the error message is quite clear. You have backed up a database from a SQL Server 2008R2 instance and tried to restore it on a SQL Server 2008 instance.

It is not possible to restore databases to a previous version of SQL Server. This has to do with internal database structure differences between the versions. The only way to get your data from a SQL Server 2008R2 instance onto a SQL Server 2008 instance would be to export the data and import it. You could achieve this through SSIS, bcp, Replication or Linked Servers.


Move column to another table by reducing? I'm not very experienced with database so sorry for lacking correct terminology.

I have a structure similar to

Employee
-Id
-Location
-Name
-StoreId

Store
-Id
-Name

And I want to simplify/refactor so that the store has the location rather than the employee. And all employees should all have the same location if they have the same storeId.

How can I move location to Store while dropping location on Employee and setting it to the correct value?
If somehow the locations are mismatching, can I use the one with the most hits?

Something like

ALTER TABLE store
    add column location ???

alter table employee
    drop column location

You'll need to do it in several steps:

Add the column to the store (I'm not sure what the type is, maybe it's something else):

ALTER TABLE store ADD COLUMN location VARCHAR(50)

Populate it, based on the most prevalent location of the employees. I'm not entirely familiar with MySQL syntax but the query should look something like this:

UPDATE store SET location = locations_by_storeid.location
FROM store INNER JOIN
(SELECT storeid, location,
  ROW_NUMBER() OVER(PARTITION_BY storeid ORDER BY COUNT(*) DESC) row_number
  FROM employee
  GROUP BY storeid, location) locations_by_storeid
WHERE store.id = locations_by_storeid.storeid
  AND locations_by_storeid.row_number = 1

The subquery (locations_by_storeid) lists all locations for employees on a certain storeid, and we take only the most frequent one (the one with row number 1) as per your requirement 2.
Now we can remove the location from the employee:

ALTER TABLE employee DROP COLUMN location


Returning results from many functions / statements I have a function that I will be running 10-50 times at once:

importsitechanges(33, 209703, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone')

This function that I made returns the number of changes made as a string.

When I run many of them at once in pgadmin, I only get the return string from the last one:

SELECT * FROM importsitechanges(33, 321344, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone');
SELECT * FROM importsitechanges(32, 209703, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone');
...

How can I get the return strings from all of these functions?

Unfortunately, as you already noticed, pgAdminIII shows only the latest result set.  A logical solution could be transforming your 10-50 result sets into one.   This can be achieved using UNION ALL:

SELECT * FROM importsitechanges(33, ...)
    UNION ALL
SELECT * FROM importsitechanges(22, ...)
    UNION ALL
...


Indexing options for Postgres for a table with billion records when you want to index only recent records We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes.
This table has a updated_at column which gets updated whenever an entry in the table is added/updated. The use case we have now is to query recently added/modified entries. We don't want to add an index on the updated_at column for the whole table.
We are considering the following options:

At the beginning of a month, create a partial index on the table for the duration of the month (Example: If we run this on May 1st, we will create an index for range May 1 - May31).
At the end of the month, drop the partial index for the previous month (Example: If we run this on May 31, we will drop the index created on April 1 for the range April 1 - April 30)

This way we will have not more than two indexes on the column at a given time.
Since the query loads might not look past more than two months right now, this will work well but not sure if there are any gotchas around it.
Specifically.

Is the constant create and drop indexes bad for the table?
How does the performance of the inserts/updates go if there are multiple indexes to update. Though all the updates should only affect one index at a time. The index for the previous month will not receive any updates once we have next month.

Are there better ways to handle this situation than outlined above ?
Cheers

Creating and dropping indexes does not harm the table in the least. Also, with a partial index, you will pay the price for index maintenance only if the row matches the condition.
You should consider range partitioning by updated_at. Then queries with that column in the WHERE condition will only scan the necessary partitions. As a side effect, deleting old data becomes easier.
The price you would be paying is twofold:

Most other queries would become at least slightly slower. Make sure that you don't have too many partitions.

Updating a row so that updated_at crosses a partition boundary will incur the overhead of moving the row to another partition.


Does sp_addtabletocontents Use Precomputed Partitions I am using merge replication in SQL 2012.

When I bulk insert rows into a table, and run this stored procedure to add references into the merge tracking table,

sp_addtabletocontents

Is that missing out on precomputing the partitions using the precomputed partition optimisations?

Intriguing question... as long as you have an actual scenario to apply it to it wouldn't be too difficult to figure out.  Perform your BCP process, run sp_addtabletocontents and then before the subscriber syncs verify the existence of the records in the MSmerge_current_partition_mappings and MSmerge_past_partition_mappings tables.

According to this technet article that is where the partitioning data for each row that has been changed is stored post change/pre sync.  Look into the article for deeper information and the manner in which the table structures are organized within the process.  The link provided is for SQL 2008 but if the table structure is the same its a safe bet that the pre-compute logic is close enough to answer your question.

I am not an expert on precomputed partitions but it seems to me that it would have to take advantage of the precompute process (if enabled) otherwise it wouldn't sync these records at all.


warning sign on the select operator - what does it mean? while comparing the execution plan of 2 stored procedures, on the second one I get a warning sign (marked by the red arrow).

what does it mean?

If you right-click on the warning -> properties you should see some Warnings:


How to check the growth of database I would like to know how to check the growth of entire database and individual data files.

Also, what to suggest to the client if the database is growing rapidly?

You can query the default trace to get information about recent growth events.

DECLARE @path nvarchar(260) = (
    SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) +'log.trc'
    FROM    sys.traces
    WHERE   is_default = 1)

SELECT gt.DatabaseID,
       gt.FileName,
       COUNT(*) AS NumberOfEvents,
       CASE WHEN te.name LIKE'%Grow' THEN 1 ELSE 0 END AS is_growth_event
FROM  sys.fn_trace_gettable(@path, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE   te.name in ('Data File Auto Grow','Log File Auto Grow','Data File Auto Shrink','Log File Auto Shrink')
GROUP BY gt.DatabaseID,
       gt.FileName,
       te.name

You can also use Event Notifications to be notified on DATA_FILE_AUTO_GROW,LOG_FILE_AUTO_GROW without having to poll the trace.


Load Balancing multiple databases in a multi-tenant setup We have a multi-tenant setup where each client of ours runs a DB that is identical to every other client. We were using Mysql sofar and with CentOS 7.x ,switched to MariaDB.

If we want to distribute the load so that requests for Databases 1-500 go to Node-A, request for Databases 501-1000 go to Node-B, would that be possible through some out of the box solution like haproxy? I looked into their documentation but the "balance" options seem to be about distributing the load based on pre-defined headers/values rather than my use-case above. Would it be possible to add a custom "balance" algorithm that will send the requests to the corresponding nodes?

The other approach I was considering was to use a local key-value store that contains the information about which clients are in which Node and switching to those based on the request. I also want to have master-slave replication for these, but have already handled that through a proxy in my application.

Our application runs on PHP(Symfony Framework) with Mysql, if that matters.

How would you design a system that will have few thousand databases and be able to spread the load?

You can use Galera for multi-master replication. If you could move individual clients to specific nodes you will not run into locking issues.  It also has automatic recover of failed nodes.
http://galeracluster.com/


Pgbouncer getting auth failing when trying to connect using psql I'm trying to configure the latest pgbouncer to work with postgres 9. I can connect to my db using psql with the right password but when I use psql -p 6432 I can't connect with the error message of psql: ERROR:  auth failed

This seems like it might be caused by my userlist.txt file, but I'm not sure of that. I checked and all required files are owned fully by Postgres system user

pgbouncer.ini

[databases]
postgres = host=localhost port=5433 auth_user=postgres dbname=postgres

[pgbouncer]
pidfile = /usr/local/pgbouncer-1.9.0/pgbouncer.pid
logfile = /usr/local/pgbouncer-1.9.0/log/pgbouncer.log

user = postgres

listen_addr = *
listen_port = 6432

auth_type = md5
auth_file = /usr/local/pgbouncer-1.9.0/etc/userlist.txt

Userlist.txt

"postgres" "md5<MD5 SUM>"

Command used to start pgbouncer

./bin/pgbouncer -d etc/pgbouncer.ini

Log output showing failure

2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:01.080 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43286 closing because: client unexpected eof (age=0)
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 login attempt: db=postgres user=postgres tls=no
2019-08-20 13:46:06.980 16446 LOG C-0x1028ce0: postgres/postgres@127.0.0.1:43414 closing because: auth failed (age=0)
2019-08-20 13:46:06.980 16446 WARNING C-0x1028ce0: postgres/postgres@127.0.0.1:43414 pooler error: auth failed

To create an md5 password for PGBouncer (or PostgreSQL for that matter), the formula is:

"md5" + md5(password + username)

Here are 3 ways you can create one, where the username is "admin" and the password is "password123"...

Linux:

# echo -n "md5"; echo -n "password123admin" | md5sum | awk '{print $1}'
md53f84a3c26198d9b94054ca7a3839366d

MacOS:

➜ echo -n "md5"; md5 -qs "password123admin"
md53f84a3c26198d9b94054ca7a3839366d

Python 2:

>>> import hashlib
>>> print("md5" + hashlib.md5("password123" + "admin").hexdigest())
md53f84a3c26198d9b94054ca7a3839366d

Python 3:

As above, but use binary strings...

print("md5" + hashlib.md5(b"password123" + b"admin").hexdigest())

Config Files

The entry in your pgbouncer.ini should be:

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

and the relevant entry in /etc/pgbouncer/userlist.txt should be:

"admin" "md53f84a3c26198d9b94054ca7a3839366d"

You can then test this by connecting to pgbouncer

psql -h localhost -p 6432 -U admin

... and then typing the plaintext version of your password when prompted.


Sql Server 2012 and native client 10 What is the difference between SQL Server Native client versions 10 and 11? Can I use Native Client 10 with SQL Server 2012 running?

Yes, you can use Native Client 10

Simply use Provider=SQLNCLI10 in the connection string


SQL query to get the leaf record in a parent-child relationship I use PostgreSQL. I have 2 tables, products and product_renewals.

Products

+----+--------+
| id |  name  |
+----+--------+
|  1 | Foo    |
|  2 | Bar    |
|  3 | Foo    |
|  4 | Foo    |
+----+--------+

ProductRenewals

+----+--------+--------+
| id | old_id | new_id |
+----+--------+--------+
|  1 | 1      | 3      |
|  2 | 3      | 4      |
+----+--------+--------+

Each time a product is renewed, a renewal record is creating with the reference to the old and the new product.

What I'd like to be able to build is a query that, given a Product ID, returns the ID of the latest product in the renewal chain. If the product is not renewed (or is already the most recent one), then that ID is returned.
What the database has to do is to walk the renewal chain from parent to child, down to the leaf.

Examples:

Case 1) Given ProductID 1, it returns 4 (4 renews 3 that renews 1)
Case 2) Given ProductID 3, it returns 4 (4 renews 3)
Case 3) Given ProductID 4, it returns 4 (4 is not renewed)
Case 4) Given ProductID 2, it returns 2 (2 is not renewed)

I would be ok if Case 3 and Case 4 returns nil (for simplicity).

I tried to build something and it seems to me I need a recursive query, but honestly I did not get a lot out of the attempts. Can you provide some guidance or reference?

This was written while exploring various ways, so if you just want the best solution, see at bottom, otherwise read on to follow the train of thoughts and the progressive refinements.

If you can guarantee order in our IDs (that is new_id > old_id for any renewal, if that is not true see below for enhanced method) then you can do:

WITH RECURSIVE renewals AS (
    SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 1
    UNION ALL
    SELECT pr.old_id, pr.new_id FROM ProductRenewals pr
        INNER JOIN renewals r ON pr.old_id=r.new_id)
SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1

Replace the 1 in old_id = 1 by the value you want to query for.
This will return either nothing (cases 3 and 4) or the id (cases 1 and 2) as such:

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 1 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 2 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
(0 rows)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 3 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 4 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
(0 rows)

If you replace the latest SELECT by SELECT MAX(new_id) FROM renewals you will get either an id or NULL. You could also do something like SELECT COALESCE(MAX(new_id), 0)  FROM renewals, or reuse the id you used as input instead of 0 to match your first design.

Postgresql documentation on recursive CTE is at https://www.postgresql.org/docs/8.4/static/queries-with.html and you can find a canonical example with employees and managers at http://www.postgresqltutorial.com/postgresql-recursive-query/

With as slight change of the above, you do not need to hold any condition on how the id are ordered, just store a counter at each "iteration" step of the recursion, like that:

test=# WITH RECURSIVE renewals AS (SELECT 1 iter, old_id, new_id FROM ProductRenewals WHERE old_id = 1 UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;

(do not change the first 1, it is the starting counter completely not related to your id values, even if any value would fit here in fact)

In fact by slightly elaborating on this you can use this final form that both allows you to get exactly the result as expected (specially in case of non renewals) and also do not care about ordering at all. See:

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 1) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 2) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      2
(1 row)

rest=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 3) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 4) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)


Organizing Relational Database Logical Model with Varying Categories I'm having difficulty designing a schema for my postgresql database that relates to the items a store can create.

There are three possible categories: (1) Clothing (2) Footwear (3) Other.

The problem is that each clothing item can vary in the colours available, the sizes available, and number of images. Meaning a user can click to create an item, and then continue adding more available colours with their respect size and quantity. Something similar to:

[ITEM TITLE input]

[COLOR input]
    - [SIZE input] - [QUANTITY input]
[IMAGES input]
[+ to add more colors]

If the only variable value was color, I could just “normalize” the model, and create an available colours table with a relationship back to the item. I'm not sure how to handle the additional variable of available sizes. What's more, the Other category wouldn't have colours or sizes available to it, only quantity.

Does anyone know if there's a solution to this type of problem, or a design pattern that I could read up on that might apply? I'm trying to wrap my head around how I could design this model and I'm coming up blank. Ideally I'd be working with a nosql database in this case but that's not possible.

Something like this,

CREATE SCHEMA catalog;

CREATE TABLE catalog.item   ( item_id  serial PRIMARY KEY, item_name  text );
CREATE TABLE catalog.color  ( color_id serial PRIMARY KEY, color_name text );
CREATE TABLE catalog.size   ( size_id  serial PRIMARY KEY, size_name  text );
CREATE TABLE catalog.item_color (
  item_id int  REFERENCES item,
  color_id int REFERENCES color
);
CREATE TABLE catalog.item_size (
  item_id int  REFERENCES item,
  color_id int REFERENCES size
);

Now, we need orders

CREATE SCHEMA orders;

CREATE TABLE orders.shipment (
  shipment_id serial PRIMARY KEY,
  customer_name text,
  customer_address text
);

CREATE TABLE orders.item (
  item_id serial PRIMARY KEY
  item_id int    REFERENCES catalog.item,
  color_id int   REFERENCES catalog.color,
  size_id int    REFERENCES catalog.size,
);

CREATE TABLE orders.shipment_item (
  shipment_id int REFERENCES orders.shipment,
  item_id     int REFERENCES orders.item
);

to query the catalog,

SELECT item_name, color_name, size_name
FROM catalog.item
LEFT OUTER JOIN catalog.item_size USING (item_id)
LEFT OUTER JOIN catalog.item_color USING (item_id)

Then you just have a shipment schema which includes different order items.

Now for your categories, you can either create a category table that has  (1) Clothing (2) Footwear (3) Other. in the same style, or you can just add a tags column to catalog.item and tag them with clothing and footwear or whatever. Whatever you want to do..


Cursor doesn't print text when I tell it to I have used WAITFOR combined with a cursor in the following script:

create table orders(orderId int primary key,productId int,productName varchar(20));

insert into orders values(1,11,'book');
insert into orders values(2,13,'flower');
insert into orders values(3,24,'microwave');
insert into orders values(4,7,'food');
insert into orders values(5,46,'clothes');

declare cur cursor for
select * from orders;
open cur;
fetch next from cur;
while(@@fetch_status=0)
begin
    waitfor delay '00:00:01';
    fetch next from cur;
end;
close cur;
deallocate cur;

I'm looking to display one record from the cursor per the specified interval of 1 second. For example: display the first row, then wait a second; display the second row, then wait a second; and so on. The cursor above does not do that. Say orders has 5 rows. The WHILE block waits 5 seconds and then displays all records together.

Is this the cursor's fault?  How can I modify this script to work as intended?

Add RAISERROR('', 0, 1) WITH NOWAIT before the delay to flush anything in the buffer to the client (SSMS?) first otherwise SQL Server will wait for more data to fill the packet.

Declare cur cursor for
select * from orders
open cur
fetch next from cur
while(@@fetch_status=0)
begin
    RAISERROR('', 0, 1) WITH NOWAIT
    WAITFOR DELAY '00:00:01'
    fetch next from cur
end
close cur
deallocate cur


How many stored procedure parameters is too many? I've just started writing a stored procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too many parameters?

For context... this procedure will essentially INSERT a single row into a single table. There would also be a very similar; albeit somewhat smaller; version that performs an UPDATE on the same table. Most columns are relatively small with a mix of int and strings (varchar(200)).

What are the issues; good or bad; to having a procedure with a large number of parameters and what is the threshold where I should start considering other patterns?

Issues? I'd argue none.

The limit is 2100 parameters. IIRC it has been 2100 since SQL2000 but a documentation error suggested it was 1024.
If a table has 1000 columns (due to a Sharepoint-esque sparse column arrangement for example) and you are enforcing access via stored procedures, your insert proc may have 1000 parameters. Nothing wrong with that.
Do pause to review the schema when you encounter a wide table (not that 30 is particularly wide). It's not uncommon to find tables that started off life normalized but through laziness and/or fecklessness have expanded beyond recognition.
Don't even briefly consider passing a set of parameters as a CSV list or XML. Blinds the query optimiser and saves little to no time or effort.
Don't hand crank the client code to call a procedure with a large number of parameters. Code generation tools like T4 templates or CodeSmith to the rescue.


How to define alias in an ARRAY_AGG expression? I'm trying to return pure JSON from a Postgres 9.2 table.

  SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
  FROM (
    SELECT
      album,
      max(release_year) AS release_year,
      max(artwork_path) AS artwork_path,
      MD5(concat(album,release_year,artist)) AS token,
      ARRAY_AGG((media_files.position, media_files.token, media_files.title) ORDER BY media_files.position) as media_files
    FROM media_files
    INNER JOIN playlist_media_files ON playlist_media_files.media_file_id = media_files.id
    WHERE playlist_media_files.playlist_id = 1
    GROUP BY album, release_year, artist
    ORDER BY artist, release_year
  ) as ALBUM_ROW

This query works fairly well, however on the line:

ARRAY_AGG((media_files.position, media_files.token) ORDER ...) as media_files

I would like to alias position and token attributes in the result set.

AS apparently it's not permitted here.

I would have written:

ARRAY_AGG((media_files.position AS xxx, media_files.token AS yyy) ORDER BY media_files.position) as media_files

But it's not working.

Postgres documentations says:
"where aggregate_name is a previously defined aggregate (possibly qualified with a schema name), expression is any value expression that does not itself contain an aggregate expression or a window function call, and order_by_clause is a optional ORDER BY clause as described below."

http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-AGGREGATES

Am I out of luck?

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

Arrays types can only be built upon well-known types. Your option is to announce  such a type to the system and cast the record to it before forming the array.
Create a well-known composite type:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

I am guessing data types for lack of information here. Fill in your actual types.

Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.


When is SELECT necessary in PL/pgSQL functions? While assigning the result of a function or expression to a variable in PL/pgSQL, when is it necessary to use a SELECT statement (sub-query)?

This may be just my misconception from SQL. I thought that everything should be enclosed in a (SELECT ...) (even if it just involves computing a number), e.g.

$$
DECLARE parts text[];
BEGIN
  parts := (SELECT string_to_array(my_str,'_') );
...
$$

but I just found that it works without the SELECT:

parts := string_to_array(my_str,'_');

(It's almost as if I can use PL/pgSQL as if its Pascal.)

In general, what kind of expressions can be used directly without SELECT in computing values?

This is only an educated guess.

It seems that the SELECT is unnecessary because it's already present, e.g. in the implicit SELECT ... INTO that an assignment := is equivalent to. From the documentation:

  An assignment of a value to a PL/pgSQL variable is written as:

  variable { := | = } expression;

  As explained previously, the expression in such a statement is
  evaluated by means of an SQL SELECT command sent to the main database
  engine.

An example may be shown here, where:

name := (SELECT t.name from test_table t where t.id = x);

can also be written as

name := t.name from test_table t where t.id = x;

This seems possible because the second simplified form is equivalent to a SELECT INTO in PL/pgSQL:

SELECT test_table.name INTO name FROM test_table WHERE id = x;

The first form would be equivalent to:

SELECT (SELECT t.name from test_table t where t.id = x) INTO name;

, which has a redundant layer of SELECT that can be removed.

On the other hand, when an implicit SELECT is not present, an explicit SELECT seems necessary. For example, the same function cannot be used directly (i.e. without SELECT)

string_to_array(my_str,'_');

if it's not in an assignment to a variable.


Replacing MYSQL table with data from another table - no downtime I have two tables with identical structures:

Table1 and Table2

id INT PRIMARY KEY,
a TINYINT,
b TINYINT,
c TINYINT

Table1 is actively being used by queries from other sessions; Table2 is not referenced/accessed by anything else.  My goal is to end up with just Table1, but having the data from Table2.
(Note that Table2 and Table1 do not have the same set of id-PRIMARY KEY.)

What is the best way to do this without risking Table1 not existing momentarily for other sessions?

I know I can do something like:

DROP TABLE Table1;
ALTER TABLE Table2 RENAME Table1;

But am not sure how to ensure nothing tries to query Table1 while it doesn't exist or is empty.  Do I use transactions or lock the tables or something else?  (Ideally, I'd want any other sessions' queries to return either old Table1 or new Table1 data rather than failing.)

I am using CodeIgniter in PHP to make the calls.

UPDATE:
Looks like
this post answers it using a method from the MySQL RENAME TABLE Documentation:

RENAME TABLE Table1 TO Table1_old, Table2 To Table1;

And then I can DROP Table1_old at my leisure

How about a view? Point your application at the view and then alter the view at an instant when there is no access to point to table2. Once that is done, you could drop table1.


Update Select in postgresql I have three tables, in form of grandparent, parent, and child. By this I mean a foreign key exists in child linking to parent, and another foreign key exists in parent linking to grandparent.  Child and grandparent's only connection is via parent, and I currently have to join to ask questions like who are child's grandparents and who are grandparent's children.

I realised that a tiny bit of denormalisation is going to make my application code nicer to write, so, I want to add another foreign key in child, which links directly to grandparent (which never changes).
This of course is redundant information, but it means I don't have to join to ask questions like give me all of child's grandparents, or give me all of grandparents grandchildren.  This will make my app faster and my application code better, and I don't really need advice on the whys of doing this, but the how.

I've added the columns and I'm looking for advice on an UPDATE query to update the database with the correct values for the grandparent id in the child's new foreign key grandparent_id.

The actual tables are not grandparent, parent and child, but minicourse, topic and lesson, as below.

I tried the below query, from here, but, as I suspected, it is not quite what's needed here. The below updates all children with the same value (i.e. the wrong value, seems to be the first one it gets) of the grandparent id.  I want each child updated with the correct grandparent_id.
I'm rather new to operations like this in sql, so help would be much appreciated.

UPDATE lessons
SET minicourse_id=subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  JOIN lessons
    ON topics.id=lessons.topic_id
) AS subquery;

I could just do it manually (there are only about 10 minicourses) by running an update like this 10 times, having found what the values are by regular queries:

UPDATE lessons
SET
  minicourse_id =<I enter an actual minicourse_id here>
WHERE
  lesson_id in(<I enter a manually-found list of lesson_id's here>);

but I thought it could be cool to learn how to do it with sql instead.

The problem with your query is that the subquery is not correlated. You dodn't need and you shouldn't use lessons inside the subquery.

UPDATE lessons
SET minicourse_id = subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  WHERE topics.id = lessons.topic_id   -- this refers to the "lesson"
                                       -- of the main query
) AS subquery ;

In fact, it can be written without a subquery at all:

UPDATE lessons
SET minicourse_id = topics.minicourse_id
FROM topics
WHERE topics.id = lessons.topic_id ;

or with a different subquery:

UPDATE lessons
SET minicourse_id
    = ( SELECT minicourse_id
        FROM topics
        WHERE id = lessons.topic_id
      ) ;

Regarding your design, I assume you have added a lessons.minicourse_id column and this foreign key:

ALTER TABLE lessons
    ADD minicourse_id INT ;

ALTER TABLE lessons
    ADD FOREIGN KEY (minicourse_id)
        REFERENCES minicourses (id) ;

While this achieves what you want, there is a small issue: you may end up with rows in child (lessons) that refer to a grandparent (minicourse) A and also to a parent (topics) that refers to a different grandparent B.

Of course if all your applications and users have code that is correct, this won't happen. But I suggest you enforce this in the database level and not (only) in the application level. This is quite easy to do, with the following change in the foreign keys.

It basically sets the FK to minicourses to be "through" lessons, without a direct FK. You'll still be able to use direct joins between lessons and minicourses:

ALTER TABLE lessons
    ADD minicourse_id INT ;

-- the UPDATE is the same!
UPDATE lessons
SET minicourse_id = topics.minicourse_id
FROM topics
WHERE topics.id = lessons.topic_id ;

ALTER TABLE lessons
    ALTER minicourse_id SET NOT NULL ;

-- we need this for the FK below
ALTER TABLE topics
    ADD CONSTRAINT minicourse_topic_UQ
        UNIQUE (minicourse_id, id) ;

-- the FK is "lessons -> topics"
ALTER TABLE lessons
    ADD CONSTRAINT lessons_to_topics_FK2
        FOREIGN KEY (minicourse_id, topic_id)
        REFERENCES topics (minicourse_id, id) ;

-- drop the previous FK to topics
ALTER TABLE lessons
    DROP CONSTRAINT lessons_to_topics_FK ;


MySQL Workbench Alter Procedure i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right click it, after "Alter Procedure" click but doesn't happen anything!

i'm updated to MySQL Workbench program "5.2.45 revision 10251" to  "5.2.47  revision 10398" but both are can not make alter procedure. And one odd thing is alter table is working fine.

Have u got any idea why doesn't work?

Are you sure you have the ALTER ROUTINE privilege? See if you can create an own procedure and alter that.


Can't get mysql running After canceling an Ubuntu upgrade, Mysql refuses to start.

this is what I get when I try to run it :

$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

When I try starting it using service command I get this :

$ service mysql start
start: Rejected send message, 1 matched rules; type="method_call", sender=":1.74" (uid=1000 pid=16096 comm="start mysql ") interface="com.ubuntu.Upstart0_6.Job" member="Start" error name="(unset)" requested_reply="0" destination="com.ubuntu.Upstart" (uid=0 pid=1 comm="/sbin/init")

I tried also to uninstall it but I get this error :

$ sudo apt-get remove mysql
E: dpkg a été interrompu. Il est nécessaire d'utiliser « sudo dpkg --configure -a » pour corriger le problème.

The error is in french, but those familiar with Ubuntu will get it I guess.

After that I do what they suggested :

$ sudo dpkg --configure -a
Paramétrage de mysql-server-5.5 (5.5.31-0ubuntu0.12.04.2) ...

And then it endlessly configures.. until I get this error :

start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg : erreur de traitement de mysql-server-5.5 (--configure) :
le sous-processus script post-installation installé a retourné une erreur de sortie d'état 1
Des erreurs ont été rencontrées pendant l'exécution :
mysql-server-5.5

almost the same thing when I try an autoremove :

sudo apt-get autoremove
Lecture des listes de paquets... Fait
Construction de l'arbre des dépendances
Lecture des informations d'état... Fait
Les paquets suivants seront ENLEVÉS :
  libdevil1c2 libgearman6 libgts-0.7-5 libgts-bin liblasi0 libtokyocabinet8
0 mis à jour, 0 nouvellement installés, 6 à enlever et 6 non mis à jour.
1 partiellement installés ou enlevés.
Après cette opération, 3 232 ko d'espace disque seront libérés.
Souhaitez-vous continuer [O/n] ? O
(Lecture de la base de données... 536300 fichiers et répertoires déjà     installés.)
Suppression de libdevil1c2 ...
Suppression de libgearman6 ...
Suppression de libgts-bin ...
Suppression de libgts-0.7-5 ...
Suppression de liblasi0 ...
Suppression de libtokyocabinet8 ...
Traitement des actions différées (« triggers ») pour « libc-bin »...
ldconfig deferred processing now taking place
Traitement des actions différées (« triggers ») pour « man-db »...
Paramétrage de mysql-server-5.5 (5.5.31-0ubuntu0.12.04.2) ...
start: Job failed to start
invoke-rc.d: initscript mysql, action "start" failed.
dpkg : erreur de traitement de mysql-server-5.5 (--configure) :
 le sous-processus script post-installation installé a retourné une erreur de sortie d'état 1
Des erreurs ont été rencontrées pendant l'exécution :
 mysql-server-5.5
E: Sub-process /usr/bin/dpkg returned an error code (1)

My Mysql version is :

$ mysql -V
mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

Any hints about this ?

1 - What message do you get when you try to restart mysql ? : /etc/init.d/mysql restart

2 - Verify if the /var/run/mysqld directory is owned by mysql, if it's not th case, create a new directoy and assign it to mysql :

sudo mkdir /var/run/mysqld
sudo chown mysql:mysql mysqld

And restart the mysqld daemon :

sudo mysqld_safe


How to get SQL Server CPU and Memory read and write usage within two weeks? Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems complicated. Any help will be appreciated.

You can query sys.dm_os_ring_buffers DMV to extract information from it. Please note sys.dm_os_ring_buffers is Undocumented Command but safe to use.

NOTE: These queries are tested on from SQL Server 2008 R2 to 2014. I have not tested them on 2008 or 2005.

To get information about SQL Server memory usage  you need to query sys.dm_os_ring_buffers and filter out for "RING_BUFFER_RESOURCE_MONITOR". Below is the sample query. If the query only produces results for current date and you want for previous date you can use GETDATE() -1 instead of GETDATE() in the query

;with RingBufferMemoryMonitoring
as
(SELECT

 x.value('(//Record/MemoryRecord/MemoryUtilization)[1]',
       'bigint')
       AS Memory_utilization_Percentage ,
       x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]',
       'bigint') as [TotalPhysicalMemory_KB] ,
       x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]',
       'bigint') as [AvailPhysicalMemory_KB] ,
x.value('(//Record/@time)[1]', 'bigint')
 as [Record_Time]
FROM  (SELECT Cast (record AS XML)
 FROM   sys.dm_os_ring_buffers
 WHERE  ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)
 )
 select RingBufferMemoryMonitoring.*, Dateadd (ms, RingBufferMemoryMonitoring.[record_time] - SI.ms_ticks, Getdate()) AS
       Notification_time
      from  RingBufferMemoryMonitoring
 cross join sys.dm_os_sys_info SI

Below is another query,I took the query from This Blog, to get CPU utilization history. If you want for previous date you can use GETDATE() -1 instead of GETDATE() in the query

DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info

SELECT top 20 record_id, EventTime,

  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,

  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization

FROM

(

  SELECT

    record.value('(Record/@id)[1]', 'int') AS record_id,

    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,

    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,

    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,

    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,

    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2

  FROM (

    SELECT timestamp, CONVERT (xml, record) AS record

    FROM sys.dm_os_ring_buffers

    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'

      AND record LIKE '%<SystemHealth>%') AS t

) AS t

ORDER BY record_id desc

If you want to read more about how to extract information from  Sys.dm_os_ring_buffers please read Inside Sys.dm_os_ring_Buffers


Check the Oracle 11g R2 installation type How can I check if the installation of Oracle Database is done with the Administrator Option Type?

There are four installation types while installing the Oracle 11g R2 as listed below:

Instant Client
Administrator
Runtime
Custom

How can I check to see if the installation was completed with the Administrator option?

Are you talking about the Oracle Client?

In that case you can review the installation logs in the Oracle Inventory directory and look for the INSTALL_TYPE property:

INFO: INFO: ---------------------------------------
INFO: INFO: Starting to setup InstallClient
INFO: INFO: Initializing setup InstallClient
INFO: INFO: Session Details :
INFO: ---------------------------------------------------------------------
INFO:  PROPERTY                                               VALUE
INFO: ---------------------------------------------------------------------
[...]
INFO:  INSTALL_TYPE                                           Administrator


Problem using sample schemas by Oracle in Oracle APEX I am new to Oracle and Oracle APEX. I have been practicing to make Oracle APEX database application using "hr" schema of the database. Before doing so, I unlocked the schema, along with schemas "scott", "pm", "sh" - so that I can work using them later.

Now I am trying to make an application using "scott" schema. The schema is already unlocked, so I can connect to this schema using sqlplus or TOAD.

C:\Users\srl13>sqlplus scott/scott

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 7 15:39:23 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to create a workspace, I cannot select "scott" as the active schema for the workspace - as the workspace is not shown in the list of existing schemas.

If I manually input "scott" in the "Schema Name" and try to proceed, I get the error

Schema is reserved or restricted

I don't know what went wrong and what's the problem - as I already did same things before making APEX application on "hr" schema. Can you suggest me what to do so that I can make the workspace? I also noticed that although I unlocked a number of schemas, no schema except "hr" and "bi" is shown in the schema selection list; so I basically can't make any workspace on any of the unlocked schemas except "hr" and "bi" - and I presume that I am going to face same error if I try to use those schemas "manually".

How should I solve this issue, so that I can create the workspace(s)?

You can unrestrict it.

You just need to run the APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA proc.

EXEC FLOWS_XXXXXX.APEX_SITE_ADMIN_PRIVS.UNRESTRICT_SCHEMA(p_schema => 'SCOTT');

Replace FLOWS_XXXXXX with the name of your APEX schema.

You can also check which schemas are restricted:

SELECT * FROM flows_XXXXXX.wwv_flow_restricted_schemas;

Documentation link.


Reason of ORA-00604 - ORA-00904: on online Redefining I made Redefining Tables Online on a table . It worked fine but later when I tried to truncate the table I got this error .

ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "CURRENT_SCN": invalid identifier.

also I get the same error when I move the table:

 alter table tab1 move;

what is the reason of the error ? If you need more information please ask me.

Performing online redefinition creates materialized view log on the source table, that is where DML changes are tracked during the redefinition process.

Finishing the online redefinition gracefully (finish_redef_table or abort_redef_table) removes the materialized view log automatically, otherwise it will stay there.

Truncating the table with an existing materialized view works with the following syntax:

truncate table tab1 purge materialized view log;

Or just simply drop the materialized view log before.

Without specifying the above, truncate fails because of a recursive query on SYS.MLOG$, as that table does not have the column CURRENT_SCN (version <= 11.2). It is fixed from 12.1.


Online page restore hitting 1000 limit I've been tasked to try to recover a database which suffered from corruption (due to I/O failure, which has been fixed since). I'm not familiar with the database or what it contains.

I've been given an old (~3 weeks) full backup and a series of transaction logs... however there are missing transaction logs, so I can only recover up to a certain date. There's like 2.5 weeks of data missing (and there's a lot of data being added to this database constantly).

I also have been given a copy of the corrupt database (which is accessible, but with a lot of pages corrupt/missing).

I've tried the typical DBCC CHECKDB commands (still no repair_allow_data_loss, that will be my last resort if nothing else works).

After many comes and goes to the database (the db is a 1.5 terabyte little monster and everything I do is slow and takes a while), I've tried to do an online page restore from the last known good backup for the corrupt pages.

To do that, I've done an script that creates many RESTORE DATABASE <foo> PAGE='pages' FROM DISK='<bar.bak>' commands from the DBCC CHECKDB output (básically a regex and a distinct)... so far so good, this worked up to a point where it said I had reached a limit of 1000 pages per file (there are 8 files on this db) per restore command.

So it asks me to "complete the online restore", but I'm at a loss at how to do that... I don't have a tail log or anything more complete than the full backup I'm starting with, so I basically don't know how to complete the restore to keep trying with the rest of pages.

I've tried a RESTORE DATABASE <foo> WITH RECOVERY but that didn't work either, it asks me for a log which I don't have.

Does anyone have any tips on how I could try to recover anything from here? Or how to "complete" the online restore so I can keep trying to recover more pages? Would I have the same problem if I try an offline restore (basically adding WITH NORECOVERY to everything and then try to bring it back at the end?)

Working out the database by hand is basically undoable... there are hundreds of tables with millions of rows and there's no clear meaning of what any of it are. The corrupt DB will fail on SELECT queries after some million of rows but I'm not sure I can work out where. I've tried rebuilding all non-clustered indexes, but there are corrupt pages with row data, so that didn't work either.

Some data loss would be acceptable, but consistency on the DB should at least try to be achieved.

The corrupt database is -still- online and clients are working on it (so it keeps getting new data), so any process I do on the lab bench should be reproducible on the production database afterwards (downtime will be hard for it).

This is SQL Server 2014 Enterprise

PS: I'm no DBA... I'm a programmer, but the client has tried some "expert" sql disaster recovery services and they have given up, so I've been asked to look at it and see if I could do anything.

Update: after many tests, the page by page restoring was a no-go, so we've ditched the idea. We are going for a manual recovery (manually selecting missing records from the corrupt tables and inserting them in the last known good backup), doing some automated tools for it (again, there are hundreds and hundreds of tables).

The standard procedure would be to:

Obtain the page IDs that have to be restored.
Start a page restore with a full database.
Apply the most recent differential backup.
Apply subsequent log backups.
Create new log backup.
Restore the new lob backup.

After the new log backup has been applied, the page restore is completed and the pages are then usable.

Example Restore

RESTORE DATABASE <database> PAGE='1:57, 1:202, 1:916, 1:1016'
   FROM <file_backup_of_file_B>
   WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
   WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
   WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>;
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
GO

Reference: Restore Pages (SQL Server) (Microsoft Docs)
Reference: RESTORE Statements (Transact-SQL) (Microsoft Docs)

However, you have holes in your TLOG backups, and restoring with above procedure might bring your database back into a state in time that you do not desire.

You are in a complicated situation.

Your database has corrupt pages and your company is constantly adding new data to a database with issues. This could result in a total downtime of the database. Do you want to risk that?
Somebody is going to be held responsible and the more you try to fix it, the more management might be inclined to decide that you might be that person in the end. Do you want to risk that?
You are putting yourself in a difficult situation by taking on a role that you were not employed for. You are trying to achieve something that neither your company DBAs nor your external consultant were capable of. While it may seem to be a noble gesture, you are putting yourself at risk. You might have "implicitly promised" something that you will never be able to fulfil. Do you want to risk that?
When somebody working with the database queries data that is corrupt, they are possibly going to receive an error message. Daily work is already being impacted. The longer you wait with the inevitable the more productivity will be impacted. Do you want to risk that? (This question could also be raised with management)
Your company's backup procedure seems to be faulty (otherwise how would TLOG backups be missing?) and you are still running your production database as if there were no issues. Do you want to risk that?

The best recommendation I can give you is to halt production and call Microsoft!
Or at least call Microsoft and possibly halt production.

While my writing may seem overly cautious and slightly dramatised from your perspective, I can personally relate to an experience as DBA where data was lost in a similar situation. We only lost half a days data, but we had to re-synchronise a lot of data with surrounding systems.

The longer you wait the more expensive recovery could become.

As for the limitation on page restores, here a quote from the official documentation:

  The maximum number of pages that can be restored into any single file in a restore sequence is 1000. However, if you have more than a small number of damaged pages in a file, consider restoring the whole file instead of the pages.

(emphasis mine)

Reference: RESTORE Statements - Arguments (Transact-SQL) (Microsoft Docs)

When all is back to normal, the DBAs and/or external consultants might want to consider implementing a different backup/restore policy/procedure for your database. As it has to be up 7x24 you can't risk having a backup procedure that doesn't provide adequate restore capabilities for any situation.


Database table to keep track of objects I have a table called FoodMaker and another table called Settings. Every FoodMaker has multiple Settings. I also need a 3rd table to record what has been made, when and with what Settings. It might have these fields: id, food_made, date_time, settings_used.

This is pretty straightforward since I can put IDs' of FoodMaker and Settings in the 3rd table to reference them. But the problem is, the Settings may change later but I want the Settings which the food has been made with and not the updated one. So I need to take a snapshot of Settings each time I'm INSERTING into 3rd table.

One easy way could be just to add all of the Settings fields to the 3rd table on each INSERT. But is it not redundant and ugly? Is there a better solution?

I asked this question on SO but then I figured out this might be a better place to post it.

It is quite common to copy the data as it stands at the point in time that a transaction occurred - in your case the Settings when a FoodMaker made something.  This happens with financial systems a lot for audit and reporting purposes.  Yes, it is duplication, but in a known and controlled way for a purpose.

The alternative is to add time range columns to Settings.  These delimit the interval in which those settings values are applicable.  The third table must hold the datetime when a row is inserted to it, as you propose.  When you read you add the predicate

.. and Third.date_time >= Settings.FromDateTime and Third.date_time < Settings.ToDateTime

With this approach you have to be very careful to never update values in Settings, only ever the ToDate, or insert whole new rows.  Ensuring there are no gaps or overlaps in these time ranges can be tricky, too.  Frequent changes to Settings can cause this table to bloat, slowing down the transactional part of your application.  Eventually you will want to purge old Settings which will fragment the table.

With a separate audit table containing snapshots of values most of these concerns go away.  You can deny all UPDATE activity on it, allowing only INSERTS so there is no risk of history changing.  The table can be partitioned by date allowing for fast purging.  Indeed, the whole table can be removed to a separate data mart for long-term storage and reporting.


Compare each column in a row and return an error if any of them is different I'm attempting to find any identifiers with any values that are different in the same row.

+------------+--------+--------+--------+--------+
| Identifier | Value1 | Value2 | Value3 | Value4 |
| f001       | a1     | a2     | a2     | a2     |
| f002       | a4     | a4     | a4     | a5     |
| f003       | a2     | a2     | a2     |        |
| f004       | a1     | a1     | a1     | a1     |
| f002       | a9     |        |        |        |
+------------+--------+--------+--------+--------+

For example,

The first identifier should return "MisMatch",
The second identifier should return "MisMatch",
The third identifier should return "NoIssue",
The fourth identifier should return "Mismatch",
The fifth identifier should return "NoIssue",

Any help would be great, I'm stuck at this point.

Some lines have hundreds of columns, while others only have one. I'm hoping to be able to locate any line that contains any mismatch.

I'm using SQL Server 2008 R2.

I see this question is tagged both Excel and SQL Server.

In Excel you could use the approach from here

The formula in cell F2 above is

=IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""), IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""))>0,1))>1,"MisMatch","Match")

Pay attention to the note in the linked article

  The formulas in this example must be entered as array formulas. Select
  each cell that contains a formula, press F2, and then press
  CTRL+SHIFT+ENTER.

Curly braces will then appear around the formula in the formula bar

Another (SQL Server) way (credit to Geoff Patterson for improvements) would be

SELECT T.Identifier,
       CASE
         WHEN min_val <> max_val
           THEN 'MisMatch'
         ELSE 'NoIssue'
       END
FROM   @T T
       CROSS APPLY (SELECT MIN(Val),
                      MAX(Val)
                    FROM   (VALUES (Value1),
                                   (Value2),
                                   (Value3),
                                   (Value4)) V(Val)) V(min_val, max_val)

It should work well for the "hundreds of columns" mentioned.

Though it introduces some extra complexity into the plan compared to just a simple compute scalar.


Index on a highly updated field causing excessive locks in SQL server I'm in a bit of a conundrum about what the best course of action on this (bad?) index is. I have a table which i use as a notification holder that i watch for database changes with.

CREATE TABLE [dbo].[TrackSessions](
[DataPartID] [int] NOT NULL,
[UserMasterID] [int] NOT NULL,
[RecordID] [bigint] NOT NULL,
[DateChanged] [datetime2](7) NOT NULL,

CONSTRAINT [PK_TrackSessions] PRIMARY KEY CLUSTERED
(
    [DataPartID] ASC,
    [UserMasterID] ASC,
    [RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

When a record from any number of other tables is updated i use a trigger to record the change. Then a SqlChangeNotification is triggered on the table and i use the following select to get the new info. This is done several times a second constantly.

SELECT
  [DataPartID],
  [UserMasterID],
  [RecordID],
  [DateChanged]
from dbo.TrackSessions WHERE DateChanged > @DT ORDER BY DateChanged DESC

Here is the index in question:

CREATE NONCLUSTERED INDEX [DateChanged-cr20151217] ON [dbo].[TrackSessions]
(
    [DateChanged] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Here are the index usage statistics over a 3 week period. (courtesy of [sp_BlitzIndex])

dbo.TrackSessions.DateChanged-cr20151217 (5):
   Op Stats: 0 singleton lookups; 5,553,690 scans/seeks; 0 deletes; 0 updates;
   Reads: 5,529,416 (5,529,416 seek) Writes:1,455,333
   Row lock waits: 212,192; total duration: 226 minutes; avg duration: 0 seconds;
   Page lock waits: 232; total duration: 10 seconds; avg duration: 0 seconds;
   Lock escalation attempts: 1; Actual Escalations: 0.
   Size: 26,741 rows; 7.3MB

For reference here is one of the triggers used to update the rows.

set transaction isolation level serializable
begin transaction
    if exists(select *
                from inserted i with (updlock)
                inner join userD u on u.id = i.id
                inner join UserMaster m on m.UserLoginId = u.userid
                inner join TrackSessions s on s.DataPartID = 2 and s.UserMasterID = m.UserMasterID and s.RecordID = i.id)
    begin
        update s set DateChanged = SYSDATETIME()
        from inserted i
        inner join userD on u.id = i.id
        inner join UserMaster m on m.UserLoginId = u.userid
        inner join TrackSessions s on s.DataPartID = 2 and s.UserMasterID = m.UserMasterID and s.RecordID = i.id
    end
    else
    begin
        --First time this event fired. Should only happen once for a given user
        insert into TrackSessions (DataPartID,UserMasterID,RecordID,DateChanged)
        select 2,m.UserMasterID,i.id,SYSDATETIME()
        from inserted i
        inner join userD u on u.id = i.id
        inner join UserMaster m on m.UserLoginId = u.userid
    end
 commit

So my problem is that i'm getting deadlocks on statements associated with updating this table. I can't quite figure out what exactly the cause is but I believe this is because of[DateChanged] is being fought over.

This is by far the most used index on that table and is extremely valuable in terms of that select performing quickly. But it appears to also be incredibly expensive to maintain.

I'm not sure exactly what to do about it. Would changing the dateChanged data type to something like [datetime2](1) help with the expense of updating? How about just a [datetime]? Should i just stop trying to index it and let the select query scan the table instead?

Its ok if the select takes a second or so longer if it means that i have a reduced chance of deadlocks and i don't really care about fractions of a second as long as the select query will pickup the changed rows.

I'm running on SQL server 2012 with the latest updates in a mirror'd configuration. The pulling application is a .NET SqlConnection.

Instead of using the DateChanged column in the where clause perhaps you could add an IDENTITY column, and use it in the .Net client to keep track of processed rows?  The .Net client could periodically save the last processed identity value in case it needs to restart.

This would allow you to get rid of the index on DateChanged completely, and queries getting the new rows would be fast since they would be performing a range scan on values greater than the last processed identity value.  Something like:

SELECT
  [TrackSessionID],
  [DataPartID],
  [UserMasterID],
  [RecordID],
  [DateChanged]
FROM dbo.TrackSessions
WHERE [TrackSessionID] > @LastTrackSessionID
ORDER BY DateChanged DESC;

A potential downside to this approach might be the IDENTITY column becoming a source of contention for inserts into the table.  That could potentially be mitigated by using several partitions with a partition function that uses a hash function to decide which partition each insert goes into.

Something like this comes to mind:

CREATE PARTITION FUNCTION pf_TrackSessions (INT)
AS RANGE RIGHT FOR VALUES (1, 2, 3);

CREATE PARTITION SCHEME ps_TrackSessions
AS PARTITION pf_TrackSessions
ALL TO ([SessionFileGroup1]
    , [SessionFileGroup2]
    , [SessionFileGroup3]
    , [SessionFileGroup4]);

CREATE TABLE dbo.TrackSessions
(
    TrackSessionsID INT NOT NULL IDENTITY(1,1)
    , TrackSessionsIDModulus AS (TrackSessionsID % 4) PERSISTED
    , DataPartID INT NOT NULL
    , UserMasterID INT NOT NULL
    , RecordID INT NOT NULL
    , DateChanged DATETIME2(7)
        CONSTRAINT DF_TrackSessions_DateChanged
        DEFAULT (GETDATE())
    , CONSTRAINT PK_TrackSessions
        PRIMARY KEY CLUSTERED
        (TrackSessionsIDModulus, TrackSessionsID)
) ON ps_TrackSessions (TrackSessionsIDModulus);
GO

The above DDL requires 4 filegroups, one for each partition.  Rows would be evenly distributed among the 4 filegroups based on the modulus of the identity value.  This reduces contention against the allocation bitmap pages by splitting the load across 4 sets of each structure.


Bus timetable Database structure I am creating a database for bus timetable in android app. I have created four tables: stops, route, detail_route and schedule. columns in each table are as follows.

Database structure

route (id, name)
stops (id, route_id, name)
detail_route (route_id, stop_id, time, stop_order) here time means: time require to reach to that stop
schedule (source_stop_id, destination_stop_id, route_id, start_time)

Now I have a problem here.  User can select any source and any destination, but in the database I am only storing data of bus about source, destination and route.

Ex:
Here Bus route is from Point A to E :

A > B > C > D > E

I will store this entry in schedule table as

source_stop_id : A

destination_stop_id : E
route_id : 1
start_time : 9:15 AM

now if user select source as B and Destination as D. There is no bus available from B to D according to my database. Here same bus should be shown because it is on same route. I don't know how to do it. please suggest.

You'll need to redefine your database to store that information. An example is the following:

CREATE TABLE routes (
  route_id int PRIMARY KEY,
  route_name varchar(200)
  );

CREATE TABLE stops (
  stop_id int PRIMARY KEY,
  stop_name varchar(200)
  );

CREATE TABLE route_details (
  route_id int,
  stop_number int, -- starting from 1
  stop_id int,
  leg_minutes int, -- minutes from the previous stop to this one (0 for stop_number=1)
  FOREIGN KEY (route_id) REFERENCES routes (route_id),
  FOREIGN KEY (stop_id) REFERENCES stops (stop_id),
  PRIMARY KEY (route_id, stop_number)
  );

CREATE TABLE schedules (
  route_id int,
  weekend bool,
  start_time varchar(8)
  );

-- Query which route goes from B to E
SELECT r.*
FROM routes r
JOIN route_details rs ON (rs.route_id = r.route_id)
JOIN stops ss ON (ss.stop_id = rs.stop_id)
JOIN route_details re ON (re.route_id = r.route_id)
JOIN stops se ON (se.stop_id = re.stop_id)
WHERE ss.name = 'B' AND se.name = 'E' AND rs.stop_number < re.stop_number;

When designing a database, I find it convenient sticking to a single naming convention, either plurals or singulars (i.e. routes and stops, or route and stop). Here I opted for all plurals. Also, suffixes work better than prefixes for listing tables alphabetically (it doesn't really matter in this case because there are so few tables, but it still is good practice).

This design allows you to have the same stop shared in different routes. Note I've included a weekend in schedules. This was just a suggestion, since sometimes bus tables have different schedules for weekdays and weekends.

The query would be significantly more complex for finding a route from a stop to another one using two routes, but it can be done. It also allows you easily calculate travel time, and stop times at subsequent bus stops.


Complex constraint across all data in a table We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status.

I want to ensure that the result of this is always either zero or one:

select count(id) from jobs where status in ('P', 'G');

We're using explicit transactions so ideally this check would happen on commit and abort the transaction if the invariant doesn't hold. Handling any racy processing that throws the occasional error is much easier for us to deal with than suddenly ending up with more than one job that is 'in process'.

The solution only needs to work with Postgres so we're quite happy to take a non-standard solution to this. We're currently on 8.4, but we'll be upgrading at some point to 9.x if that makes any difference.

Tested with Postgres 9.1 & 9.2. Most of it should work for 8.4 as well.

Simple case

Without intermediate states violating the constraint over the course of a single transaction, a partial UNIQUE index on a constant value does the job. Given this test case:

CREATE TEMP TABLE jobs(jobs_id int primary key, status text);
INSERT INTO jobs (jobs_id, status)
VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'G');

Use this index:

CREATE UNIQUE INDEX jobs_status_uni_idx ON jobs ((TRUE))
WHERE status in ('P', 'G');

Note the additional set of parenthesis around the value TRUE.

Test (one line at a time):

INSERT INTO jobs (jobs_id, status) VALUES (5, 'G'); -- fails
INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- fails

DELETE FROM jobs WHERE status = 'G';

INSERT INTO jobs (jobs_id, status) VALUES (5, 'P'); -- succeeds
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G'); -- fails

SQL Fiddle.

Related answer with more explanation:
PostgreSQL multi-column unique constraint and NULL values

Advanced case with DEFERRED constraint

  ideally this check would happen on commit

A partial unique index cannot be deferred and is always checked immediately. Therefore, continuing the example from above, this would fail:

BEGIN;
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- fails immediately!
DELETE FROM jobs WHERE status = 'P';
COMMIT;

Example is with explicit transaction handling. Your client may have automatic transaction handling (autocommit on or off).

If you need this to work, you need a DEFERRABLE constraint that is INITIALLY DEFERRED or SET CONSTRAINTS ALL | name  DEFERRED within the transaction.

I quote the manual here

  Non-deferred Uniqueness Constraints

  When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
  checks for uniqueness immediately whenever a row is inserted or
  modified. The SQL standard says that uniqueness should be enforced
  only at the end of the statement; this makes a difference when, for
  example, a single command updates multiple key values. To obtain
  standard-compliant behavior, declare the constraint as DEFERRABLE but
  not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
  significantly slower than immediate uniqueness checking.

More gory details about Index Uniqueness Checks in the manual.

However, a UNIQUE CONSTRAINT can only be defined for columns, not for expressions.
Add a redundant column for this purpose and keep it current with triggers. Like this:

ALTER TABLE jobs ADD COLUMN status_uni boolean;
UPDATE jobs set status_uni = TRUE WHERE status in ('P', 'G');  -- rest stays NULL

ALTER TABLE jobs ADD CONSTRAINT jobs_status_uni
UNIQUE(status_uni) DEFERRABLE INITIALLY DEFERRED;

The additional column is cheap, since it is filled with NULLs, an existing NULL bitmap should normally swallow it without the need for additional physical disk space.
NULL values do not violate a unique constraint per definition.

Create triggers to keep the column current at all times:

CREATE OR REPLACE FUNCTION trg_jobs_status()
  RETURNS trigger AS
$func$
BEGIN
NEW.status_uni = (NEW.status IN ('G', 'P') OR NULL);
RETURN NEW;
END
$func$  LANGUAGE plpgsql;

For all inserts:

CREATE TRIGGER insbef
BEFORE INSERT ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

To optimize, only for relevant updates:

CREATE TRIGGER upbef
BEFORE UPDATE OF status, status_uni ON jobs
FOR EACH ROW EXECUTE PROCEDURE trg_jobs_status();

Test

Now your transactions go through, as long as the state is consistent at the end:

BEGIN;
-- SET CONSTRAINTS jobs_status_uni IMMEDIATE;  -- only for INITIALLY IMMEDIATE
INSERT INTO jobs (jobs_id, status) VALUES (6, 'G');  -- check deferred
DELETE FROM jobs WHERE status = 'P';
COMMIT;  -- succeeds!

SQL Fiddle.


SQL 2008 R2 - Odd timeout issues I'm having some recent timeout issues in our SQL 2008 R2 server. This started occurring late last week and has since somewhat improved.

Issues:
When using SSMS and right clicking over a database...menu will delay on showing. Could be anywhere from 2-3 seconds to 10 seconds.

When trying to access reports in SSMS, hour glasses. It will either error out or eventually show the reports.

When using Team Foundation Server and checking in changes, It won't check in due to timeouts.
When debugging web apps, can timeout when accessing DB

I've looked at profiler and haven't seen anything that stands out. CPU utilization is low, memory is fine, and there is no locking issues of any sort.

Problems go away when:
Logged into the local DB server and connect via SharedMemory? "[local]". Everything is quick and runs as it is suppose to. When I switch the connection to use TCP/IP ["127.0.0.1"] I see the same delays.

Again, this doesn't happen all day....mornings are typically fine and then points in the after noon I'll start seeing timeouts.

Not seeing anything odd in: "select * from sys.dm_exec_requests order by status". Most are showing sleeping.

Not seeing anything odd in: "select * from sys.dm_exec_sessions order by login_time desc". About 48 active sessions.

 sp_monitor

 cpu_busy           io_busy         idle
 31331(2593)-17%    573(41)-0%  4025036(224233)-1531%

 packets_received         packets_sent  packet_errors
 6131943(343656)          6305718(356043)   0(0)

 total_read           total_write       total_errors    connections
 1372503(103382)      1678338(140267)   0(0)            5324156(296335)

 select @@MAX_CONNECTIONS == 32767

Try looking at a couple of things to help determine if you are having issues with the network.
Wait Stats

ASYNC_NETWORK_IO

On The Server

Network Interface:Output queue length
Redirector:Network error/sec

This should always be zero

TCPv4:Segments retransmitted/sec & TCPv6:Segment retransmitted/sec

When divided over the total number of segments should not be more than 3%.