Oracle Min - ORA-00937 not a single-group group function I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command;

SQL> select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id;

       BUS       FARE DEST
---------- ---------- -------------------------
        11      21.36 Kayseri
         1      23.32 Kayseri
         1      63.32 Kars
         5      44.31 Edirne
         1      54.13 Edirne
         1      33.36 Fatsa
         4      33.37 Ankara
         4      33.39 Kayseri
         7      53.37 Istanbul
        10      51.36 Izmir
         7      51.36 Izmir
        10      32.35 Ankara
        10      42.34 Istanbul
        10      52.33 Hatay
        10      32.83 Bursa
         2      30.00 Izmir
        11      52.00 Kayseri
        11      42.00 Kayseri
         2      40.00 Kayseri

Output I want:

       BUS       FARE  DEST
---------- ---------- -------------------------
        10      32.83 Bursa
         2      30.00 Izmir

select bus, min(fare), dest from
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
group by dest having count(bus)>1;

>ERROR at line 1: ORA-00937: not a single-group group function

When I try to group dest, It gives error. I can't understand what is mistake? Has anyone idea to fix it?

--- EDIT TABLES ---

CREATE TABLE buscompanies (
      bus_id  number(4) not null PRIMARY KEY,
      name    varchar2(25) not null);

table created.

CREATE TABLE route (
     route_id number(4) not null PRIMARY KEY,
     origin   varchar2(25) not null,
     destination varchar2(25) not null);

table created.

CREATE TABLE tickets (
     tickets_id number(4) not null PRIMARY KEY,
     route_id number(4) not null,
     bus_id number(4) not null,
     fare   number(5,2) not null,
     CONSTRAINT routeid_fk FOREIGN KEY (route_id) REFERENCES route(route_id),
     CONSTRAINT busid_fk FOREIGN KEY (bus_id) REFERENCES buscompanies(bus_id));

SQL> select * from route;

  ROUTE_ID ORIGIN                    DESTINATION
---------- ------------------------- -------------------------
         1 Ankara                    Kayseri
         2 Ankara                    Kars
         3 Ankara                    Edirne
         4 Ankara                    Fatsa
         5 Denizli                   Ankara
         6 Denizli                   Kayseri
         7 Hatay                     Istanbul
         8 Hatay                     Izmir
         9 Izmir                     Ankara
        10 Izmir                     Istanbul
        11 Izmir                     Hatay
        12 Izmir                     Bursa
        13 Bursa                     Izmir
        14 Bursa                     Kayseri

14 rows selected.

--EDIT--

I used this command but that shows only for 1 city

select bus, min(fare), dest from
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
     group by bus, dest having count(dest) > 1;

       BUS  MIN(FARE) DEST
---------- ---------- -------------------------
        11      21.36 Kayseri

there should be other destinations too. Question was;

  find cheapest company for each city such that more than 1 company
  travels to the city?

You want to aggregate an aggregate, so the easiest way to see this is to group twice, I believe. You may also want to rename the subquery table aliases I've used here to make them more meaningful.

SELECT sub2_.bus, MIN(sub2_.fare), MIN(sub2_.dest)
  FROM (SELECT sub1_.bus, MIN(sub1_.fare) AS fare, sub1_.dest
          FROM (SELECT tickets.bus_id    AS bus,
                       tickets.fare      AS fare,
                       route.destination AS dest
                  FROM tickets
                  LEFT JOIN route
                    ON route.route_id = tickets.route_id) sub1_
         GROUP BY sub1_.bus, sub1_.dest
        HAVING COUNT(sub1_.bus) > 1) sub2_
 GROUP BY sub2_.bus
HAVING COUNT(*) > 1

I think you have to include the column bus in the group by


Building year to date aggregations using SQL I need some help with building a monthly aggregation using only SQL.

Imagine the following table:

TranID  DateCode    Account Value
   1    20140101        1   5
   2    20140106        1   -3
   3    20140207        1   6
   4    20140409        1   3
   5    20140103        2   3
   6    20140215        2   7
   7    20140519        2   6

There are two accounts that have transactions on various dates. I would like to write code that gives me this result assuming we are in July:

MonthID     Account YTD
20140101        1   2
20140201        1   8
20140301        1   8
20140401        1   11
20140501        1   11
20140601        1   11
20140701        1   11
20140101        2   3
20140201        2   10
20140301        2   10
20140401        2   10
20140501        2   16
20140601        2   16
20140701        2   16

I'm thinking I should be able convert the dates to MonthCode using

DATEADD(day, -DAY(DateCode) + 1, DateCode) AS MonthCode

I'm thinking I should be able to solve this somehow by joining the table to itself but I don't really get the numbers right. Also I need to somehow get the months without transactions in as well..

Any and all help will be greatly appreciated!

Code to generate the mock data:

Create table #Tran(
TranID int identity(1,1),
DateCode Date,
AccountCode varchar(50),
Value int
);

insert into #Tran (DateCode, AccountCode, Value)
values ('20140101', '1', 5),
('20140106', '1', -3),
('20140201', '1', 6),
('20140401', '1', 3),
('20140101', '2', 3),
('20140201', '2', 7),
('20140501', '2', 6);

For SQL Server 2012, given this sample data:

CREATE TABLE #t(TranID INT, DateCode DATE, Account INT, Value INT);
CREATE CLUSTERED INDEX x ON #t(Account, DateCode);

INSERT #t VALUES(1,'20140101',1,5 ),(2,'20140106',1,-3),(3,'20140207',1,6 ),
(4,'20140409',1,3 ),(5,'20140103',2,3 ),(6,'20140215',2,7 ),(7,'20140519',2,6 );

I would use this query (imagine the first line as stored procedure parameters):

DECLARE @year INT = 2014, @cutoff DATE = NULL; -- just state the year

-- and optionally an explicit cutoff date (leave NULL for today)
SET @cutoff = '20140731';

DECLARE @startdate DATE = DATEADD(YEAR, @year-1900, 0);

;WITH accounts(a) AS
(
  SELECT DISTINCT Account FROM #t
    WHERE DateCode >= @startdate
    AND DateCode < DATEADD(YEAR, 1, @startdate)
),
months(m) AS
(
  SELECT TOP (12) DATEADD(MONTH, ROW_NUMBER() OVER
      (ORDER BY s.[object_id])-1, @startdate)
    FROM sys.all_objects AS s
),
s AS
(
  SELECT a.a, m.m, v = COALESCE(SUM(t.Value),0)
  FROM accounts AS a
  CROSS APPLY months AS m
  LEFT OUTER JOIN #t AS t
    ON t.DateCode >= m.m
    AND t.DateCode < DATEADD(MONTH, 1, m.m)
    AND t.Account = a.a
  WHERE m.m < COALESCE(@cutoff, SYSDATETIME())
  GROUP BY a.a, m.m
)
SELECT
  MonthID = m,
  Account = a,
  YTD = SUM(v) OVER(PARTITION BY a ORDER BY m ROWS UNBOUNDED PRECEDING)
FROM s
ORDER BY a,m;

If you are using a version older than SQL Server 2012, then you may want to try some of the alternative approaches here:

Best approaches for grouped running totals

WITH TestData AS (
SELECT
    DATEADD(DAY,(-1*DATEPART(DAY,CONVERT(DATETIME,DateCode)))+1,CONVERT(DATETIME,DateCode)) DateCode,
    AccountCode,
    Value
FROM (
    VALUES ('20140101', '1', 5),
    ('20140106', '1', -3),
    ('20140201', '1', 6),
    ('20140401', '1', 3),
    ('20140101', '2', 3),
    ('20140201', '2', 7),
    ('20140501', '2', 6)
) AS T (DateCode, AccountCode, Value)
)
SELECT DISTINCT
    TD1.DateCode, TD1.AccountCode,
    (SELECT SUM(TD2.Value) Total
     FROM TestData TD2
     WHERE TD2.AccountCode = TD1.AccountCode AND TD2.DateCode <= TD1.DateCode) Total
FROM TestData TD1
ORDER BY TD1.AccountCode, TD1.DateCode;

You have to find a way to group the dates into the month of the year, and then perform the calculation such as the above query.


Login failed for user NT AUTHORITY\IUSR I have some developer colleagues in my office that are having a problem similar to this one:

db connection uses an anonymous logon instead of the domain user

the connection string (he is working on his local machine) is:

Data Source=localhost\Development;Integrated Security=SSPI;Database=COLA;Connection Reset=FALSE

and it is getting the error message below:

Login failed for user 'NT AUTHORITY\IUSR'.

where this login AUTHORITY\IUSR came from - we don't need it?

How can we make the Integrated Security=SSPI; get his local login instead of the AUTHORITY\IUSR?

Following the links in the comments provided by Biju jose and vonPryz

Understanding Built-In User and Group Accounts in IIS 7

Configure ASP.NET Impersonation Authentication (IIS 7)

we were following these steps:

To grant an anonymous account rights on the network by using IIS Manager:

Click Start, type INetMgr.exe, and then click Enter. If prompted, click Continue to elevate your permissions.

In the Connections section, click the + button next to the name of your computer.
In IIS Manager, double-click the site that you want to administer.
In the Features View, double-click Authentication.
Select Anonymous Authentication, and then click Edit in the Actions pane.
In the Edit Anonymous Authentication Credentials dialog box, click the Specific user option, and then click Set.
In the Set Credentials dialog box, input the user name and password desired, and then click OK.
But instead of doing the number 6 we just changed the setting to Application Pool Identity as you can see on the second picture below.

Changing the IIS settings to Application Pool Identity allowed the Windows Authentication to kick in and the active directory account was used to login into sql server.

That account had been previously configured to do so, therefore no more login problems for that user.

I would add that: Is “NT AUTHORITY\ANONYMOUS LOGON” login “mandatory”?

I just got this error and solved it on my own. In SQL Server Management Studio, create a new login NT AUTHORITY\IUSR. Add it to the roles public and systemadmin.


How to compute a membership expiration date on SQL This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded database is fine, and it provides portability (I could send the file to someone and they can see the data, without haing to connect to an SQL server). The downside: LibreOffice Base uses HSQLDB version 1.8 (from circa 2008). Hence, the available functions are limited.

