Foreign key in MySQL I am learning about database, I need to know something about the foreign key in MySQL.

Consider the following two tables.

UserGroupType.
UserGroup.

UserGroupType:

UserGroup:

In this table the foreign key is defined as follows,

foreign key   (GroupType_id) references UserGroupType(GroupType_id)

I just want to know whether the name of the field in UserGroup can be changed? consider the following altered statement.

GroupType:

foreign key (Type_id) references UserGroupType(GroupType_id)

Is it necessary that both the field names must be same?

How to set the default value for datetime?

Thanks in advance, sorry if it is very basic things.

foreign key would have a different name from the primary key it is referring to, but necessarily , their data type and other attributes should be the same.
for example , if one is INT(10) unsigned the other should be defined the same.

but sometimes it is a good practice to name them similarly in complex queries. for example when you want to JOIN the relating tables, you can use the syntax :

userGroupType INNER JOIN userGroup USING (GroupType_id)

instead of :

userGroupType INNER JOIN userGroup ON userGroupType.GroupType_id = userGroup.GroupType_id


Bulk insert not retaining special chars of UTF-8 Encoded txt file Sql Server 2008 I have a stored procedure that bulk imports a text file and inserts it into my database.

CREATE TABLE DBO.TEMP_STORE
(
            ID nvarchar(max),
            [MONTH] nvarchar(max),
            [YEAR] nvarchar(max),
            STORE nvarchar(MAX),
            SUBMITTAL nvarchar(MAX),
            ENTITY nvarchar(MAX),
            SUBMITTAL_TYPE nvarchar(MAX),
            iCOUNT nvarchar(MAX),
            STATE nvarchar(MAX),
            COUNTRY nvarchar(MAX),
            REGION nvarchar(MAX),
            GLOBAL_REGION nvarchar(MAX),
            LOCAL_CURRENCY nvarchar(MAX)

 )
