How to create trigger on database that 'triggers' when mirroring role changes from mirror to principal I want to perform some sql when my database mirroring state changes

Create an event notification for the Database Mirroring State Change. See Implementing Event Notifications for how to design a service that processes the notification and does whatever is appropriate. Keep in mind that event notifications are asynchronous.


PostgreSql - Search cost with compound selection Assume i have the following query:

SELECT * FROM my_table WHERE Name="Tom" AND age=18;

The cost of finding records with
Name="Tom" is B1=130 blocks and the cost of finding records with age=18 is B2=200 blocks.

What would be the cost of my query ?
Is is the minimum between the two?

Thanks in advance!

The cost of performing an SQL query depends on many factors. The way to really know is to ask the database to actually perform the query and measure it:

That would be done with the EXPLAIN command:

EXPLAIN ANALYZE VERBOSE SELECT * FROM my_table WHERE Name='Tom' AND age=18;

The database will output the execution plan that it decided that was the best (with the given configuration settings), together with the estimated and actual costs and row counts.

If the query is asumed to take a long running time, or you just want to know which is the execution plan that the database would execute, you can just use

EXPLAIN SELECT * FROM my_table WHERE Name='Tom' AND age=18;

This will just output estimated costs and number of rows.

For your specific case:

If the database doesn't have an index on columns Name nor age, it will be forced to perform a complete sequential scan (that is, it will have to look all the rows in the table, and take only the ones where the condition Name='Tom' and age=18 is met).

If the database has one index on either the Name or the age columns, and the number of rows in the table is big, and the number of rows where Name is 'Tom' or age is 18 is (according to the statistics collected by it) is comparatively low, it will use this index to fetch only the rows where one of the conditions is met, and check whether the other condition is also met.

If the database has one multi-column index containing both columns as the first two (or the only two), such as (age, Name, ...) or (Name, age, ...) it will be able to fetch the rows by finding the rows it must retrieve by just using this index.

What the database actually does (by means of its query planner/optimizer) is: check all (or a reasonable number of) the possible execution plans to retrieve the information; make an educated guess at how long it will take to execute each of them, and choose the fastest (cheapest in terms of cost). Some parameters in the database configuration may further limit the plans which can be considered.


DBA's - What did you do that helped you gain the most understanding in your role? I'm hopeful this question isn't so subjective that it's closed, as I think it can be helpful for newer DBA's who want to get a better overall understanding of being a competent, knowledgable DBA who is skilled in their trade.

What did you do that helped you gain the most understanding in your role?
Was there any training, books, experiences, that best helped you gain an understanding of your role and how to perform it well?
If you could summarize your best advice for someone to get a good overall understanding of how to administer Microsoft SQL-Server, what would you say based on your specific journey?

Thank you.

If you're referring to Microsoft SQL Server then my path directly applies, otherwise you can derive the same generalizations and apply them to the database system you're learning about. The following four things were the major contributors to how I grew in my DBA knowledge:

Brent Ozar - Specifically I was lucky enough to stumble on his podcast Office Hours and would listen to all the old ones everyday until I was caught up and then I'd participate in asking questions in the new live sessions. (You can find the podcast on any modern podcast system.) This helped introduce new key terms and system features in my brain, even if I didn't fully understand them at the time. Just hearing the vocabulary used over and over was super helpful in itself.

RTM (Reading The Manual) - After I got hooked on Brent Ozar, and learned a lot of new key terms, I'd read the Microsoft documentation a few times over on that feature to try to understand its purpose and how it's used. Some days I'd scan page after page in the documentation looking for more features I wasn't aware of. I was hungry to have at least heard of every feature SQL Server had to offer.

Reputable Articles and Blogs - Aside from all the content on Brent Ozar's website, there's a ton of reputable sites out there with awesome information on everything SQL Server under the sun. To name just a few of the many, SQLPerformance, RedGate's Simple Talk, Erik Darling Data, Kendra Little, Pinal Dave's SQL Authority, anything written by Aaron Bertrand on the multitude of places he contributes to (including SQLPerformance), among many many others out there. I initially stumbled on a lot of these from Googling issues I ran into with SQL Server as they came up, or when trying to further my knowledge from the Microsoft docs.

DBA.StackExchange - This site has always been greatly helpful for me, especially when I was in my early phases of learning and got stuck. I learned a lot from the many questions I asked on here, and resultantly continue to further my expertise as I share that knowledge to help others on here or as I am forced to research more into the answers I provide to others.


Best way to design / query multi-level relationship table My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database design is good enough to handle the searches.

The main concern I have is, not only should the user be able to search the services by name or description, but also by service location.

Let me first show you what I designed:

Solution 1
Table: Service
+------+----------+--------------+--------+----------+----------+
| id   | name     | description  | cityid | address1 | address2 |
+------+----------+--------------+--------+----------+----------+
|     1| Service1 | Description1 |       1| Address1 |          |
|     2| Service2 | Description2 |       2| Address2 |          |
|     3| Service3 | Description3 |       3| Address3 |          |
+------+----------+--------------+--------+----------+----------+

Table: City
+------+-----------+---------+
| id   | name      | stateid |
+------+-----------+---------+
|     1| KL        |       1 |
|     2| Georgetown|       2 |
|     3| JB        |       3 |
+------+-----------+---------+

Table: State
+------+---------------------+-----------+
| id   | name                | countryid |
+------+---------------------+-----------+
|     1| Wilayah Persekutuan |         1 |
|     2| Penang              |         2 |
|     3| Johor               |         3 |
+------+---------------------+-----------+

Table: Country
+------+-----------+
| id   | name      |
+------+-----------+
|     1| Malaysia  |
|     2| Singapore |
+------+-----------+

Above are my tables with some sample data. The requirement is to allow the user to enter a keyword to start searching in the Service table – if the keyword matches a service name, then return that result from Service. If not, then search for the keyword in the City table – if the keyword matches a particular City row, then get all the Service rows that match that city by the cityid. If no row is found, then go on searching in the State table, up until the Country table.

So here is a problem with my solution. If the user enters a Country name, I will get a match result from the Country table, then I use the result's countryid to get all the related states in the State table, then I use those results' stateids to get all related cities in the City table – and only then I use the last results' cityid to search for the related services in the Service table. Which I think is not a good approach as it needs to use a lot of recursive IN searches to filter the results, which is not good for performance.

My colleague then came up with another solution, which put all the search related references into the Service table – something like this:

Solution 2
Table: Service
+------+----------+--------------+--------+----------+-----------+----------+----------+
| id   | name     | description  | cityid | stateid  | countryid | address1 | address2 |
+------+----------+--------------+--------+----------+-----------+----------+----------+
|     1| Service1 | Description1 |      1 |        1 |         1 | Address1 |          |
|     2| Service2 | Description2 |      2 |        2 |         2 | Address2 |          |
|     3| Service3 | Description3 |      3 |        3 |         3 | Address3 |          |
+------+----------+--------------+--------+----------+-----------+----------+----------+

So, if Country matched the search, then use the result's countryid to perform search straight in the Service table, otherwise, if State matched the search, then use the result's stateid to perform the search in Service and so on. This method is more efficient as it doesn't have so much recursive searches, but the downside is it violates the Normalisation practices: the Service table has redundant info, which is countryid and stateid. And logically speaking, I can find those two IDs with only cityid, even though I need to perform multiple look-ups/joins.

So which solution should I go for? Or do you have a better suggestion? Please advise.

I would be tempted to deploy all of the techniques you've mentioned. Let me explain. The normalised Service/ City/ State/ Country is great for OLTP processing. So retain it for that part of the application and treat it as the actual data store.

As you mentioned, this is likely to make search complicated so denormalising would be advantageous. Unlike your proposal I would denormalise into a separate table which exists only to assist search. It is an explicit acknowledgement that a real DBMS on an actual computer does not have the performance characteristics the theoretical computer scientists would like to imagine. By putting it in a separate table one isolates the compromised physical necessity from the preferred, normalised design.

Finally I would combine all the search terms in a single column in this denormalised table. One way would be to together the Service Name, City Name, State Name and Country Name, space delimited, and place a full text index on that column.

Another would be to have three columns - id, name and source_table. The first two come from the data values listed in the question. source_table says which of the normalised tables this comes from. Sample data, taken from the question, would be:

  id     name      source_table
  -----  --------  ------------
  1      Service1  Service
  2      Service2  Service
  ...
  1      KL        City
  3      JB        City
  ...
  2      Penang    State
  ..
  1      Malaysia  Country

The query would match the user's search term against this table's name column:

select
  id,
  source_table
from search_table
where name = <user-provided value>

The returned id and source_table can be used to determine which values to choose from the normalised tables. These can be joined to each other in the normal fashion. Since there are four layers in the hierarchy there can be only four possible queries (service, service/ city, service/ city/ state, service/ city/ state/ country). It would be simple to call the appropriate one like this:

if source_table = "Service"

  select
    <columns>
  from Service
  where id = <search_table.id>

if source_table = "City"

  select
    <columns>
  from Service
  inner join City
    on Service.cityid = City.id
  where City.id = <search_table.id>

... etc.

Be aware that names may be duplicated across these levels (Monaco, for example) and decide what you wish to do with these cases.

The process could be simplified somewhat if names could be guaranteed to be unique and natural keys were used rather than surrogate ones.