I have several tables and forms and queries to add and retrieve data from my database, including payments. Among the payments, my culprit are the membership renewal. There are several types of membership, each with different prices, and in some cases, different expiration periods. Members can purchase membership for 1 year or 3 years, and they could do it at any given point in time. Each membership expire on December 31st of the calendar year.

I have a table (actually a View produced by a filtering Query) called "membershipPayment" where I have

memberID paymentID paymentDate expirePeriod
0        0         2012-02-02  1
0        10        2013-02-15  3
0        30        2014-01-10  1
0        60        2015-08-15  3
1        5         2012-03-12  1
1        15        2013-04-01  1
1        20        2013-10-01  3
1        35        2014-02-01  1

The expirePeriod is the number of years of membership associated with that payment. Notice that in the example, in some cases members are renewing membership when their membership is still active due to a previous payment. I need to query this table/view to summarize these payments and have an expiration date and membership status, with one line per member

memberID expirationDate memberStatus
0        2019-12-31     active
1        2017-12-31     inactive

The memberStatus column should be computed from the expirationDate and CURRENT_DATE. No problems here. My issue is how to compute the expirationDate. I would like to use only a Query, if possible. I don't know how to loop (or if it's even possible to do such thing) along the rows of the "membershipPayment" table to increment the expirationDate based on the value of expirePeriod, or to keep track of a value from a previous row while checking another. I managed to compute the total number of membership purchased on a sigle year using SELECT, SUM() and GROUP BY, according to the answer of the previously cited post. But keeping track of the expirationDate, still no clue.

If macros are required, any help or advice is appreciated.

Thanks for any advice or comment, even if it's to point that my question is trivial,

All that you have to do is to add the ExpirationPeriod to the Year of payment and add 12.31 as the date of expiration.

If someone pay 2014.05.07 for 3 years then his membership expired at 2014+3.12.31


Storing terabytes of data in SQL Server I need to develop a production system capable of storing a 100 kb textfile and a serial number for every piece produced. I know the approximate output over 2 years, the time the data has to be stored, and have calculated a need of around 25-30 TB of storage.

I have some experience working with databases, but handling that amount of data is new to me.

My first guess would be to simply store the files as a blob inside a table in the database. What are the problems of storing that amount of data in a single table? What are possible backup scenarios?

Another approach would be to store the data in several tables (e.g. a table per month).

A third approach would be to store references to the data in SQL Server, zip the files, and store them somewhere else.

The purpose of this storage system is to query the text file by using the serial number.

What would you do?

Historically, I'd shy away from storing files in the database and instead include pointers like in your alternative 3.  With SQL Server 2012, however, you might look into the FileTable feature.  The files are still stored within SQL Server (meaning that you'd have to be aware of the implications on your backup strategy), but also are easily accessible through the standard Windows API so you can manipulate them in Windows Explorer or PowerShell.  If you actually need to search within the text files as well, FileTable can do that.

On the other hand, storing 25-30 "extra" TB of data within a SQL Server database will be a challenge to maintain.  If this is just for compliance/auditing purposes (i.e., the probability of somebody actually looking at a particular file is low) and files aren't inserted very often, I'd probably stick with the pointers-to-files approach.  But if you need access frequently or there are a lot of changes, the advantage goes to FileTable because it takes care of the pointer-handling part of the process.

If you want to do efficient queries and have real time access to your records, perhaps you want to stay away from compression or references to external disk storage... ugh...

Here are some directives I was able to find, you might also find them useful:

Construct an index for each field that requires range queries. Use a B+ tree to implement the index. A B+ tree organizes sorted data for efficient insertion, retrieval and removal of records. Each record is identified by a key (for this problem, it is the field value). Since it is a dynamic, multilevel index, finding the beginning of the range depends only on the height of the tree, which is usually quite small. Record references are stored in the leaves, sorted by the key. Additional records can be found by following a next block reference. Records will be sequentially available until the key value reaches the maximum value specified in the query. Thus, runtimes will be dominated by the number of elements in a range.
Avoid using trees that store data at interior nodes, as traversing the tree will be expensive since it won’t be resident in memory.


Programmatically finding minimum set of fields necessary to create a unique composite key I am importing flat files from different sources into tables in SQL Server. I am creating a composite primary key using a combination of fields from the extracts that will give me a unique key for every row.

The way I do it now is I just start with 1 field and then I keep concatenating fields until I find a key that is unique for all records. This can be a little time consuming or I might end up concatenating more columns than I really needed to in order to obtain the unique key.

Is there some sort of SQL script I can run on a table that will give me the minimum number of fields (names) I would need to concatenate in order to obtain a unique key? So if there is 1 field in the table that is unique for all records then that 1 field name would be returned. If I needed to concatenate [memberid], [claimid], and [date of service] in order to obtain a unique key then those 3 field names would be the result of the script.

While comments and srutzky offer some great advice, there is a tool made exactly for your situation. The SSIS Data Profiling Task is intended for the purpose of identifying potential Primary Keys (for multiple columns) along with providing a lot of other useful insights into your data.

Simply create a new SSIS package, add the task then use the wizard like interface to profile your data. Create a new output file somewhere you can access it, select Quick Profile... then profile the appropriate information from the database and table desired.

Once you're done run the package and return to the component to select Open Profile Viwer... and look at all the exciting data! The tool gave me a 96% match for one of my fact tables for a three column PK when up to 7 columns were requested for the candidate key consideration (not shown).

Just to be clear I definitely agree that the business rules should determine data uniqueness... just because you find a combination of columns that fits over your data for uniqueness doesn't necessarily mean it makes any sense. =)

Although I agree with some posts that keys should be defined by the business case this seems to me the view from the perspective of a database administrator.

From the perspective of data analytics where you may face a static data set you want to analyze such key combinations might be interesting and useful.  Imagine a situation where you found a subset of data and you are wondering what the key fields are which determine it. For instance let's assume you found a subset of orders for some combination of products and you wonder what the parameters which define these orders best.
Of course  you could define them by their order ids but there might be a smaller and more interesting key combination such as the age of the customer and the time the order was made.

That's a typical clustering/classification problem.


SQL Deadlocks - Same Stored Procedure Called Concurrently I am working with a relatively complex system, where static tables are updated from multiple data sources as they are needed, because dynamically loading the data takes 4-5 seconds and we prefer to display results to the user quickly.

The process:

User or application opens a specific order
A stored procedure is called to retreive the data (lets call it dbo.Get_Data)
dbo.Get_Data checks event logs to see if there have been any changes since the last time it looked for the data
If it finds a new event, it runs the expensive query to update the data
Data is returned

IF (    SELECT [LastStaticUpdateEvent] < [LastSaveEvent]
        FROM [dbo].[Events]
        WHERE [OrderNumber] = @OrderNumber
    )
    BEGIN
       -- Update Static table
       UPDATE [Static]
       SET [Static].[A] = [App].[A]
          ,[Static].[B] = [App].[B]
       FROM [dbo].[AppData]            AS [App]   -- View with many joins (4-5 secs)
       INNER JOIN [dbo].[StaticResuts] AS [Static]
           ON [Static].[OrderNumber] = [App].[OrderNumber]
       WHERE [App].[OrderNumber] = @OrderNumber

       -- Update Event Log
       UPDATE [dbo].[Events]
       SET [LastStaticUpdateVent] = SYSDATETIME()
       WHERE [OrderNumber] = @OrderNumber

    END

SELECT * FROM [dbo].[StaticResults]

The problem is that this data can be requested concurrently.  If User A and User B both call dbo.Get_Data before the first UPDATE is complete, a deadlock occurs.

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

Is there a good method or pattern to have the 2nd call wait until the 1st is finished before continuing with its lookups?

The easiest way to have a block of code that can be run by only one session at a time is to use an Application Lock.  You can use SQL Server's locking engine, but instead of locking a specific row, page, or table, you create a lock with a custom name.  eg

begin transaction;

--begin exclusive section
exec sp_getapplock @Resource = 'Get_Data Exclusive Lock', @LockMode = 'Exclusive';

--do stuff in only one session

--end exclusive section
exec sp_releaseapplock @Resource = 'Get_Data Exclusive Lock';

commit transaction;

The simple fix could be with (nolock) SELECT * FROM [dbo].[StaticResults] with (nolock)

Which means that you are not blocked by the concurrent transactions in process and process will read the table data even though the lock is there on the table. The consequence may be users will not see the updated data in that attempt. As long as it is not a financial transaction or business-critical transactions it will serve the purpose.


Install SQL Server 2012 missing vs_Setup.msi from Visual Studio 2010 I am trying to install SQL Server 2012 Enterprise with SQL Server Data Tools included.  I understand that SQL Server Data Tools is part of what was formerly known as BIDS.  The installation for SQL Server 2012 prompts me for the ms_setup.msi installation file from Visual Studio 2010.  However, I am using Visual Studio 2012 currently only.  The equivalent file in VS 2012 is called vs_professionalcore.msi and is located in Visual_Studio2012_Pro\packages\professionalcore\Setup.

How can I install SQL Server 2012 with Data Tools having only Visual Studio 2012 given the setup?  Is there a download or registry entry I need to make? Thanks for any pointers.

In my case, I had Visual Studio 2010 Enterprise on my system when I initiated the setup for SQL Server 2012 Standard. After the SQL 2012 got underway, it stopped and prompted for the VS 2010 setup.msi. At that point, I ejected the SQL Server disc and inserted the VS 2010 setup disc. Then I used the dialog presented by the SQL Server 2012 setup program to browse to the root of the VS 2010 DVD, selected the msi there, and opened it. The SQL 2012 setup continued and when I saw that it was again installing SQL components (rather than VS 2010 components), I ejected the VS 2010 disc (which caused the SQL 2012 setup program to display an error that there was no disc in the drive), placed the SQL 2012 setup disc back into the drive, then clicked the Try Again button. At that point, SQL Server 2012 resumed and after some time it completed successfully.


Multiple system.replset collections in local I have a replicaset that's showing multiple system.replset collection in the local db.