--SELECT * FROM DBO.TEMP_STORE
--prepare bulk insert query to load data from file to temp table
SET @SQL='BULK INSERT DBO.TEMP_STORE FROM '''+ @FilePath+''''
SET @SQL=@SQL+'WITH ('
SET @SQL=@SQL+'DATAFILETYPE = ''char'','
SET @SQL=@SQL+'FIELDTERMINATOR = ''|'','
--SET @SQL=@SQL+'ROWTERMINATOR = ''\n'','
SET @SQL=@SQL+'ROWTERMINATOR = ''' + nchar(10) + ''','
SET @SQL=@SQL+'FIRSTROW =2)'

--print @SQL
EXEC (@SQL)

For instance, importing Update ROLLING_CONE_SHOP_DETAIL set SHOP_STATE = 'São Paulo' results in storing the data as'S+úo Paulo' when I select the same row that was imported.  I can update the row and explicitly fix the fix the issue:

Update TEMP_STORE set STATE = 'São Paulo' where STATE= 'S+úo Paulo'

But when I do my Bulk insert, it does not retain the special character.  I have confirmed the text file I am importing is saved at utf-8 and contains the correct character.

How can I make sure my bulk inset retains the special character properly?

SQL Server 2008 does not support UTF-8 natively prior to SQL Server 2019.

So how can you import your data correctly?

Convert your data to UTF-16 prior to your insert
Upgrade to SQL Server 2014 (SP2) or above and take advantage of code page 65001 in the bulk insert statement as below

BULK INSERT #table

FROM  'C:\MyFile.csv'+ WITH

(
    CODEPAGE = '65001',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR ='\n'
);

Since UTF-8 is an encoding style, not a collation, store the data as VARBINARY and decode it in the front-end rather than the database.


MySQL "Statement may not be safe to log in statement format" warning What is the meaning of the following MySQL warning:

Statement may not be safe to log in statement format.

I have some tens of thousands of these statements every day in my mysqld.err file, and I'm not sure where should I begin my investigation.

This means that your binary log format binlog_format is STATEMENT, which is the default binary log format.

STATEMENT-based format logs the statement issued, while ROW-based format logs how individual tables were changed. The problem with STATEMENT logging is certain statements cannot guarantee that the same data written on the Master will end up on the slave.

An example:

INSERT INTO audit (CURRENT_USER());

You can get a good idea of different types of these non-deterministic statements by looking at how the MIXED format handles switching to ROW-based format. ROW-based format writes a lot more data to the binary log, but is much safer.


SUM() 3 columns with alias into a fourth one This is one part of my procedure:

 select nome,

       --casos expedidos (- 3 meses)
       isnull((SELECT      count(cod_rep)
                    FROM   tbReps
                    where  tbreps.cod_status = 7
                    and          tbreps.cod_perito_primeiro=cod_usuario
                    AND          (MONTH(tbreps.datahora_emissao) = MONTH(@DATA1)
                    AND          YEAR(TBREPS.datahora_emissao) = YEAR(@DATA1))),0) as Laudos_Expedidos1,

       --casos expedidos (- 2 meses)
       isnull((SELECT      count(cod_rep)
                    FROM   tbReps
                    where  tbreps.cod_status = 7
                    and          tbreps.cod_perito_primeiro=cod_usuario
                    AND          (MONTH(tbreps.datahora_emissao) = MONTH(@DATA2)
                    AND          YEAR(TBREPS.datahora_emissao) = YEAR(@DATA2))),0) as Laudos_Expedidos2,

       --casos expedidos (- 1 meses)
       isnull((SELECT      count(cod_rep)
                    FROM   tbReps
                    where  tbreps.cod_status = 7
                    and          tbreps.cod_perito_primeiro=cod_usuario
                    AND          (MONTH(tbreps.datahora_emissao) = MONTH(@DATA3)
                    AND          YEAR(TBREPS.datahora_emissao) = YEAR(@DATA3))),0) as Laudos_Expedidos

It will create this:

How can I sum, for example, LAUDOS_EXPEDIDOS1 + LAUDOS_EXPEDIDOS2  Into a 4º column? like this:

I'm pretty sure it's something really easy. I'm trying to use sum with the aliases, with no success.

You cannot refer to an alias defined in the same SELECT list. There are various workarounds. Assuming your query is something like this:

select nome,
       -- various complex expression / subqueries
       <complex_expression_or_subquery>
           as Laudos_Expedidos,
       <complex_expression_or_subquery>
           as Laudos_Expedidos2
from <table_name> ;

Duplicate code. As this is the least attractive solution, for various reasons, I won't add a code sample.
a derived table or a CTE allows to use the aliases as they are already defined in a previous level:

with cte as
( select nome,
         -- various complex expression / subqueries
         <complex_expression_or_subquery>
             as Laudos_Expedidos,
         <complex_expression_or_subquery>
             as Laudos_Expedidos2
  from <table_name>
)
select c.*,
       c.Laudos_Expedidos + c.Laudos_Expedidos2
           as Total_Laudos_Expedidos
from cte as c ;

Use CROSS / OUTER APPLY allows also to refer to previously defined aliases. This offers the possibility of using multiple outer/cross apply subqueries, if you want to use the aliases of the first apply in further calculations. (With the derived/CTE solution, that is also possible but the code usually looks more complicated):

select t.nome,
       f.*,
       g.Total_Laudos_Expedidos,
       f.Laudos_Expedidos / g.Total_Laudos_Expedidos
           as Percent_Expedidos
from <table_name> as t
  outer apply
    ( select
          -- various complex expression / subqueries
          <complex_expression_or_subquery>
              as Laudos_Expedidos,
          <complex_expression_or_subquery>
              as Laudos_Expedidos2
    ) as f
  outer apply
    ( select
          f.Laudos_Expedidos + f.Laudos_Expedidos2
              as Total_Laudos_Expedidos
    ) as g ;


PostgreSQL, how to query the database to retrieve the percentile elements of a dataset? I need a PostgreSQL query that do the following job: given a percentage (e.g. 10%), the query should return each 1st element of each percentile, among all the elements selected.

So suppose in this simple case my table contains elements which are integer values between 1 and 100:

id    | value
--    | -----
id1   | 1
id2   | 2
...
id100 | 100

And suppose the input percentile is 10%.
The query should divide all the dataset into spans whose range is 10%, and return each first element.
So the output should be:

id1 | 1
id11 | 11
id21 | 21
id31 | 31
id41 | 41
id51 | 51
id61 | 61
id71 | 71
id81 | 81
id91 | 91

Does anyone knows how I could write this Sql query? Thanks!

You can utilize NTILE to create n quantiles:

select id, min(value)
from
 (
   select id, value,
      ntile(10) over (order by value) as nt
   from tab
 ) as dt
group by id, nt


Table values being cut-off by table lines Is there a way in the SSRS table component to avoid that the values get cut-off by the lines of the table.

You should format your label so that there is a background color of white (instead of default none).

Right-click on one of the labels and select Series Label Properties.  Select Fill.  By default this is set to No Color.  Change it to White.  Click OK.  That should take care of it!

ADDITION: It may be possible to write an expression to change the distance between the label and the bar.  When you select one of the labels, check out the properties window.  You would need to play around with SmartLabels - specifically MaxMovingDistance and MinMovingDistance.  You would need to have chart lines at specific intervals and then build the expression around the value being within one of those intervals.

I know that is short on details, but hopefully the idea sketch gets you started in the right direction!


Open source alternative to SQLMaestro for PostgreSQL I'm looking for an open source alternative to SQLMaestro for PostgreSQL because it has visual database designer which is not avaliable in pgAdmin which makes database design so easy.

PS I'm not sure if this is illegal here but I've to ask about it because google didn't help me to find the alternative.

I found awesome tools in PostgreSQL wiki thank for your help guys.

The summary:

PGmodeler: If we mixed pgAdmin with PGmodeler we'll get full alternative to SQLMaestro for PostgreSQL but we'll miss one thing which is "Reverse Engineering" to the database because PGmodeler works in one direction.
As of 2017, importing existing schema / layout (aka. "Reverse Engineering") is possible with PGmodeler!
Power Architect: Supports many database engines but its designer has problem with database syncing "Forward Engineer" in case the developer changed the design of the scheme by another tool (ex. pgAdmin) in addition to slow performance in case of designing big schemes because it uses Java/Swing and finally, the user guide is totally commercial for 99$... damn :(
Open System Architect: Supports many database engines but it seems dead project although it built by Qt (latest commit was in 2010) I couldn't connect to any database because it failed to find unixODBC path although I already installed it in my Mint Linux!
TOra is also worth noting: TOra - An Oracle Toolkit for DBA's and developers. PostgreSQL support is officially not complete - seems to work reasonably well with older psql versions (<= 9.4), while having a segfault now and then. Special: All SQL queries (used to fill the GUI with data) may be edited.

I wish this will be helpful to you.

UPDATE: As of 2017, importing existing schema / layout (aka. "Reverse Engineering") is possible with PGmodeler!


Most suitable DB Architecture for real-time high-volume processing API I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5.

The DB consists of only a few tables without high usage or data - but one table is growing REALLY fast (each use updates it each second with a new record).

On my server load testing it grew to above 400,000 records in an hour or so - my performance declined from 200ms RTT to 800ms RTT in about 2 hours.

How should I build my DB to support this? Mayvbe the problem lies in another place?

For now it works with all the tables in one DB, but I thought maybe I should move the "heavy" table to a new DB to increase performance. Should it work? is it a smart move?

I've though about moving that table to NoSQL - will it help?

Any ideas are welcome!

Are you doing a round-trip to the DB for every request? If so, the classic cause of performance deteriorating as a table grows is stale statistics, as in, a query plan that made sense when a table was small is suboptimal once it grows. Check the query plans. Also check that you have auto stats creation, update, and async update enabled. I am assuming that you are already using a clustered index.

There is no point moving your hot table to a different database unless you a) understand what it is that you are waiting on and b) the different database relieves that. E.g. if you are waiting on I/O perhaps moving to a different database stored on different disks might help - but if its running through the same storage controller, it might not. If you are waiting on CPU, then this will not help, you would need to add CPUs or move it to another host. If you are waiting on lock contention, then this will not help, you will need to use a more suitable isolation level. So first, perform the necessary analysis to see where your SQL Server is spending its time.

But also consider if a round-trip to the DB is necessary for every query, in C# for example there are data structures offering O(1) access. This may be a better approach.


Sybase ASE monitoring tool of choice I have inherited an Sybase ASE 16 database server and my initial task consist on deploy some Key Performance Indicators to monitor the RDBMS instance performance.

I have 3 tools available in the company:

Cacti
Nagios
Ganglia

And I'd like to have easy to read, nice-looking performance graphs like this:

I'm starting to look for information about configurations to be made to achieve this.

Any advice?

Working with SybaseASE since last 7 years, I can tell that the newer versions of sybaseASE relies on MDA tables (just like SQLServer DMVs). The tools out there for SybaseASE monitoring are pretty expensive.

I my company, I have build reporting on the top of MDA tables. The prettiness of the graphs you have linked comes at cost.

As a cheap alternative (It is a modest 6.99 U$K per core ) would be to use ASEProfiler or DBACockpit (sybase 16 and up).

As for the configuration settings, each environment is different, so the configuration settings will be different. Start with the common ones and monitor them sp_monitorconfig 'all', null, 'full' .. esp the reuse count should never be more than 0.

Data cache
procedure cache
max memory
partition the data cache
have multiple data cache e.g. 2K, 8K or 16K.
Online engines (sybase 15.7 and up)
max lava operators

All above settings can be tuned using sp_sysmon.


select query on postgis not working for specific fields So I have what seems to be a really weird problem.

I have imported a table from QGIS into a postgresql + postgis database.
I simply want to select a few columns from that, but I can't.

whats funny is that i can use the select query for the lowercase fields and it works, but not for the uppercase ones, such as LABELRANK. the error shows that such a column does not exist.

why is that so?

Probably this columns names has been created using double-quotes.

You should use double-quotes in your SELECT statement.

create table tbl (field1 int, "FIELD2" int, field3 int);

insert into tbl values (1,2,3),(4,5,6);

select field1, field2, field3 from tbl;

ERROR:  column "field2" does not exist
LINE 1: select field1, field2, field3 from tbl;
                       ^
HINT:  Perhaps you meant to reference the column "tbl.field1" or the column "tbl.field3".

select field1, "FIELD2", field3 from tbl;

field1 | FIELD2 | field3
-----: | -----: | -----:
     1 |      2 |      3
     4 |      5 |      6

db<>fiddle here


Replicating two PostgreSQL databases without using master db I'm trying to create a simplest possible db cluster for my application and i'm not sure if that's the best way to achieve that so i'm gonna try to explain my situation the best i can.

Im basically running two instances of my app on two separate servers - one in US and one in Europe. I'm looking for a way to have one single database for both of them (i'm not interested in situation in which both apps has completely different data set in separate databases) while keeping the latency between app and db as low as possible.

It would be the best and the cheapest ("cheap" is fine in my situation because i'm not aiming to make my app super highly available, i just want the latency to be tolerateable for my users. And by cheap i mean as smallest amount of servers involved as possible.) to have two separate postgresql servers (one in US and one in EU) dedicated for each app instance BUT with the same dataset.

That's why my main question is - is that possible to have two PostgreSQL servers (one server for US and one for EU) which synchronises their contents with each other? I don't even need that small cluster to be server-down-proof (i'm not sure how that's called but i'm talking about the probably most common case and target of 99% of tutorials related to that topic on the internet - when one database is down, use the backup one)

If not, what would be the cheapest and most efficient solution to this problem?

Thanks in advance for any clues!

As far as which server Bucardo should run on, if both of your servers have the same resources, I would just pick one, because in multi-master, with two servers, the bucardo daemon won't have anything to do in a situation where the other server is down anyway. When the other server comes back up, you should just be able to restart the bucardo daemon and have it sync from the server that didn't go down.

https://mail.endcrypt.com/pipermail/bucardo-general/2013-June/001882.html

If you need something with HA, then, I would ask specifically on the bucardo-general mailing list, which is a wealth of knowledge.

You also might find these resources useful for dealing with Bucardo, in addition to the mailing list and the documentation.

Bootstrap Bucardo Multi-Master

Bucardo Multi-Master for PostgreSQL

Conflict Handling with Bucardo


alter authorization in SQL Server I will to made a database diagram in my SQL Server Management Studio. But I have this warning that said:

  Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Can you help me whit this error? How can I give myself (hein_) the promision to continue?

@JeffersonBElias suggested me to execute this code:

select suser_sname(owner_sid) as usersname from sys.databases where name = 'FreeTimeDBSSA'

What me gives this:

+-----+-----------------------+
|     |       usersname       |
+-----+-----------------------+
|  1  | DESKTOP-3BMCMPC\hein_ |
+-----+-----------------------+

Here you have my Object Explorer:

I have found it whit a hint from @JeffersonB.Elias. I've execute this code:

ALTER AUTHORIZATION ON DATABASE::FreeTimeDBSSA TO sa;
GO


Select ONE most recent post for each author I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.

I use recent versions of PostgreSQL and MySQL.
I have 2 tables:

CREATE TABLE authors (
    id INT,
    name VARCHAR
)

CREATE TABLE posts (
    id INT,
    author_id INT,
    text VARCHAR,
    date DATE
)

I need to select one most recent post for each author. Thanks!

UPDATE

Thanks, both links provide answers to my question with some exception.
All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?

SELECT p1.*
FROM posts p1
LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date < p2.date
WHERE p2.author_id IS NULL
ORDER BY p1.author_id;

SELECT p1.*
FROM posts p1
INNER JOIN (
  SELECT author_id, MAX(date) AS max_date
  FROM posts
  GROUP BY author_id) p2
  ON p1.author_id = p2.author_id AND p1.date = p2.max_date
ORDER BY p1.author_id;

SELECT *
FROM posts p1
WHERE date = (SELECT MAX(p2.date)
              FROM posts p2
              WHERE p1.author_id = p2.author_id)
ORDER BY author_id;

SELECT * FROM (
    SELECT author_id, MAX(date) date
    FROM posts GROUP BY author_id
) p1 INNER JOIN posts p2 USING (author_id, date)
ORDER BY author_id;

If you aim is to have queries with maximum efficiency, none of the above queries is really the best. Not always at least.

Efficiency depends on many different things, like the specific DBMS, the specific version (different versions have different improvements on the optimizer and the available syntax), the type of columns, the indexes available, the size of the tables and distribution of values, the hardware the server is running, the configuration settings etc.

You should always test various different ways of writing the queries, on your tables, with the sizes and distribution you expect to have on production, with your hardware and configuration settings, to decide which rewritings of the queries should be kept.

This specific kind of query is often called greatest-n-per-group (there is even a tag for it!) and under certain assumptions, one of the many ways to write them, is often quite efficient in both MySQL and PostgreSQL. It uses a LATERAL join in Postgres, which is available in 9.3+ versions (in SQL Server lingo CROSS/OUTER APPLY) and a simulation of this join in MySQL.

The assumptions are that the number of authors (the attribute we group by on) is small, compared to the number of posts (the table where we apply the group by). It's also best if there is an index or a table to find all the distinct author_id values and an additional index on the posts table for the group by.

This solution to the greatest-n-per-group problem matches also your request about ties, as it returns always one result per group. If you want to be precise about which one (of the tied) will be returned, the ORDER BY in the subquery can be modified (to ORDER BY pi.date DESC, pi.id DESC or ORDER BY pi.date DESC, a.name for example).

Query in PostgreSQL:

SELECT p.*
FROM authors AS a
   , LATERAL
       ( SELECT pi.*
         FROM posts AS pi
         WHERE pi.author_id = a.author_id
         ORDER BY pi.date DESC
         LIMIT 1
       ) AS p ;

Query in MySQL:

SELECT p.*
FROM authors AS a
  JOIN posts AS p
    ON p.id =
       ( SELECT pi.id
         FROM posts AS pi
         WHERE pi.author_id = a.author_id
         ORDER BY pi.date DESC
         LIMIT 1
       ) ;

The useful index is on posts (author_id, date, id) for MySQL and or on posts (author_id, date DESC) for Postgres.

Needless to say again but before using any of the above, they should be tested in your environment and cross tested against all the many other versions/rewritings of the query. In Postgres for example, the DISTINCT ON syntax can be used in version older than 9.3. The resulting query is more compact than the LATERAL and might be more efficient, under different data distributions. Query:

SELECT DISTINCT ON (author_id) p.*
FROM posts AS p
ORDER BY p.author_id,
         p.date DESC ;


Group Policy - replication snapshot Has anyone seen a domain Group Policy stop a transactional replication snapshot from being configured and executed "A required privilege is not held by the client".

To cut a long story short... I am having trouble getting to much from our server admin guys.  All they would do was create me a new server, added it to an OU with a filter to block the Group Policy and I got it to work (using domain user for agent) but now I can't get anything out of them about the Group Policies being delivered.

So my question is, does anyone know the policy setting that could be blocking the making of a snapshot.

Any help greatly appreciated.
David

I cannot say for sure, never having encountered this exact problem before, but I'd recommend looking into the local security policies. In the standard GPO tree, you can find those under:
"Computer Configuration" -> "Policies" -> "Windows Settings" -> "Security Settings" -> "Local Policies".
Under that, I'd look particularly closely at the "User Rights Assignment" section.

If your server admins won't let you see what GPOs are being applied, you can get a list yourself if you've got administrative access on the server you're having problems with.
Assuming you're running Windows Server 2008 or newer on it, try:

gpresult /H {Your path here, for example C:\gporeport.html}

This will output an HTML report to the file you specified, which details what GPOs are applying, what specific settings are configured and which GPO is controlling which setting.
This might not be a direct answer to your question, but a good starting point to troubleshoot from.
(Specifically, if anything at all is set in the local security policy, try asking your server admins to filter out the specific GPOs that are applying those settings. Narrow it down, rinse and repeat.)


How to store long string with netstring format in varchar column? I am involved in project where we have to store very long string in varchar column (varchar(max)). Due to this case have to questions:

This column is one of other 40 columns in table (but only this has to be varchar(max)). To this table we will insert approximately 300 records per second. What is more this table will be partitioned for 32 partitions. What will be a good practise ?:

Leave this column in this table
Create new table with this column and ID (FK (references to main table)) ?
Any other approach ?

Data in this column will be stored in netstring format. Moreover we will have to retrieve some specific field from this long string from this column. What is the best and most efficient way to do that ? Is it s good idea to for example to parse this netstring to json format ?

Thank you in advance for your answers :)

Don't worry about where you put the column. In the background the sql server will store it in a special place (allocation unit for lob_data). See here
So keep it simple and leave the column in the table.
Now if you have a possibility to store it as json instead of netstring try to do that as the sql server has native functionality to deal with json since version 2016


difference between having many non clustered index with single columns and with combination of many columns Folks

I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed

many non clustered index with single columns and non clustered index with many columns combined.
Or ideally under what scenario I can combine columns to make a single non clustered index and if so, how many columns should I combine ?
What is the criteria to choose the column for non clustered index ? (I am aware that its applied only to not frequent updation table)....
Whats the difference between 2005/2008 and 2012 SQL Server in terms of non clustered index with regard to my question and also is it 249 is the max we can create ?

Thank you in advance.

Having many indices requires SQL Server to maintain and update many indices for each insert, delete or update statement. So having less indices is generally better.

However, when you have a compound index (made up of multiple columns), that only helps if you use/specify the n left-most columns in your query.

So if you have an index on (City, LastName, FirstName) for an address table, that index might be used if

you have all three columns in your WHERE clause
you specify WHERE City = 'London' in your query (using the 1 left-most column)
you specify WHERE City = 'London' and LastName = 'Smith' in your query (using the 2 left-most columns)

However, such an index cannot ever be used for:

finding all rows with a WHERE FirstName = 'Joe' clause - you're not using the n left-most columns in that index
finding all rows with a WHERE LastName = 'Brown' AND FirstName = 'Charlie' clause - again: you're not using the n left-most columns in that index

A compound index cannot (in many cases) replace all one-column indices. Be very careful when designing and creating compound indices to make sure you design them in a way they'll really be useful!

So while compound indices do have their reason to be, and they're generally preferable - you also need to make sure they'll really be able to be used. An index that is just being maintained (and causes effort on SQL Server's side), but can never be used in any queries, is the worst index ever - no gain, only maintenance overhead.

You should also read Kimberly The Queen of Indexing Tripp's excellent blog posts on SQL Server indexing - most notably Indexes: just because you can, doesn't mean you should!


Send email only if no backups within 24 hours I'm trying to combine the script from Tim Ford (http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/)

-------------------------------------------------------------------------------------------
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours
-------------------------------------------------------------------------------------------
--Databases with data backup over 24 hours old
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM    msdb.dbo.backupset
WHERE     msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

UNION

--Databases without any backup history
SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   master.dbo.sysdatabases.NAME AS database_name,
   NULL AS [Last Data Backup Date],
   9999 AS [Backup Age (Hours)]
FROM
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
ORDER BY
   msdb.dbo.backupset.database_name

with the sp_send_dbmail proc.  Right now it is sending emails even when 0 rows are returned (i.e. all databases have been backed up within last 24 hrs).

What I would like to have happen is an email sent out ONLY when the recordCount is > 0.  i.e. there is a database that has not been backed up within 24hrs.

Current script which sends an email even when there are 0 results returned.

EXEC msdb.dbo.sp_send_dbmail
    --@profile_name = 'SQLDBEmail',
    @recipients = 'dba@someemailaddress.com',
    @query = 'SELECT
               CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
               msdb.dbo.backupset.database_name,
               MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,
               DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
            FROM    msdb.dbo.backupset
            WHERE     msdb.dbo.backupset.type = ''D''
            GROUP BY msdb.dbo.backupset.database_name
            HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

            UNION

            --Databases without any backup history
            SELECT
               CONVERT(CHAR(100), SERVERPROPERTY(''Servername'')) AS Server,
               master.dbo.sysdatabases.NAME AS database_name,
               NULL AS [Last Data Backup Date],
               9999 AS [Backup Age (Hours)]
            FROM
               master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
                   ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name
            WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> ''tempdb''
            ORDER BY
               msdb.dbo.backupset.database_name',
    @subject = 'Databases not backed up within 24 hrs',
    @attach_query_result_as_file = 1 ;

Instead of e-mailing the results of the query as a file attachment (which, SQL Server won't know it's empty until after it runs the query as a part of sending the e-mail), I suggest instead building up the body of the e-mail as follows, and then using a simple IF condition to only send the e-mail if at least one database has contributed to populating the string:

DECLARE
  @dblist NVARCHAR(MAX) = N'',
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT
   bs.database_name AS db,
   MAX(bs.backup_finish_date) AS d,
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs
  WHERE [type] = 'D'
  GROUP BY database_name
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,
    NULL AS d,
    9999 AS age
  FROM
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS
  (
    SELECT 1 FROM msdb.dbo.backupset
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  --EXEC msdb.dbo.sp_send_dbmail
  --  @recipients = 'dba@someemailaddress.com',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END


SQL Server 2016 Express: System databases As per recommended practice, on a new server that I have created with SQL Server 2016 Express SP1, I am trying to move the system databases OFF the C: drive onto E: and F: drives (the MDF's onto E: and the LDF's onto F:). Is this possible with the EXPRESS version of SQL Server?

You should be able to move the system database for Express just like any other edition.  Move System Databases contains very straightforward instructions on the steps required to move the system databases.

For future reference, you can specify the storage locations of the system database at install time and save yourself some time.


How to organize a single query to several sql servers? I need to make an inner join sql query to several SQL Servers (one is 2000 and another is 2008 R2). The query is like the following:

SELECT
   k.ID, k.NAME, k.BIRTH, l.PROFILEID
FROM
   CUSTOMERS..REGISTRY k
INNER JOIN
   SECURITY..USERS l ON K.ID = l.ID

The CUSTOMERS..REGISTRY is on the 2008 R2 and SECURITY..USERS is on the 2000 servers. The servers are situated in different networks, connected through internet. Is there any way to organize such query?

The servers are situated in different networks, connected through
  internet.

Preferably, don't do that.

VPN and (as AceCTO suggested) linked servers if you must but if this is a query you want to run regularly, move the data. BCP it, replicate it, log ship it... just move it.


SQL:StmtStarting event originated from application What does an SQL:StmtStarting event originated from application mean? Does it mean a raw SQL statement executed directly from the application?

For example, in the case of an SP:StmtStarting, it means that the current statement is inside an stored procedure, and thus not directly executed by the application, but by the stored procedure.

Yes, this is an ad hoc SQL statement that is not part of a module. It could be sent on its own or it could be part of a batch.

Though I'm not sure why you're using Profiler; it is notoriously horrendous for performance. Just take a look at what Joe Sack observed regarding the impact Profiler has on throughput and similar observations by Jonathan Kehayias.

Or what value you gain from collecting the Starting events. The Completed events tell you much more about what happened (including things like duration).


I may be wrong, but I believe that MBRWithin(GeomFromText(geo),@zone), should be MBRWithin(geo, @zone) -no need to convert anything, it is stored already in spatial format.

When functions like these get bad-type operands, they return null.


Does MS Access have anything equivalent to triggers? I'm developing some code that will eventually talk to an Oracle 10g database. While doing this, I am also redesigning the relevant database tables. I'm not a database pro, and I don't have sufficient privileges on our Oracle server (even in the development database) to create and alter tables at will. Rather than constantly harass our DBA every time I discover my design is broken, I've just created an MS Access database that I can alter as I please. I'll use the access database while I develop my code, and then we will install the updated code and create the new tables at the same time.

One of the fields in my tables is an ID number that needs to be generated when a row is inserted to the table. In Oracle I'd make a trigger for this, but I have not been able to find this functionality in Access. Is it possible to achieve the same effect in any way?

If the ID is simply a number look intot the autonumber feature in Access.


Postgresql on AWS RDS JDBC Connection Refused, psql connection succeeeds I have an Amazon RDS Postgres instance running. I can see the data from my local machine (whitelist office IP) and our web app can connect (running on EC2 instance).

On that EC2 instance, these things happen:

[ec2-user@ip ~]$ nc -zv mydb.us-east-1.rds.amazonaws.com 5432
Connection to mydb.us-east-1.rds.amazonaws.com 5432 port [tcp/postgres] succeeded!

[ec2-user@ip ~]$ psql postgres -h mydb.us-east-1.rds.amazonaws.com -U userguy
Password for user userguy:
psql (9.5.4, server 9.6.1)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \q

[ec2-user@ip myjars]$ java -jar MyJar.jar param1
Connecting to jdbc:postgresql://mydb.us-east-1.rds.amazonaws.com:5432/postgres
org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

I know this EC2 instance can connect to the DB. The nc command works and the psql command works. We have a Glassfish application server that connects as well.

Same parameters are in this JAR file and it does not work! Any thoughts? Any ideas to diagnose? (as I type this, I feel it will be some file version or mistyped value in the JAR file, since everything else is working)

Ultimately the error was in the JAR. A connection was made the DB on two different occasions, and each one from a different config location. I changed one, but not the other - the joy of inheriting code : )

So now my DB connections works. Hopefully the original question has some useful ways to know if a connection can connect.


I have run truncate table but it is still showing table occupying space? I have run truncate table but the Heidi SQL is still showing table occupying space.
Database: MYSQL 5.6.26

I have truncated the con_consumer table but heidi is still showing it taking space. Even other tools like Toad shows it taking up space.
Mysql Engine:Inno DB

Tables with Engine=InnoDB take a minimum of one block -- 16KB.

mysql> CREATE TABLE minsz (x int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.17 sec)

mysql> SHOW TABLE STATUS LIKE 'minsz'\G
*************************** 1. row ***************************
           Name: minsz
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384   <-- Brand new table, still empty, yet 16KB
Max_data_length: 0
   Index_length: 0
...

If I had added an secondary index (not PRIMARY KEY), Index_length would also be one block.  (Heidi would probably show 32KiB.)

I might add a hundred rows before the table and/or its index expands beyond 16KB.


SQL Server Agent & sp_send_dbmail failing on permissions I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the Database Role 'public' has select permission. The job runs fine, the email arrives, all good.
The problem is, if I turn off the worryingly generous 'public' select permission and try to add the SQLServerAgent user (which is the user the agent is running under and the user that owns the agent job) and give them select permissions, the job fails with this error message:

Failed to initialize sqlcmd library with error number -2147467259.
[SQLSTATE 42000] (error 22050)

Can anyone shed any light on this for me? I suspect that maybe a different user is involved in some way. I cannot get SQL Server Profiler to run (it's not installed) and I don't have access to the server to install it.

It seems that the SQL server agent User has some problem to run sql cmd.
It uses sql cmd to run the query to attach in the email.
Please try to to give the agent user the database mail user role.
exec sp_addrolemember 'DatabaseMailUserRole', 'domain\agentuser'


Chance of duplicate on single select max into statement Against my will, I am told to use the following method to generate a new pk value.

INSERT
    INTO
        SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, SERVER_IP, SERVER_NAME, OBSTACLE_YN, CHECK_TIME)
    VALUES((SELECT MAX(SM_SEQ)+1 FROM SERVER_MONITERING), #cpuStat.combinedUsed#, #memStat.total#, #memStat.used#, #diskStat.size#, #diskStat.used#, #serverIP#, 'KW', 'N', TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI'))

I told him it still looks like there could be duplicate SM_SEQ values.

However, I don't know for sure whether this approach will eliminate duplication or not.

Will the approach above eliminate the chance of duplicate values ?
Is this a good practice ?
Will a sequence.nextval eliminate the chance of duplicate values ?
What is the best practice ?

Edit

Sorry, I forgot to mention.
There are multiple devs and their own code that inserts into this table and they all use select max()+1. I am a "guest" dev at this company. So I have little influence on their decision. If I switch to using a sequence only by myself, other devs will still select max()+1 and there will be collision between the two approaches.
Probably, I would have to tell all of them why even nesting a select max()+1 won't solve their problem, if it truly would not.

Assuming that you allow multiple users in your application, max()+1 will guarantee that you will get duplicate values.  Therefore, it is a bad practice.  It's also less efficient but that is a lesser concern.  If you have two sessions, session 1 gets the max() + 1, and session 2 tries to get the max() + 1 before session 1 commits (or rolls back), session 2 will get the same value that was given to session 1.

Using a sequence will eliminate the possibility of duplicates.  Use a sequence.


SSAS - what could I monitor in SSAS to fine tune its memory settings? This question is a follow up from the
SSAS - what could be a good starting point for memory settings?

What and how can I monitor the server resources in order to best identify the settings that I should apply to my SSAS instance?

In this case we're talking about sql server 2014, and the server is a windows server 2012, with the spect below

When trying to fine-tune your memory settings there are two things you'd want to watch:

MSOLAP Memory counters for your server in Performance monitor

Specific DMV queries over discover_object_memory_usage rowset and discover_object_activity rowset.

You can always use a third-party tool to monitor your resources. Also, you might want to check out this article – it gives a detailed description on how to achieve exactly what you are looking for.


Try this:

SELECT
  DAY(time_stamp) AS Day,
  COUNT(time_stamp) as Tot,
  SUM(CASE WHEN origin = 's' THEN 1 ELSE 0 END) as Syd,
  SUM(CASE WHEN origin = 'n' THEN 1 ELSE 0 END) as Norr
FROM (select time_stamp, 'n' as origin from ink_norr union all select time_stamp, 's' as origin from ink_syd) x
GROUP BY DAY(time_stamp)
ORDER BY 1;


How does FILLFACTOR reduce page splits? The default FILLFACTOR is 100 and I have been told at times to reduce it to 80 or lower so that page splits will be reduced due to the empty space on each page.

I don't understand this--if a new index is created on a table with a default FILLFACTOR of 80, 20% space of each page is left empty.  So assume a row needs to use 10% of a page, so when it's full it can hold 10 records.  With a FILLFACTOR of 80, now it can only hold 8 records.  So when you add the 9th record, will there be a  page split?  With 100% FILLFACTOR, a page split will only happen on the 11th record, so it seems that a lower fill factor makes it worse.

The fill factor does not apply to new rows being added to a page.  The fill factor only applies to how full a page is filled when the index is created, rebuilt, or reorganized.

Accordingly, if the index is rebuilt or reorganized on a scheduled basis, your index will be able to accommodate new rows with fewer page splits if the fill factor is set appropriately.


Slow SSD on Dell 710 We recently upgraded our Dell T110 server to a Dell T710 with a 100GB SSD drive.  We were hoping that we would see a tremendous improvement in mysql select performance, but unfortunately the new system is slower than the old.

I have tried tweaking the fstab and the mysql onfiguration, but nothing has seemed to help.  It seems that for some reason the system is unable to use the full speed of the drive or there is something else slowing it down.

According to hdparm the new SSD drive has a similar read speed and surprisingly a slower cached read speed.

When doing a random access data read test using seeker the SSD drive is much faster.

Old System:
Dell T110, Single 2.4GHz Quad core processor
8GB of RAM
250 GB 7.2K SATA Drive
Mysql 5.1
ext2 filesystem

Timing buffered disk reads:  100 MB in  0.87 seconds = 114.99 MB/sec
Timing cached reads:   22636 MB in  1.99 seconds = 11361.70 MB/sec
Results: 81 seeks/second, 12.28 ms random access time

New System
Dell T710, Dual 2.13GHz Quad core processor
16 GB of RAM
100 GB SSD Drive
Percona 5.5
ext4 filesystem

Timing buffered disk reads:  366 MB in  3.01 seconds = 121.47 MB/sec
Timing cached reads:   10960 MB in  2.00 seconds = 5487.23 MB/sec
Results: 3742 seeks/second, 0.27 ms random access time

The new system has the following /etc/fstab entry:
/dev/mapper/vg_katahdin-lv_root /                       ext4    defaults,noatime,discard,data=ordered,errors=remount-ro        1 1

Before tweaking the disk you should tweak memory usage for the DB - especially with mySQL.

From what I read I suspect that your DB is doing heavy write IO - which is faster on an ext2 and on a "real" disk.

Update 2011-11-23 (after migration to dba):

Perhaps you should analyze your DB with the free TOAD version.


How to sync two databases (alternative to mysql replication)? Hello i have 2 mysql databases where most of the structure is constant except few tables (additions) in 2 dbs. I need to synchronize these 2 databases mainly common tables from one db to another. This is only 1 way.

I cannot use replication here because my databases are not same. I need to take the changes from db1 and then sync into db2 and this needs to happen in real time. Are there any tools or scripts available on linux which does this or do we need to write custom scripts? If custom scripts, any suggestions would be helpful.

mysqldbcompare, part of mysql utils, can check differences in structure and data among two servers. However, if you are making updates to one of the servers at the same time, this may not be enough.

pt-table-checksum and pt-table-sync, from Percona Toolkit, can respectively check and synchronise differences in data between a master and a slave in real time. It doesn't matter if both servers have different data, but it requires having the same tables with the same definition.

So probably you can use a combination of both. Both MySQL utils and Percona Toolkit are open source, freely downloadable and well established utilities among MySQL DBAs.


How to remote access Oracle 12c using SQL Developer? The listener has started correctly and its status shows OK!

I enter the correct IP address of the server in the listener.ora file, not localhost.

But when I try to connect to my database by entering the following in SQL Developer:

user: system
password: *******

host: 54.215.5.46
port: 1521
SID: orcl

I get this error:

  Statut : échec -Echec du test : Erreur d'E/S: The Network Adapter could not establish the connection

I think that I need to open the 1521 port but I don't know if this is the problem?

You need to check the following in-order to connect to remote database:

Make sure server is accessible from client side, easiest way to do it by using ping command to verify.
There is a connection between host and server using a specific port, you can use telnet command to verify.
Enable the instance listener to be listening on an IP address not localhost.
Database is running


Access - SQL Server: cannot generate SSPI context I actually understand why this error messages pops up, but I do not know how to implement my solution.

There is a SQL Server network instance I am providing some MS Access forms to interact with, and I myself have no connectivity issues. This is because both in the server and in the database I am actually registered with the same username and password as in my own pc, so the kerberos authentication works brilliantly... until I share the Access file with someone else, then they get this error (because their local users are not registered in the server &/or database)

The thing is, I don't want the process to use client-local user and password to authenticate the connections (or the kerberos service all together), but rather database-local user and passowrd. I originally linked the remote tables to Access using ODBC calls, providing database-local user and password (different from my credentials) and I would like it to be kept that way (or similar enough) so that someone else can open the forms and interact with them using database-local credentials.

I just don't know how to get around this. Help please :'(

Last-minute updated before posting: after this error pops up the user is prompted with the connection dialog where they can edit the username and password provided to make the connection. So the problem is not actually a blocking wall, but rather an uncomfortable detail.

The question remains though: is there a way to make this the default behavior?

Why not have both?

That login screen you're seeing is SQL server rejecting the login parameters.

Create a OU and label it something like SQLAccess. Add all the appropriate users to the OU.
From there goto SSMS, add the OU to the appropriate logins.
Set your permissions
Create a DSN(Or use current). When it says use Network ID for authentication, make sure it's checked/bubbled. It uses the current user's credentials.

With the above steps done, when your enduser opens the access file, SQL will shake behind the scenes and thus allow your custom login screen to pop... Only showing 1 login screen. Also should a user ever leave the company, just remove them from the OU and you're golden(or deactivate the account)

In theory you could develop a DSN for every specific users and specify the login with a SQL login but that is tedious, unless of course you do a universal login. I don't recommend it for obvious reasons.


Delete in big table data all data, where foreign_id in other table didn't exist Let's say i have two tables:

table_a:

    id |  article_id  |  text

article:

    id  |  text

table_a for example contain data:

1 1 ""
2 1 ""
3 1 ""
4 2 ""
5 3 ""

and table articles such data:

1 ""
3 ""

as result i must in table_a left only such data:

1 1 ""
2 1 ""
3 1 ""
5 3 ""

but if i write such sql code (pseudo):

 delete from table_a where article_id not in (article.id)

but main trouble is that table_a contain 40 billion entries, and articles 2 billion, and if for each table_a entry i will look up which id's are in articles and collect and then check them - it will be very long...
does mysql contain any variables, which could once be filled, and then used for each table_a entry?

how could i do this task more quick, than i imagine?

This should do it:

delete from table_a where article_id not in (select id from article)

A variant that maybe faster in older versions

delete a.* from table_a a
left join article on a.article_id = article.id
where article.id is null

Oh and regarding the second part of your question...  You may want you put another column on table_a,  like checked that you can set.  And of course you need to put an index on article.id.

It occurs to me that if you are going to be deleting a good fraction of the records then it will be faster to simply make a new table which is a subset of the original.

create table table_b as
select * from table_a where article_id in (select id from article)

Mmm... actually given the problem with old version IN optimization this is probably the best query to go with

create table table_b as
select  a.id, a.article_id, a.text
from table_a a
inner join article on a.article_id = article.id

And just make sure you have an index on article.id (an index on table_a.article_id won't hurt either)

(just an addendum regarding the changing status of IN clause optimisation - I cannot find recent specific benchmarks on googleland but relevant discussion is here for Mysql and Maria


Select MAX DATE from joined Table I have two tables, PROJECT and BILLINGS, as follows:

Project_ID  Project_Client
----------  ------------------
P1          ABC Company
P2          DEF Company
P3          GHI Company

Proj_ID     Bill_Date   Bill_Amount
----------  ----------- -----------
P1          01-01-2013  123.45
P1          11-01-2013  234.56
P2          08-15-2013  345.67
P2          09-15-2013  456.78
P3          07-31-2013  567.89

I want to return the list of Projects and Project Clients only for projects that have billings AFTER 09-01-2013. How do I do the proper JOIN to do this?

If you want to get just project id and clients (which are both on project table) there is no need to join Billings :

SELECT p.Project_ID, p.Project_Client
FROM  PROJECT p
    WHERE EXISTS
      (
         SELECT NULL FROM BILLINGS b WHERE b.Project_ID = a.Project_ID
         AND b.Bill_Date > 'your_date'
      )


How to return JSON objects in tabular format using T-SQL? I am storing student data by group (or class) and week in the following format:

where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can change from week to week, so I am trying to come up with a way to display the results by group as follows:

where I can see the grades by week side by side.

The solution I have come up so far is not very elegant:

DECLARE @json VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 1)
drop table #week1
drop table #week2
SELECT *
into #Week1
FROM    OPENJSON(@json)
WITH (
        StudentId INT '$.PersonId',
        StudentName VARCHAR(100) '$.StudentName',
        Week1 FLOAT '$.GradePoints'
)

DECLARE @json2 VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 2)
SELECT *
into #Week2
FROM    OPENJSON(@json2)
WITH (
        StudentId INT '$.PersonId',
        StudentName VARCHAR(100) '$.StudentName',
        Week2 FLOAT '$.GradePoints'
)

select w1.studentid, w1.studentname, w1.week1, w2.week2
from #week1 w1
FULL join #week2 w2
on w1.studentid = w2.studentid

Besides not being elegant, this approach is not scalable for a 14-week course.

If anyone can think of a better solution (even if it requires a change in how the data is stored), I am open to suggestions.

Thanks in advance.

You don't need to split the data out into separate tables and re-join for this. Simply use OPENJSON to decompose the JSON into a table and use PIVOT to shift the grade point data into columns.

The below example code can be seen in action here.

Setup example data:

CREATE TABLE DataTable
(
  Id INT,
  GroupId INT,
  Week VARCHAR(25),
  GroupData VARCHAR(MAX)
)

INSERT INTO DataTable
VALUES (1, 3840, 'Week 1', '[ { "StudentName": "Barry", "GradePoints": 100 }, { "StudentName": "Kelly", "GradePoints": 98 }, { "StudentName": "Jenny", "GradePoints": 100 } ]'),
  (2, 3840, 'Week 2', '[ { "StudentName": "Barry", "GradePoints": 95 }, { "StudentName": "Kelly", "GradePoints": 87 }, { "StudentName": "Jenny", "GradePoints": 99 } ]')

Query the data:

SELECT
 GroupId, StudentName, p.[Week 1], p.[Week 2]
FROM
(
 SELECT GroupId, Week, StudentName, GradePoints
 FROM DataTable
 CROSS APPLY OPENJSON ([GroupData])
 WITH (
  StudentName VARCHAR(25) '$.StudentName',
  GradePoints VARCHAR(25) '$.GradePoints'
 )
) src
PIVOT
(
  MAX(GradePoints) FOR [Week] IN ([Week 1], [Week 2])
) p

Results:

GroupId     StudentName     Week 1  Week 2
------------------------------------------
3840        Barry           100     95
3840        Jenny           100     99
3840        Kelly           98      87


Reset Root Password MySQL on Ubuntu I know there are tons of posts about this on here but nothing seems to work.  I have tried every command I could find it will not work.
I just recently setup mysql on Ubuntu (SQL version is 8.0.23-0ubuntu0.20.04.1).  Afterwards i installed myphpadmin and I can get to it, but I noticed that it said that root doesn't have a password, even though I set one in the mysql_secure_installation.  I don't really want to go any farther until i get a password on root, as eventually i'm going to have this server exposed to the outside, but every single command I have tried seems to do nothing.  I am testing if it is working by entering "sudo mysql -u root" with no password and i'm assuming if there is a root password set then it will prompt for one instead of just going straight into the mysql> prompt.
These are the commands that i've tried:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('newpassword')' at line 1

mysql> SET PASSWORD = PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('newpassword')' at line 1

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
RESULT: Query OK, 0 rows affect (0.01 sec)

Seeing this made me think its working, so i ran FLUSH PRIVILEGES; and refreshed myphpadmin but it still shows that it doesn't have password, so i kept going.
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('newpassword')' at line 1

shell> sudo mysqladmin -u root password
New password: newpassword
Confirm new password: newpassword
RESULT: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

This one seems like the right path maybe?  But it says i need SSL...which is weird because i'm on the computer in a terminal, i don't know how to do an ssl connection to mysqladmin on the same computer using the terminal.
shell> sudo mysql_secure_installation
RESULT: Success.  All done!

This acts like it works, but it does nothing
Change from myphpadmin:  I tried changing it from myphpadmin as well, it goes through with no issues, but the password still seems to be the same.
mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') WHERE User='root';
RESULT: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('newpassword')' at line 1

There was also mention of getting the first root login from /var/log/mysqld.log, but that doesn't exist on my computer
There are probably a few other odd things here and there i've tried but nothing has worked, does anyone have any insight into this?

I managed to figure it out.  I started digging more into the user table and found the root user entry, and then found the authentication_string column.  I had already followed the guides that someone commented and it didn't work, not sure why, so I tried the following.
mysql> USE mysql
mysql> UPDATE user
    -> SET authentication_string = 'newpassword'
    -> WHERE User = 'root';

RESULT: Query OK, 1 row affected (0.01 sec)
Rows matched:1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;

I then checked on phpmyadmin and it shows that it has a password on it now.  I think it should be good now...unless there is another check i need to run to be sure?


How to remove linked servers provider? I installed an Oracle OraOLEDB.Oracle provider. Now I want to remove that provider from the list. How can I do that?

To remove the linked server provider, uninstall the OleDb Provider.  For Oracle XCopy use the uninstall.bat in the installation folder, eg
PS C:\odacinstall> .\uninstall.bat oledb c:\oracle\odac64


How to get an actual execution plan from the linked server? There is a "main" SQL Server to which I have full access and I can connect to it via SSMS. Its version is:
Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64)
    Jun 15 2019 07:56:34
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

There is a linked server defined on the main server. The linked server is:
Microsoft SQL Azure (RTM) - 12.0.2000.8
    Feb 20 2021 17:51:58
    Copyright (C) 2019 Microsoft Corporation

I can't connect directly to that remote server via SSMS, but I can run all sorts of queries against it using OpenQuery. For example, I got its version by running this:
select * from OpenQuery([LinkedServerName], 'select @@version');

There are a number of complex legacy queries that are run on the remote linked server via OpenQuery.
I wanted to analyze them and see if there are any obvious/easy ways to improve their performance. To do that I wanted to get their execution plans. If I try to get an execution plan on a local server all I get is one Remote Scan operator that takes 35 minutes without any details.
I know that there is a SET STATISTICS XML ON statement that returns an execution plan of a query.
Unfortunately, when I tried to put it into the OpenQuery, it didn't work.
Let me explain.
I can run the actual query:
select * from OpenQuery([LinkedServerName],
'
--SET STATISTICS XML ON;
select TOP(10) * from bms.digitalbookinglinezone;
--SET STATISTICS XML OFF;
');

This returns me 10 rows as expected. When I uncomment the SET STATISTICS XML lines I get the following error message:

Msg 11527, Level 16, State 1, Procedure
sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0] The
metadata could not be determined because statement 'SET STATISTICS XML
ON;' does not support metadata discovery.

At the same time I can run the following query just fine:
select * from OpenQuery([LinkedServerName],
'
SELECT * FROM sys.dm_exec_describe_first_result_set
(N''select TOP(10) * from bms.digitalbookinglinezone'', null, 1);
')

And I'm getting valuable information about all columns in the remote table.
Is there any other "T-SQL" way of getting the execution plans?
Something that I could use via the OpenQuery?

I don't believe it's possible to get the execution plan via OPENQUERY() remotely, and the only way would be to connect to the server directly and run the query there, unfortunately.
That being said, you should definitely see an instant performance improvement for all of your linked server queries, if you migrated the remote server data to the local server (e.g. via one of the data synchronization features such as Replication or AlwaysOn Availability Groups), assuming your local server can handle the additional query load. Just an idea.


count total matching id if a single column contain multiple value separated by comma I have problem in my school management software.

I have to count how many classes a teacher has in all week, for this the admin may assign more than one teacher to a class and that selected teacher is inserted in database by their ids. If there are multiple teachers they will be inserted in single column separated by commas.

I have to count the total number of classes for a teacher.

How could I create such a query

mysql_num_rows(mysql_query("select * from table_teacher where employee_id in (1))

The root cause of your problem is that database is not normalized. Assuming your table looks like:

CREATE TABLE COURSES
( APPLICATION_CODE ... NOT NULL PRIMARY KEY
, ...
, TEACHERS VARCHAR(100) NOT NULL );

INSERT INTO COURSES (APPLICATION_CODE, TEACHERS)
VALUES ('MA101', '1,2,5');

The correct thing to do is to normalize this into two tables:

CREATE TABLE COURSES
( APPLICATION_CODE ... NOT NULL PRIMARY KEY
, ... );

CREATE TABLE COURSE_TEACHERS
( APPLICATION_CODE ... NOT NULL
, TEACHER_ID INT NOT NULL
,     PRIMARY KEY (APPLICATION_CODE, TEACHER_ID) );

INSERT INTO COURSES (APPLICATION_CODE)
VALUES ('MA101');

INSERT INTO COURSE_TEACHERS (APPLICATION_CODE, TEACHER_ID)
VALUES ('MA101', 1), ('MA101', 2), ('MA101', 5);

In this case, it is sufficient to count the rows where TEACHER_ID = 1

SELECT COUNT(1)
FROM COURSE_TEACHERS
WHERE TEACHER_ID = 1;

Not only will it be easier to ask questions like this, the queries will be much more efficient since they can utilize indexes in a way that is not possible in your current model.

That said, if you know that the teacher string always looks like for example:

' 1, 2, 5,'

You can use LIKE to count the rows:

SELECT COUNT(1)
FROM COURSES WHERE TEACHERS LIKE '% 2,%';

Pitfalls, if the last item is not terminated with the separator token (in this case ",") you need to handle that in a special predicate. The same goes for the start item, it needs to start the same way as the remaining ones.

If you can not trust the string to be in this simple form, you may be able to come up with a regular expression that describes a teacher item in the list of teachers.


Is polling the only way for updating app's data from a database? An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the database?

I work with a MS SQL Server 2008 (and .NET applications + Entity Framework), but I'd like to get knowing about other types of databases as well.

In Oracle you can use the built in DBMS_ALERT package to facilitate this.

  DBMS_ALERT supports asynchronous
  notification of database events
  (alerts). By appropriate use of this
  package and database triggers, an
  application can notify itself whenever
  values of interest in the database are
  changed.

  Suppose a graphics tool is
  displaying a graph of some data from a
  database table. The graphics tool can,
  after reading and graphing the data,
  wait on a database alert (WAITONE)
  covering the data just read. The tool
  automatically wakes up when the data
  is changed by any other user. All that
  is required is that a trigger be
  placed on the database table, which
  performs a signal (SIGNAL) whenever
  the trigger is fired.


Which columns to index with LEFT JOIN and GROUP_CONCAT So i have a complicated MySQL search query, and I wonder which columns I need to index to speed up searching:

        SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, supplier_company,
        GROUP_CONCAT(wp_terms.slug SEPARATOR ', ') AS allslug,
        GROUP_CONCAT(wp_terms.name SEPARATOR ', ') AS allcatname
        FROM wp_posts AS wpp
        LEFT JOIN wp_term_relationships ON wpp.ID = object_id
        LEFT JOIN wp_terms ON term_taxonomy_id = wp_terms.term_id
        LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

        WHERE post_type = 'post'
        GROUP BY wpp.ID

1) How to speed up the group_concat?
2) Should I index the column for ON at my LEFT JOINs? Like: wpp.ID and object_id?

You have to create indices for all the fields you have used to bind tables or to restrict results:

wpp.ID
object_id
term_taxonomy_id
wp_terms.term_id
wpp.post_author
s.ID
post_type

Some of fields are already indexed as primary keys so do not duplicate indices, that can impact RAM consumption as well as performance.

First you have to do now - is to launch your query with EXPLAIN prefix and show the result.

N.B.

Always use aliases for joined tables, like third one in your code. Always specify explicitly table you refer to, like wp_terms.term_id or s.id.


Importing order information Our new project is to write a customer order management system.  My specific task is to import customer orders from various different sources - XML, CSV, XLS... etc.

Our old DBA has left the company and I have stepped up to the plate. I have plenty of SQL experience but for the first time I'm required to design a brand new system with a feasible data structure to hold the orders of many different customers, which we import into the database.  I don't want to have to change the database every time we have a new customer order with a slightly different structure.

The problem is that every customer wants to provide us with an output from their computer system, and every customer's orders are different.  Our plan is to write a script for each unique customer to take their data file and transform it into our orders table.

So far so good, but what will my customer orders table look like?  Every customer is going to have their own unique fields, some will be mappable to generic fields (like order number, delivery date) for example.. but if I'm to build a solid foundation, I need ideas to store this data in a flexible and scalable way.

Does anyone have any experience with this kind of thing?  Any ideas or tips would be very much appreciated.  Thanks for your time.

I think the best way to approach this would be to have tables like so:

table Customer
    ID
    Name
    ...

table CustomerFields
    CustomerID
    FieldID
    FieldName
    ... (could have things like type, required/optional, size, etc.)

table CustomerOrders
    CustomerID
    OrderID
    OrderNumber
    DeliveryDate
    ... (all your generics and mappable fields)

table CustomerOrderFields
    CustomerID
    OrderID
    FieldID
    FieldValue

By having the table CustomerOrders, you satisfy the ability to map common customer order data to common fields (simplifying reporting at the expense of making the import a little more painful since the fields must be mapped), and the CustomerOrderFields gives you the ability to have the custom fields per customer necessary for the un-mappable data.

The custom fields are still reportable, but not as easily as your generics as they'll come to you in multiple rows (instead of multiple columns). There are some ways around all that depending on your report creator (e.g., pivoting the results).

The only other option would be to do something like this (which, personally, I would avoid):

table CustomerFields
    CustomerID
    Field1Name
    Field2Name
    Field3Name
    ...
    Field99Name

Table CustomerOrders
    (all your generics)
    Field1Value
    Field2Value
    ...
    Field99Value

This has the advantage that all your data is in one row, but also has the disadvantage that it isn't immediately apparent from the row what each value means. The first method can always be joined to the field list to give a good definition of the data in each custom field. In addition, what happens if some company requires 100 custom fields? In the above example, you'd be making changes to your data structures and code whereas in the first example, you'd never have the issue -- customers could have infinite custom fields.

I've seen it done both ways, and both ways work. Both ways have their downsides and upsides. The first is far more scalable, but harder to get in to columns (instead of rows). Everything's a trade-off.

Hope that helps some!


Oracle: unable to change to noarchivelog We discovered that our Oracle 12c database was taking up a large amount of disk space with archive logs, so I figured out how to delete the files, but I'm unable to disable archive logging.  We don't have an in-house DBA, so I'm trying to figure this out myself.  From lots of Googling, it's my understanding that I need to do the following:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;

I've got two problems though.  I can't do the "sqlplus / as sysdba", it complains about "ORA-12560: TNS:protocol adapter error" unless I specify the SID, even though it the environment variable is defined.  Also, when I do the shutdown, I get "ORA-12514: TNS:listener does not currently know of service requested in connect descriptor" and it disconnects me from Oracle.

C:\Users\administrator.MYDOMAIN>set oracle_sid=orcm

C:\Users\administrator.MYDOMAIN>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 5 11:11:33 2018

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

SQL> connect / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

SQL> connect sys/password@orcm as sysdba
Connected.
SQL>
SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\app\oracle\product\12.1.0\dbhome_1\RDBMS
Oldest online log sequence     6869
Next log sequence to archive   6871
Current log sequence           6871
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Warning: You are no longer connected to ORACLE.
SQL>

Any assistance would be greatly appreciated.

ORA-12560: TNS:protocol adapter error on Windows is typically caused by incorrent ORACLE_HOME and ORACLE_SID environment variables.

Find the database service:

Find the path and name of the instance:

Based on that:

set ORACLE_HOME=O:\oracle\product\12.2.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=RTG

Then try again:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;


Moving database from one server to another I have a virtual machine running SQL Server 2012 in Azure and have also generated a script of my database to be run on the SQLSERVER2012 VM on Azure, however I am getting an error

  "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PXDEV.mdf" failed with the operating system error 3 (The system cannot find the path specified.).

I have checked that path and the file is actually there on the source server.

Are you getting the error on the destination server?

Is the path present on the destination server?

You need to make sure that the paths defined in your CREATE DATABASE script exist on the destination server or you need to create them there.


How to maintain a global index on a large table undergoing regular partition purges? Problem

I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate querying by column needle. This is simple enough.

The issue arises at the end of the month when the oldest table partition is dropped. I can't afford to rebuild a global index on a 3 TB table every month. Only the four most recent month partitions of haystack are in the database at any given time. So roughly 25% of the table is removed when dropping a monthly partition.

Attempted Solutions

I've tried creating an index on column needle which was global partitioned by hash on needle. But this means having to rebuild the entire global index when dropping an old month partition from the table. No good.

I've also tried creating an index on columns (date, needle) which was global partitioned by range on date with ranges that match the table's monthly partitions. But again, I'm still forced to update my entire global index when dropping that old month partition from the table - even though my table and index are partitioned identically (excepting the table's subpartitions).

Of course, I can't simply create a locally partitioned index. I would need the partitions to be local but without the subpartitions. From what I can tell, that's not possible; it's an all or nothing choice with the local partitioning option on indexes.

Dropping Partitions

For completeness, here's a few options I've tried for dropping old partitions at the end of the month...

alter table HAYSTACK drop partition P_MONTH update global indexes;
This takes far too long, as it attempts to maintain the global indexes during the drop.

alter table HAYSTACK drop partition P_MONTH; Without the global index clause, my global index is left with all of its partitions marked unusable. (Understandably so.)

alter index HAYSTACK_IDX drop partition P_MONTH; Dropping my index partition invalidates the partition whose high value was just above the dropped partition's. If I were to go on and drop the table partition now, I'm still going to have to rebuild my entire global index.

My Question

Is there a way I can purge old table partitions without invalidating or rebuilding the global index partitions? Essentially, can I somehow tell Oracle to handle these global index partitions as if they were local, but without subpartitioning? (Sadly, my research says no.)

Or... Is there another (better?) way to solve my problem?

version <= 11.2? No.*

But in 12c, there is a new feature called Asynchronous Global Index Maintenance.

The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens automatically in the maintenance window, or it can be started manually.

More details about this: https://richardfoote.wordpress.com/category/asynchronous-global-index-maintenance/

*: There is, but I don't think it is applicable in your case. Deleting (and NOT truncating) all rows from the partition before dropping it leaves the global index in usable state. But this is enormous extra work for the 25% of a 3 TB table.


Recalculate and replace large table without interrupting table reads There's a large table in the database containing statistical information. This table must be read frequently (by lookups which return a single row).

This table must be completely recalculated every so often. There's a procedure which does the calculation. Currently it does this into a temporary table, and then when it's done it truncates the real table and inserts the data from the temporary table into it.

The problem is that this truncation and insertion takes a while and interrupts lookups during that time. The question is how to do it without interrupting lookups, or only interrupting lookups very briefly.

I'd be happy to hear all options which achieve the desired behaviour; the solution doesn't necessarily have to involve a temporary table. Sorry if this is an established common problem -- I wasn't sure which terms to search.

Two options, depending on the engine and/or the permissions you have - populate a second table, then rename that table (e.g. sp_rename 'thetable', 'deadtable'; sp_rename 'newtable', 'thetable'; drop table deadtable;), or populate a second table then have your application choose to use that table instead.


I think this will get you what you want.

It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1.

It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates.  This gives you one record per toolid for every day within the range of rental dates.

Lastly, it groups by the toolID, and counts the distinct dates that the tool was rented to get rid of duplicate date values.

;WITH Dates(Date_Day) AS
(
    SELECT Convert(DateTime, '2016-01-01') AS Date_Day
UNION ALL
    SELECT DateAdd(day, 1, Date_Day) FROM Dates
    WHERE Date_Day < '2016-03-01'
)
SELECT
    Rental_Dates.ToolId,
    Count(DISTINCT Calendar_Dates.Date_Day)
FROM
    Dates Calendar_Dates
Inner Join
    #tmpToolRentalDays Rental_Dates
        ON
            Calendar_Dates.Date_Day BETWEEN Rental_Dates.StartDate AND Rental_Dates.EndDate
GROUP BY
    Rental_Dates.ToolId;


Where can I get a SQL Server Trace Dataset? I need a huge SQL Server query trace file (like the profiler trace) with the SQL query text and the time-stamp. I need a trace of SQL traffic produced by a real application because I want to do some pattern analysis. I check many online dataset sites, but I couldn't find one. I don't think anyone will set a profiler trace in production environment since it reduces the performance. But I need a huge trace file (or just SQL Text and Time-stamp will be enough) with the database (with schema and data). Is it possible to get such a data-set ?

At least is it possible to find a data-set with 24 hours of trace?
Does Microsoft has trace files for it's sample databases such as AdventureWorks?

Pattern analysis would be entirely dependent on workload.

Every workload is different. Not just differences between transactional (OLTP) and analytics (OLAP), but even a dozen OLTP applications will produce entirely different patterns.

If you want to build a synthetic one, capture a trace while benchmark tests run. You can start with Benchmark Factory or HammerDB as mentioned in this Stack answer.


SQL Server Migrate Database - Set Compatibility Level We are planning on migrating from SQL Server 2008R2 to SQL Server 2014 (or 2016).

One 3rd party application is not supported after version 2008R2.

Is it OK to migrate this application database to 2014, and then change the Compatibility Level of that database to 100?

Is changing the compatibility level to 100 in effect the same as having it on SQL Server 2008R2? Or are there some potential issues?

In general, it is not advisable to run a database in an older compatibility mode permanently, and the relevant Microsoft documentation from
ALTER DATABASE Compatibility Level (Transact-SQL) is:

  Use compatibility level as an interim migration aid to work around version differences

So the best option is to avoid doing it if possible.

Having said that, in the real world, we have ridiculous numbers of databases out there on our servers, and security concerns are pressuring us to get off of older versions of SQL Server and Windows.  Meanwhile, the application vendor is out of business, or the organization won't upgrade it because it is planning to retire the application (and maybe has been planning to do that for the past few years), or it would just cost too much to upgrade it, etc.  So I think it is not a rare situation where databases are being run permanently in an older compatibility mode.  Limit your risk by:

Thoroughly testing the solution
Maybe don't do this with a database that has a massive amount of code, a lot of users, or a high rate of transactions
Advise management of the risk


How to create a unique index with only certain values required to be unique? I have a list of codes being stored in a table, and some codes should be unique, others can be repeated. The codes should be unique-scoped to another field, product_id.

Assuming the code 11 should only be allowed once per product_id, and other codes are allowed to repeat, the table would look like:

product_id     code
1              11   # Needs to be unique for product_id 1
1              222
1              222
1              333
2              11   # Needs to be unique for product_id 2
2              222
2              444

With MySQL, I took advantage of the fact that you can have multiple NULL values in a unique index, so by adding a "tie breaker"(?) field, ucode , I was able to hack together a solution:

product_id  code   ucode
1           11     1    # Code 11 needed to be unique, so 1 for ucode
1           222    NULL # Code 222 can be repeated, so NULL for ucode
1           222    NULL
1           333    NULL
2           11     1
2           222    NULL
2           444    NULL

Unique index was then made on [product_id, code, ucode].
For unique codes, the ucode field was set to 1, otherwise NULL.

This works, but feels very kludgy. Is there a better way I can do this?

(I am using MySQL)

In versions MySQL 5.7 and MariaDB 5.2+ you can use a (generated) VIRTUAL column to accomplish this. You defineucode as a virtual column and then add a UNIQUE constraint:

CREATE TABLE codes
  ( product_id  INT NOT NULL,
    code INT NOT NULL,
    ucode BIT AS (CASE WHEN code  = 11 THEN b'1' ELSE NULL END)
        VIRTUAL,
        -- PERSISTENT,    -- for persistent storage of the value in MariaDB
        -- STORED,        -- for persistent storage of the value in MySQL
    CONSTRAINT code_11_product_id_unique
        UNIQUE (ucode, product_id)
  ) ;

Test at dbfiddle.uk:

insert into codes
  (product_id, code)
values
  (1, 11),
  (1, 222),
  (1, 222),
  (1, 333),
  (2, 11),
  (2, 222),
  (2, 222);

✓

select * from codes;

product_id | code | ucode
---------: | ---: | :----
         1 |   11 | 1
         1 |  222 | null
         1 |  222 | null
         1 |  333 | null
         2 |   11 | 1
         2 |  222 | null
         2 |  222 | null

insert into codes          -- should fail
  (product_id, code)
values
  (2, 11) ;

Duplicate entry '\x01-2' for key 'code_11_product_id_unique'

select * from codes;

product_id | code | ucode
---------: | ---: | :----
         1 |   11 | 1
         1 |  222 | null
         1 |  222 | null
         1 |  333 | null
         2 |   11 | 1
         2 |  222 | null
         2 |  222 | null


Installing PostGIS in a different schema causes topology extension to not find geometry type I have a sql dump of the schema of an existing database that is working correctly. When I try to load this into a new, empty database psql new_db < dump.sql, I get the following error:

ERROR:  42704: type "geometry" does not exist
LOCATION:  LookupTypeNameOid, parse_type.c:228
Time: 51.680 ms

The dump.sql file is quoted below down to the failing line:

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.1
-- Dumped by pg_dump version 10.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: bridj; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA bridj;

--
-- Name: postgis; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA postgis;

--
-- Name: topology; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA topology;

--
-- Name: SCHEMA topology; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA topology IS 'PostGIS Topology schema';

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis;

--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';

--
-- Name: postgis_topology; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;

It appears that the postgis_topology extension cannot find the geometry type because it's in the postgis schema rather than the public schema. I have tried running

set show_path to "$user", public, postgis;

but that has no effect; creating the extension fails with the same error.

More background:

The reason for the schema move is that we are using database schemas to isolate data for multiple tenants. Thus, public will not always be in the search path. By moving PostGIS to its own schema, we can keep that in the search path at all times

  Postgres 10.1

  POSTGIS="2.4.2 r16113" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.7" LIBJSON="0.12.1" LIBPROTOBUF="1.3.0" TOPOLOGY RASTER

The reason for the schema move is that we are using database schemas to isolate data for multiple tenants. Thus, public will not always be in the search path. By moving PostGIS to its own schema, we can keep that in the search path at all times

Good luck with that. It's no longer possible in new versions of PostGIS, including 2.4.2 r16113 -- the version you're using. From the site,

  As of PostGIS 2.3, the postgis extension was changed to no longer allow relocation. All function calls within the extension are now schema qualified.

You'll have to install it into public, and then move it. YMMV. It's not supported. Again, from the official site,

UPDATE pg_extension
  SET extrelocatable = TRUE
    WHERE extname = 'postgis';

ALTER EXTENSION postgis
  SET SCHEMA postgis;

ALTER EXTENSION postgis
  UPDATE TO "2.4.1next";

ALTER EXTENSION postgis
  UPDATE TO "2.4.1";


MySql global variable disconnect_on_expired_password This is very annoying....
disconnect_on_expired_password is set to ON.
MySql version is:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.5-m15 - MySQL Community Server (GPL)

I need to understand which of my MySql users is causing this error: [Note] Your password has expired. To log in you must change it using a client that supports expired passwords.

I tried setting default password lifetime to 0 so:
SET GLOBAL default_password_lifetime = 0; according to
MySQL password expired, and default_password_lifetime now shows 360 and still I get the error.

I tried setting disconnect_on_expired_password but with no luck.
SELECT @@GLOBAL.disconnect_on_expired_password;
Gives:

+-----------------------------------------+
| @@GLOBAL.disconnect_on_expired_password |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

So I tried:
SET GLOBAL disconnect_on_expired_password=0;
ERROR 1238 (HY000): Variable 'disconnect_on_expired_password' is a read only variable

My questions are:

how can I know which of my MySql users is causing the error mentioned above?
how can I change disconnect_on_expired_password if @all??

Thanx

This should give you a good idea as to which mysql user(s) is/are causing the error:

SELECT user, host FROM mysql.user WHERE password_expired = 'Y';
To change disconnect_on_expired_password: Edit /etc/my.cnf, find the mysqld section, add this line:

disconnect_on_expired_password=OFF

Then restart mysqld.


DB2 equivalent to T-SQL's REPLICATE? In T-SQL there is a method, REPLICATE(char,N), which will make a string with N values of a certain  character.  This is very helpful when querying into JDE.
Does anyone know if there is a DB2 equivalent to this?

I believe REPEAT will give you what you are looking for.

First example from the page referenced above:

Example 1: Repeat 'abc' two times to create 'abcabc'.

SELECT REPEAT('abc', 2)
FROM SYSIBM.SYSDUMMY1;


How to create a CSV file which imports data from an Access Database on a Weekly Schedule? I have very little experience in database management but have come across a problem I am unable to find a solution to online. I currently have access to a database which is updated in real time with the most recent sales figures for a company. In simple terms it looks like this on 01/09/2016:

Sales
50

I wish to make a CSV report which takes the sales levels from the live database each week and inserts them as a new row in the CSV file. In order to track the report date, I had planned to add a new column named 'Report Date' with the date of report update listed for each observation taken from the live database.

The desired output would have a format similar to

Report Date     Sales
01/09/2016      50
08/09/2016      70
15/09/2016      79

It would be good to know what people think is the best method to produce this type of dataset. If there are any options that involve SQL that would be good, but open to any method that does the job.Let me know if any more information is needed!

If I understand your question correctly ... you are trying to store the Report Date and Sales Value in a CSV file on a weekly schedule.

Why not save this information in your database? Have a scheduled job run a query to produce your sales numbers. That scheduled job would insert the Report Date and Sales number into a database table. That way you don't have the sales numbers in an external CSV file.

When you want to check the report date you just have to run a simple query to pull back that information. Let me know if I'm missing the boat.

*** EDIT ****
In your post you said "I have access to a database". I suppose I should have first asked if this was an "Access Database" or a "SQL Server Database".


stored procedure logic,data flow visualization tool I have a very poorly written stored procedure with cursors, if else etc. Is there any toll available to visualise the logic and data flow?

Thanks

I've used a program called Code Visual to Flowchart, if you feed in a SQL file it will generate a flowchart based on it. The free version is all I've used and it met the needs I had, making it much easier to work my way through a 3000 line procedure that was completely devoid of comments.

"I can confirm I have no affiliation to this product"


Account locked but I see it open I have an issue with my database Oracle 11gR2.

When I try to connect to my database via SQL Developer, it says that my account is locked after too many missed connections due to a wrong password but when I use the query

SELECT username, account_status FROM dba_users

on SQLPlus, the account is OPEN. I tried to unlock/lock/unlock the account without success.

This problem comes after the expiry of passwords. I've changed them via SQLPlus (with the command ALTER USER <username> IDENTIFIED BY <password>) but when I wanted to connect with my new password on SQL Developer, I had the error message

  ORA-01017 Invalid Username/Password.

(and if I put the old password, I had the error message "expired password".)

I would like to know how to unlock the locked account (even though I see it open).

Try running the following from sqlplus and posting the results.

select username, account_status, lock_date, expiry_date from dba_users where username='<your user name>';
select instance_name, host_name from v$instance;

set term off
set serveroutput on
set long 10000000
set trimspool on
set tab off
set linesize 190
set pagesize  50
set feedback  on
set editfile "/tmp/_USER_CONNECT_IDENTIFIER_PRIVILEGE.buf"
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE> "
set timing on
set term on


Connection with SQL*Plus to Oracle I've installed Oracle Database Express 10g on Ubuntu, but the following error occures when I try to connect to sql plus using the sysdba as username and it's password which I gave during installation :

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

These are some iformations I think they may help :

uname -a

Linux aimad-Vostro-1015 3.11.0-17-generic #31-Ubuntu SMP Mon Feb 3 21:53:31 UTC 2014 i686 i686 i686 GNU/Linux

env | grep ORACLE

ORACLE_SID=XE
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

sysctl -p |grep kernel

kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
kernel.shmmax = 1073741824

sysctl -a |grep kernel | grep shm

kernel.shm_next_id = -1
kernel.shm_rmid_forced = 0
kernel.shmall = 2097152
kernel.shmmax = 1073741824
kernel.shmmni = 4096

sysctl -a | grep kernel | grep sem

kernel.sem = 250    32000   100 128
kernel.sem_next_id = -1

df -k /dev/shm

Filesystem     1K-blocks  Used Available Use% Mounted on
none             1014392   156   1014236   1% /run/shm

How can I solve this problem ?

You must install Oracle as a non-root user.  Generally, that user would be called oracle, but it can be anything, as long as it's not root.