I wonder, though, if all this effort is worthwhile. How many rows will there be in these tables? How poor is performance of a query on the normalised tables, with indexes, that compares the user's search value to each name column with ORs between? By creating a representative amount of dummy data and measuring response time on production-like hardware you could save some development, and a lot of maintenance, work.


RowNum For T-SQL (Not TOP #) Trying to run an update, but I want to step through it as it's quite a bit of data and don't want to blast it all at once.

In oracle it's a bit easier to select the rows I want because you can include it in the WHERE clause, but T-SQL you include it in the select. I'm wanting to write it something along the lines of this:

Update Instances
set thing = 'new thing'
Where rownum<500

Basically want to do 500 at a time, is this syntax possible in TSQL?

Have a look at MS Docs about it:

Limitations and Restrictions
When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic.

UPDATE TOP(500) instances
SET thing = 'new thing'
WHERE Something = 'SomethingElse';

Or
UPDATE instances
SET thing = 'new thing'
FROM (SELECT TOP (500) Id
      FROM instances
      ORDER BY Something) AS t1
WHERE instances.Id = t1.Id;

Or you can update the derived table directly without a join:
UPDATE t1
SET thing = 'new thing'
FROM (SELECT TOP (500) *
      FROM instances
      ORDER BY Something) AS t1;

An example of this approach can be found at dbfiddle.


Cannot log in to SQL Server 2008 R2 I have problem with my SQL Server 2008 R2 instance. I want to log in using windows authentication and server name "local", but error 2 is returned. I've tried to login with a server name of "IP address of my server" but again it failed. Does anybody know what should I do?

Error message:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

Make sure you TCP/IP is enabled under SQL SErver Network Configuration

If it is disabled than please enable it and restart services for that instance.

you will be able to log-in.


How compatible are Sybase and SQL Server clients connecting to the alternate back end? Given the shared history of the Sybase and SQL Server products, and their use of a similar (identical?) wire protocol in TDS, what definite statements can be made, if any, about cross compatibility.

If I have a legacy system (binary, no source) that was written against a Sybase 11 back end, is it likely to run against SQL Server 2008?

Similarly, if I have a Windows binary, linked against sybase libs, would it just work against a Sybase server, in general or of specific vintage/versions?

It's true that Sybase and the Microsoft SQL Server databases share common roots, but they are not any more compatible.

Yes, both engines support T-SQL, they have very similar syntax, even many functions looks and works the same. But this is not enough, between the two products have appeared too many differences at this moment. If you want to migrate between them you need to review code, it's easier because many similarities, but must be done.

Microsoft recommended to use SQL Server Migration Assistant (SSMA) for Sybase - it can help you migrate tables, views, indexes, triggers, and stored procedures. In most cases you need to perform just a few manual patches.

Microsoft provided many helpful resources within subject of Sybase and SQL Server:

Similarities and Differences description
Guide to Migrating to SQL Server 2008
Guide to Migrating to SQL Server 2012


Is it possible to combine 7 sources without a UNION? I have a source table that looks essentially like this:

EmployeeCode
WeekStartDate
HoursWorkedDay1
HoursWorkedDay2
HoursWorkedDay3
HoursWorkedDay4
HoursWorkedDay5
HoursWorkedDay6
HoursWorkedDay7

The actual table has something like 500 numbered columns (didn't really count them - there are various and numerous fields numbered 1-7, and then another handful numbered 1-25, times 7) per weekday (no, that's not my design), and there are currently something like 38,600 rows (growing every week).

I have an SSIS package that's trying to normalize this data... that currently looks like this:

Each "source" is selecting one set of numbered columns from the same source table, and the UNION ALL component combines the 7 sources into one, resulting in some 258,900 rows.

The rest of the workflow adds some calculated columns, looks up surrogate keys (e.g. EmployeeCode is used to lookup an EmployeeId, and then the date is computed and used for looking up a TimeId), and then the "modified" rows get updated and the "new" ones get inserted into a normalized table; unchanged rows end up nowhere.

Is there any better way (e.g. a bit less heavy on memory pressure) to normalize the source data?

Without the full table definition, it is difficult to provide a perfect answer.  However, in an attempt to show the differences in a limited repro, with a very small amount of data, I've created the following testbed:

IF OBJECT_ID('tempdb..#src') IS NOT NULL
DROP TABLE #src;
CREATE TABLE #src
(
    EmployeeCode INT NOT NULL
    , WeekStartDate DATE NOT NULL
    , HoursDay1 INT NOT NULL
    , HoursDay2 INT NOT NULL
    , HoursDay3 INT NOT NULL
    , HoursDay4 INT NOT NULL
    , HoursDay5 INT NOT NULL
    , HoursDay6 INT NOT NULL
    , HoursDay7 INT NOT NULL
    , Widget1Day1 INT NOT NULL
    , Widget1Day2 INT NOT NULL
    , Widget1Day3 INT NOT NULL
    , Widget1Day4 INT NOT NULL
    , Widget1Day5 INT NOT NULL
    , Widget1Day6 INT NOT NULL
    , Widget1Day7 INT NOT NULL
    , Widget2Day1 INT NOT NULL
    , Widget2Day2 INT NOT NULL
    , Widget2Day3 INT NOT NULL
    , Widget2Day4 INT NOT NULL
    , Widget2Day5 INT NOT NULL
    , Widget2Day6 INT NOT NULL
    , Widget2Day7 INT NOT NULL
    , PRIMARY KEY CLUSTERED (WeekStartDate, EmployeeCode)
);
GO
INSERT INTO #src (EmployeeCode, WeekStartDate
    , HoursDay1, HoursDay2, HoursDay3, HoursDay4, HoursDay5, HoursDay6, HoursDay7
    , Widget1Day1, Widget1Day2, Widget1Day3, Widget1Day4, Widget1Day5, Widget1Day6, Widget1Day7
    , Widget2Day1, Widget2Day2, Widget2Day3, Widget2Day4, Widget2Day5, Widget2Day6, Widget2Day7
    )
VALUES (1, '2016-09-18'
    , 0, 8, 8, 8, 8, 8, 0
    , ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID()))
    , ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID())), ABS(CHECKSUM(NEWID()))
    );

Below we are comparing the two queries; the first uses the CROSS APPLY method, detailed by me at SQLServerScience.com, and the second uses the UNION ALL method.

SELECT s.WeekStartDate
    , s.EmployeeCode
    , ItemsByDay.DayOfWeekName
    , ItemsByDay.HoursWorked
    , ItemsByDay.Widget1
    , ItemsByDay.Widget2
FROM #src s
CROSS APPLY (VALUES ('Sunday', HoursDay1, Widget1Day1, Widget2Day1)
    , ('Monday', HoursDay2, Widget1Day2, Widget2Day2)
    , ('Tuesday', HoursDay3, Widget1Day3, Widget2Day3)
    , ('Wednesday', HoursDay4, Widget1Day4, Widget2Day4)
    , ('Thursday', HoursDay5, Widget1Day5, Widget2Day5)
    , ('Friday', HoursDay6, Widget1Day6, Widget2Day6)
    , ('Saturday', HoursDay7, Widget1Day7, Widget2Day7)
    ) ItemsByDay(DayOfWeekName, HoursWorked, Widget1, Widget2);

SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Sunday'
    , s.HoursDay1
    , s.Widget1Day1
    , s.Widget2Day1
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Monday'
    , s.HoursDay2
    , s.Widget1Day2
    , s.Widget2Day2
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Tuesday'
    , s.HoursDay3
    , s.Widget1Day3
    , s.Widget2Day3
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Wednesday'
    , s.HoursDay4
    , s.Widget1Day4
    , s.Widget2Day4
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Thursday'
    , s.HoursDay5
    , s.Widget1Day5
    , s.Widget2Day5
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Friday'
    , s.HoursDay6
    , s.Widget1Day6
    , s.Widget2Day6
FROM #src s
UNION ALL
SELECT s.EmployeeCode
    , s.WeekStartDate
    , 'Saturday'
    , s.HoursDay7
    , s.Widget1Day7
    , s.Widget2Day7
FROM #src s;

First thing to note, the CROSS APPLY is easier to look at.  This already makes me happy.

Lets check the execution plans for the two variants:

The UNION ALL variant scans the source table 7 times, whereas the CROSS APPLY uses a single table scan.  By using the cross apply, we're #Winning.

Let's add more data:

/* create a table with 2 years worth of week start dates */
IF OBJECT_ID('tempdb..#Weeks') IS NULL
BEGIN
    CREATE TABLE #Weeks
    (
        WeekStart DATE NOT NULL
        PRIMARY KEY CLUSTERED
    );

    INSERT INTO #Weeks (WeekStart)
    SELECT TOP(104) DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY o1.name) - 1) * 7, '2016-01-03')
    FROM sys.objects o1
        CROSS JOIN sys.objects o2;
END

/* remove the single row from the source table we inserted above */
TRUNCATE TABLE #src;

/* insert a load of rows into the #src table */
INSERT INTO #src (EmployeeCode, WeekStartDate, HoursDay1, HoursDay2, HoursDay3, HoursDay4, HoursDay5, HoursDay6, HoursDay7)
SELECT ABS(CHECKSUM(NEWID()))
    , w.WeekStart
    , 0, 8, 8, 8, 8, 8, 0
FROM #Weeks w
    CROSS JOIN sys.objects o1;