replset1:PRIMARY> show collections;
me
oplog.rs
replset.minvalid
slaves
system.indexes
system.profile
system.replset
system.replset

Running db.system.replset.find()  returns the documents from both with out error.

They are different versions, one showing the current membership, another an older setup.  I haven't seen this before.  Is this expected?

I'm not really seeing any problems from the mongo or application side, this just seems strange.  The duplicates are present on the primary and secondaries.

Should .remove({"version":}) be safe?

I ran into this issue while trying to reconfigure my replicaset. db.system.replset.find().pretty() yielded two replicasets with different versions and different members.
Running db.system.replset.remove({"version":199}) ended up removing the bad replicaset configuration. Reconfiguring my replicaset worked after that.


Way To Speed Up Query My query returns roughly 590 rows and 8 columns.  The issue that I have is that from start to finish the query takes 2 minutes 30 seconds to complete.  The great group of people on here have taught me a lot on how to write more efficient queries, so here is another!

I am using a date variable  and not a datetime since my variable only contains a date - and I am also using storing my date(s) in the yyyymmdd format as suggested by Aaron Bertrand - Bad Habits To Kick.

Is there anything I can do to optimize this query and have the results return faster?

DECLARE @Startdate date = '20170101',
        @Enddate   date = '20170131';

WITH fc As
(
    Select
    Teacher
    ,Team
    ,fc
    FROM [Helper].[dbo].[fc]
)
,ia As
(
    Select
    Teacher
    ,tia
    FROM dbo.ia
    WHERE [hiredate] >= @Startdate
    AND [hiredate] <  DATEADD(DAY,1,@Enddate)
)
,inb As
(
    Select
    Teacher
    ,tinb
    FROM inb
),
ripcord As
(
    Select
    Teacher
    ,pit
    FROM [homebase].[dbo].[rip]
)
,YRTR As
(
    Select
    Teacher
    ,totamt
    FROM totamt
    WHERE CAST([begindate] As DATE) BETWEEN CAST(DateAdd(yy, -1, @startdate) As Date)
                                     AND CAST(DateAdd(yy, -1, @enddate) As Date)
)
Select
DISTINCT rost.[Teacher] As Teacher
,[Team Name] = fc.team
,[TIA] = ROUND(SUM(ISNULL(ia.tia,0)),0)
,[SUM] = CAST(ROUND(SUM(ISNULL(inb.tinb,0))+SUM(ISNULL(rip.pit,0)),0) As INT)
,[YR] = ROUND(SUM(ISNULL(tr.totamt,0)),0)
,[fc] = ISNULL(fc,0)
,[1st Count] = COALESCE(b.[students],0)
,[2nd Count] = COALESCE(c.[potstudents],0)
FROM dbo.roster rost
LEFT JOIN fc fc
ON rost.Teacher = fc.Teacher
LEFT JOIN ia ia
ON ia.Teacher = rost.Teacher
LEFT JOIN inb inb
ON rost.Teacher = inb.Teacher
LEFT JOIN ripcord rip
ON rost.Teacher = rip.Teacher
LEFT JOIN YRTR tr
ON rost.Teacher = tr.Teacher
OUTER APPLY
( SELECT
        DISTINCT Teacher [Teacher]
        , COUNT(students) AS students
    FROM students
    WHERE Teacher = rost.Teacher
    AND regdate >= @Startdate
    AND regdate <  DATEADD(DAY,1,@Enddate)
    GROUP BY Teacher
) AS b
OUTER APPLY
(
    Select
    DISTINCT Teacher Teacher
    ,COUNT(potstudents) As potstudents
    FROM dbo.potstudents
    WHERE Teacher = rost.Teacher
    AND returndate >= @Startdate
    AND returndate <  DATEADD(DAY,1,@Enddate)
    GROUP BY Teacher
) AS c
GROUP BY rost.[Teacher],fc.TEAM, fc.fc,b.[students],c.[potstudents]
ORDER BY rost.[Teacher] ASC

EDIT (forgot the link, sorry)
At the requet of @sabin bio - here is a link to the Query Execution Plan

Also, the CTE's query views which have no indexes on them.

Start by understanding the plan that the query optimizer generated by your query. In your question you say that the query returns around 590 rows but the query plan included only returns 18 rows. Did you attach the correct plan? I will walk through it anyway. Reading from right to left:

Do a full table scan of roster and get back 18 rows. This result set is used as the outer part of all of the following nested loop joins. For each row in the outer result set:

Do a full scan on the ia table
Do a full scan on the inb table
Do a full scan on the ripcord table
Do a full scan on the YRTR table
Do a full scan on the students table
Do a full scan on the potstudents table

At some point the number of rows gets bumped up to 19. The GROUP BY is implemented as the SORT with a node id of 4. The DISTINCT is implemented as a Sort (Distinct Sort) with a node id of 0.

As the programmer you do not have direct control over the query plan but there's a lot that you can do to influence it. You said that the query execution time is too slow. Does the above plan sound efficient to you? Is it what you would do if you could choose the query optimizer's actions? You're doing around 140 table scans just to return 18 rows. For a very small number of rows this might be okay but it sounds like you have more data in production.

One way to optimize a query is to reduce the IO requirements of that query. Here you are doing lots of table scans so that should be straightforward to implement. Create indexes so that the query optimizer can get the relevant data more efficiently. Even if you are referencing views you can create indexes on the tables used by the view to improve performance. To give you an example, here is the predicate for the table access on fc:

[Test].[dbo].[roster].[Teacher] as [rost].[Teacher]=[Test].[dbo].[fc].[Teacher]

If you create an index on the Teacher column of fc that may result in a different, and possibly more efficient, table access method for that table. Optionally, you could add all of the columns used by the Teacher table as INCLUDE columns.

Looking at just your code, you should be aware of the impact of using local variables in your query. The query optimizer will not know the values of those local variables when creating a query plan. It will do default cardinality estimates based on hard-coded rules. For some queries, adding an OPTION (RECOMPILE) hint or replacing the local variables with hard-coded values can result in a much better query plan because the query optimizer knows the values of the variables before creating the plan.

Also, do not haphazardly add DISTINCT and GROUP BY to your queries. It looks like the query optimizer optimized away some of them, but most of the time if you have both GROUP BY and DISTINCT in the same query you're doing something wrong. It is not clear how much of a performance impact making that change would have for this query.

I can't immediate see anything that will improve performance, but I can see dozens of things you can do to better format your SQL so that that someone on here can give you a performance improving answer.

First of all, it is completely valid in SQL to write many words on the same line. In fact, SQL server will ignore all of the white space in parsing, so you can write as much or as little on a single line as you want. So the lines that currently read

WITH fc As
(
    Select
    Teacher
    ,Team
    ,fc
    FROM [Helper].[dbo].[fc]
)

Could be written as:

WITH fc as (SELECT Teacher, Team, fc
    FROM [Helper].[dbo].[fc])

As could all of the other common table expressions.

Further, all of the:

LEFT JOIN YRTR tr
ON rost.Teacher = tr.Teacher

Can be written as:

LEFT JOIN YRTR tr ON rost.Teacher = tr.Teacher

If you can compact the SQL making these types of adjustments the SQL becomes a lot more readable, will fit on one page, and you'll be much more likely to see how to fix the functional parts.

As for the functional parts, one red-flag that jumps out is that you are calling DISTINCT in scenarios where you are using a GROUP BY clause. Calling DISTINCT and GROUP BY in the same select is redundant, since GROUP BY will already ensure the results are distinct.

I'm not sure if removing the DISTINCTs will speed up the query, but since they are redundant it is worth a shot.

Also, the generic advice of "run your script in SSMS, turn on the actual execution plan and add any suggested missing indexes" is usually helpful in these situations, you could be missing an index, and if it is an important one you could get significant performance gains without much analysis.


Do SQL Server stored procedures cache data results? I have heard this from friends, but I never investigated whether this is true.

Is it true that the data results of an executed query are stored in cache?

I mean, if I have a stored procedure like :

SELECT * FROM USERLIST

...is it true that the result (list of users, in this case) is stored in cache.

Also, if I have these :

SELECT * FROM USERLIST WHERE user="user"
SELECT * FROM USERLIST WHERE user="userzzz"

(in which user/userzzz are passed as parameters), is it correct that it stores 2 different results on the database cache.

I don't think so, but I want the confirmation from you, experts!

Query results are not cached

However, the source table and index data and metadata will be cached after the 1st use (subject to continued use, load and memory pressure though)

That is, the results of a query will be evaluated every execution but the tables(s) (and any indexes etc) used by the query will most likely be in memory already.

The compiled execution plan will be cached which is where the confusion comes from I suspect

SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

1) syntactically check the query
2) if it's okay - it checks the plan cache to see if it already has an execution plan for that query
3) if there is an execution plan - that plan is (re-)used and the query executed
4) if there is no plan yet, an execution plan is determined
5) that plan is stored into the plan cache for later reuse
6) the query is executed

(copy of Marc_s answer)


SQL Reporting Services - T2Embed.dll missing When generating a report in SSRS, I receive the following error:

There was an error generating your report. Please refer to item 673778174106859 for additional diagnostic information. There was an error during the render phase of the report generation. There was an error generating the document Network Statistics There was an error generating the document Network Statistics An error occurred during rendering of the report. --->

Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. --->

Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. --->

System.DllNotFoundException: Unable to load DLL 'T2Embed': The specified module could not be found. (Exception from HRESULT: 0x8007007E) An error occurred during rendering of the report. --->

Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException: An error occurred during rendering of the report. --->

Microsoft.ReportingServices.OnDemandReportRendering.ReportRenderingException: An error occurred during rendering of the report. --->

System.DllNotFoundException: Unable to load DLL 'T2Embed': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Running a repair install of the specific SQL and RS instance did not help.  Any suggestions?

SQL Server Reporting Services specifically is not supported on Windows Server Core 2008 R2 SP1 and higher.