On my system the above code generated around 85,000 rows.  The plans for the two queries are now:

SQL Sentry Plan Explorer shows the following summary information, which is invaluable:

This says the CPU is used more intensively by the CROSS APPLY, however there is 7 times more I/O used by the UNION ALL variant.


Transfer relational data from one DB to another production DB currently populated with data - MySQL Details
So, unfortunately, at work the other day we put a site into production after some editing/changes that still had the development database configuration. As a result, we have several records spanning across 4 tables in the development DB that need to be appended onto the production DB.
Currently I have the necessary data extracted into temporary tables on the development DB.
Table structures
All of the id's are auto-inc
t1 - Contact information - contains contact_id
t2 - Submission information - contains submission_id
t3 - Swing table - contains contact_id & submission_id
t4 - Submission attributes - contains submission_id
Possible solutions
I have tried just to simply use a query like this for each table:
insert into Production.submission (select null, col1, col2, etc from Dev.tmp_submission)

However, I quickly found that this is tedious & very error prone. Records could be inserted while I am working and I may get id's mixed up, etc..
I have thought about adding a very high number to all of the id's in the development data set such as 50000 (the highest id in production out of all of the tables is around 28000). then Just insert all of the data into production. Would MySQL figure out the auto-inc values for all of the tables if I did this or would there be a clash eventually?
Conclusion
How could I get this relational data from the development database to the production database?
Clearly, I am not the most seasoned of DB admins. Would really appreciate some answers! Please, let me know if there is any other information you might find useful.

You'll have to remap them to a non-conflicting insertion range, and to create that you'll need to adjust the AUTO_INCREMENT setting on each affected table.

For example, alter the AUTO_INCREMENT values on the affected tables to introduce some headroom. If you need to insert ~100 records into a table with the increment set to 2500, then set it to 2700. That way you have 200 non-conflicting IDs to work with.


Does InnoDB Log Files take RAM space or Disk Space When I set InnoDB Log File size to 512M, does it take the space (512M * 2) from RAM or the SSD Disk space?

When I explored, I can see that the log files are located inside /var/lib/mysql folder. So from this I am assuming it is infact stored in disk space and not RAM. I always thought when I allocate log files space it was coming off the RAM and now I have a feeling I was wrong with that assumption. Can someone confirm this please...

Also I am considering to increase the log size to maybe 1GB (for a 3GB Buffer Pool Size) and I am not sure if that would have an effect on my ram usage where I would need to increase my Ram too when I increase the demand for log size. If log files doesnt consume Ram space then I dont see it affecting my ram even if the logfile size are increased. And hence my question to clarify this...

InnoDB log files are stored on disk and so consume disk space - http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html. However the innodb_buffer_pool_size does impact memory usage.

I find this a good reference for memory sizing - http://www.mysqlcalculator.com/


Design - duplicating properties vs not allowing delete I'm designing a database for a mobile app CMS that contains user data. These objects are Address and Job (a job can have 1 address and an address can have many jobs). The first version of a similar project basically inherited the address properties in the job, which would allow the user to delete the address while maintaining job properties. This essentially results in duplicate columns (both objects have street_address, city, state/province, country).

I'm curious about input as to whether the better design and experience is to either not allow object deletion or use a flag to indicate delete status and basically hide those from the user. This would allow the job to maintain its address foreign key even after the address is "deleted".

There are a few issues that come to mind.

A normalized approach (jobs in one table, addresses in another, related only by a foreign key) is desirable from a purely database design perspective. It makes both storage and reporting more efficient.
With this normalized schema, however, you'll want to make sure that an address isn't entered in duplicate (for instance when creating or changing a job). This could typically be the task of a "create job" stored procedure, but you'd have to build some sort of matching logic to merge addresses like 101 First Street with 101 East 1st St, etc. This logic may well be in the GUI, in the form of an "auto-complete" dropdown or similar.
I'm guessing that there could be legal aspects as well: there are juristictions where a person (like a customer) can request that you remove any personally identifiable information about them after your relation ends. If that information includes that you've done business at their street address, then you'd probably want to ask your legal beagles about the legality of retaining a record of addresses even though you've deleted or masked the job data.


SUM 2 columns with 2 columns same name I have this table id, teama, teamb, pointa, pointb.

Columns teama and teamb containing the same names. I want to output the sum grouped by teama from teama where pointa and where teamb pointb.

for example:

id, teama, teamb, pointa, pointb
1,  aa,    bb,    1,      2
2,  bb,    aa,    2,      1

What  want as result:

aa, 2
bb, 4

I'm really stuck on this one. I'm using MySQL and PHP.

Looks like a list of match results and you want to get won/lost points:

SELECT team, SUM(pointa), SUM(pointb)
FROM
 ( -- use all rows twice with switched points
   SELECT teama AS team, pointa, pointb
   FROM tab
   UNION ALL
   SELECT teamb, pointb, pointa
   FROM tab
 ) AS dt
GROUP BY team


Which database type to use I want to implement database for following application:-

User creates task and assigns to other user who can furthur split it into subtasks and assign it to other users.creaking a tree like structure.

Should i use graph database or what?

You could use a graph database, or a plain old relational one:

/* set parent_id for child tasks */

create table task (
  id serial primary key,
  parent_id integer null references task(id),
  subject text not null,
  body text null,
  due_at date null
);

create table task_role_type(
  id smallint primary key,
  name text not null
);

insert into task_role_type values
(1, 'Created By'),
(2, 'Assigned To'),
(3, 'Completed By');

create table task_role (
  task_id integer not null references task(id),
  party_id integer not null references party(id),
  role_id smallint not null references task_role_type(id),
  from_date timestamp not null default current_timestamp,
  to_date timestamp null,
  primary key (task_id, party_id, role_id, from_date)
);

Create a task:

insert into task (subject, due_at) values ('do stuff', '2013-02-18');

insert into task_role (task_id, party_id, role_id) values (1,1,1);

Assign a task:

insert into task_role (task_id, party_id, role_id) values (1, 2, 2);

Split up a task:

insert into task (parent_id, subject) values (1, 'do stuff B');
insert into task_role (task_id, party_id, role_id) values (2, 1, 1);

Complete a task:

insert into task_role (task_id, party_id, role_id) values (1, 2, 3);

I would recommend PostgreSQL, as it can handle hierarchical data better than MySQL


Can I Simply Change A Transaction Log From Unrestricted to Restricted? I've got a database with a data file size of 674 MB and a transaction log of 6.1 GB. The transaction log is about 10 times bigger than the data file.The transaction log settings can be seen in the image below.

The server is constantly running out of space and this transaction log takes up about 11% of the total disk space. I'd like to make this transaction log file smaller.

Can I simply just change the "Maximum file size" by switching to Restrict file growth [MB]: and set the size to say 80 MB?

I prefer restricting to unrestricted.

If some process goes awry, then I don't want the log file to grow and grow and fill the disk up. I'd rather the DB stopped working than the entire Server/SQL Server Instance.

However, in this case you have a different issue: most likely "FULL" recovery and no log backups. Change to simple, shrink the log to, say 500MB, allow growth at 250MB, set a maximum of 2GB.


Getting around MASTER_HOST length limit in 'CHANGE MASTER TO...'? I'm trying to configure external slave connecting to Aurora RDS. To my delight, I run into a silly problem where the Aurora cluster name -
 assigned by AWS - exceeds permissible MASTER_HOST limit of 60 characters.

I want to use the cluster name (not IP) since it can be dynamically changed by Route53 on failover.

Can anyone suggest a solution?

Create a DNS CNAME record in one of your own domains and use the cluster endpoint hostname as the target of the CNAME record.  Then set MASTER_HOST to that alternate (shorter) name.

MySQL has no problem with another name in the resolution chain being longer than the somewhat arbitrarily-chosen 60 character hostname limit.


SQL Server 2008 R2 error: 15023, user, group, or role already exists I have a test database that I'm having permissions issues with.
I can't access the reporting database, and the application's help documentation says to do the following:

Resolution:

1. Launch the SQL Server Management Studio and connect to the database server(s) hosting   the Vision and Reporting Server databases.
2. Expand the security folder.
3. Select logins and right click on the <username> user and choose properties.
4. Click the User Mapping tab
5.Make sure the following databases are selected in the Users mapped to this Login:

    ReportServer
    ReportServerTempDB
    Your Vision databases
    This maps the login/user to the respective databases.
6. As you select each database (including your Vision database), select the db_owner role in the Database role membership for: section. You must select this option for each database.

When I do this, I get the following error:

"Create failed for user '<servername>\<username>'.  User, group, or role '<servername>\<username>' already exists in the current database. (Microsoft SQL Server, Error: 15023)"

I have googled this error and tried the following command on each database:

 ALTER USER [<username>] WITH LOGIN = [<username>]

The message indicated that the commands completed successfully but I am still getting the above error when I try to map each database as instructed above.

What am I missing?

Per Kin's comment (thanks) I tried this:
- I right clicked the user and selected: Script Login As > Drop and Create To > New query window.
- I ran the resulting query and tried to map the user roles by selecting the other two databases and db_owner again but I am still getting the same error message as above.

Thoughts??

There are a number of things you can try, and the success of each will probably depend on what server role you have.

For starters, if it is just one or two users then honestly, the easiest way is to drop the database user from the restored database, and providing there is an existing server login already, remap the database user to the server login using SSMS. If the server login does not exist then just create it, map the user, and presto! Away we go.

Next option: If you are migrating a large number of users, use sp_help_revlogin. sp_help_revlogin is a Microsoft supplied stored procedure that will help migrate logins from one server to another, including passwords and SIDs. Here is a good article about it SP_HELP_REVLOGIN


Referencing another table from a computed column I am working on a project where many of the entities in the data model do not have a name column, but instead the "name" needs to be constructed from a concatenation of multiple columns.  Initially I thought this might be a good use case for a computed column, as it would allow me to define this logic in the database and only once.

However with a fully normalized data model, the computed columns often require values from other tables.  Querying other tables can be accomplished through a UDF, but I have read in several places that using UDFs in computed columns prevents parallel execution (see here).

I could use views to handle the formatting of these names, but would like to define the logic only once, and if the logic is in a view it would likely result in nesting views, which can also cause problems with performance.

I'm hoping someone has an idea for how I might define this formatting logic only once in the database without causing a performance problem.

You could use an indexed view for this. Assuming that the relationships are simple (foreign key) relationships, I don't see why this would cause any issues.

Simple example, tested in dbfiddle.uk:

create table game
( game_id int not null primary key,
  game_name varchar(100) not null
) ;

create table area
( area_id int not null primary key,
  area_name varchar(100) not null
) ;

create table player
( player_id int not null primary key,
  player_name varchar(100) not null
) ;

insert into game
values
  (1, 'chess'),
  (2, 'go'),
  (3, 'reversi'),
  (4, 'backgammon'),
  (5, 'hex'),
  (6, 'havannah'),
  (7, 'pacman') ;

insert into area
values
  (11, 'usa'),
  (12, 'russia'),
  (13, 'greece'),
  (14, 'uk'),
  (15, 'france'),
  (16, 'hungary'),
  (17, 'ukraine'),
  (18, 'belgium'),
  (19, 'canada'),
  (20, 'new zealand')  ;

insert into player
values
  (7, 'John'),
  (8, 'Mary'),
  (9, 'Alex'),
  (10, 'Anna'),
  (11, 'Fred'),
  (12, 'Fay') ;

23 rows affected

create table playground
( playground_id int not null identity primary key,
  game_id int not null references game,
  area_id int not null references area,
  player_id int not null  references player,
  various_stuff varchar(100) null default 'abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz',
  constraint playground_uq
    unique (game_id, area_id, player_id)
) ;

create view dbo.play
  (game_id, area_id, player_id, name)
WITH SCHEMABINDING
as
select
    pg.game_id,
    pg.area_id,
    pg.player_id,
    name = g.game_name + '-' + a.area_name + '-' + p.player_name
from dbo.playground as pg
  join dbo.game   as g on g.game_id   = pg.game_id
  join dbo.area   as a on a.area_id   = pg.area_id
  join dbo.player as p on p.player_id = pg.player_id ;

 -- create an index on the view
create unique clustered index play_cix
    on play (game_id, area_id, player_id) ;

insert into playground
  (game_id, area_id, player_id)
select game_id, area_id, player_id
from game, area, player ;

420 rows affected

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;

select -- top (10)
   game_id, area_id, player_id, name
from play
   WITH (NOEXPAND)            -- Hint used because we are in Express edition
  ;
set statistics xml off;

game_id | area_id | player_id | name
------: | ------: | --------: | :--------------------------
      1 |      11 |         7 | chess-usa-John
      1 |      11 |         8 | chess-usa-Mary

   --- a few hundred rows omitted

  7 |      20 |        11 | pacman-new zealand-Fred
  7 |      20 |        12 | pacman-new zealand-Fay

  | Microsoft SQL Server 2005 XML Showplan                                                                                |
  | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="select -- top (10) &#xa;   game_id, area_id, player_id, name &#xa;from play &#xa;   WITH (NOEXPAND)" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00522548" StatementEstRows="420" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xBA9BAED6D700FD27" QueryPlanHash="0xE0086846AA2CCE7F" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2073864"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="15" CpuTime="15"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="420" EstimatedRowsRead="420" EstimateIO="0.00460648" EstimateCPU="0.000619" AvgRowSize="174" EstimatedTotalSubtreeCost="0.00522548" TableCardinality="420" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="420" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="420" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Index="[play_cix]" IndexKind="ViewClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |

dbfiddle here

and the execution plan of the query that selects from the view, showing that the clustered index on the materialized view is used:


sqlcmd slow against SQL Server 2008 R2 I am trying to import a longish SQL script (3.5 millions of lines, 1,5 GB in size) containing mainly short INSERT INTO statements with data using sqlcmd -E -d <database> -S 127.0.0.1 -i <scriptfile> and it is nearly crawling to a halt (approximatly 150 lines per second) without inferring any noticeable load on the SQL server host (all CPU cores are nearly idle, Disk throughput is at around 200 KB/s).
As the source file is located on an NFS share, I first suspected this to be the culprit, but an identical script available locally runs at just the same speed.
The database is basically empty, the tables are created by the very same script, no triggers or other fancyness - just the raw, mainly numeric or varchar data.
What is SQLCMD waiting for? Is there any way to speed it up?
Edit:
we are limited in our means to alter the data in the SQL script files. The data is provided by a third party for an import procedure. I believe it was originally exported using Management Studio 2005 "Script table as..." feature.
Editing the file is tedious due to sheer size - any operation takes forever with common text editors, although prepending a "SET NOCOUNT ON" was done by copying two files together - and it brought a speedup of approximately 50%.
Due to the text encoding (Unicode-LE) it is impossible to edit with the common set of GNU textutils (which would otherwise work fine with large files) without converting - and I am reluctant to converting because of data fidelity issues which are likely to come with it.
So I am somewhat stuck with how to apply the suggestions on inserting the BEGIN TRAN/COMMIT TRAN blocks or converting the individual inserts into larger sets.

Some ideas:

Inject some GO commands every thousand or few thousand lines. Then instead of one ginormous batch it is broken up into multiple batches.
 Change your individual INSERT statements to INSERT ... VALUES () with a thousand sets each.