You've probably stumbled upon one of the reasons why it's not supported. (I.e., it flat out doesn't work.)

Besides hacking around, which may or may not actually fix the issue, I think your only recourse is to install SSRS on a full version of Windows Server, and try again.

i think the best way to fixed this dll missing .. Try to download it manually from browse by Google...


Is it necessary for a foreign key to be a primary key in another table? Why? I am new to database implementation. I want to know if it is necessary for a foreign key to be primary key of another table.
Foreign key is not unique - it may have duplicate entries. If it must be primary key of another table then why it is necessary?
I think any column can be a foreign key because you can put condition at any column.

The referencing column may not be unique, as you point out. If the referenced column is also not unique, you have created a many-to-many relationship, which is a right pain to implement and can cause problems maintaining data integrity.
For example, I have two rows in table 1 with the same value in the referenced column. I also have two rows in table 2 with the same value in the referencing column. I then change one of the rows in table 1; which rows in table 2 should also be updated?
If the referenced column is unique, but is not the primary key, then your table may be in an "abnormal form" and the additional unique column may be redundant. The test for this is to ask yourself two questions:

If I make the unique column(s) the new primary key, and drop the old primary key column(s), do I lose any data about the entity? If not, your table is in an abnormal form.
Can the unique column(s) change during the lifespan of the entity? If not, then the column(s) constitute a key candidate key.

In practice, there are sometimes very good reasons for denormalizing tables, but these must be considered carefully on a case-by-case basis.
Note that it is possible to reference a unique non-primary-key column or column group. However, since conceptually the primary key is "The" canonical identifier for the row, it makes more sense to reference the primary key.

is it necessary for a foreign key to be primary key of another table

Yes, foreign key has to be primary key of parent table.

foreign key is not unique it may have duplicate entries

Yes, it may not be unique and may have duplicate entries in child table, but it must be unique and does not have any duplicate entries at the parent table (as it is a primary key).
For example consider below tables:
create table city (city_id int(PK), name varchar(25));
create table author (author_id int(PK), city int (FK), name varchar(25));

Here, in the author table, the city (foreign key) column can be non-unique as multiple authors belongs to a city. But in city table, city_id (primary key) must be unique as each city must have only one city_id.
However, if you really need to reference non-primary key as foreign key to other table, make sure its a unique column in parent table - sometimes this is helpful as well.
Have a look at my blog post for background.


Poor performance of JOIN, WHERE, ORDER BY combination (MySQL) Goal: select 10 most recent rows from posts which are related to a specific user.

There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows).

posts columns:

--------------------------------------------------------------------------------------------
|  id (int)  |  source_id (int)  |  title (varchar)  |  content (longtext)  |  date (int)  |
--------------------------------------------------------------------------------------------

relations columns:

----------------------------------------------------
|  id (int)  |  source_id (int)  |  user_id (int)  |
----------------------------------------------------

I tried using JOIN for retrieving 10 most recent rows from posts which are related to a specific user:

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        posts AS p
JOIN        relations AS r
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10

However, it takes ~30 seconds to execute it (SDD hosting!). I tried lots of index combinations for both tables, including indexes for single and multiple columns – none of that interfered the time of execution anyhow. Is there any way to speed up the selection?

For user_id=1 there are ~1,000 source_id's and ~450,000 rows in posts.

SHOW CREATE TABLE... results:

CREATE TABLE `relations` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `source_id` bigint(20) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `source_id` (`source_id`),
 KEY `source_id_2` (`source_id`,`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=7692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `posts` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `source_id` bigint(20) unsigned NOT NULL,
 `title` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
 `date` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `source_id` (`source_id`),
 KEY `date` (`date`),
 KEY `date_2` (`date`,`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4867283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN results:

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise.

You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then returning the 10 most recent ones. You'll need an index on posts by (date, source_id). It would return quickly for the most recently active users, but take much longer for users without recent posts. Something like the following:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name(u_id int)
BEGIN
  DECLARE fd DATE;
  DECLARE d DATE;

  SELECT MIN(date), MAX(date) INTO fd, d FROM posts;

  CREATE TEMPORARY TABLE last_posts (id int);

  WHILE d > fd AND (SELECT COUNT(*) FROM last_posts) < 10 DO
    INSERT INTO last_posts (id)
    SELECT p.id
    FROM relations AS r
    JOIN posts AS p ON (p.source_id = r.source_id AND
                        p.date > date_sub(d, interval 7 day) AND p.date <= d)
    WHERE r.user_id = u_id
    ORDER BY p.date DESC
    LIMIT 10;

    SET d = date_sub(d, interval 7 day);
  END WHILE;

  SELECT p.id, p.title, p.content, r.id AS rid
  FROM posts p
  JOIN relations AS r ON (r.source_id = p.source_id)
  WHERE p.id IN (SELECT * FROM last_posts)
  ORDER BY p.date DESC
  LIMIT 10;

  DROP TABLE last_posts;
END;;
DELIMITER ;

You could play with the intervals, reducing them to 1 day (more cycles with faster queries). Ensure you have an index on posts (date).

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data.

So it would look like this:

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        relations AS r
JOIN        posts AS p
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10


Open source & cheap 'data at rest' encryption solutions So I am exploring some options about database encryption. The best options are commercial (TDE). I am looking for an open-source implementation. Recent releases of MySQL and MariaDB have data-at-rest capabilities:
MariaDB
https://mariadb.com/kb/en/mariadb/why-encrypt-mariadb-data/
MySQL 5.7.11 comes with InnoDB tablespace encryption
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
What is important in this implementation (for companies) is: are these compliant with PCI-DSS / HIPAA etc?
From MariaDB:

The MariaDB file_key_management plugin enables the configuration of keys in a file. The key file is read at system start and no additional access is needed during runtime. The security of the encryption depends on access restriction to the key file. The key file can itself be encrypted, providing additional layer of protection.

From my point of view this will mean providing the decryption of the key during start (and OS reboot)? So whenever we (re-)boot a system does this mean we need to manually provide this key? Having this key readable on the server itself will defeat the use of data-at-rest encryption in the first place.
In MySQL 5.7.11+

The InnoDB tablespace encryption feature in non-enterprise editions of MySQL use the keyring_file plugin for encryption key management, which is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).
MySQL Enterprise Edition offers the keyring_okv plugin, which includes a KMIP client (KMIP v1.2) that works with Oracle Key Vault (OKV) to provide encryption key management. A secure and robust encryption key management solution such as OKV is critical for security and for compliance with various security standards. Among other benefits, using a key vault ensures that keys are stored securely, never lost, and only known to authorized key administrators. A key vault also maintains an encryption key history.

Now I am wondering, can this be made compliant with security standards? When using this data-at-rest, will root or mysql user have access to the keys since they could read encryption keys from memory?

Data-at-rest
There may be a terminology problem here. Data-at-rest encryption usually means

Storage-encryption
Not peer-to-peer nor any other form of data-at-use encryption.

On the forms of encryption suggested, I would advise staying away from those RDBMS-specific solutions as they're less tested than the other options which PostgreSQL suggests

Storage encryption can be performed at the file system level or the block level. Linux file system encryption options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block level or full disk encryption options include dm-crypt + LUKS on Linux and GEOM modules geli and gbde on FreeBSD. Many other operating systems support this functionality, including Windows.
This mechanism prevents unencrypted data from being read from the drives if the drives or the entire computer is stolen. This does not protect against attacks while the file system is mounted, because when mounted, the operating system provides an unencrypted view of the data. However, to mount the file system, you need some way for the encryption key to be passed to the operating system, and sometimes the key is stored somewhere on the host that mounts the disk.

Essentially, different operating systems and file systems abstraction layers provide a better better-tested method of handling data-at-rest encryption.
Yes, that means you have a key. Yes, that means if the key is compromised the data can be read. But if your database is compromised and not the key, the data is secure. And, that's why it's data-at-rest.
So you normally store the key owned by root. Have root mount the secured location, and let the postgres user access that. Obviously PostgreSQL needs access to the data and has to know how to decrypt it.
Now, if other users are on the machine they can't access the data unless they're they postgres user. Moreover, they can't access the key. And if they do manage to compromise the data or even steal the physical encrypted back up they can't access it without the key.

So would you like a free solution for your business critical application and data? MySQL provides a free solution, but the key file is stored on the same place where the data is. PostgreSQL also provides a free solution and encrypts the whole partition/disk, but when the server is under attack it doesn't provide a safe solution because the user can read the mounted disk and its data. It is a risk so you should store the encryption key in a safe place. I think the question where this safe place is and who will protect it without any money. My opinion is that, you won't find such a solution.


Audit specific information How can I get logon events from audit table for Feb, June, Oct and Dec?

I tried the query below, but it retrieves a lot of duplicate rows although I am using the distinct keyword in the query. Also this query takes long time.

select   distinct os_username ,username, userhost, terminal, timestamp, action_name,
         logoff_time
from     dba_audit_session
where    timestamp between '01-FEB-2017' and '28-FEB-2017'
order by timestamp;

your timestamp and logoff_time columns contained time part too, please use trunc if you do not wish to consider the time part and then use the distinct.


Generate CREATE DATABASE FOR ATTACH for all existing user databases Does anyone have a TSQL script that can automatically generate the code necessary to attach ALL existing user databases using the CREATE DATABASE FOR ATTACH syntax?

example:

CREATE DATABASE [mydatabase] ON
(FILENAME=N'E:\MSSQL\Data\mydatabase.mdf'),
(FILENAME=N'D:\MSSQL.1\MSSQL\Data\mydatabase_log.ldf'),
(FILENAME=N'E:\MSSQL\Data\mydatabase_ndf.ndf')
 FOR ATTACH

I have found many examples using sp_attach_db, but none with the CREATE DATABASE FOR ATTACH syntax.

Thanks,

Craig

This one does the job for me.  You can tweak the first CTE to include or exclude specific database and it will then generate the appropriate commands.

;WITH CTE_Databases AS
    (
    SELECT D.name AS DatabaseName
        , D.database_id
    FROM sys.databases AS D
    WHERE D.name <> 'tempdb'
        AND NOT(D.name IN ('master', 'msdb', 'model'))
    )
, CTE_FirstAndLast AS
    (
    SELECT 1 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'IF NOT EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = ' + QUOTENAME(D.DatabaseName, '''') + ')' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 2 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'BEGIN' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 3 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , ' CREATE DATABASE ' + QUOTENAME(D.DatabaseName) + ' ON ' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 20 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '     FOR ATTACH;' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 90 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'END' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 91 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , 'GO' AS CommandText
    FROM CTE_Databases AS D
    UNION ALL
    SELECT 92 AS CommandStep
        , 0 AS SubStep
        , D.database_id AS DatabaseID
        , D.DatabaseName AS DatabaseName
        , '' AS CommandText
    FROM CTE_Databases AS D
    )
, CTE_Files AS
    (
    SELECT 10 AS CommandStep
        , ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) AS SubStep
        , F.database_id AS DatabaseID
        , D.DatabaseName
        , CASE WHEN ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) > 1 THEN '        , ' ELSE '      ' END + '(FILENAME = N' + QUOTENAME(F.physical_name, '''') + ')' AS CommandText
    FROM sys.master_files AS F
        INNER JOIN CTE_Databases AS D ON d.database_id = F.database_id
    )
, CTE_AllTogether AS
    (
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_FirstAndLast
    UNION ALL
    SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
    FROM CTE_Files
    )
SELECT * FROM CTE_AllTogether
ORDER BY DatabaseID, DatabaseName, CommandStep, SubStep;

Have you tried this one?  It works quite well.


Simple select is taking too long I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement.

Select Distinct Col1,Col2,Col3 + CHAR(13) + CHAR(10) Col3
From Table
Order by Col1,Col2

Update

Created indexes on col1 and col2 but col3 data is very big, more than 8000 characters, so SQL-server will not allow index on such column.

I am not sure how group by will help in removing the distinct.

As I mentioned it is simple select from table, yes 300k records has to be returned from table.

CREATE TABLE Tablex(
[Col1] [nvarchar](5) NULL,
[Col2] [nvarchar](20) NULL,
[Col3] [nvarchar](max) NULL) ON [PRIMARY]

Create Clustered Index ix_FCL_Tran On Tablex (Col1 Asc, col2 Asc)

Let me know if any more details is required.

Update
No change after adding option fast here is the execution plan

If you only need to know that a row is distinct, and don't need the actual contents of col3, then perhaps returning the hash of col3 would speed up the query?

You could even perhaps pre-compute the hash using a calculated column so that you aren't computing the hash on the fly.

If you do need the contents of col3, but have a lot of duplicates of col1+col2+col3, then it still may be beneficial to work with the hash to remove duplicates as a sub-query, then only return the col3 contents for the distinct rows.

For the columns that are normally too large for an index key, you may be able to gain some benefits of indexing by including them in an index. Use the Query as below,

     CREATE TABLE Tablex(
     [Col1] [nvarchar](5) NULL,
     [Col2] [nvarchar](20) NULL,
     [Col3] [nvarchar](max) NULL) ON [PRIMARY]

    Create Clustered Index ix_FCL_Tran On Tablex (Col1 Asc, col2 Asc) Include(Col3);


What are the main differences between InnoDB and MyISAM? What are the main differences between InnoDB and MyISAM?

First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn't have FULLTEXT search indexes until v5.6, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not.

The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.

So to summarize (TL;DR):

InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

MyISAM
MyISAM is a storage engine for MySQL. Before MySQL 5.5 it was the default storage engine for MySQL. It is based on the older ISAM storage engine.
MyISAM is optimized for environments with heavy read operations, and few writes, or none at all. The reason MyISAM allows for fast reads is the structure of its indexes: each entry points to a record in the data file, and the pointer is offset from the beginning of the file. This way records can be quickly read, especially when the format is FIXED. Thus, the rows are of constant length. A typical area in which one could prefer MyISAM is data warehouse, because it involves queries on very big tables, and the update of such tables is done when the database is not in use (usually by night). Inserts are easy too, because new rows are appended to the end of the data file. However, delete and update operations are more problematic: deletes must leave an empty space, or the rows' offsets would change; the same goes for updates, as the length of the rows becomes shorter; if the update makes the row longer, the row is fragmented. To defragment rows and claim empty space, the OPTIMIZE TABLE command must be executed. Because of this simple mechanism, usually MyISAM index statistics are quite accurate. Other major drawbacks of MyISAM are the absence of transactions support and foreign keys.
InnoDB
InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity).
It implements both SQL and XA transactions, tablespaces, FULLTEXT indexes, and spatial operations following the OpenGIS standard.
It is included as standard in most binaries distributed by MySQL AB, the exception being some OEM versions. The software is dual licensed by the Oracle Corporation; it is distributed under the GNU General Public License, but can also be licensed to parties wishing to combine InnoDB in proprietary software.
Forks
MariaDB has a storage engine called Aria, which is described as a "crash-safe alternative to MyISAM". MariaDB and Percona Server use a fork of InnoDB called XtraDB by default. XtraDB is maintained by Percona. Oracle InnoDB's changes are regularly imported into XtraDB, and some bug fixes and extra features are added.


NoSQL approach: design table "scheme" We need to store simple log data in a database, but we also need to get and filter data in realtime, based on 'user', simple 'tag' (ie. redis, memcache, php, mysql, etc.) and timestamp. We need to scale horizontally and real fast data access on billions rows.

In a SQL approach, table can be like this:

ID | timestamp | tag | user       | log text
1  | 19543532  | 1   | root       | { text log }
2  | 19543532  | 3   | redis-user | { text log }

where tag 1 and 3 are different and related to another table (ie. tag_id | tag_name). I think this is a relational approach and we can create three index (timestamp, tag and user) in order to speed up data access.

What is a good practice to reproduce this in a NoSQL database like DynamoDB (AWS) where we can create only HASH or RANGE index? Does a SQL database fit better than a DynamoDB?

My first attempt is:

First table: ID hash index

ID | log text
1  | { text log }
2  | { text log }

Second table: USER, TIMESTAMP range index

user | timestamp | id
root | 123145122 | 1
redis| 123491241 | 2

Third table: TAG index

tag        | id
debug      | 1
production | 2

Thank you in advice!

w/o any secondary index I can think of PIVOT. (Data in Key-Value pair way)

Depending on the usage and query you are expecting you need to decided if key "LogText" needs to go in to another table or ok to put in Table-2.

You can have key on both columns and that will give you option of defining local secondary index to improve performance for complex queries like

ALL DATA where (TimeStamps is between x and y) and User = "ABC"

this can be done in two steps,

select ALL data where KeyIDs are IN
( get unique KeyIDs for that satisfy this WHERE conditions.)


Clustered Index and PK - Two different Objects? I have a table which has a Clustered PK as follows:

CREATE TABLE [dbo].[PanelCcmRawData] ADD  CONSTRAINT [PK_CcmRawData] PRIMARY KEY CLUSTERED
(
    [CcmRawDataId] ASC,
    [LineageId] ASC
)

I understand that when you create a Clustered Index, the table itself is the clustered index. However, when you make the Clustered Index the PK as well, it seems SQL Server creates an actual index file. I noticed this because when I ran a query to see all the indexes on my table, the number of seeks on the index and the size etc., I saw an entry for PK_CcmRawData with a size of 331GB.

I was a bit surprised to see this. I'm curious why, when creating a Clustered Index that is also the PK, SQL Server creates an index file. I would have thought the table itself would act as the Clustered Index and PK. So there appears to be something I'm not understanding.

The leaf level of the Clustered index is the table.  You still have the other levels of the B-Tree  (root, intermediate) as a part of the clustered index which account for why there is an index for your PK on the table.

When you create Index or PK, SQL never creates a "File".
When you create PK, it will be clustered by default, unless you already have clustered index or specified PK as NON CLUSTERED.


How does the database decide which Index to use DDL

create table t
(
    id int,
    id1 int
)

create index Example_Index
  On t(id,id1)

create index Example1_Index
  On t(id1,id)

DML

insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)

Sql Query - select * from t Where id = 107  -- uses Example_Index

Sql Query - select * from t Where id1 = 107 -- uses Example1_Index

Sql Query - select * from t                 -- uses Example1_Index

Confusion - How does the system decides to use Example1_Index

Below are the details...

First two queries are self-explanatory - they're performing a seek on the leading column in the chosen index.

Third one is a coin-flip since, without a clustered index, both indexes have the same cost.

You might observe that the most recently-used index (from query 2) will be used for query 3 - but you shouldn't rely on it.

The index created latest is used or index with higher index id is being used.This is shown in below query but this is just the observation based on below query and might not be consistent behaviour as i couldnt find any documentaion from MS related to this.
Run below query and then run the same by swapping the two index creation statements.

But as long as optimizer picks the best index it shouldnt bother you.In this case both indexes are same.

drop table t
go
create table t
(
    id int,
    id1 int
)

create index Example_Index
  On t(id,id1)

 create index Example1_Index
  On t(id1,id)
 go
 insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)
go
select * from sys.dm_db_index_physical_stats(9,object_id('t'),null,null,'DETAILED')
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t Where id1 = 107 -- uses Example1_Index

select * from t -- uses Example1_Index
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t -- uses still Example1_Index even though that index is not used at all.However, this index id is 3 higher than example_index.
go
--now try the same thing but change the order of the index and you will see example_index is used.


Derby/SQL - How to increase performance when left joining and retrieving varchar columns Through trial and error I found that if I do a left join such as:

SELECT
    firsttable.id,
    secondtable.id,
    secondtable.varcharColumn
FROM
    firsttable
LEFT JOIN
    secondtable
ON
    firsttable.id=secondtable.id

The performance is terrible for larger tables. If I EITHER remove secondtable.varcharColumn as a column in the resultset OR change the type then the performance is about an order of magnitude better. The column is a varchar 255, so it should not make that much of a difference. If I change the type of column to integerColumn or dateColumn than I also see an order of magnitude difference in the performance. Even a varchar of a few characters seems to result in the same performance degradation.

Please note that the WHERE clause is NOT the issue here. I've omitted it because the issue is the same whether or not a WHERE clause is included. The issue has to do with the type of data in the column and not the quantity of data returned. I could also use LIMIT 1000 and have the very same issue.