Use transactions and commit and/or checkpoint gratuitously (again, every 1000 inserts or so is probably a good place to start, in combination with GO). Your watch and the log will thank you.
It's a silly trivial little thing, but make sure your script has SET NOCOUNT ON - otherwise the UI, SQL Server, and the network in between spend a lot of time sending 1 row(s) affected messages back and forth for every single insert.
Best is probably to use BULK INSERT, BCP or maybe even SSIS instead of Management Studio. Part of the problem is probably the memory overhead on your own machine just loading that huge file, never mind trying to parse/execute it. I'd be curious to see private bytes for ssms.exe while this is going on...
Based on offline discussion with the OP, adding another option: ask the vendor for a backup instead of a script. Then you can restore that to some test or dummy system, and generate inserts from that manually (or use import/export, or Red Gate's data compare tool). This may end up being net same amount of time, but won't kill the log with that huge script of millions of inserts...


How to customize constraint violation messages? I want define my own message for each constraint. Is it possible to customize constraint violation messages in PostgreSQL?

This has been asked in Postgres Forum: Custom Constraint Violation Errors

and after some discussion:

  ...
  So am I got that right, that If I want to have eg custom error messages
  for a foreign
  key violation, I need to replace all fkeys by trigger functions?!

  Michael Musenbrock

answered there:

    The short answer is, while it is possible to custom make your
    own constraint triggers that emulate foreign keys, don't do it.  This
    would be a maintenance nightmare.

    I'd be more maintainable to catch these errors in your client
    application.  Here you would reword these error messages according the
    business rules of your client application.

    Richard Broersma Jr.

with what I think would work best:

      It's not hard to create:

CONSTRAINT some_constraint_name FOREIGN KEY col REFERENCES blah(id)

      ... then in the app, match "some_constraint_name" and map it to a suitable error. That's what I do and it works very well for all constraint types, not just foreign key constraints.

      --
      Craig Ringer


JDBC local MSSQL Server connection string I have installed open source database management tool "dbeaver" and want to use it instead of Sql Server Management Studio. But there is a problem. I searched from the internet but somehow I could not connect to db using dbeaver.

My instance name is: "OMER-HP\OKSQLEXPRESS2014"
I want to connect: "Follower" db
User name: sa
Pwd: Aa123456

Connection String:
jdbc:sqlserver://localhost:1433;databaseName=Follower;instanceName=OMER-HP\OKSQLEXPRESS2014;

Could you help please on this issue

Here is the scrennshot:

I have found my answer from dbeaver support forum.

http://dbeaver.jkiss.org/forum/viewtopic.php?f=3&t=624&p=4261#p4261

First enable TCP/IP for your SQL instance in the SQL Server Configuration Manager tool.

Open SSCM and navigate to SQL Server Network Configuration > Protocols for
SQLEXPRESS > TCP/IP
Enable the TCP/IP protocol in the Properties
dialog for TCP/IP. (You can also enable it on the right-click menu.)
Set the TCP/IP port -- which can only be done in the Properties
dialog (i.e., can't do it with just a right-click).  On the "IP
Addresses" tab of that Properties dialog, I used the IPAll settings
at the bottom. Clear out TCP Dynamic Ports, then set TCP Port to
1433.

From within DBeaver, you can create either a jDTS or a "Microsoft Driver" connection

To create a jTDS connection in DBeaver

Create a new connection, specifying MS SQL Server | jTDS driver
Fill out the info on the first (General) tab, complete with User name and Password. I was able to enter my User name as just my Windows user name, not domain\username. But domain\username worked too, and that may be required on some networks. (I'm not sure.)
Go to the "Driver properties" tab and re-specify the DOMAIN and the USER as two separate parameters.
I did not need to re-specify my PASSWORD on the "Driver properties" tab.
And just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.

To create a "Microsoft Driver" connection in DBeaver

Create a new connection, specifying MS SQL Server | Microsoft Driver
Fill out the info on the first (General) tab, without specifying User name and Password (leave them blank).
Go to the Driver properties tab and set integratedSecurity=true.
And, once again, just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.


How to execute some script when database starts up I want to execute some T-sql code, when a database starts up.

the objective is to resume a session of mirroring in case the session is "suspended" when the database comes "back to life".

So I've done this code:

begin try
    Declare @state_desc nvarchar(60)
    SELECT @state_desc = mirroring_state_desc
    FROM SYS.database_mirroring
    WHERE database_id = DB_ID('MyDataBase')
    if @state_desc = 'SUSPENDED'
    begin
        ALTER DATABASE [MyDataBase] SET PARTNER RESUME
    end
end try
begin catch
end catch

but how can I make it when the sql server starts up?

I used this with no success:

create procedure dbm_startup_resume
                                as
                                begin
begin try
    Declare @state_desc nvarchar(60)
    WAITFOR DELAY '00:00:10'
    SELECT @state_desc = mirroring_state_desc
    FROM SYS.database_mirroring
    WHERE database_id = DB_ID('MinhaBaseDeDados')
    if @state_desc = 'SUSPENDED'
    begin
      ALTER DATABASE [MinhaBaseDeDados] SET PARTNER RESUME
    end
  end try
  begin catch
  end catch

exec sp_procoption @ProcName = dbm_startup_resume,
    @OptionName = startup, @OptionValue = 'on'

end

[Updated]

Create SQL Agent job for what you need.
Use Server-scoped DDL trigger for that

Approximately like this:

Create TRIGGER ddl_trig_AlterDatabes
ON ALL SERVER
FOR ALTER_DATABASE
AS
    -- your code here
    -- use EventData() to check database name if needed.. or other info..
    Exec msdb..sp_start_job @job_name = 'job_name'


Why does json_agg fail with error "function json_agg(record) does not exist"? With postgresql version 9.2, I'm trying to run a query that selects certain columns to build a json object. Here's the query, which I derived from this question:

select
  a.id
  , json_agg((SELECT x FROM (SELECT b.fieldA) AS x)) AS item
from a join b on a.id = b.foreign_key
limit 10

When I run this, I get an error that's rather hard to parse.

  ERROR: function json_agg(record) does not exist   Hint: No function
  matches the given name and argument types. You might need to add
  explicit type casts.

  , json_agg((SELECT x FROM (SELECT b.fieldA) AS x)) AS item

It seems as though the complaint is that json_agg is not receiving the right sort of input, but I don't understand why.

When I run the same query without the json_agg, i.e. just with the sub-select expression, I get a column with the value of b.fieldA inside parentheses. I'm not sure what the parens indicate in terms of a data type, but I could see that perhaps json_agg is only getting the values an so doesn't know how to supply the property name.

Can anyone explain where I've gone wrong?

json_agg does not exist in postgres version 9.2 (which is now at end-of-life).  It was introduced in version 9.3.

The HINT does seem to be slightly inappropriate. If no function by that name exists, then no amount of casting the arguments can work.


UPDATE Column values with Case and JOIN I have 2 tables "a" and "b".

Table "a"

| property | element | ply | ..

Table "b"

| property | ply | material | ..

What I want to do is to add a column to the first table called "material" and provide values based on the following rule:

if a.element < 300000 write 80000 else write the value you would get from joining tables "a" and "b" on (property and ply)

What I wrote for the above is this:

ALTER TABLE a ADD COLUMN Material INTEGER
UPDATE a SET Material = CASE
    WHEN a.Element <= 300000
        THEN 80000
    ELSE b.Material FROM
        a JOIN b ON (a.PCOMP = b.PCOMP AND a.Ply= b.Ply)
    END

  I am using SQLite 3.14.1

But this does not go through. Any ideas?

Your CASE is almost correct, but to be able to do a separate join, you have to use a subquery:

UPDATE a
SET Material = CASE
                 WHEN Element <= 300000
                 THEN 80000
                 ELSE (SELECT b.Material
                       FROM b
                       WHERE a.PCOMP = b.PCOMP
                         AND a.Ply = b.Ply)
               END;


How to speed up queries in MySQL on a table with ~50 million rows The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each column separately. The table has ~50 Million rows and grows rapidly, the plan is to keep it under 100 million rows by archiving the oldest data. Reports based on various datetime ranges and other combinations of the PK columns are run on it frequently, but these reports have begun to take too long. Around 5 minutes to return 10 records.

Column Names

DateTimeStamp--DateTime------PK,NN

SystemNumber--SmallInt(6)------PK,NN

DataNumber------TinyInt(4)-------PK, NN

Value---------------Decimal(15, 5)

Indexes

Primary-------------------BTREE-Unique---DateTimeStamp, DataNumber, SystemNumber, value

DateTimeIDX------------BTREE-Non Unique--DateTimeStamp

DataNumber-------------BTREE-Non Unique---DataNumber

SystemNumberIDX----BTREE--Non Unique---SystemNumber

Select  DateTimeStamp, Value
    From  SystemStats
    WHERE  DataType=16
      AND  DateTimeStamp >='" & StartDate & "'
      and  DateTimeStamp <= '" & EndDate & "'
      and  SystemNumber =" & mVars.SystemNumber & "
    Order by  DateTimeStamp;

My Question
How would you change the index on the table to speed up queries?

Provide SHOW CREATE TABLE; what you provided is incomplete.  4 indexes on 50M rows is likely to slow down INSERTs; we need to discuss that.

For that one query, you need the one composite index:

(DataType, SystemNumber, DateTimeStamp) -- or
(SystemNumber, DataType, DateTimeStamp) -- (but not both)

The order is important.  A "composite" index is not the same as indexes on each column.  One of those could (should) probably be the PRIMARY KEY instead of the one you mentioned.

More discussion on creating the best index:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql

If you have other queries; let's see them.  Else get rid of the rest of the indexes.

Perhaps you meant "<" instead of "<="?  (BETWEEN is inclusive.)

Is there some reason for using DECIMAL(15,5) instead of, say, FLOAT (which takes 4 bytes (float) versus 8 and still gives 6-7 significant digits of precision.)

Is the table InnoDB?  This is important because of the way the PK works.

You have PRIMARY KEY(DateTimeStamp, DataNumber, SystemNumber, value); does that mean you expect to have multiple values for each date+num+sys combination?  (It is unusual to have something called "value" included in a PK.)


Restart event scheduler and alter event when slave being promoted to master I am using innnodb cluster. It is good that every time a R/W node failed the R/O node will being promoted to R/W.

However I have to restart scheduler and alter event enabled (originally slave side disabled) manually for the cluster again. It pretty error prone and troublesome. I always have to run the script to manually fill the losing data for the time event is not running.

Is there anyway make it automized so I can be hands off?

As much I understand your question to Automatically restart event scheduler and alter event when slave being promoted to master. You have always have to run the script to manually fill the losing data for the time event is not running. AS much MySQL documents states my Answer is No

You have to follow these steps manually:

To check  event scheduler status on promoted master, use SHOW EVENTS, like this:

 SHOW EVENTS
 WHERE STATUS = 'SLAVESIDE_DISABLED';

Alternatively, you might wish to query the INFORMATION_SCHEMA.EVENTS table as shown here:

  SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE STATUS = 'SLAVESIDE_DISABLED';

When promoting a replication slave having such events to a replication master, use the following query to enable the events:

 UPDATE mysql.event
 SET STATUS = 'ENABLED'
 WHERE STATUS = 'SLAVESIDE_DISABLED';

Before executing either of the previous two UPDATE statements, you should disable the Event Scheduler on the slave

  using SET GLOBAL event_scheduler = OFF;

run the UPDATE, restart the server, then re-enable the Event Scheduler afterwards

  using SET GLOBAL event_scheduler = ON;

If you later demote the new master back to being a replication slave, you must disable manually all events
enabled by the UPDATE statement. You can do this by storing in a separate table the event names from the
SELECT statement shown previously, or using an UPDATE statement to rename the events with a common prefix
to identify them, as shown in this example:

 UPDATE mysql.event
 SET NAME = CONCAT('replicated_', NAME)
 WHERE STATUS = 'SLAVESIDE_DISABLED';

When demoting this server back to being a replication slave, you can then rename and disable the events like this:

 UPDATE mysql.event
 SET NAME = REPLACE(NAME, 'replicated_', ''),
 STATUS = 'SLAVESIDE_DISABLED'
 WHERE INSTR(NAME, 'replicated_') = 1;


PostgreSql allocate memory for each connection When we configure the memory setting for the Postgres DB what is the recommended memory allocation for each connection ? Is there any formula to apply ?

I know 25% of the memory of the Server should be allocated. But how do we allocate based on the DB connections? How do we know, what is the maximum number of connections should be allocated?

Also in a multi-nodes environment can we allocate more connections for each node (in Postgres-ds.xml max connections) than what is actually allocated in the DB?

When we configure the memory setting for the Postgres DB what is the
  recommended memory allocation for each connection ? Is there any
  formula to apply ?

There is no per-connection pre-allocated memory that a DBA would define. A session will allocate dynamically what is needed, or use the shared memory pre-allocated at server start depending on the kind of usage.
 See Resource consumption in the doc about what can be defined.

  I know 25% of the memory of the Server should be allocated. But how do
  we allocate based on the DB connections? How do we know, what is the
  maximum number of connections should be allocated?

The maximum number of client connections (max_connections) is factored into the amount of shared memory that is pre-allocated at server start.
In Managing Kernel Resources, the doc says that its size in bytes is:
(1800 + 270 * max_locks_per_transaction) * max_connections

But memory is not the main factor in deciding the maximum number of connections, it's the raw power and number of cores of the machine, and wether a connection pooler is going to be associated to the server. The point is mostly to avoid too many concurrent active queries provoking the server to halt to a crawl.

  Also in a multi-nodes environment can we allocate more connections for
  each node (in Postgres-ds.xml max connections) than what is actually
  allocated in the DB?

If there are multiple client nodes that point to the same PostgreSQL instance, the combined count of their connections cannot outreach max_connections.

However when using a connection pooler, connection as a word becomes ambiguous. You want to know whether it's a connection from the client to the pooler or a connection from the pooler to the database server. Generally there are much more allowed connections to the pool than to the database.


SSAS Tabular Model or SQL Relational Database for new Datawarehouse? Is SSAS still a good to learn technology? I am in the process of creating a data warehouse using SQL Server for my company.
I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process.
Now that I have business buy in to step it up to a production state, where I will be tripling the data, it's the right time to ensure the technology I use is best suited for our needs and is aligned with general best practices.

We will be processing about 500k records per day, with a maximum guess at about a million a day. The POC then aggregated all this data down into 5-minute chunks per client per day for the last 4 years. Total being a bit over 500k time slots, for each fact (POC had 4 fact tables) for each customer (Lets plan for 25). So for the POC, we are looking at approximately 50 million rows across all the facts by client and time. That leaves final views of the data at minor amounts, given that the last 6 months satisfies 85% of all questions, that means we should be sitting with result sets of approx 250k rows for 85% of the time if not less. Given that I expect to triple data, let's call it a maximum of 1 Million rows to satisfy 85% of queries, split across about 8 to 10 fact tables with about 4 or 5 dimension tables.

With that all said and done, as mentioned, I am in a very fortunate position where I can now choose the technology that is used for the backend of the warehouse. I have never really had the opportunity to really get entangled in SSAS and feel like its a technology I am lacking.
Is SSAS still a popular choice for Data Warehouses or has its popularity declined over the past few years? Is it still used as much as it was 5 years ago?

SHORT VERSION: Is SSAS still something worth learning/investing time and effort into?

We are currently on SQL 2016 and this will be built on SQL Server 2017

Thank you for all your input.

In my opinion, yes - it is still a technology that is completely valid. Even more so if we are discussing Tabular rather than Multi-Dimensional SSAS implementations. For example, VertiPaq (the database engine that runs Tabular Models) is the same engine that can be found in PowerBI and any skills you learn using SSAS/TOM/DAX will help you if you ever decide to learn that as well. Its also good to note that PowerBI, Excel, SSRS and many other tools can connect to SSAS/Tabular to obtain data to build dashboards and reports.

I personally have been using SSAS/Tabular since early 2014 and I have already rolled out 3 large-scale implementations (I'm currently working on my 4th). My current company uses SSAS/Tabular as the Analytics Engine for its core product. It can be integrated with many other products other than just SQL Server by using PowerQuery to obtain data from other sources.

If all you were planning to do was to have a data warehouse with a star schema (or even snowflake schema) then Tabular is a good choice as it offers fantastic performance (hardware dependent) and amazing compression. You should be aware though that it is a pure in-memory analytics engine, so if you choose to go down that route, be prepared to procure lots of RAM. To estimate how much RAM you will need, a typical Tabular installation will be 1/10th the size of the originating data (subject to good modelling principles).

I have a Tabular Model which contains circa 800 million rows of data in the main fact table and around 16 million rows in each of the dimension tables. It takes up about 48GB of RAM, but queries against it are sub-second when applying a filter and less that 30 seconds when just dumping the entire table. Unlike SQL Server, partitioning in a Tabular Model does not speed up the query as the data is not on disk, but it does speed up your ETL process as you can load individual partitions.

With all that being said, I still have a SQL Server data warehouse which stores data that I don't want in my Tabular Model. As with any technology, choose the right tool for the job. If you want something that can give you high performance analytics Tabular is a great choice, but does require you to learn a few more technologies (C# & PowerShell are a must in my opinion as they help with deployment, management and builds). However, if you don't have the budget, resources or need for the features that a Tabular installation offers, then a traditional data warehouse or a multi-dimensional SSAS installation may be the better way to go.


How to shrink big database files faster than dbcc command? There is a DBCC command to shrink files of a database. Unless the files are almost empty or the free space is nearly unfragmented this command is often refered as 'don't use'. In rare cases I need to shrink files to a new size. An alternative to the DBCC command in this szenario is what I am looking for.

The databases I'm working on are 'big data': Size of each DB some TB, Size of each file some 100GB.

An alternative to the DBCC command in this szenario is what I am
  looking for.

One possible alternative to shrink is described in Paul Randal's article:
Why you should not shrink your data files

  The method I like to recommend is as follows:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the    CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the
  tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or    shrink it way down if its the primary filegroup)

Even if this method will lead to desired result and even more: you'll achieve defragmentation of all your indexes, it is not faster than shrink, it requires additional free space before you can shrink the old files, it can hardly be automated and there will be a problem with LOB data as it will remain in the old FG unless you copy it manually to new FG.


Can't start mysql on Ubuntu 14.04 When I try to start

sudo service mysql start
start: Job failed to start

Then I look the /var/log/mysql/error.log

160122  8:43:42 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
160122  8:43:42 [Note] Plugin 'FEDERATED' is disabled.
160122  8:43:42 InnoDB: The InnoDB memory heap is disabled
160122  8:43:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160122  8:43:42 InnoDB: Compressed tables use zlib 1.2.8
160122  8:43:42 InnoDB: Using Linux native AIO
^G/usr/sbin/mysqld: Can't create/write to file '/tmp/ibv0Dwa6' (Errcode: 13)
160122  8:43:42  InnoDB: Error: unable to create temporary file; errno: 13
160122  8:43:42 [ERROR] Plugin 'InnoDB' init function returned error.
160122  8:43:42 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160122  8:43:42 [ERROR] Unknown/unsupported storage engine: InnoDB
160122  8:43:42 [ERROR] Aborting

160122  8:43:42 [Note] /usr/sbin/mysqld: Shutdown complete

The permissions for /tmp

drwxr-xr-x   4 root root  4096 Jan 22 08:39 tmp

I also tried

sudo apt-get purge mysql-server-core-5.5 mysql-common

and to reinstall everything new.. But it didn't solve the problem.
The socket /etc/mysql/my.cnf exists:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

EDIT:

/var/run/mysqld I just saw this doesn't exist. How can I create this?

EDIT2:

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

When I try to connect.

The answer from Phil is the solution:

/tmp should be chmod'ed to 777 – Phil 2 days ago

sudo chmod 777 /tmp

(I can not set a comment as answer, so maybe some mod can change my answer to his?)


Rebuilding Indexes, DB now 10x the size I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very fragmented.

The job finished and the fragmentation is gone, but now the database is over 120 gigs! I understand that it would have used extra space to do all the rebuilds, but now that the job is done, I would think all that space would be free space, but the free space only shows as 3 gigs, so 117 gigs is being used even though the index rebuild job is finished.

I'm very confused and could use some guidance, I have the get the db back to a reasonable size, we don't have the disk space for this.

Thanks in advance!

Here's the results of both the queries posted:

  log_reuse_wait_desc NOTHING

name    TotalSpaceInMB  UsedSpaceInMB   FreeSpaceInMB
LIVE_Data   152             123             28
LIVE_Log    18939           89              18849
LIVE_1_Data 114977          111289          3688

The 3rd file is an .ndf file, that's the one showing only 3688 in unused space, but 111289 in used for about 15 gigs of data.

In the meantime I just figured this out, total brain burp. I had indicated what I thought was a fill factor of 90 in the rebuild job, but it's worded as "change free space percentage to" so by using a value of 90 in there, I was actually using a fill factor of 10!! DOH. No wonder it grew 10x as big. I'm going to rebuild with the correct fill factor then shrink. Thanks to everyone for the input though.


Is it ok to have 'view count' column in the same table? Let's say I have a website similar to YouTube where I'm storing and displaying the 'view counts' for each video. It is ok if I'm storing the view count column in the same table as the actual view table - I.e the row which defines the video information?
Or should I have a separate table that stores the view count information - I.e having two tables, one that stores the video information such as video Id, title, description, and another table that's only stores the counts, such as having columns: ID, video_id, view_count

The reason I'm asking this is because obviously the view count columns will need to be updated so many times throughout the years - lets say 1million views. I'm wondering if having 'view count' in the same database will have negative performance when it comes down to reading the main video for other users since that column will need to be locked for writing view count so many times throughout the day.
Because the video table will be pretty much static in content, and it's pretty much just reads.

Thanks,

It is usually better to have a separate table for "view counts", "likes", "hits", etc.  This extra table would have just the id and the counter.  The id would match the main table.  A missing row in the new table implies 0 (or you could make sure to add a row with 0).  Then use LEFT JOIN (or JOIN) when you need the main info plus the count.  No auto_increment needed; just PRIMARY KEY(video_id).

Part of the issue is to avoid locks/deadlocks due to constantly bumping the counters versus other activity that does not touch that table.

If you get millions of views/day, then we need to discuss how to keep that from stumbling over itself.


copy tables from one database to another I have two Databases on mysql. They are used for completely different things and contain different tables. The problem is I need them both on the same server that can only host 1 db (and I can't change that fact). So I thought I can just use the same db for the to projects, since they use different tables and not overlaps.

One would think there is a simple way to do so.

There are not a lot of tables, so I can copy it manually one by one, but it seems that there is also no simple command to copy a table including all of the table rule's and restrains. (Working on a mac, so cant use sql server management studio).

It seems unreasonable that there is no simple way to do this. Is there one?

Consider the source database name is databaseA and destination  is databaseB.

Step-1. Take dump of tables to be loaded to new host:

mysqldump -uroot -pPASSWORD --routines --skip-drop-tables --single-transaction --master-data=2 databaseA > databaseA.sql

Notes:

--skip-drop-tables is used to avoid accidental override of tables from  databaseA to databaseB.
If there is no replication and active writes, you can ignore --single-transaction and --master-data options. (Harmless to keep them as well.)
If you  have only a few of the tables to be dumped them  you  can specify list of tables (space delimited) after database name in above command as follows:

mysqldump -uroot -pPASSWORD --routines --skip-drop-tables --single-transaction --master-data=2 databaseA table1 table2 ... > databaseA.sql

Step-2. Backup the destination database: (Just in case something goes wrong)

mysqldump -uroot -pPASSWORD --routines --databases databaseB > databaseB.sql

Step-3. Load the database:

mysql -uroot -p databaseB -hHOSTNAME < databaseA.sql

Notes:
- Here HOSTNAME is the hostname/ipaddress of the destination

Just to add, if your databases are on same machine, (which is not the case here, but still saying) you can use RENAME operations to move tables:

rename table databaseA.table1 to databaseB.table1;

You can generate these SQLs  from information_schema database and source it to mysql to quick action.


How can I determinate the pc specs I need for my database? I am writing an application for a client that says he will have 20 orders / day and that will increase to 200 within 2 years.
He wants to have his own local server for that and wants me to suggest him a PC specification that may supply his needs for N years say 7~10.
The database will be mariadb.
What data do I need to look at to evaluate that?
My initial idea is to generate data to cover for 300 daily records for a year and then calculate based on that.
I will be writing an app that writes dummy data to the db using N connections to emulate concurrent insert, reads, etc, not sure if this is a good way to do this either. What are your advice?
But I am not sure how I would calculate memory, cpu and disk io into the equation only the disk space.
How can I calculate the memory cpu and disk io for this purpose to know say what sort of HD/SSD/M2 or cpu and memory that it would require?

300 orders per day for 10 years is about a million rows. Assuming 10KB of data per sale (probably will be a lot less), that's 10GB of disk space. As for how to check your assumptions about disk usage and throughout, you will need to write some test scripts to create those million sales and measure latency for each transaction with an increasing number of concurrent connections.
A Raspberry Pi would be adequate for a volume of data that small.
My advice is to start with an Oracle free tier VM and see if it is responsive enough for you. If it isn't, get a VM from Scaleway for €3/month. Or, if your client is really welded to the idea that he must have the server on site, get a Raspberry Pi.
Whatever solution you go with, make sure you have reliable, frequent backups.
In terms of how to work out the resources, work out how many transactions an order creates. If it is 1 transaction, and you have 300 orders per day, that is 300 transactions per day. 300 transactions per second would be relatively low throughput.
What you really need to test is response time when somebody is using the system. With such low throughput, your requirements will be dictated by response latency, not throughput for 300 transactions per day.


For a boolean settings table, should I toggle a flag, or insert/delete? I have a settings table.

SETTINGS
--------
settingId int
settingName varchar(20)

Each user can choose to apply the settings, or not.  The interface allows them to quickly toggle the settings on or off.  The setting is by default off.

I'm looking for best practice on the USER_SETTINGS table.

Should it be designed such that only active settings are included in the USER_SETTINGS table?  This would involve a lot of inserts and deletes on the table.

USER_SETTINGS (only active settings)
------------------------------------
settingId int
userId int

Or, should it be designed with an active flag?  This would involve a single insert for each setting, followed by several updates.

USER_SETTINGS (settings enforced by a flag)
-------------------------------------------
settingId int
userId int
active bit

Setting history is not really important to me, so I wouldn't need to see if the active flag was ever used.

Is it best to use a smaller table and insert, delete, insert, delete, etc., or is it best to add that extra bit, and insert, update, update, update?

I'm running SQL Server 2005.

active flag seems redundant to me - I don't see any value in storing it. Don't worry too much about delete/insert performance, I cannot think why user would change his options frequently. Even if some application controls user settings, I believe it's still relatively rare operation. Also, if on application side you use one or other ORM library, it's usually easier to map many-to-many relationship if link table has no extra columns.


SSAS Calculated Member Conditional Count I have a table representing Accidents and a column with Accident Severity ranging from 'Slight' to 'Fatal'. I want to create a measure counting Fatal Accidents depending on other columns on the Accidents table and others (Road Type, Weather Conditions etc.)

What would the calculated member for this measure be and would adding it to a Fact Table consisting of Foreign Keys from all those tables be enough to accomplish what I want?

Assuming you already have designed

a measure Total that counts the accidents
a dimension Severity that describes the severity of the accident

you can define a calculated member such as:

([Measures].[Total],[Severity].[Severity].&[Fatal])


Is CREATE EXTERNAL DATA SOURCE Azure PaaS elastic databases broken? I have set up Azure elastic database queries before by using the following on Azure PaaS databases:

On the data source database:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<encryption key>';
GO
CREATE DATABASE SCOPED CREDENTIAL CRED_Azure_Login
WITH IDENTITY = 'SomeUser', SECRET = '<Some password>'
GO

This run, no problems and I can see the credential created when looking at sys.database_scoped_credentials. So it seems the source is fine.

On the destination database:

CREATE EXTERNAL DATA SOURCE Source_AzureDatabase
WITH
(
    TYPE=RDBMS,
    LOCATION='<DB location>.database.windows.net',
    DATABASE_NAME='SourceDBName',
    CREDENTIAL= CRED_Azure_Login
);

This used to work a-OK, but I get the following error:

Msg 46516, Level 16, State 26, Line 1
The specified credential cannot be found or the user does not have permission to perform this action.

I am using the admin login in the destination database (I changed the admin login via the Azure Portal Overview page for the database and re logged in, just to be sure). The documentation says you need CONTROL permission on the database, so this shouldn't be an issue. As I have successfully set up the source databases Master Key and credential, linking to it should not be a problem.

Can someone else try this to see if it is just me? Another DBA has reviewed the code and can't find any problems. Feels like Microsoft might have broken this feature - can someone else check they can do it?

You need to create the db scoped credential and the external data source all on the same (destination) database.

The IDENTITY='' should be an account that has access to the source database where you want to query from, in my example Test2.

So for example to access database Test2's tables from database test, execute all this on the database: Test.

USE TEST
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGP@SSWORD124321';
GO
CREATE DATABASE SCOPED CREDENTIAL CRED_Azure_Login
WITH IDENTITY = 'SomeUser', SECRET = 'STRONGP@SSWORD124321' -- this user needs to  have sufficient rights on the test2 db.
GO

CREATE EXTERNAL DATA SOURCE Source_AzureDatabase
WITH
(
    TYPE=RDBMS,
    LOCATION='<DB location>.database.windows.net',
    DATABASE_NAME='Test2',
    CREDENTIAL= CRED_Azure_Login
);

CREATE EXTERNAL TABLE [dbo].[test]
([Test ID] int)
WITH
(
DATA_SOURCE= Source_AzureDatabase
);

Source


Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases? I have come across the following line in a text book (Database System Concepts
Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686:

  Thomas’ write rule allows schedules that are not conflict serializable
  but are nevertheless correct. Those non-conflict-serializable
  schedules allowed satisfy the definition of view serializable
  schedules (see example box).

What I understood from the above lines is that every schedule generated by timestamp protocol following Thomas's write rule is view serializable.

Now let's take the following little schedule: $S: R_1(X), W_2(X), W_1(X)$.

This schedule $S$ is allowed under timestamp protocol which follows Thomas's write rule.

And serialization order is $R_1(X), W_1(X).$

But I was not able to prove that it is view serializable.

Actually I think that it is non-view serializable because,

Consider serial order as $T_1, T_2$

Now final value of $X$ is being written by $T_2$. So not equivalent.
Next alternative serial order is $T_2, T_1$

here, $R_1(X)$ will read value of $X$ written by $T_1$ not original value
which was there before start of both transaction. So this too is not
view-equivalent.

What is going wrong here? Please help me with this one.

The given schedule S is:

T1    T2
----  -----
R(X)
      W(X)
W(X)

To be view serializable W2(X) must move before R1(X) or after W1(X). However, the first would violate the "initial read" rule and the second would violate the "last write" rule.

T2 starts after T1 so has a higher timestamp. When T1 comes to write X it sees that T2's timestamp on it. By Thomas' write rule the write by T1 is void and can be removed. The given schedule then becomes

T1    T2
----  -----
R(X)
      W(X)

Now all of T2's actions occur after all of T1's - the schedule is serialized.

(You have this the other way around in your question, removing W2(X). I think that is an error.)

To show that a schedule is serializable it is only necessary to show that there is at least one equivalent schedule that is serial. It is not necessary to show that every possible serial schedule is achievable. I agree that T2,T1 is not view serializable on the original schedule because of the read/ write dependency. However, T1,T2 is serializable under the Thomas write rule.


MYSQL Read Replica Bottle Neck If my MySQL master has heavy writes, my read replicas will in-turn gets bogged down by the replication logs & executing them. Then could it serve the reads?

Assume MYSQL master is executing at 100% CPU and disk utilization, now these needs to be executed in the read replicas too. So in turn read replicas would also incur 100% CPU and disk utilization. Then how could it serve the read requests?

Note: Assume the MYSQL master write is just updating a field Ex: UPDATE where ID = <>. No more complex queries so there is no possibility of optimizing in read replicas too.

If replication is using Row Based Replication (RBR), then, say, an UPDATE statement is faster on the Slave than on the Master.  This is because all that is sent to the Slave is the changed record.  The Slave does not have to think, does not have to search for the row, does not get the rows that don't need to change.

With Statement Based Replication, the Slave is essentially repeating whatever complex processing was done on the Master -- with an exception:  If there are a bunch of SELECTs happening to figure out what to UPDATE/INSERT/DELETE, then those happen only on the Master.

If any server has "high" CPU usage, crank up the slow log; let's see what is causing it.  Almost always, performance can be improved by a better index, a reformulation of a SELECT, etc.


sql join query through Adventure works database I want to show a list of Product Colors. For each Color then show how many SalesDetails there are and the Total SalesAmount (UnitPrice * OrderQty). Only show Colors with a Total SalesAmount more than $50,000 and eliminate the products that do not have
a color.

Please see my query below:

select * from Production.Product
select * from Sales.SalesOrderDetail

Select pr.Color
from Production.Product as pr
left Join
--on pr.ProductID=sr.ProductID
(SELECT  count( sr.SalesOrderDetailID) as count1,sum(sr.UnitPrice * sr.OrderQty) as total
     FROM    Sales.SalesOrderDetail as sr
     GROUP BY sr.ProductID,
     HAVING  sum(sr.UnitPrice * sr.OrderQty) >50000
    ) as sa
 on sa.ProductID=pr.ProductID

I am not sure what to do after this point.please help

Select pr.Color, count(*), sum(sr.UnitPrice * sr.OrderQty) as total
from Production.Product as pr
Join Sales.SalesOrderDetail as sr
on sr.ProductID=pr.ProductID
WHERE pr.Color IS NOT NULL
GROUP BY pr.Color
HAVING  sum(sr.UnitPrice * sr.OrderQty) >50000
 ;


changing the connection string in SQL Server we deployed the packages from old server to new server .
how to change the connection string i.e initial catalog or server name parameter from Agent job > configuration  > connection manger .Is there a possibility to do from here ?

I'm receiving the below error if i'm changing the data source and servername

After SSIS package deployed to new environment still it may use old connection string.

  how to change the connection string i.e initial catalog or server name
  parameter from Agent job > configuration > connection manger .Is there
  a possibility to do from here ?

Yes, It's possible when you are sysadmin and you can change manually by following:

  Right click on AgentJob -> Properties -> Job step -> Configuration ->
  Connection Managers

  Below Connection Managers -> select Name of database then on
  properties ->

  Connectionstring -> Data Source=yourServerName;InitialCatalog=Databasename;Provider=SQLNCLI11.1;IntegratedSecurity
  = SSPI;Auto Translate=False; (you can edit Data source and intial catalog manually)

  and

  Servername = 'yourservername'


Install DBeaver SQLite driver offline? I'd like to use DBeaver with SQLite, but the network firewall disallows external connections. So DBeaver cannot download the driver automatically.

Where can I download the SQLite driver for it and how can I install it manually in DBeaver?

DBeaver supports any database that has a JDBC driver. So, the best thing to do is to go to the SQLite people themselves - they have a list of such drivers here. The page itself is quite old, so don't worry about the dates - just check around and see which one best suits your needs/is supported. Just as a matter of interest, SQuirreL SQL also works via JDBC - it's pure Java and is, IHMO, more mature than DBeaver, but YMMV.


Error "column does not exist" in a SELECT with JOIN and GROUP BY query I'm using PostgreSQL 9.1 with a Ruby on Rails application.

I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id (proj_sous_projet_id = 2).

This makes me use the max() aggregate function and apply the result to a JOIN function on the same table as PostgreSQL does not authorize to use the columns in the SELECT clause if they do not appears in the GROUP BY clause, ALTHOUGH using a max() mean obviously I'm interested to the row containing the max values!

This is my query :

SELECT h_v_charges.*,
       max(last_v.version) as lv
FROM hist_versions_charges h_v_charges
    JOIN hist_versions_charges last_v
      ON h_v_charges.version = lv
    AND h_v_charges.proj_charge_id = last_v.proj_charge_id
GROUP BY last_v.proj_sous_projet_id,
         last_v.proj_charge_id
HAVING last_v.proj_sous_projet_id = 2
ORDER BY h_v_charges.proj_charge_id ASC;

The error message I got :

ERROR:  column "lv" does not exist
LINE 1: ..._versions_charges last_v ON h_v_charges.version = lv AND h_v...
                                                             ^
********** Error **********

ERROR: column "lv" does not exist
SQL state: 42703
Character: 147

I also tried with "last_v.lv" but the error remains the same.

If anybody got an idea about what's wrong, she is more than welcome.

=== UPDATE ===

According to * a_horse_with_no_name * and Colin 't Hart answers, I finally ended up with the following query :

SELECT *
FROM (
    SELECT *, max(version) OVER (PARTITION BY proj_charge_id) AS lv
    FROM hist_versions_charges
    WHERE proj_sous_projet_id = 2) AS hv
WHERE hv.lv = hv.version
ORDER BY hv.proj_charge_id ASC;

It is slightly quicker with a single ORDER BY.

I tried as well the query with a WITH clause. Though "nicer", it creates additional processing charge. As I know I will not re-used in the future the sub-query twice or more in the same main query, I'm fine with using a simple sub-query.

Thanks anyway to *a_horse_with_no_name* and Colin 't Hart. I learned many things !

You probably want something like this:

SELECT h_v_charges.*,
       last_v.last_version
FROM hist_versions_charges h_v_charges
  JOIN (select proj_charge_id,
               max(version) as last_version
        from hist_versions_charges
        where proj_sous_projet_id = 2
        group by proj_charge_id
  ) last_v
  ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
   select hvc.*,
          row_number() over (partition by proj_charge_id order by version desc) as rn
   from hist_versions_charges as hvc
   where proj_sous_projet_id = 2
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
  select hvc.*,
         row_number() over (partition by proj_charge_id order by version desc) as rn
  from hist_versions_charges as hvc
  where proj_sous_projet_id = 2
)
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;


Create dynamic set conditionally excluding member if exists As part of my cube definition I am creating a dynamic set of all claim status' minus the PEND status.  This has worked great for years, but now we have someone who does not have the PEND status which is resulting in an error when opening the cube.  I would like to create a set that is all Claim status minus PEND if it exists.

The error I am getting is "The level '&['PEND]' object was not found in the cube when the string, '[Claim].[Claim Status].%[PEND], was parsed."

CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims]
  AS [Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].&amp;[PEND];

I tried using except without luck.

CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims]
  AS Except ([Claim].[Claim Status].[All Claims].Children, [Claim].[Claim Status].&amp;[PEND]);

Even tried

Except ([Claim].[Claim Status].[All Claims].Children, StrToSet("[Claim].[Claim Status].&[PEND]"))

Does anyone know how to safely tell if a member exists then exclude it?

IsError to the rescue!  Combined with an iif was able to solve it.  Thanks to this post for leading me right to it.

iif (
        isError(
            ([Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].[Pending])
            )
      , ([Claim].[Claim Status].Children)
      , ([Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].[Pending])
)


DB2 - How to estimate proper BUFFERPOOL size? DB2 9.7.
8GB RAM on server.
Currently BP  has 500 MB (number of pages*pagesize)
Database has issue with increased IOPS.
So we think maybe BP should be increased.
How to estimate needed size for BP? Is there some recommendation? Maybe I would like to  put it on 1GB. What can be negative effect? Is it always greater better for BP?

You can't "estimate" the size of a buffer pool, because the answer is, "it depends".

Try using the AUTOCONFIGURE command to get started, and enable STMM.  Together these will go a long way towards getting your database running more efficiently.

I would also recommend you spend some time reading about DB2 Performance Tuning.  There is a ton of information available on the web, starting from the entire Performance Tuning section of the manual.