Indexing the column will not help as the column is not used for joining. It is also not part of the where clause if there was one.

Any suggestions on how to improve the performance would be greatly appreciated!!

The lack of a WHERE clause or an ORDER BY clause probably means that your query is not using an index (and is most certainly selecting all rows in the table).

So, I'd suggest:

Add a "WHERE" clause
Add an "ORDER BY" clause
Make sure that you have appropriate indexes on the two tables for the ids and the columns in the WHERE and ORDER BY clauses.
Look at the query execution plan to make sure that your indexes are used.

A bit late to the party, but perhaps this idea will be helpful for anyone.

My suggestion is that query without varcharColumn doesn't need to go into the joined table: using the index (being a primary key, I think) is enough to get requested data.

I believe if you had a composite index { secondtable.id, secondtable.varcharColumn }, you would not have any difference in processing time.

Most probably this time would be greater compared to the "fast version" due to request for an extra field and increased physical size of the index - and thus extra IO operations).


Recreating the control file I have experienced a problem with flashback and the solution is to recreate the control file (the error was ORA-01207).

So to do this I tried

 alter database backup controlfile to trace as '/tmp/trace.sql';

This creates a file that instructs one to either use set one or set two. i.e. with or without resetlogs.

How does one determine what set is the most applicable? Is there any tests that one needs to perform to find out?

From /tmp/trace.sql:

  The first set opens the database with the NORESETLOGS option and should be used only if the current versions of all online logs are available. The second set opens the database with the RESETLOGS option and should be used if online logs are unavailable.

If you're only recreating the controlfile you should be able to perform a complete recovery. i.e. without resetlogs. If you are missing some online redo logs or if they are not complete, then it will be a partial recovery, and RESETLOGS will be required.

In addition to @Andrew Brennan answer.
The following steps are using RESETLOGS option, assuming online logs are unavailable.

Trace the Control File backup

ALTER DATABASE BACKUP CONTROLFILE TO TRACE as 'path\trace.sql'

Restart the database in NOMOUNT stage

SHUTDOWN IMMEDIATE
STARTUP NOMOUNT

Run the traced script

--Traced Script

Open the database with RESETLOGS

ALTER DATABASE OPEN RESETLOGS;


MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM? I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said

InnoDB: Initializing buffer pool, size = 128.0M

Does it mean even I am using in such a small size, the server still use 128M RAM?

According to the MySQL Documentation, the InnoDB Buffer Pool is set to 128MB by default in MySQL 5.5.

You can show how much of the InnoDB Buffer Pool is in use and reserved as follows:

SELECT
    BufferPoolUsed BytesUsed,
    (BufferPoolUsed / power(1024,1)) UsedKB,
    (BufferPoolUsed / power(1024,2)) UsedMB,
    (BufferPoolUsed / power(1024,3)) UsedGB,
    BufferPoolReserved BytesReserved,
    (BufferPoolReserved / power(1024,1)) ReservedKB,
    (BufferPoolReserved / power(1024,2)) ReservedMB,
    (BufferPoolReserved / power(1024,3)) ReservedGB
FROM
    (
        SELECT
            (A.num * B.num) BufferPoolUsed,
            (A.num * C.num) BufferPoolReserved
        FROM
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_page_size') A,
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_buffer_pool_pages_data') B,
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_buffer_pool_pages_total') C
    ) AA
;

I have it set to 256M in MySQL 5.5.12 for Windows. Here is my output:

mysql> SELECT
    ->     BufferPoolUsed BytesUsed,
    ->     (BufferPoolUsed / power(1024,1)) UsedKB,
    ->     (BufferPoolUsed / power(1024,2)) UsedMB,
    ->     (BufferPoolUsed / power(1024,3)) UsedGB,
    ->     BufferPoolReserved BytesReserved,
    ->     (BufferPoolReserved / power(1024,1)) ReservedKB,
    ->     (BufferPoolReserved / power(1024,2)) ReservedMB,
    ->     (BufferPoolReserved / power(1024,3)) ReservedGB
    -> FROM
    ->     (
    ->         SELECT
    ->             (A.num * B.num) BufferPoolUsed,
    ->             (A.num * C.num) BufferPoolReserved
    ->         FROM
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_page_size') A,
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_buffer_pool_pages_data') B,
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_buffer_pool_pages_total') C
    ->     ) AA
    -> ;
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
| BytesUsed | UsedKB | UsedMB   | UsedGB             | BytesReserved | ReservedKB | ReservedMB | ReservedGB |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
|   6864896 |   6704 | 6.546875 | 0.0063934326171875 |     268435456 |     262144 |        256 |       0.25 |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
1 row in set (0.00 sec)

mysql>

You can set this value when MySQL starts up. For example:

To set it to use 1G of RAM

[mysqld]
innodb_buffer_pool_size=1G

To set it to use 256MB of RAM

[mysqld]
innodb_buffer_pool_size=256M

After changing it in my.cnf, you must restart mysql for it to take effect.

Yes - at its maximum! The point of buffer pool - is to use memory cache, not any other 8-)


1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column My SQL statement returns properly on my laptop's MySQL (Server version: 5.5.62-0ubuntu0.14.04.1 - (Ubuntu)) but on my server (Server version: 5.7.26-0ubuntu0.16.04.1 - (Ubuntu)) it returns an error.

SELECT *
FROM `orders`
WHERE `mail_sent`='No'
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY `contact_id`
ORDER BY `datetime` ASC;

  1055 - Expression #1 of SELECT list is not in GROUP BY clause and
  contains nonaggregated column 'shop.orders.id' which is not
  functionally dependent on columns in GROUP BY clause; this is
  incompatible with sql_mode=only_full_group_by

I think this problem due to strict mode enabled in your MySQL version. Kindly disable strict mode and try again.
To disable follow How to turn on/off MySQL strict mode in localhost (xampp)?
STRICT_TRANS_TABLES is responsible for setting MySQL strict mode.
To check whether strict mode is enabled or not run the below sql:
SHOW VARIABLES LIKE 'sql_mode';

If one of the value is STRICT_TRANS_TABLES, then strict mode is enabled, else not. In my case it gave
+--------------+------------------------------------------+
|Variable_name |Value                                     |
+--------------+------------------------------------------+
|sql_mode      |STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|
+--------------+------------------------------------------+

Hence strict mode is enabled in my case as one of the value is STRICT_TRANS_TABLES.
To disable strict mode run the below sql:
set global sql_mode='';

[or any mode except STRICT_TRANS_TABLES. Ex: set global sql_mode='NO_ENGINE_SUBSTITUTION';]
To again enable strict mode run the below sql:
set global sql_mode='STRICT_TRANS_TABLES';

If you don't what to disable strict_mode then you have to change your sql query to follow sql standard.
like this..
SELECT
   aggregated_column,
   aggregated_column,
   aggregation_function(column),
   aggregation_function(column),
   aggregation_function(column)
FROM
  TABLE
WHERE [CLAUSE]
GROUP BY [GROUP BY FIELD]
WHILE [CLAUSE]
ORDER BY

for your query..
SELECT
  contact_id,
  min(mail_sent),
  min(datetime),
  max(datetime),
FROM `orders`
WHERE `mail_sent`='No'
  AND `datetime` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
GROUP BY `contact_id`
ORDER BY `datetime` ASC;

Let me know if that helps.

Hello I found a very solution to a very specific case.
If you are

running a linux server (in my case CentOS 7)
Working locally using XAMPP or similar
Your code works fine locally but you get errors on your server

I realized that my local environment was using MariaDB, hence the mysql 8 installation on my linux server was having some problems.
The solution:

Remove your mysql installation and install MariDB.

I answered this same question in stackoverflow in which I describe the process of removing mysql 8 to installing MariaDB and creating a user. (https://stackoverflow.com/a/67444420/12696307)


Safest way to perform mysqldump on a live system with active reads and writes? I'm not sure if this is true but I remember reading if you run the following command in linux

mysqldump -u username -p database_name > backup_db.sql

while reads and writes are being made to a database then the dump may contain errors.

Are there particular options in the command mysqldump to make sure this is done safely on a live system? I'm okay with reads/writes being disabled for our users for a few seconds (The database <50MB)

All Data is InnoDB
This is what will give you an exact point-in-time snapshot of the data:
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
--routines dumps all stored procedures and stored functions
--triggers dumps all triggers for each table that has them
All Data is MyISAM or Mix of InnoDB/MyISAM
You will have to impose a global read lock, perform the mysqldump, and release the global lock
mysql -uuser -ppass -Ae"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
sleep 5
mysql -uuser -ppass -ANe"SHOW PROCESSLIST" | grep "SELECT SLEEP(86400)" > /tmp/proclist.txt
SLEEP_ID=`cat /tmp/proclist.txt | awk '{print $1}'`
echo "KILL ${SLEEP_ID};" > /tmp/kill_sleep.sql
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql
mysql -uuser -ppass -A < /tmp/kill_sleep.sql

Give it a Try !!!
UPDATE 2012-06-22 08:12 EDT
Since you have <50MB of total data I have another option. Instead of launching a SLEEP command into the background to hold the global read lock for 86400 sec (that 24 hr) just to get the process ID and kill outside, let's try setting a 5 second timeout in mysql rather than in the OS:
SLEEP_TIMEOUT=5
SQLSTMT="FLUSH TABLES WITH READ LOCK; SELECT SLEEP(${SLEEP_TIMEOUT})"
mysql -uuser -ppass -Ae"${SQLSTMT}" &
mysqldump -uuser -ppass --single-transaction --routines --triggers --all-databases > backup_db.sql

This is a cleaner and simpler approach for very small databases.

if you have a very big MYISAM table and you need to dump the table without lock and avoid high server load, you can use the following script.

#!/bin/sh

my_user="user"
my_password="password"
my_db="vpn"
my_table="traffic"
my_step=100000

read -p "Dumping table ${my_db}.${my_table} to ${my_table}.sql?" yn
case $yn in
    [Yy]* ) break;;
    * ) echo "User cancel."; exit;;
esac

my_count=$(mysql $my_db -u $my_user -p$my_password -se "SELECT count(*) FROM $my_table")
my_count=$(($my_count + 0))

if [ ! $my_count ]
then
    echo "No records found"
    exit
fi

echo "Records in table ${my_db}.${my_table}: ${my_count}"

echo "" > $my_table.sql

max_progress=60

for (( limit=0; limit<=$my_count; limit+=$my_step )); do
    progress=$((max_progress * ( limit + my_step) / my_count))

    echo -ne "Dumping ["
    for ((i=0; i<$progress; i ++)); do
        echo -ne "#"
    done
    for ((; i<$max_progress; i ++)); do
        echo -ne "."
    done

    mysqldump -u $my_user -p$my_password --complete-insert --no-create-info --opt --where="1 limit $limit , $my_step" $my_db $my_table >> $my_table.sql
    echo "" >> $my_table.sql

    echo -ne "] $((100 * ( limit + my_step ) / my_count)) %"
    echo -ne "\r"

    sleep 1

done

echo -ne "\n"


Increase Oracle buffer cache gets slower So we're contemplating increasing our Oracle 11gR2 buffer cache.  They said they tried it in the past and things appeared to get worse - so they undid the change.  They, however, have no idea why things could have gotten worse.

Its possible they were preemptive and just didn't give it time considering it was full server reboot, but what are the situations where increasing the cache would yield worse results?  I've seen this page (situation B) but I can't find anybody else claiming that.  We're definitely doing more scattered reads than 'normal' probably given the kinds of queries we have to run.

In any system, if you make the part of the system that is not the bottleneck more efficient, there is the potential to make the system as a whole less efficient.  If, for example, your system's bottleneck is the CPU, making I/O more efficient by increasing the size of the buffer cache may end up causing many more threads to be waiting on the CPU which can make the system as a whole much slower as the operating system is now spending more time swapping between the competing thread, the CPU caches get overwhelmed, and you get the classic hockey stick graph of response time where once you get to the "knee", adding a bit more load adds a lot to the response time of the system.

If you have a system where I/O requests are highly uncorrelated, increasing the size of the cache could mean that Oracle will spend more time looking through the cache for a particular block before being forced to do a physical read after all.  That's probably relatively unlikely in an OLTP application but it's possible in a data warehousing type system where various reports are asking for blocks that no other report will want to reuse.

Of course, whether this is actually the problem that they encountered in the past is still an open question.  It is entirely possible that it was simply a matter of having a cold cache that needed to be warmed up or a perception problem where someone drew an incorrect conclusion from an earlier test or a poorly constructed test where, for example, someone inadvertently increased the size of the buffer cache at the expense of the PGA rather than at the expense of the operating system I/O cache.  I tend to share your distrust of human recollections of the past that aren't corroborated by some nice AWR reports so I'm not claiming that either of the above explanations are likely merely that they are potential explanations if the human recollections are correct.

Increased buffer cache and worse performance; classic case of not tuned SQL applications that restricts scalability
Check your SQLs and tuned them.
When you are confident that your SQLs are well tuned, you can consider increasing buffer cache to improve performance.


Business Periods/Weeks to date conversion Our current date system divides the business year into 'Periods' which last for 4 weeks. Period one begins on a fixed date, lets say 1st March.

I have a simple table with Weeks and which period they come under. I would like to add another column [Date] with each day in it, effectively multiplying my number of rows by 7. How can I go about calculating these values using an inbuilt calender in SSMS?

There isn't a built-in date table for SQL Server, but it's easy to add or expand your own. If you want to add weeks to your existing table, the code might look like this:

UPDATE tblDates
SET WeekOfYear = DATEPART(WEEK, [date])

If you're just starting out and only have dates so far, it may be easier to find someone else's table script and use that. That way, you don't have to write the code to populate all those other columns.

A good example of a simple table using calendar years is here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

This is important: Weeks are goofy -- you'll notice in the script linked above that there's a [week] and there's an [ISO_week] in the DATEPART() functions. Note that the [week] column won't always have seven days (e.g, the first week of January 2013 is only five days long -- Tuesday through Saturday). This is what you'll find with the DATEPART(WEEK, [date]) function. If you need each week in your 4-week period to be exactly 28 days, it's easier to use the ISO_week than to string weeks together in code.

I would suggest dropping the calendar table in favor of a function, like:

CREATE FUNCTION getperiod ( @indate AS date)
RETURNS int
AS
BEGIN
    RETURN DATEDIFF(day,'2016-03-01',@indate) / 28;
END;

(Please adapt to your requirements.)


Why the table gets deleted by the server in mysql? I have a table in mysql. I have one java application which will keep inserting the data into this table. So the size of the table grows drastically and at one point it gets deleted automatically by the mysql server. I am not sure about the reason yet. So please if anybody has come across this issue then please share the way to handle this, so that I can have my data preserved.

UPDATE

This is what I found in the sql log of the server.

> 2013-11-27T15:07:39.285156Z    9 Query    SELECT s.Sql_Id
>       ,s.Child_Number
>       ,s.Executions
>       ,s.Last_Active_Time
>       ,s.User_Io_Wait_Time
>       ,s.Object_Status
>       ,s.Plan_Hash_Value FROM   V$sql s WHERE  s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */ T1.SYNC_TIMESTMP, T1.CUSTNO, T1.XAFID_ID_IFX,
> T1.XAFID_CLASSIFIED%' 2013-11-27T15:07:39.286132Z    9 Query  SHOW
> WARNINGS 2013-11-27T15:08:08.791015Z    9 Query   select
> @@session.tx_read_only
> 2013-11-27T15:08:08.791992Z    9 Query    drop table devicedata 2013-11-27T15:08:08.926757Z    9 Query    SHOW WARNINGS
> 2013-11-27T15:08:08.936523Z    9 Query    select @@session.tx_read_only

Thanks

Reading the log seems to make the problem fairly apparent:

2013-11-27T15:08:08.791992Z 9 Query drop table devicedata

That's not mysql dropping the table.  That's your application dropping the table.

There's another line in the log that is very suspicious:

 ,s.Plan_Hash_Value FROM V$sql s WHERE s.Sql_Text LIKE 'SELECT /*+ FIRST_ROWS XX */

V$sql is not a valid table name, at least not without backticks.  It sounds like the application may be trying to build the table name from a string and building the string incorrectly... when that query fails, it drops the table in some kind of misguided attempt at "recovery."

Search the code for the word "drop."  MySQL has no concept of dropping a table because it's too full.

We had similar problem where we lost all the tables of the database but the database remains as it is in the mysql. The problem and the solution is as follows,
  1) Check where you have installed your MYSQL/XAMPP/WAMP
  2) Check whether you have all the permissions to install the MYSQL/XAMPP at the
     place where you have installed your XAMPP/MYSQL on your desktop/server.

The solutions is simple, just re-install your XAMPP/MYSQL on your desktop/any other location where you have permission to install. Your will not get this problem in future. Please do not forget to check your error logs and take necessary actions after checking logs daily.


Does Express Profiler have a major effect on performance of SQL Server Express I'm wondering does running Express Profiler against an SQL Express Server have a major impact on performance.  The versions of SQL Express I'm looking to run against are on 2008 and 2013.

This is the express profiler I'm using. http://expressprofiler.codeplex.com/

Performance impact of expressprofiler is almost the same as of standard sql profiler and can be up to 25%


What permission is needed to set Database read-only? I am dbowner of a database which I want to set read-only. (through sql management studio). When I try that I get a permission error (297)

Which role is needed to perform that action?

Form Query Analyzer or Management Studio, you can issue the following command:

ALTER DATABASE database-name SET READ_ONLY

As mentioned here, you need the alter database rights and you must set the database to single mode.

  Changing the state of a database or filegroup to READ_ONLY or
  READ_WRITE requires exclusive access to the database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

There is a system stored procedure. You can also use this for this task.

> Syntax: sp_dboption [ [ @dbname = ] 'database' ]
>     [ , [ @optname = ] 'option_name' ]
>     [ , [ @optvalue = ] 'value' ]  [;]

Example:

 EXEC SP_DBOPTION   -- To check Settable option

 EXEC SP_DBOPTION 'MyDatabase' -- Return the following option that are
 set.

 EXEC SP_DBOPTION 'MyDatabase','READ ONLY' --- Set the Read Only to the db

 EXEC SP_DBOPTION 'MyDatabase','READ ONLY','FALSE' --- Change the
 current setting of the option


MySQL Query missing from the slow query log I've been looking at queries creating many temporary tables in the performance_schema.events_statements_summary_by_digest.

FROM performance_schema.events_statements_summary_by_digest
where SUM_CREATED_TMP_TABLES > 0 and digest is not NULL
order by SUM_CREATED_TMP_TABLES desc limit 1\G

I have a clear worst offender:

                 COUNT_STAR: 76703
             SUM_TIMER_WAIT: 96029343223000
             MIN_TIMER_WAIT: 631886000
             AVG_TIMER_WAIT: 1251963000
             MAX_TIMER_WAIT: 19597013000
              SUM_LOCK_TIME: 14475751000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1125890
          SUM_ROWS_EXAMINED: 29941322
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 76742
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 76754
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2019-09-25 00:08:30
                  LAST_SEEN: 2019-09-25 11:22:49

If my understanding is correct.  The above suggests the query has been executed 76,703 times since 2019-09-25 00:08:30 and created 76,754 temporary tables.  Implicitly.

What I don't understand is that if I set the long_query_time to 0 the query does not show up in the slow query log.
I thought if the long_query_time was set to 0 all queries are written to the log.
Thanks in advance.

Thanks to Vinicius@Percona.  He explained the reason the query does not appear in the slow query log is due to the fact that existing connections are not impacted by the long_query_time being changed.  Only new connections.

by 8adger on Sep 25, 2019  "I thought if the long_query_time was set to 0 all queries are written to the log."

Wrong thought.  When long_query_time = 0, NO queries are logged to the Slow Query Log.


Create read only user without being root I want to create read only user in MySQL without any intervention of root user.

CREATE USER ‘$user‘@’127.0.0.1′ IDENTIFIED BY ‘$password‘;

Works for me only If I am a root user. But I want it to happen from a non root user. Can this be done?

From the manual

  To use this statement, you must have the global CREATE USER privilege
  or the INSERT privilege for the mysql database.

So first you must have the root account grant you access to CREATE USER. Then your account will be able to create other users.

You can read the manual http://dev.mysql.com/doc/refman/5.1/en/create-user.html

First you have to create a user and then assign permission to it.

CREATE USER 'test'@'%' IDENTIFIED BY 'test';

and later execute this query to grant global SELECT privilege.

GRANT SELECT ON *.* TO 'test'@'%';


Authoritative source that  and != are identical in performance in SQL Server Consider this answer on SO that reassures the asker about the <> operator that:

<> is ... the same as !=.

But then a commenter pipes up and says:

It's true that they are, functionally, the same. However, how the SQL optimizer uses them is very different. =/!= are simply evaluated as true/false whereas <> means the engine has to look and see if the value is greater than or less than, meaning more performance overhead. Just something to consider when writing queries that may be expensive.

I am confident this is false, but in order to address potential skeptics, I wonder if anyone can provide an authoritative or canonical source to prove that these operators are not just functionally the same, but identical in all aspects?

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present:

This occurs well before anything in the optimizer gets involved.

  From Comparison Operators (Transact-SQL)

Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register eax** as follows:

╔════╦══════╗
║ Op ║ Code ║
╠════╬══════╣
║ <  ║    1 ║
║ =  ║    2 ║
║ <= ║    3 ║
║ !> ║    3 ║
║ >  ║    4 ║
║ <> ║    5 ║
║ != ║    5 ║
║ >= ║    6 ║
║ !< ║    6 ║
╚════╩══════╝

!= and <> both return 5, so are indistinguishable in all later operations (including compilation & optimization).

Though secondary to the above point, it is also possible (e.g. using undocumented trace flag 8605) to look at the logical tree passed to the optimizer to confirm that both != and <> map to ScaOp_Comp x_cmpNe (not equal scalar operator comparison).

For example:

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID != 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

SELECT P.ProductID FROM Production.Product AS P
WHERE P.ProductID <> 4
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

both produce:

LogOp_Project QCOL: [P].ProductID
    LogOp_Select
        LogOp_Get TBL: Production.Product(alias TBL: P)
        ScaOp_Comp x_cmpNe
            ScaOp_Identifier QCOL: [P].ProductID
            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
    AncOp_PrjList

Footnotes

* I use WinDbg; other debuggers are available. Public symbols are available via the usual Microsoft symbol server. For more information, see Looking deeper into SQL Server using Minidumps by the SQL Server Customer Advisory Team and SQL Server Debugging with WinDbg – an Introduction by Klaus Aschenbrenner.

** Using EAX on 32-bit Intel derivatives for return values from a function is common. Certainly the Win32 ABI does it that way, and I'm pretty sure it inherits that practice from back in the old MS-DOS days, where AX was used for the same purpose - Michael Kjörling

That is incorrect, Books Online (BOL) says they are functionally the same:

!= (Not Equal To) (Transact-SQL)

And if you look at an execution plan where != is used, under Predicate, it changes != to <>.


Have you tried this one?

Case # 1

This SQL Query only check out in February month of 2014 of all type (that is Cat,dog,horse):

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
GROUP BY type
ORDER BY type;

Case # 2

This SQL Query check out the only type cat for the month of February 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-02-01'
                  AND '2014-02-28'
AND      type LIKE '%cat%'
GROUP BY type
ORDER BY type;

Case 3#

This SQL Query check out all type in the year 2014:

SELECT   type, YEAR(date), SUM(count)
FROM     myTable
WHERE    date BETWEEN '2014-01-01'
                  AND '2014-12-31'
GROUP BY type
ORDER BY type;

Sorry, there is no simple answer.  Use MySQL for the raw data; do pivoting in your UI.


Save schedules (working hours) I have an app to make appointments with doctors. Doctors have schedules (working hours).

For example, let's say doctor A usually works 10:00 - 14:00 on weekdays. I could save this info in each doctor's table; however, there are holidays, sick days and so on- maybe even short days 11:00 - 13:00. How to represent them in the PostgreSQL database?

My current approach is to create a schedules table and store the usual working hours. For doctor A on weekdays from 10:00 - 14:00, and for other days create separate tables in which we specify when the doctor cannot work.

save the unavailable hours as a special type of appointment


Replacing composite key with surrogate I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current composite 6 field primary, then make the current key a unique key for referential integrity and recreate the indexes.

    CREATE TABLE [dbo].[Autocompleter](
        [CountryId] [int] NOT NULL,
        [ProvinceId] [int] NOT NULL,
        [LocationId] [int] NOT NULL,
        [PlaceId] [int] NOT NULL,
        [EstabId] [int] NOT NULL,
        [LocaleId] [int] NOT NULL,
        [Title] [varchar](400) NOT NULL,
        [Hotels] [int] NULL,
        [AlternateTitles] [varchar](4000) NULL,
        [EnableHotels] [bit] NOT NULL,
        [EnableHolidays] [bit] NOT NULL,
        [DisplayPriority] [int] NOT NULL,
     CONSTRAINT [PK_autocompleter_1] PRIMARY KEY CLUSTERED
    (
        [CountryId] ASC,
        [ProvinceId] ASC,
        [LocationId] ASC,
        [PlaceId] ASC,
        [EstabId] ASC,
        [LocaleId] ASC
    )

Any gotchas I should be looking out for ? if it is an identity field I am thinking this should not break code that inserts into the table (as long as it specifies the columns explicitly)

I plan to create a new clustered index on the surrogate key and then make the current clustered index on the 6 fields a NC index.

Reading up on Kimball dimensional modeling, use of a surrogate key, in particular an IDENTITY, will help reduce fragmentation caused by page splits as you'll be appending rows at the end of leaf pages vs. attempting to insert them in the middle if the keys are not in the order of the index (ascending or descending depending on how the index was defined).

But, if the surrogate key will not be used in joins with other tables or in WHERE criteria, use of the surrogate key as a clustered index might not provide any additional benefit after the initial data import. If you keep the composite clustered key, sorting the source data in the order of the six-column composite key prior to import should avoid the page split fragmentation you're witnessing.

As for a choice of clustered index, the best candidate may be the column(s) that would be used in your most common/critical queries for WHERE criteria or joins. Use of a NCI will require a bookmark lookup to obtain the remaining values in the table if needed in the resultset.

At the storage level in SQL Server (probably in all dbms), there aren't any tables. At the storage level in SQL Server you have one of two things. (I think.)  You have

the heap plus some other things, or
the clustered index plus some other things.

Heaps get fragmented by randomly distributed inserts, updates, and deletes.  AS far as I know, Microsoft doesn't provide any direct way to defragment the heap.

But create clustered index rewrites the heap. So you could create a clustered index to rewrite the heap, then drop the clustered index. I don't think of this as a direct way to defragment the heap, because rewriting the heap is a side-effect of creating a clustered index.

Here's what I don't understand yet. (And it might be pretty basic knowledge, but I don't work on SQL Server a lot.)

The entire table gets reloaded every day. It's never updated, apart from that daily reload. So where does the fragmentation come from? Bad choice of fill factor? Would a fill factor of 100 be a defensible choice for a table like this?


Storing multiple contact addresses for a person Simplified diagram for reference.

My database stores information about people in the following way. There is a  Person table containing common personal information fields; name, date of birth, gender, etc.

The contact information for each person is kept in the ContactInformation table with a 1:1 relationship.

Finally, there are then individual tables for each specific type of person in the organisation we store data on with a 1:1 relation to Person

I have a bit of a problem with the Student table. Basically my client now wants to store both a home address and term time address for a student. But I'm not sure how to approach this.

I see three possible options/solutions:

Junction Table - Create a junction table (e.g. ContactAddressMapping) between Person and ContactInformation.
New Table - Create a TermTimeAddress table with a 1:1 relation to Student
New Fields - Add extra fields to the Student table to hold the term time address.

Now here are the main "problems" I see with each option:

A junction table would allow multiple addresses to be stored for a person, but we'd only ever need to do this for students (for now). There would also be no way to determine which address is the home/term time address without using some kind of boolean field which seems like pointless information in that table since it's only unique to students.

Creating a new table separates the term time address from the main address but these feels like duplication since I'm basically creating a specific type of address with the same fields, minus things like phone numbers and email addresses.

Adding the fields to the Student seems like I'm cluttering the table with information that should be elsewhere. The table should only be for information about that student such as their school, the area of study and the start and end date of their placement.

Which of these would be the most appropriate option, or is there another solution that I haven't seen yet?

I wouldn't have the ContactInformation linked 1-to-1 to Person anyway; storing several addresses like that in ContactInformation violates normal form. Instead I'd have ContactInformation with its own PK, an FK for PersonID back to the Person table, and store as many Contacts as necessary for each Person.

CREATE TABLE ContactInformation (
    ContactID int IDENTITY(1,1) PRIMARY KEY,
    PersonID int FOREIGN KEY REFERENCES Person(id),
    ContactInfo nvarchar(250),
    ContactType char(2)
)

Populate the ContactInformation table with the PersonID, then an email address/phone number/mailing address/twitter handle/etc and mark it with a ContactType. This could be 'MA' for mailing address, 'EM' for email, "TW' for Twitter, 'TT' for Term Time as in your question.

Triggers could validate any inserts to make sure contacts don't have a TT (term time) address unless they're a student.

Hope that helps you think outside the box. It may be too hard to make these changes at this point, if you're already deep in the application.

Edit:
I didn't feel I properly addressed your direct question. If I were you and making deeper changes isn't possible or feasible at this point, I would add TermTime address to the Students table.

It belongs there because 1) it only applies to Students, so having it in the Person or Contact tables (as they exist now) is less than ideal, and 2) Each student should have one so it's already part of their student record and 3) this can be done with the least disruption to the existing schema.

Your already have it setup just create a new record in the "ContactInformation" table with the same PersonID and then the person has two addresses