Help with a logic inside WHERE or ORDER BY I Have this query :

;with cte as (
Select tConc.Faturamento
     , tFat.TotalFatura
     , tCod.Sequencial
     , tCodDet.CdCodigoBarras
     , tCodDet.Linha
     , ValorRecebido
  From Sistema.Conciliacao_Faturamento_x_Arrecadacao as tConc
 Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
    On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
 Inner Join Arquivo.CodigoBarras as tCod
    On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
 Inner Join Arquivo.Faturamento as tFat
    On tConc.Faturamento = tFat.CdFaturamento
 Where Faturamento In(Select Faturamento
                        From Sistema.Conciliacao_Faturamento_x_Arrecadacao
                       Group By Faturamento
                      Having Count(CodigoBarras) > 01))

And the result is this one:

| Faturamento | TotalFatura | Sequencial | CdCodigoBarras | Linha | ValorRecebido |
|-------------|-------------|------------|----------------|-------|---------------|
| 2458874     | 320,90      | 5783       | 141            | 18393 | 320,90        |
| 2458874     | 320,90      | 5783       | 141            | 18394 | 320,90        |
| 3283899     | 840,50      | 5783       | 141            | 27140 | 840,50        |
| 3283899     | 840,50      | 4397       | 142            | 4010  | 840,50        |
| 3901364     | 89,44       | 21442      | 139            | 23595 | 89,44         |

Long story short, if you notice, there are some equal "faturamento".

What I need is a distinct of:

1) If Faturamento is the same, we need the biggest sequencial. Ok with this, I can order by 3.

2) If faturamento and sequencial is the same, I need the biggest cdCodigoBarras. Ok, again, I can do it with Order by 3,4.

3) If everything is the same, but valorRecebidois the same, I need to get the biggest Linha. but if ValorRecebido is different ( and faturamento, sequencia, cdcodigobarras is the same ), I need the biggest ValorRecebido.

Is this something that devs should do in the source code? I really can't think how I can do it with a Query.

I've spend some time tryint to do something with count, but even thinking I'm in the right way, I can past this when vlaorRecebidois the same, get biggest Linha, or if it's different, the biggest vlaorRecebido.

I think you should be able to get what you want with ROW_NUMBER().  Please see the below example.

;with cte as (
Select tConc.Faturamento
     , tFat.TotalFatura
     , tCod.Sequencial
     , tCodDet.CdCodigoBarras
     , tCodDet.Linha
     , ValorRecebido
     , RN = ROW_NUMBER() OVER (PARTITION BY  tConc.Faturamento, tCod.Sequencial, tCodDet.CdCodigoBarras, ValorRecebido
                                ORDER BY tCodDet.Linha DESC, tCodDet.CdCodigoBarras DESC, tCod.Sequencial DESC)
  From Sistema.Conciliacao_Faturamento_x_Arrecadacao as tConc
 Inner Join Arquivo.CodigoBarras_Detalhe as tCodDet
    On tConc.CodigoBarras = tCodDet.CdCodigoBarras_Detalhe
 Inner Join Arquivo.CodigoBarras as tCod
    On tCodDet.CdCodigoBarras = tCod.CdCodigoBarras
 Inner Join Arquivo.Faturamento as tFat
    On tConc.Faturamento = tFat.CdFaturamento
 Where Faturamento In(Select Faturamento
                        From Sistema.Conciliacao_Faturamento_x_Arrecadacao
                       Group By Faturamento
                      Having Count(CodigoBarras) > 01))
)
SELECT * FROM cte WHERE RN = 1

You may want to change or play with the PARTITION BY section as well as the ORDER BY to get what you want.  Basically, the PARTITION BY is what you want to be the business keys.  And I wasn't sure if it was just the Faturamento or Faturamento + Sequencial + ... or not.  Order by is if (given the same partition), what order should they appear in.


in MySQL what is the query to show these student's that each marks less than 50? I have this table marks in MySQL database

I used a query to output this table that shown as below:

it's my output:

I want use a query to show these student's that each marks less than 50,
output for example above only Liza and Jake with marks appear

What is the MySQL query for do this work? how i can?

SELECT Name,
       MAX(CASE WHEN Subject = 'Word' then Marks END) Word,
       MAX(CASE WHEN Subject = 'Excel' then Marks END) Excel,
       MAX(CASE WHEN Subject = 'Access' then Marks END) Access
FROM Datatable
GROUP BY Name
    -- If ALL marks must be below 50
-- HAVING GREATEST(Word, Excel, Access) < 50;
    -- If at least one mark must be below 50
HAVING LEAST(Word, Excel, Access) < 50;


Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore? I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long as it works.

I'm abou to try it on an experimental db, but here's a puzzle: I have a mysqldump from the production database, which naturally wants to recreate the tables as myisam.

Several options present themselves:

edit the dump file -- which is an 8 GiB file so editing might be a tad difficult.
just load the dump and convert the engine afterward -- but sourcing in the file seems to take a long time.
Find some magic that will force the new DB engine to be innodb no matter what the dump says.

Is there another option? Which option should I choose?

I'll cheerfully admit DBA is not my primary skill.

I am going to answer based on option #3.
You can dump all databases except the following:

information_schema
performance_schema
mysql

You should dump it like this:
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g'  > AllMyData.sql

You can just load that and you are all set.
If you want to move the MySQL Users and Passwords, do this:
MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

Then, just import MySQLUserGrants.sql into the MySQL 5.6 instance.
Give it a Try !!!


solve high stolen memory I've a SQL Server 2012 SP2 with max memory set to 13GB but there is 8GB of stolen memory (SQLServer:Memory Manager - Stolen Server Memory (KB)). I know stolen memory is memory taken from the bufferpool to do sorting and hashing operations.

But how can I identify what process/request is using all that stolen memory, or what query is doing a lot of hashing/sorting?

Update
Further research showed that MEMORYCLERK_XE was using 4.3GB. This could be fixed by SP3. We will schedule this update and monitor if the problem is fixed.

You could try querying various dmvs with the following script.

SELECT TEXT,
   request_time,
   grant_time,
   query_cost,
   timeout_sec,
   wait_order,
   wait_time_ms,
   group_id,
   requested_memory_kb,
   granted_memory_kb,
   required_memory_kb,
   used_memory_kb,
   max_used_memory_kb,
   ideal_memory_kb,
   query_plan,
   'all information >',
   *
FROM   sys.dm_exec_query_memory_grants qmg
   OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) qp

It queries the following dmvs for information:

sys.dm_exec_query_memory_grants
sys.dm_exec_sql_text
sys.dm_exec_query_plan

This script was handed over to me by Ramesh Meyyappan at a course on "SQL Server Performance Tuning and Optimisation" at Microsoft SA, Wallisellen, Switzerland.


Postgresql equivalent of --i-am-a-dummy MySQL's command line tool has a flag --i-am-a-dummy that sets execution limits:

  With --safe-updates, you can modify rows only by specifying the key values that identify them, or a LIMIT clause, or both. This helps prevent accidents. Safe-updates mode also restricts SELECT statements that produce (or are estimated to produce) very large result sets.

Is there a way to turn on similar features in psql?

There was a proposal for an extension that would do part of this, but it never got finalized.  Perhaps another push could get it done.


SSRS 2016 Report Portal - KPI option no available in drop down Is there a way to enable the KPI selection in reporting services 2016 web portal?
I eagerly wanted to try this feature, I then wasted 2 hours looking for it, still haven't found it. In most examples it seems to be under the "New" drop down.

I'm on the home portal page , I did create a dataset and datasource. Still no KPI option. I've remoted onto the host server, the user has System Administrator and System User rights in SSRS.

KPIs are an Enterprise feature. They are only available in the Enterprise and Developer Editions. https://msdn.microsoft.com/en-us/library/mt742500.aspx

You will also need to do two things in order to have KPIs show up in the "New" dropdown.

You must have Content Manager or Publisher privileges in the folder you will be creating the KPIs in.
You must have at least Browse privileges to a shared data source and shared data set.  These do not have to be in the same folder as where you want to publish the KPI.

Keep in mind local admin isn't a privilege/role. I set up my own SSRS 2016 instance. But I still needed to add my self(and the admin group) to the Content Manager role.


Hierarchical data with versioning I have a domain where I deal with data which has a parent/child relationship, of arbitrary depth.

Also, full time traversal needs to be enabled, to show the state of the data at a specific point in time.

Currently I am looking at two different types of databases, and was curious as to which would be more appropriate, or how I would overcome some issues in either of the solutions.

Any links to articles regarding this information would also be appreciated.

Solution 1: rdbms

good fit for time versioning with a solution like temporal tables in sql server (although the db needs to be open source and free, so I would have to add this to something like PostgreSQL)
not that great a fit for arbitrary hierarchical data; need to implement adherent list/nested set

Solution 2: graphdb

natural fit for hierarchical data
I don’t know a proper way to model time versioning in a way which is performant.

So I am looking on some feedback on the advantages/disadvantages of either database type, and how to overcome some of the shortcomings of either.

I personally was leaning towards an graphdb solution, where I add a start_time and end_time attribute to all nodes and relationships, but I am not sure about the performance and if there are any better ways to get time versioning.

The two main considerations are of course performance and simplicity of the query.

I realize this is a rather open question; I am merely looking to see if I am overthinking this or perhaps fail to take some other positives/negatives inti account.

Every graph graph dbms seems to be handling these questions with their own unique ways. From what I know about neo4j, It appears that they can handle this somewhat similarly to how Dgraph Labs can. By placing facets, aka edge properties, on the edges, a kind of point in time graph could be interpreted. (I know more about Dgraph, so that is why I lean that direction with links.)
Here are a few discussions around this topic in Dgraph community:

Storing and querying historical data
Facets in GraphQL
Versioned predicates
Adding property to the edges
Querying graph structure at a certain point in time


PostgreSQL bug(?) - logging with a minimum log level I am using the log_min_error_statement - Setting in the PostgreSQL configuration file, but the logger does not react on the setting, either if I turn it on, or off, or set it to another level, the logger logs every statement. Could this be a possible bug in PostgreSQL logging?

the logger logs every statement

Then that behavior comes from log_statement:

  log_statement (enum)

  Controls which SQL statements are logged. Valid values are none (off),   ddl, mod, and all (all statements). ddl logs all data
  definition statements,


SQL Server unusual behavior on LOB insertion We are facing unusual behavior on a SQL Server 2008 R2 instance. We have a small .NET application, which we use to insert images and pdf files inside database as VARBINARY. Sometimes, we are facing query timeouts when the file size is bit large, sometimes larger than 3 MB, and sometimes this behavior is observed with 4 to 5 MB files.

IIS is installed on a separate machine.

Through trace we have found that no such insertion query is received on the SQL Server end during save image process from application.

This could be an application server issue, but it's the fifth time that we have resolved this issue by restarting SQL Server services. Why so?

During this problem rest of discrete data operations and small LOB insertion queries work fine.

Version: SQL Server 2008 R2 Service Pack 3
Edition: Standard
Windows Server 2008 R2 Enterprise (SP1)
Intel(R) Xeon(R) CPU E5-2630 v2 @2.60Ghz (2 Processors)
Memory 36GB to SQL Server (out of total 64GB)

(We have requested IT to assign at least 20GB more to SQL Server as it's a dedicated DB instance.)

Optimize for adhoc option is set to 1. Adhoc query plans size is about 18% of total cached plans. We are going to clean these adhoc plans periodically using Kimberly's script. I have verified that things are normal on wait stats and performance counters side, and nothing received on Trace when query is submitted from app. LOB ndf file is on local drives.

Current workaround
Thanks Kin for this idea. This time, when we faced the same issue, we just refreshed the system cache by executing the following DBCC statement, and issue is resolved:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

We are still in search of permanent solution as periodically clearing system cache is not possible for some of workloads (specially 24x7 cases).

If your SQL server trace is not showing anything, it is most likely on the application. Even if it is resolved by restarting the SQL service. I have faced a very similar issue in the past and the connection for the upload process was the culprit. Restarting the SQL service basically gives you another attempt at reconnecting. Since it works some of the time, this second attempt may be all that you need to get it going.

Look into how you build your connection for large files. Limit the time it takes to send the query to the server, and when you get past it, mimic the service restarting by trying it again.


Performance inprovement UDB DB2 v10.5.0.5`` We are using db2 UDB for our operational data store that is accessed via JAVA service to display information to front end [24*7]

we are facing a lot of performance and deadlock issues.

1)deadlock is something that i can understand,our ETL jobs are such that it reads the data from the source table and writes to the target and update the reads rows as completed within informatica mappings.As per my knowledge DB2 locks the only at ow level but we had instance where row level raises to table level lock.I am not able to understand why it turns into table level lock and eventually falls into deadlock there by failing the process.Do i need to decrease the commit level to 1000 in order to reduce the no of locks.

2) Also we found that the table overflow value is high and PCTFREE parameter was set to -1[means defaulted to zero].what is optimal value it PCTFREE can be set
[planning to re org but it is not possible

3) Is there any other list of things/parameters that can be set in-order to improve the performance ?

WE are looking at the application level SQL optimization but need a help with Admin eye View

Please advice

I'm in a bit of a hurry, but I'll try to answer question 1 to some extent.

There are two major parameters that affects locking:

a) LOCKLIST - This is the number of 4K blocks that db2 can use to keep track of locks

b) MAXLOCKS - This is the percent of LOCKLIST that each transaction is allowd to use.

A lock is using bit of memory from the LOCKLIST, and if either the limit for a) or for b) is violated, db2 tries to free up memory by replacing rowlocks with a table lock. To realize that this can cause a deadlock concider:

Tx_a updates a row
Tx_b updates a huge amount of rows and excausts the LOCKLIST. db2 tries to escalates the lock to a table lock, but that is prevented by tx_a's lock
Tx_a tries to update one of the rows locked by Tx_b -> DEADLOCK

Check whether it actually is deadlocks, the error message for -911 says something like "Deadlock or locktimeout". Deadlock is rc=2, timeout is rc=68.

You can try to minimize this problem by either add more resources (increase LOCKLIST, MAXLOCKS) or by reducing the amount of locks that you acquire (commit more frequently). You may also want to have a look at the isolation level that your transactions use, also concider CURRENTLY COMMITED

For your question 2, there's no such thing as an optimum value (otherwise I'm pretty sure that would be the default :-) It all depends on the workload

Question 3's answer is pretty similar to answer 2, it depends. It is way beyond a stackexchange post to walk through all parameters that can affect performance, but I would start looking at statements that read the most rows in your system, and see if they can be improved. In many situations you can find indexes that improve the situation a lot.


More efficient/cleaner way of doing a select that traverses a specific column? Given the following Table structure:

Id  OldUrl  Target
1    /a      /b
2    /b      /c
3    /c      /d
4    /x      /y

I want to do a select that "follows" the Target Column, example:

If I pass in ID 1 I want to return row 3 because A would resolve to C if you Select the Target and then Selects the next row where OldUrl = Target
Like this:

a -> b
b -> c
c -> d

SQL for creating the table:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OldUrl] [nvarchar](450) NOT NULL,
    [Target] [nvarchar](max) NOT NULL,
    [Wildcard] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Permanent] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I've managed to do that by using the code below, but my SQL skills are very limited so I just want to know if there is a better/cleaner way of doing this?

DECLARE
@Id INT = 1,
@Target nvarchar(MAX) = '',
@Result nvarchar(MAX) = ''

WHILE (1=1)
    BEGIN
        SELECT @Result=(SELECT [Target] FROM MyDatabase.MyTable WHERE Id = @Id)
        if(@Result = @Target)
            break
        SELECT @Id = [Id] FROM MyDatabase.MyTable WHERE OldUrl = @Result

        SET @Target = @Result
    END

SELECT * FROM MyDatabase.MyTable WHERE Id = @Id

Wanted output when passing in Id = 1

3   /c  /d

A recursive CTE will find that for you.  By adding in the desc and TOP 1 it will look for the longest possible path.  If there were two paths it could follow it will pick one based on the ORDER statement

;WITH follow(idstart, idend, oldurl, target, depth, path) as
(
    SELECT S.id idstart, S.id idend, S.oldurl, S.target, 1 AS depth, cast(S.oldurl as varchar) AS path
    FROM mytable as S
    UNION ALL
    SELECT S2.id idstart, p.idend idend, S2.oldurl, p.target, p.depth + 1 AS depth, cast(RTRIM(p.path) + '->' + S2.oldurl as varchar) AS path
    FROM follow AS p JOIN mytable as S2 on S2.target = p.oldurl
    WHERE p.target is not null
)
SELECT TOP 1 * FROM follow
WHERE idstart=1
ORDER BY depth desc;


table does not exist CREATE OR REPLACE PROCEDURE ProcedureX()
    TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER;
    AVERAGE_COUNT AVERAGE_COUNT_TBL;
BEGIN
    INSERT INTO AVERAGE_COUNT
    VALUES(10);
END ProcedureX;

It throws the error:
    PL/SQL: ORA-00942: table or view does not exist

What is the problem?

A PL/SQL table is NOT the same as a temporary table in Oracle.

An Oracle temporary table is created with they syntax

CREATE TEMPORARY TABLE xxx ...

and is a DBMS table the contents of which are transient, vanishing either at the end of the transaction or the end of the session. It can be used in any SQL just like a normal table.

A PL/SQL table which you have defined above cannot be used in SQL but must be referenced programmatically. See http://docs.oracle.com/cd/A57673_01/DOC/server/doc/PLS23/ch4.htm for an overview on how to do this.


Tracking grant violations We are changing one of our application user logins from being dbowner to a more granular permission scheme--finally.

Is there a way from within SQL Server 2016 that I can track permission violations? That is, this new user cannot SELECT from table dbo.TableName so that I can review it and determine if it is appropriate or not.

I know I can trap errors from the program side of things and log them, but I would rather not rely on that if the database can log that for me.

You can use Extended Events™ to accomplish this. The error_reported event allows you to log a row to a file every time a certain error occurs. You can add additional information to what is logged such as username and client_hostname. Here is some example T-SQL to get you started that filters on only error code 262:

CREATE EVENT SESSION [error_262_to_file] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.username)
    WHERE ([error_number]=(262)))
ADD TARGET package0.event_file(SET filename=N'C:\XE\error_262_to_file.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Suppose one of my most troublesome users logs in with his sean_gallardy login. I don't trust this user so I gave him very limited permissions. If he tries to view a query plan he gets the following error message in SSMS:

  Msg 262, Level 14, State 4, Line 1

  SHOWPLAN permission denied in database 'master'.

Here is what is logged to the extended event file in response:


Setup the tables and some sample data:

drop table if exists users;
drop table if exists views;
drop table if exists tracks;

create table users
(idu      int
,username varchar(30));

create table views
(id       int
,`by`     int
,track    int);

create table tracks
(id       int
,uid      int
,title    varchar(30)
,time     datetime);

insert into users values
(567,'TrainingPuppy'),
(687,'BathroomMakeover'),
( 45,'PoshNachos'),
( 15,'SewingButtons');

insert into views values
(1,  5, 55),
(2,  5, 55),
(3, 67, 55),
(4,  6, 29),
(5,125,  2),
(6,  5,698),
(7,  5,698);

insert into tracks values
(  2, 15,'Worf is in the air','2016-02-11 22:57:35'),
( 29,567,'Stargold'          ,'2016-08-11 22:57:28'),
( 55,567,'No love liers'     ,'2016-10-11 22:57:51'),
(698,567,'Lofe'              ,'2016-11-11 22:57:44');

Start with a (ANSI standard group by) query to obtain our counts:

select tracks.id,
       tracks.uid,
       count(*) as ucount,
       users.username,
       views.track,
       tracks.title,
       tracks.time

from   tracks
join   views
on     views.track = tracks.id

join   users
on     users.idu = tracks.uid

group by tracks.id,
         tracks.uid,
         users.username,
         views.track,
         tracks.title,
         tracks.time

We'll call the above 'user_counts', and create a query that generates a ranking over username ordered by ucount desc:

select id,
       uid,
       ucount,
       username,
       track,
       title,
       time,
       @user_rank := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
       @current_username := username

from   user_counts,

        -- initialize our variables
join   (select @user_rank := 0, @current_username := 'abcdefg') r

order by username, ucount desc

Then wrap this in our final select, making sure to only display those rows with a ranking of '1' ... pulling it all together into a single query we have:

select id,
       uid,
       ucount as 'count',
       username,
       track,
       title,
       time

from   (select id,
               uid,
               ucount,
               username,
               track,
               title,
               time,
               @user_rank        := if(@current_username = username, @user_rank:=@user_rank+1, 1) as user_rank,
               @current_username := username

        from   (select tracks.id,
                       tracks.uid,
                       count(*) as ucount,
                       users.username,
                       views.track,
                       tracks.title,
                       tracks.time

                from    tracks
                join    views
                on      views.track = tracks.id

                join    users
                on      users.idu = tracks.uid

                group by tracks.id,
                         tracks.uid,
                         users.username,
                         views.track,
                         tracks.title,
                         tracks.time) user_counts

                -- initialize our variables
        join   (select @user_rank := 0, @current_username := 'abcdefg') r

        order by username, ucount desc) dt

where   user_rank = 1

order by ucount desc, username

And the results:

id uid count username      track title              time
-- --- ----- ------------- ----- ------------------ -------------------
55 567     3 TrainingPuppy    55 No love liers      2016-10-11 22:57:51
 2  15     1 SewingButtons     2 Worf is in the air 2016-02-11 22:57:35

Some fiddles:

MySQL 5.6
MySQL 5.7
MariaDB 10.2

NOTE: If a given username has the same max(count) for more than one track/title, this solution will only display one record; to display multiple records would require a tweak to ensure said records receive the same ranking (ie, user_rank = 1).


Processlist show most of the queries are sending data for select query I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and insert), but after few minutes nothing happened. Then I see in database with SHOW PROCESSLIST; most of the query is in SENDING DATA state with 1001 threads. my max query size is 1000. I decrease the size 50 to 10 but no luck. Can anyone suggest me how to solve this problem.

This is very simply a case where your server does not have sufficient capacity (often disk I/O, but sometimes memory or CPU) to handle the workload.  Poorly-written queries and insufficient or sub-optimal indexes are a common contributor, as are applications that give up too soon and re-send the same query that's already running, and an improperly sized innodb_buffer_pool_size with InnoDB.
Sending Data was a very poorly-chosen name for the thread state.  It simply does not mean that the server is necessarily "sending" anything.  The server may, in fact, still be doing table scans and may have nothing, yet, to send anywhere, and the thread will still be in this state.  A more appropriate name for the state might have been "preparing data to send."
By contrast, when the server is in fact actually sending data to the client, the state is Writing to net and it's pretty rare to actually see that in the processlist unless the resultset is particularly large or the client is particularly slow about accepting data from the network.

Sending data
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
Writing to net
The server is writing a packet to the network.
— http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html


Why does sqlcmd -Lc not show the local instance? I have two machines, each with a default instance of SQL Server 2008 installed; each exhibiting a different behavior.

On PC1, when I run sqlcmd -Lc, I do not see PC1 in the list of SQL servers on the local network when the SQL Browser Service is not running.  When I start the SQL browser service, PC1 appears in the list.
On PC2, when I run sqlcmd -Lc, I see PC2 in the list, whether or not SQL Browser Service is running.  If SQL Browser Service is running, I also see PC2\SQLEXPRESS.

Why does PC1's default SQL instance not show up in the list of SQL Servers?

Why does sqlcmd -Lc does not show the local instance?

and in your case even if sql server service is working.

ok first will talk about what sqlcmd -Lc does

it "Lists the locally configured server computers, and the names of the server computers that are broadcasting on the network" for reference

and a note to keep in mind is

"Because of the nature of broadcasting on networks, sqlcmd may not receive a timely response from all servers" for reference

SQL server browser service is the service that Broadcasts and upon startup, SQL Server Browser starts and claims UDP port 1434.

if you unplug your machine from the network and type sqlcmd -Lc, you wont be able to see your machine listed, because If it doesn't get a reply from other available SQLs , it doesn't list itself.

Now to know more about why sometimes sql server doesnt show up, you need to know about UDP and how sql server uses it to discover other sql servers

UDP
is a connection-less protocol
UDP does use ports, it allows multiple processes to be using UDP at the same time.
Since it's connection-less, any connection-like properties between two systems must be handled by the application.
It's a fairly small protocol and therefore takes up relatively little bandwidth on the wire.

SQL Server, uses UDP in two ways

To discover other SQL Servers that are out there.
To discover how to connect to a named instance.

now after knowing how sql server service works and how it uses UDP here below is list of reasons why you dont see your local instance showing up

The UDP communications gets "lost" on the way.
UDP port 1434 are blocked on the network with firewall or windows firewall or IPSEC policy

These might be the reasons and i suggest using a product like sqlping to discover your sql on the network, because if it doesnt show up it doesnt mean its not there, its just the way its broadcasting is the problem

Updates:

Make sure (HideInstance Flag is No)

1)In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then select Properties.
On the Flags tab, in the HideInstance box, make sure its No.

2)
Make sure (Sql Browser Service is Active)
go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active & restart it

3) Finally try this registry Key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL Browser\SsrpListener

make sure it has value 1
if you cant find it under 90 folder, search for SsrpListener


Determine record count grouped as year-month for each user including 0 counts I have table with following schema:

article(id,title, content, created_at, created_by)

I want to find all the records created by each user in each month. If the user did not create the the count should show 0. The date range should start from oldest record and end at most recent record. I want to group records by month-year, meaning I want to check records created by each user in each month, if a user did not create any record then it should NOT be skipped in result set rather it should show count for that month as 0.

Assume I have 5 records as follows:

id, title,      content,          created_at, created_by

1,  Some title, some description, 2017-04-21, 2

2,  Some title, some description, 2017-04-10, 2

3,  Some title, some description, 2017-07-15, 2

4,  Some title, some description, 2017-08-21, 2

5,  Some title, some description, 2017-10-04, 2

The result should be like this:

date,    count, created_by
2017-04,  2,     2
2017-05,  0,     2
2017-06,  0,     2
2017-07,  1,     2
2017-08,  1,     2
2017-09,  0,     2
2017-10,  1,     2

Please note that even if the user did not create any record in a specific month still that month-year was included, that is exactly what I want.

I want to do that in MySQL.

Thank You.
And please don't ask what I have tried, I am not good at such complex queries.

I don't have a MySQL instance handy to test and confirm that this works for you, but this should work.  It assumes that you have at least one entry for every month in the table.

SELECT DateTable.MonthDay
    , RecordCount.Records
    , RecordCount.created_by
FROM (
    SELECT DISTINCT DATE_ADD(LAST_DAY(DATE_SUB(created_at, interval 30 day), interval 1 day) AS MonthDay
    FROM article
    ) AS DateTable
    LEFT OUTER JOIN (SELECT DATE_ADD(LAST_DAY(DATE_SUB(created_at, interval 30 day), interval 1 day) AS MonthDay
                        , created_by
                        , COUNT(id) AS Records
                    FROM article
                    ) AS RecordCount ON RecordCount.MonthDay = DateTable.MonthDay
WHERE DateTable.MonthDay >= '4/1/2017' AND DateTable.MonthDay <= '10/1/2017'


ALTER USER statement modifies users on off-air node I had previously asked a question about Orphaned Users here and was directed to this link in an effort to fix it, but this didn't seem to answer my questions, so I am re-asking after some further review of my system.

We found that several database users on our secondary AG node were orphaned and set as SQL User Without Login, so we were unable to perform a successful failover. The only fix was to run ALTER USER 'user' with login = 'user'; after failover, which mapped the Database login with the SQL login on the 2nd node and allowed the app to connect. Unfortunately, this removed the configuration from the users on the 1st node and set them to SQL User Without Login. We obviously don't want this, because it means manual intervention and additional downtime in an automatic failover.

My questions are:

Why does an ALTER USER statement on one node affect the other if invoked on that first node?
Is there a setting within the Availability Group properties that needs to be checked off/added that could be missing in my setup?
There was a recommendation in another blog that stated to drop the user, and re-create with the SID of the SQL User. How is this different than running an ALTER USER statement, as the SID changes on the DB user to match with the SQL User?

The issue here is that your Login on each node has a different SID on it. When you run ALTER USER it changes the DB user's SID to match the login on that particular node but after a failover the DB User SID no longer matches the SQL Login SID.

The fix is to drop the SQL Login on one node and recreate it using the same SID as the other node. This will ensure the DB user has the same SID as the SQL Logins on both nodes, so after failing over the DB user will map to the login correctly.

You can use dbatools Copy-DbaLogin command to copy the login from Node A to Node B after dropping it from Node B, and this will bring across the SID and password for the login.


Mysql Windows Performance Problem My system:

2X E5 2680 V3 Cpu
32 GB Ram
Windows 7 Pro

Apache 2.4.41
Mysql 8.0.17

I have a script and this for Customer Online Chat Script.  I have 10 clients for customer support.  It uses php json and ajax.  It generates high select query.
What can I do?  Is there any my.cnf example for this usage?
[client]
no-beep

# pipe
# socket=0.0
port=3306

[mysql]

default-character-set = UTF8MB4

[mysqld]
port=3306
basedir="C:\AppServ/MySQL"
datadir="C:\AppServ/MySQL/data/"
character-set-server = UTF8MB4

sql-mode=""
log-output=FILE
general-log=0
general_log_file="mysql.log"
slow-query-log=1
slow_query_log_file="mysql-slow.log"
long_query_time=3
log-error="mysql-error.log"
server-id=1

secure-file-priv="datadir="C:\AppServ/MySQL/Uploads"

max_connections=500

table_open_cache=4000

tmp_table_size=1024M
max_heap_table_size=1024M

thread_cache_size=200
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=975M
key_buffer_size=512M

read_buffer_size=256K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=20000M

innodb_log_file_size=250M

innodb_thread_concurrency=0

innodb_autoextend_increment=6
innodb_buffer_pool_instances=16

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=600

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=5161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

default_authentication_plugin=mysql_native_password

I will be crazy. Only 20k line data and 10 online client how can be slow like fax :S
My system uses SSD disk.
SHOW GLOBAL STATUS pastebin.com/gLvTkZ6s
SHOW GLOBAL VARIABLES pastebin.com/UcJkxRKb
SHOW FULL PROCESSLIST pastebin.com/AaWGFurJ
SHOW ENGINE INNODB STATUS pastebin.com/aQYLW1FH
SELECT name, count FROM information_schema.innodb_metrics ORDER BY name pastebin.com/Wdz1WhU6My
My system generates query like : SELECT count(id) FROM gr_msgs WHERE gid = '1-573' AND id > '5989' gr_msgs


copy table comments from one MySQL database to another I wrote column comments in MySQL database replica made for testing. Now I need to copy these comments to production database. Since comments are stored in table COLUMNS of information_schema database, and information_schema is not writeable, what would be the way to go to copy comments across?
Test database version is 5.5.35 while production database is 5.1.71.

Is there any way to import these comments in production database other than enter them again?

Thank you

Maciej

You could use the INFORMATION_SCHEMA to sculpt the SQL commands for you.
SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')
FROM (SELECT table_schema db,table_name tb,table_comment com
FROM information_schema.tables WHERE table_schema NOT IN
('inforamtion_schema','performance_schema','mysql')
AND table_comment <> '') A;

Output this query to a text file and view the text file
SQL="SELECT CONCAT('ALTER TABLE ',db,'.',tb,' COMMENT ''',com,''';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,table_comment com"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('inforamtion_schema','performance_schema','mysql')"
SQL="${SQL} AND table_comment <> '') A"
mysql -uroot -ppass -ANe"${SQL}" > AddCommentsToTables.sql
less AddCommentsToTables.sql

If the output looks like what you need, then copy the script to the production server, login to mysql and run this to execute the script:
mysql> source AddCommentsToTables.sql

Give it a Try !!!
UPDATE 2014-04-04 16:34 EDT
It is a little harder to change each column definition's COMMENT. Why ???
According to the MySQL Documentation

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows:
ALTER TABLE t1 MODIFY col1 BIGINT;
The resulting column will be defined as BIGINT, but will not include the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain them, the statement should be:
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
Warning This conversion may result in alteration of data. For example,
if you shorten a string column, values may be truncated. To prevent
the operation from succeeding if conversions to the new data type
would result in loss of data, enable strict SQL mode before using
ALTER TABLE (see Section 5.1.7, “Server SQL Modes”).

You would have to express the entire column definition from scratch each time. Doing that is not only tedious but dangerous because it would just copy the data into a fresh temp table and rename the temp table afterwards.
ADDITIONAL WARNING : StackOverflow has this : https://stackoverflow.com/questions/2162420/alter-mysql-table-to-add-comments-on-columns
One of the comments on the accepted answer says : This solution can break the auto increments. –  workdreamer Jul 31 '13 at 12:10
That's makes sense because of what I just said: The table get's copied, so there will be some auto_increment movement.


Does MySQL or MariaDB support a LineString constructor that takes points? It seems with MySQL and MariaDB I'm getting the following when I try to use the LineString constructor. In PostGIS I can do this with ST_MakeLine(pt1,pt2)

> SELECT ST_MakeLine(Point(0,0), Point(0,1));
ERROR 1305 (42000): FUNCTION ST_MakeLine does not exist

> SELECT ST_LineString(Point(0,0), Point(0,1));
ERROR 1305 (42000): FUNCTION ST_LineString does not exist

Is the only way to construct a LINESTRING to use ST_GeomFromText or ST_LineFromText, or ST_LineStringFromText?

No, as a general rule the constructors for MySQL are only available with non-ST_ prefixes.

So you'll want LineString(pt1,pt2)

SELECT LineString(Point(0,0), Point(0,1));


Copy PostgreSQL data from one PC to another I am migrating my server application from the existing system to another system. Unfortunately, the existing system is also the database server. It has data stored. Is it possible to copy this data to the other system? I have a copy of all the table schemas.
I have no idea on how to proceed. I am using PostgreSQL.

You could dump the database using pg_dump and then restore it on the new server using psql. Here's a couple of commands from the above link:

Create the backup:

pg_dump mydb > db.sql

Copy db.sql to the new server (specific command depends on OS)

Go to the new server

createdb mydb -E UTF8 (you don't have to specify UTF8 encoding, but I always do)

Then:

psql -d mydb -f db.sql

As I was answering, JohnP just answered with a fine answer but assumes that pg_hba.conf has been edited to allow remote connections and postgres.conf has been edited to listen on the network.


Find out what encryptor name my database is using I want to find what encryptor name my database is using to encrypted. This is database encryption thing.

This is the syntax I see up site of Micosoft.

CREATE DATABASE ENCRYPTION KEY
       WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
   ENCRYPTION BY SERVER
    {
        CERTIFICATE Encryptor_Name |
        ASYMMETRIC KEY Encryptor_Name
    }
[ ; ]

Now I don't know if a symmetric key or a certificate is used to encrypt database.
The encryptor_name for one encrypted with certificate is I find in sys.certificates of master database. It is name of the row display in sys.certificates

What about the ASSYMETRIC KEY. Can you give give me examples to encrypt database with it, then the script to display information of the encryptor_name that it use. (my English is not good. Please suffer me).

Now I don't know if a symmetric key or a certificate is used to encrypt database. The encryptor_name for one encrypted with certificate is I find in sys.certificates of master database. It is name of the row display in sys.certificates

If you look in sys.database_encryption_keys it'll tell you what database is encrypted using TDE (filter out TempDB [dbid 2]) along with the type of encryptor (cert/asymmetric key), and the thumbprint of that encryptor which can be used to find the encryptor in either the certificates or asymmetric keys catalog.

  What about the ASSYMETRIC KEY.Can you give give me examples to encrypt database with it, then the script to display information of the encryptor_name that it use.

To use an asymmetric key as an encryptor you'll need a hardware security module (HSM) or a software emulation of an HSM such as Azure Key Vault (AKV) which has a complete walk through. Other than the specific Azure part of AKV, the same steps would be taken for an on-prem HSM.


How to index for multiple inequality WHERE search conditions? I am thinking on the problem, if there is a multiple columns on which search must work as fast as possible.

For example, see the table TEST(COL_A, COL_B) with a (tree) index TEST_IDX(COL_A, COL_B). This index can be used for the following WHERE conditions:

WHERE COL_A='x' AND COL_B='y'
WHERE COL_A='x' AND COL_B<'y'

But what to do, if both of the condition terms are using inequality? So I am thinking on a WHERE COL_A<'x' AND COL_B<'y'? AFAIK, normal record-tree indices in such cases can't work. I can imagine some data structure which could make such queries also fast, but I think it should use a much sophisticated data structure as a simple tree.

Do this in MySQL exist? Or in another SQL servers?

Create two separate indexes: idxA(COL_A) and idxB(COL_B).
MySQL will use both indexes and then merge the result.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Secion: 8.3.1.4.3 The Index Merge Sort-Union Access Algorithm


Best way to rank all the columns in a table and store those ranks in another table I have SQL Server 2008 and a table w/ hundreds of thousands of records and about 50 columns.

I run a sql statement like:
Select
rank() over(order by col1 desc) as Col1Rank,
rank() over(order by col2 desc) as Col2Rank, etc, etc

I truncate Table_Ranks and then insert all of those ranks for all the columns for caching purposes.  So my select statement had 50 rank() functions.  As my table grows, this operation went from taking a few minutes to a hour, and it keeps getting worse, and affecting the overall performance of that table which has a ton of writes/updates coming in at the same time.

My question is, how can I increase the performance of this operation? Should I select all the rows out of my table and store it in a temp table and do the ranks on the temp table?  Should I offload this to another server somehow?

My concern is that as I add more databases that will do the similar tasks, it will eventually make my server just unresponsive to user requests.

Thanks!

You would need an index on each column in order to avoid a sort of every row and column during the ranking process.  That would of course introduce significant overhead as scores are updated continuously.  Probably not an option unless you have a high-end hardware configuration.

The ranking processes could be offloaded onto a read-only copy maintained via log shipping on a different box.  That would avoid concurrency issues with hitting the live database system of record and provide more server resources for both the intensive batch process and live database.  If you are running Enterprise Edition, you could use a database snapshot on the same box as the source if your hardware is sufficiently sized.

You mention truncate and insert.  Just want to mention that you could also use SWITCH such that you truncate and insert into a staging table.  Once the staging load is completed, truncate Table_Ranks and then SWITCH from the staging table into Table_Ranks in a single transaction.  That way, Table_Ranks would never be empty.


Efficient way to fetch records since a given criteria I'm trying to implement a logic where the user can say give me n records since a given id#. E.g.

SELECT TOP (100) col1, col2, ... colN
FROM Table1
WHERE ID > @id
ORDER BY ID

Performance is the biggest issue here, especially when you get into nested loops for complex joins. I've looked at the new OFFSET-FETCH feature in SQL Server 2012 but they require you to specify a number rather than a WHERE clause. I wouldn't know the offset unless I count num rows beforehand.

Is there an alternate way to do this efficiently in SQL Server (2008 R2 and above)?

Update: Complete SQL as generated by EF Code first

-- Region Parameters
DECLARE @p__linq__0 BigInt = 60375518904121
DECLARE @p__linq__1 BigInt = 60375518904121
-- EndRegion
SELECT
[Project3].[Id] AS [Id],
[Project3].[C2] AS [C1],
[Project3].[C1] AS [C2],
[Project3].[C3] AS [C3]
FROM ( SELECT
    [Limit1].[Id] AS [Id],
    [Limit1].[C1] AS [C1],
    [Limit1].[C2] AS [C2],
    [Limit1].[TmId] AS [TmId],
    CASE WHEN ([Extent4].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
    FROM   (SELECT TOP (100) [Project2].[Id] AS [Id], [Project2].[TmId] AS [TmId], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2]
        FROM ( SELECT
            [Extent1].[Id] AS [Id],
            [Extent2].[TmId] AS [TmId],
            CASE WHEN ([Extent2].[TmId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
            1 AS [C2]
            FROM  [dbo].[TextMessages] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Tms] AS [Extent2] ON [Extent1].[Id] = [Extent2].[TmId]
            WHERE ( EXISTS (SELECT
                1 AS [C1]
                FROM [dbo].[Tmr] AS [Extent3]
                WHERE ([Extent1].[Id] = [Extent3].[TmId]) AND ([Extent3].[IsDel] = 1) AND ([Extent3].[UserId] = @p__linq__0)
            )) OR ([Extent1].[CreatedBy_Id] = @p__linq__1)
        )  AS [Project2]
        ORDER BY [Project2].[Id] DESC ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[Tmr] AS [Extent4] ON [Limit1].[Id] = [Extent4].[TmId]
)  AS [Project3]
ORDER BY [Project3].[Id] DESC, [Project3].[TmId] ASC, [Project3].[C3] ASC

Update:
The real bottleneck turned out to be network latency caused by EC2 servers.

To add a bit to what @SQLFox said if ID happens to be the clustered index then it should be blazing fast.  Even if it isn't the clustered index, if it is even included in an index then you are libel to at least get an index scan and go fairly quickly.

If I had to guess I would say it's your ORDER BY that is slowing you down.  With the ORDER BY in place it's going to have to check every row and check to see if it should be part of that top 100.  Without it SQL is just going to check enough rows until it gets 100 of them that match the criteria.


Chart creation in BIDS 2008 Due to low reputation point i am not able to include the picture.

I am trying to make u guys understand what i am asking for:

I had created a report. And that Report contains these fields and values:

  Name, YTDPlan, Actual, YearPlan
  Rev ,  4418  ,  4410 ,  24069

I have included the first row of this report, for that i have to create a line graph. Can anyone please tell me what fields should be taken on which axis.?

This really depends on what you want your graph to show (i.e. trending, inconsistencies, over time, over categories, etc.).  Some additional context would be helpful.

Looking at what you provided my first thought would be to show the data trending over time where one line represents "Actual" and the other "YTDPlan".  This would show an "actual vs. plan" comparison.  Using this assumption as an example, I would plot these 2 fields on the x-axis.  You can limit what you show over time in your query or a report parameter.  SQL Server Reporting Services (SSRS) will auto-create the values for you for the y-axis once the report is run (SSRS does this by analyzing the min and max bound values of your x-axis data).


What is the best way to replace a SQL Server database with an updated one? I have a SQL Server instance with 3 databases in it (SQL Server 2016 Web on Win Server 2012 r2). All databases have relations with each other. It is live and is feeding some web services.
I need to update 2 databases (or all 3) with the actual data. It takes around a week. So, I created a new VPS and a new SQL Server on it, I created same databases (with the same names and same filenames) on it and I filled them with the new data with no rush.
Now I want to learn what is the best, safest and fastest way to update the live environment.
I plan to do something like this:

Detach the new DBs on the new SQL Server

Detach the hdd from the new windows server.

Attach the hdd to my current live server.

Detach (or maybe delete ???) the old 2 or 3 databases from the live SQL Server.

Attach the new databases on newly attached hdd to old live server.

I have some questions about the logins, relations between the databases, etc...
I need to interrupt the services no more than some minutes. How can I proceed?
I would appreciate with any help.

Restore new database from backup (or attach) as yourDatabaseNew
Rename old database to yourDatabaseOld
Rename new database to yourDatabase
Drop the old database

To fix the logins problem make sure you created all logins and users on your temporary VPS.
Export logins from the existing server with sp_help_revlogin


Star schema dimension table design In a star schema, can a dimension table such as customer_dim have a column that references another dimension?
For example:
customer_dim

customer_id
first_name
last_name
email
primary_billing_address_dim

When dimensions have references to other dimensions, we call that a Snow Flake instead of a Star schema. Depending on how you query your model later on, one approach might be more efficient than the other. It is possible to transform a Snow Flake in a Star Schema by flattening everything in the dimension linked to the fact table(s) – customer_dim in your example.

Some tools (like Microstrategy) behaves very well with a Snow Flake model. On the contrary Oracle Business Intelligence Enterprise Edition will require you to model your business model as a star schema.

For ad-hoc query performance, both approach should be benchmarked. A pure star schema reduces the number of joins so it might be faster. In term of data quality and master data management, the snow flake is probably easier to handle.


@OneToMany mapping but only one is needed I have to create a new table to store version numbers of Terms&Conditions user have accepted and the time stamp of when they click accept. (This table only contains info related to user. Info about the T&C version itself is stored in another table)

That means user A could have accepted 3 versions of T&Cs that has been updated over time. The only version I care most about is the latest version the user has agreed to, but I still need to keep the history of accepted versions for integrity’s sake.

Scenario:

  I will already have the user entry in the database before the version records get stored.
  There can be users that has not agreed to any version of T&C.
  1 User may have accepted several version of T&C over time
  I want to retrieve only the latest version user has accepted along with the user object with minimal step taken.
  If possible when I delete the user I would like it if ALL accepted version records for that user also get deleted (foreign key on cascade delete or something)

How can I implement a clean solution for this? I have come up with two possibilities but I’m not sure if it’s any feasible.

Solution 1: @OnetoOne unidirectional relationship with User keeping the FK reference to the latest T&C record the user has agreed to. The T&C record will keep user_id as non primary and non unique FK field.

@Entity
@Table(name = “user”)
public class User {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name =“user”)
  Long id;

  @Column(name = “username”)
  private String username;

     .........

  @JoinColumn(name = “terms_record_id”)
  @OneToOne(cascade = CascadeType.ALL)
  private TermsRecord termsRecord;
  // joined on terms_record_id to get only the latest version accepted
}

And the entity to record version user has accepted

@Entity
@Table(name = “terms_record”)
public class TermsRecord {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = “terms_record_id”)
  private Long id;

  @Column(name = “agreed_on”)
  private Date agreedOn;

  @column(name =“version_no”)
  private String versionNo;

  // I’m not sure if it needs to have userId in here as well?
  @column(name = “user_id”)
  private Long userId;

   ........
}

And in the DB

create table user {
   user_id bigint as Primary Key,
   ...
   terms_record_id FOREIGN KEY references terms_record(terms_record_id);
   // to refer to the most updated version
}

create table terms_record {
   terms_record_id bigint NOT NULL,
   user_id bigint,
   PRIMARY KEY(terms_record_id),
   FOREIGN KEY(user_id)  REFERENCES User(user_id)            cascade on delete
   // to keep the records of terms user has agreed to
}

In the code, it will be something like:

User userA = getUser(user_id);
TermsRecord tr = new TermsRecord();
tr.setVersionNo(“1.0.5”);
tr.setAcceptedOn(....);
userA.setTermsRecord(tr);
save(userA);

So in this way, if user A had accept to terms version 1.0.3 before, it won’t delete the entry from terms_record table but it will pull a different versionNo when you retrieve userA from database.
Eg.

userA.getTermsRecord.getVersionNo == 1.0.5

And In terms_record table

  terms_record_id |     version_no
______________________________________
..........        |        .........
    3             |         1.0.3
    5             |         1.0.5
 ........         |        ..........

Solution 2

Unidirectional @OneToMany annotation in user class
With foreign key user_id stored in TermsRecord, and cascade on delete. Then retrieve the latest version manually.

Plan A:  Have a flag in the T&C table saying which is the latest.  This is clumsy to maintain, and may not meet the criteria of some entity being held by an older T&C.

Plan B:  Move the antiquated entries to another table.

Plan C:  The link (JOIN) from entity to T&C involves 2 columns:  which T&C, plus which version of it.  This may be the best version.


MySQL when using WHERE BETWEEN, query hits all partitions This is probably just me misunderstanding how MySQL partitions work, but I have a table defined with:

  `ID` int(11) NOT NULL,
  `target_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,

  PRIMARY KEY (`ID`, `created_at`),
  KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
  KEY `index_on_created_at` (`created_at`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY HASH(MONTH(`created_at`))
PARTITIONS 12

If I query the data with a single created_at timestamp:

select * from my_table where `created_at`= '2018-12-00 05:00:00' AND target_id in (6,7,8);

Then the explain knows to only hit 1 partition. This is correct.

However, if I query on a range:

select * from my_table where `created_at` BETWEEN '2018-12-00 05:00:00' AND '2019-01-04 04:59:59' AND target_id in (6,7,8);

The explain now hits all partitions. Is this a known limitation, or am I just doing something wrong?

Side note; judging by other "partitioning" questions, I expect a couple "partitioning doesn't help just index correctly" responses... I'm just trying it for myself and gathering some metrics so we can find the solution that performs best for our needs. (also partitioning the table, even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes; 2.3s vs 0.8s)

Edit 1:

I did make sure that both queries do hit the index_on_created_at index. The only difference is a 'non unique key lookup' versus an 'index range scan' on the index.

You have just found one of many reasons why PARTITIONing is mostly useless.

Even with your first SELECT, there is no performance benefit over what you could have with a non-partitioned table with a suitable index. j In particular:

INDEX(target_id, created_at)  -- in this order

The second SELECT shows how PARTITION BY HASH treats a range -- by looking in all partitions.  It has no choice but to do that.  Yeah, yeah, with a lot more smarts, it could notice that that date range should hit only two months.  Even so, non-partitioned with a suitable index would run faster and be simpler.

Other comments about the indexes:

KEY `index_created_at_target_id` (`created_at` desc, `target_id`)
KEY `index_on_created_at` (`created_at`)

has two issues:

INDEX(a,b), INDEX(a) -- you may as well toss the latter since the former can serve its purposes.
Until MySQL 8.0, DESC is ignored in INDEX definitions.  (Since you don't have an ORDER BY that depends on it, there is no harm done.)

If your goal is to understand what partitioning can/cannot do, I recommend my blog .

  even when it then checks all partitions, still cut the query time in half versus having 1 monolithic table with the same indexes

I dispute that.  Did you run with the Query cache off?  Did you run each test twice -- to avoid buffer_pool_ caching differences?  Actually, the key is probably "with the same indexes".  Almost always, when switching between partitioned and non-partitioned, the indexes need to be revised.

When partitioned, put the partition key later in each index where it is needed.  Or leave it out if the partition pruning suffices.
When not partitioned, non-range columns should be early in indexes.
When not partitioned, range columns (eg date range) should be late in indexes.

Why even a point query does not speed up with partitioning.  Notice how either way seems to be about the same amount of work:

Non partitioned:  BTree index that is used is, say, 3 levels deep.  Hop-skip-jump, there is the row you desire.
Partitioned:  First prune to the desired partition ("hop").  The, say, the BTree is only 2 levels deep ("skip-jump").

Ah.  Maybe you can discover a 5th use case for Partitioning.


Get row with maximum in one column The task: What ingredients has the maximum amount of content in Pancake?

The tables:
Recipe (Name, group)
Ingredient (Iname, price)
Composition (Name, Iname, Amount)

I tried this query:

Select Iname, Amount
From Composition
Where Name='Pancake'
Ordered by Amount ASC

But it does not return the correct result. What's wrong with it?

The typo in ORDER BY aside, you need descending sort order to get the ingredient with the maximum amount first.

SELECT Iname, Amount
FROM   Composition
WHERE  Name = 'Pancake'
ORDER  BY Amount DESC
FETCH  FIRST 1 ROWS ONLY;

Assuming Amount is defined NOT NULL.

FETCH FIRST 1 ROWS ONLY is the syntax defined by the SQL standard and supported by several RDBMS, but not all. Depending on your undisclosed RDBMS you may need a different syntax variant. Here is a pretty complete list:

https://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause


Domain Admins can't connect to SQL Server I have a Windows Server 2012 R2 domain controller and a database server (W2K12R2/SQL Server 2012).

As allowed usergroup I have given the group "domain-admins" sysadmin rights.

But somehow any domain-administrator can't connect to that SQL Server, but when I add the domain-administrator user directly as allowed user it does work.
The active directory does work. RDP-Login works. Am I missing something?

Does anybody know how to grant access to the whole group "domain-admins"?

The error message is:

Servername: SRV-DB
Error Number: 18456
Schweregrad: 14
Status: 1
Line of Code: 65536

In the event log it says (translated):

  Error checking the token based server access with infrastructure failure.

State is "1" . I log on to that db-server with RDP as an domain-admin. I have to explicitly start SSMS with "run as administrator". Then I can access the "localhost". If I leave out starting SSMS as admin then I can't do that. I thought being in the group "domain-admins" would be enough.

I found the answer to my problem in this answer by Remus Rusanu.

As an domain-admin connected to that SQL-Server-Host via Remote Desktop. I had to start SQL Server Management Studio explicitly "as an administrator". (right-click -> run as administrator).

If SSMS isn't started in administrator mode, the current login is not in "builtin\administrators"-group. That was the problem.

It comes from Windows user access control (UAC). So that is actually a security "feature".


Why does MySQL UPDATE with <= lock insert intention? I'd like to be able to update a table without locking inserts to avoid possible deadlocks. I thought I could use a query before the update to limit it so that the insert gap is not locked but it doesn't seem to work:

CREATE TABLE `foo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `value` varchar(45) NOT NULL
) ENGINE='InnoDB';
INSERT INTO foo (value) VALUES ('foo1');
INSERT INTO foo (value) VALUES ('foo2');
INSERT INTO foo (value) VALUES ('foo3');

Transaction 1

start transaction;
select MAX(id) INTO @max FROM foo;
update foo set value = NOW() where id <= @max;

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- waiting ...

Now transaction 2 is waiting for transaction 1 which I didn't expect.

My theory works if I subtract 1 from MAX:

Transaction 1

start transaction;
select MAX(id) - 1 INTO @max FROM foo;
update foo set value = NOW() where id <= @max;

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected

So why doesn't the first example work and is there any way to accomplish the update all the way up to MAX(id) without blocking inserts using a regular auto-increment primary key?

Tested using MySQL 5.7. Below is the SHOW ENGINE INNODB STATUS\G output for the first case (Transaction 2 blocked):

------------
TRANSACTIONS
------------
Trx id counter 6214932
Purge done for trx's n:o < 6214930 undo n:o < 0 state: running but idle
History list length 1836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422057928858336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6214931, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 369, OS thread handle 140582776452864, query id 7104 localhost root update
insert into foo (value) values ('foobar')
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4135 page no 3 n bits 80 index PRIMARY of table `test`.`foo` trx id 6214931 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 6214930, ACTIVE 15 sec
2 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 5
MySQL thread id 367, OS thread handle 140582775654144, query id 7102 localhost root cleaning up
Trx read view will not see trx with id >= 6214930, sees < 6214930

I believe the issue is due to the UPDATE using "next-key locking" as evidenced by "asc supremum" in the engine status conflicting with "insert intention locking" of the INSERT as evidenced by "lock_mode X insert intention waiting" in the engine status. It seems the only way around this is to effectively disable gap locking entirely by using READ COMMITTED transaction isolation level for Transaction 1 or innodb_locks_unsafe_for_binlog.

Transaction 1

set transaction isolation level read committed;
start transaction;
update foo set value = NOW();

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected

Since using READ COMMITTED disables all gap locking (including next-key locking) in favor of index record locking it is not necessary to use where id <= @max so this can now be removed. I do not believe it is possible to accomplish this update without blocking the insert using REPEATABLE READ.


Can I replicate a database from SQL Server 2012 to SQL Server 2014 (with 2014 distributor)? I am planning to have database replication running from SQL Server 2012 (publisher) to SQL Server 2014 (both distributor and subscriber) -- pull replication.

Has anyone done this type of cross-version replication and knows if there are any issues with it?

Thank you!

If you are using Transactional Replication then yes, this configuration is supported.

This is covered in Using Multiple Versions of SQL Server in a Replication Topology:

  For all types of replication, the Distributor version must be no
  earlier than the Publisher version. (Frequently, the Distributor is
  the same instance as the Publisher.)

Since your Distributor version is a later version than your Publisher version, you are good here.

  For transactional replication, a Subscriber to a transactional
  publication can be any version within two versions of the Publisher
  version. For example, a SQL Server 2000 Publisher can have SQL Server
  2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server
  2000 Subscribers.

Since your Subscriber is within two versions of your Publisher version, you are good here.

However, this configuration is not supported if you are using Merge Replication since your Subscriber will be a later version than the Publisher:

  For merge replication, a Subscriber to a merge publication can be any
  version no later than the Publisher version.

If you have anymore questions, please let me know.  I hope this helps.


Map a Login to a User that was Created Without Mapping to a Login This question has been asked before, but I didn't see any answer (https://stackoverflow.com/questions/6468813/assign-a-login-to-a-user-created-without-login-sql-server)

How does one map a login to a user, when the user was originally created using the "SQL User without login" User Type option in the GUI? I'm using SQL Server 2012

By selecting SQL user without login within SSMS' create user dialog, this is the T-SQL that is generated (with no other options besides the user name supplied):

USE [AdventureWorks2012]
GO
CREATE USER [User1] WITHOUT LOGIN
GO

If you were to create a login and attempt to alter the user with the new login, like the following attempt:

create login TestLogin1
with password = 'p@$$w0rd';
go

use AdventureWorks2012;
go

alter user User1
with login = TestLogin1;
go

You will get the following error:

  Msg 33016, Level 16, State 1, Line 8
  The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

Because of that, I don't think you can link up a login to this type of user without recreating it.  It should be relatively painless, as you could just script out the user, but change the parameters from WITHOUT LOGIN to whatever login you want it to be mapped to.

EDIT: As pointed out by Aaron below, ensure that you're taking note of permissions and role memberships before making any changes.  You'll want to ensure that you can get back to the permissions state you currently are in, if that's the desired behavior.


Setting up SQL Server Developer Edition I just installed SQL Server Developer Edition and SQL Server Management Studio, how can I configure the server? I want to configure it to have server name localhost-sql-server, with a login/password. I found the configuration manager but not sure where I can modify this.

For your first question:

  I want to configure it to have server name localhost-sql-server

This is normally done during installation.  From the "Install SQL Server from the Installation Wizard (Setup)" article:

  On the Instance Configuration page, specify whether to install a default instance or a named instance.

You would want a named instance.  Instance names are limited to 16 characters and cannot contain hyphens (thanks to Dan Guzman for pointing that out), so you unfortunately can't name it "localhost-sql-server" per your original request.

I typically have multiple versions of SQL Server installed on my laptop, for different development projects, and name them "SQL2017" or "SQL2016" per the version installed.  You may want to go with that route.

Once that's done, you can connect to it using the server name ".\SQL2017" (the computer name is part of the connection, which you can replace with ".\" for local connections).

I would recommend uninstalling SQL Server and setting it up this way from the start.  I'm not aware of a simple way to change a default (standalone) instance to a named instance.

For your second question:

  with a login/password

That's also normally done during installation, here's a relevant quote from the same docs page:

  Use the Database Engine Configuration - Server Configuration tab to specify the following:

  Security Mode - Select Windows Authentication or Mixed Mode Authentication for your instance of SQL Server. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.

You would want mixed mode.  Once the server is set up, you could connect with the "sa" account and create a login and user with the desired password:

CREATE LOGIN YourUserName
    WITH PASSWORD = 'a strong password';
GO
CREATE USER YourUserName FOR LOGIN YourUserName;
GO

Good luck!


Schedule Query and Export Is it possible, to schedule a simple Query to run Daily and export the received data into an excel file?

This can easily be done through an SSIS package and scheduled with SQL Server Agent. See this link for a tutorial/demonstration on the task: http://www.codeproject.com/Articles/402958/SSIS-Package-Export-Data-from-Database-Daily-to-Ne


Why does sql configuration manager show 32 and 64 bit options when only 64 bit version is installed? I have installed sql server 64 bit.
Why does sql configuration manager show 32 and 64 bit options when only 64 bit version is installed?

Because you could potentially have some old 32 bit instance on that computer. The config manager tool is designed to allow you to configure instance of that version and lower.
One could argue that the tool shouldn't show that node if there isn't any 32 bit instances on that computer, but that would be a wish for MS to do: https://feedback.azure.com/forums/908035-sql-server


Php code to update the profit column in database in every 1 hour I'm currently working web based hyip software but the problem I'm facing there is that I need a php code that will be increasing the user profit in every 1 hour but I still don't get it.
In that application, I have a table called ph_list whereby all deposit made by the user will be stored.
In that table, I have a column called the tracking_id(which is unique), Amount, Percentage...

But after making deposit, I want the amount donated by the user should be increasing in every 1 hour , which will be in percentage column....

Please I need someone to shield more light on this please!!

Never update it.  No UPDATE, noTRIGGER`.  Simply compute it based on the current time minus the time at which the user's entry was established.

There are standard formulas for "compound interest"; MySQL has all the math formulas needed to do the arithmetic.


Does PostgreSQL handle case-insensitive database as good as others? First things first, please forgive me if my question seems so naive (I am not a DBA).
I look up to see how can I make PostgreSQL case-insensitive and I found the Nondeterministic Collations section of the official docs useful.
I have tried the following but there was no luck.
CREATE COLLATION ci_fa (provider = icu, locale = 'fa-IR-x-icu', deterministic = false); -- also locale = 'fa-IR' and 'fa-IR.UTF8'

I found this answer but the locale used there, is not based on what I am looking for. Also, the example in the docs reads as:
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

This works fine but I faced with a wired behavior in sorting. If I had a non-English character in my order column the result would be different. When I have a, A, b and the non-English char, the result is a, A, b. And when I have a, A, B and the non-English char, the result is A, a, B.

On the other hand, reading a lot of articles and questions made me wondered about the following questions?

Having case-insensitive data impact the performance, so should I use it or not? Considering the following facts:

99% of my data has no casing. Our language is simpler so there is no Lower/Upper case in letters :)
I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect Full-Text.
I don't like any of the suggested solutions here (How to make “case-insensitive” query).
Although I use EF Core 5, I have DB functions to handle complex queries.

I am at the early stage of my project and for the first time, I decided to migrate to PostgreSQL from SQL Server. Should I use MySQL instead?

I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).
I need Geolocation.
I need Full-Text.
And obviously, performance.

When I have a, A, b and the non-English char, the result is a, A, b. And when I have a, A, B and the non-English char, the result is A, a,
B.

It's an effect of the deterministic collation property being false. There is no tie-breaker between equivalent strings, so a may come out before A or the opposite. Also sorts in database are generally not stable, because in relational theory, rows have no inherent order so sticking to the "original order" makes no sense.
If you don't like that, you may add a second order with a deterministic collation, for instance
ORDER BY field COLLATE "non-deterministic-collation", field COLLATE "C"

I use Full-Text indices for the search in the parts that users can input English words. I don't know how my collation will affect
Full-Text.

It doesn't. The full text search parser is mostly influenced by the lc_ctype property of the database. There is even a specific error associated to forcing a collation when extracting a tsvector from a string:
=> select to_tsvector('abc') collate "C";
ERROR:  collations are not supported by type tsvector

I need Persian collation (Not sure if I do, the collation in the docs handles the sorting fine but I did not test storing it - in CentOs).

When a database is created, it does inherit a collation from the template database, or get it from the lc_collate or locale clause if specified explicitly. That's the default collation for all sorts and string comparisons in this database.
If you use Linux in an UTF-8 encoding with fa_IR locale, the lc_collate of your database is probably fa_IR.UTF-8, so it does use a Persian collation implicitly. There is no clear indication in your question that your application really needs to create custom collations beyond that.
The details for creating collation can be fine here and here and to create Persian collation the following can be used.
CREATE COLLATION the_name (provider = icu, locale = 'fa-IR@colStrength=secondary', deterministic = false);


How to display query result in rockmongo by using execute method I executed a command in mongo.exe

> db.tablebusiness.find({"_id": "the-simmons-paradise__41.85_-87.88"});

I got results:

Now I try similar command in rockmongo.
If I execute

db.tablebusiness.find(
    {"_id": "the-simmons-paradise__41.85_-87.88"}
    );

Result:

{
   "retval": null,
   "ok": 1
}

Basically it seems to tell me that the result is ok or something like that? I am not sure.

If I elaborate:

var cur = db.tablebusiness.find(
{"_id": "the-simmons-paradise__41.85_-87.88"}
);
cur.forEach(function(x){print(tojson(x))});

Result:

{
   "retval": null,
   "ok": 1
}

Same problem.

If I do:

function () {
   return db.tablebusiness.find({"_id": "the-simmons-paradise__41.85_-87.88"});
}

What does it mean by hello.tablebusiness -> undefined is beyond me. As you see from above, I successfully execute the query just fine in mongo.exe

Looks like rockmongo has very limited feature. I wonder how to actually see result. How to execute random mongodb command in rockmongo and observe the result.

The find() method returns a cursor object. In order to view the first returned record in the cursor you must call the next() method.

function () {
  return db.tablebusiness.find({
    "_id": "the-simmons-paradise__41.85_-87.88"
  }).next();
}


Using NAS device as SQL server backup target I have used NAS devices as backup targets for SQL server databases. I have used them as file shares which are mapped to servers holding databases. Thing is that our current NAS supports iSCSI protocol and was wondering if I can use NAS LUNs (mapped as drives) in the SQL server virtual servers and take advantage of the protocol? Anyone who has tried this before or know about some difficulties/deficiencies using this method. Thanks.

Drive mapped iSCSI targets work perfectly for SQL Server backup destinations.

Map the iSCSI target into your VM as a drive, assign a drive letter inside the VM, and just do normal backups as you would to any other target.

I use that exact process for my clustered SQL Servers.


sql web-application Connection String for member of a domain group In my testing environment, I have a local sql express (2008R) server instance.  I've created a domain group and given it a login in the database.

But now, how do I consctruct the connection string for my web-application?  Do I give it the username and password of one of the members of the group?

And, what do I put for a "user" in the Database security section?  The group itself doesn't have a password.

Do I have to add the service account from the application pool to the AD group?

Here is what I have for a connection string; but this allows all connections, it seems:

<add name="development" connectionString="server=myserver\sqlexpress;
    database=mydatabase;Persist Security Info=True;Integrated Security=SSPI;
    Trusted_Connection=True; Pooling=True" providerName="System.Data.SqlClient"
/>

You need to configure the IIS Application Pool to use the identity (domain user) you created.

My example below shows the default, out-of-the-box configuration.  You'd need to choose 'Configurable' and specify the username and password there.

Then you need to configure the site to use the AppPool.

The connection string would then be:

server=myserver\sqlexpress;database=mydatabase;Persist Security Info=True;
Integrated Security=SSPI;Trusted_Connection=True; Pooling=True

Just like you have it.


Mysql cannot import from text file I have a table called person setup like this

(person_id SMALLINT UNSIGNED,
fname varchar(20),
lname varchar(20),
gender  enum('M','F'),
birth_date date,
race enum('B','W','A','J'),
ethnicity varchar (60),
origin varchar(20),
profession varchar(40),
CONSTRAINT pk_person PRIMARY KEY (person_id));

and another called words setup like this

(person_id Smallint unsigned,
word_id smallint unsigned,
word Varchar(20),
format varchar(40),
title varchar(40),
CONSTRAINT pk_words PRIMARY KEY (word_id),
CONSTRAINT fk_words FOREIGN KEY (person_id)
    REFERENCES person (person_id));

and i tried to import first like this

LOAD DATA LOCAL INFILE "samplepath"
INTO TABLE words
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'

and got

error 1366 incorrect integer value: '""' for column 'word_id' at row 3

and

error 1452 cannot add or update a child row: a foreign key constraint fails ('words', CONSTRAINT 'fk_words" FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))

but one value was inputted, and could be if i just added one at a time.

so then i tried

LOAD DATA LOCAL INFILE "samplepath"
INTO TABLE words
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r'
(person_id, @worder, word, format, title) set word_id =  if(@worder = "", null, @worder);

but got the same errors in a different quantity.

Here's what my data looks like. Any idea whats wrong? I'm new to SQL

"person_id","word_id","word","format","title"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"
1,"","any","interview","breakfast club"

Also I tried using null, quotes, no quotes, all different types of formatting the data that you could imagine

error 1366 incorrect integer value: '""' for column 'word_id' at row 3

This error is logical, basically, an empty string is not a number. That's the first problem.

Also I tried using null, quotes, no quotes, all different types of formatting the data that you could imagine

The problem with this is that if your word_id column is not nullable, you can't insert a null value into it. Also, formatting invalid data doesn't make it valid.

error 1452 cannot add or update a child row: a foreign key constraint fails ('words', CONSTRAINT 'fk_words" FOREIGN KEY ('person_id') REFERENCES 'person' ('person_id'))

This error is normal. This is what Foreign Key constraints are for. Basically, what they do is make sure that you can't insert invalid data when referencing some other data
In this case, you reference a word (As far as I can tell), but you don't have words that have the ID value '' (empty string), so it doesn't allow you to insert the row with an incorrect reference.
Now that the errors are explained, there are two solutions:
1: Allow your word_id column to be null in your table schema. (This is possible even with foreign key constraints, however it might violate your database design). This way, you can insert a NULL value into the word_id column instead of an actual word_id.
2: Fix your data. Basically, you've built your database in such a way that you don't allow an empty or null value for the word_id column, which coincidentally means that your CSV data is invalid in the way that it is an empty string.
You need to update the CSV data to have a correct reference to the word_id column for your data to be 'correct' again.
The so-called 'correct' approach according to how you've set up your database is the second one, where you fix the data. However, both solutions will work. Just make sure to document the changes to your database if you chose solution number 1 because the application which uses the data from your database might not be expecting NULL values and might break because of this.


Imposssible to expand a database in SQL Server Management Studio I am experiecing a strange problem. I have a SQL Server 20008 R2 instance that recently (after years of running smoothly) started to become slow.

One symptom that I notice is that when working from SQL Server Management Studio, on the server itself and logged as sa, I click on the + symbol near the database icon, after clicking I get an error and after OK I see that the  "+" is disappeared, you can see in this screenshot how it looks like after I tried on four databases:

The error I get is (in Italian):

Impossibile accedere al database MyDatabase. (ObjectExplorer)
------------------------------
Percorso programma:

   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

It means "Impossible to access the database MyDatabase".

This is just a symptom as I said, the main issue is that from a client the connection is slow, very slow but working.

I restarted the SQL Service service for this specific instance.

This is a named instance, I have also the default instance that works fine.

Can anyone give me a pointer to a solution please?

Open a new query window and run SELECT * FROM sys.databases.  See if the database in question is listed in the results.

If it is, see if you can access it by running USE MyDatabase.  If that works, see if you can pull up a list of tables by running SELECT * FROM sys.tables.

You may want to run DBCC CHECKDB MyDatabase and see if any problems are detected.


Are the index here will suffice to make the query faster? This is the continuation of the question, What column/s do I need an index for my queries?, as Rick James advices me to ask a new question.

Here is the simpler code from my queries, and how will I implement the index in the column based on from what I understand while studying about it.
Note: fields and values are changed for example purposes only.

1) INDEX in id

SELECT a, b ... FROM users_table WHERE id = '1'

2) INDEX in user_email

SELECT a, b ... FROM users_table WHERE user_email = 'me@email.com'

3) INDEX in user_link

SELECT a, b ... FROM users_table WHERE user_link = 'www.somelink.com'

4) INDEX in user_recover_link

SELECT a, b ... FROM users_table WHERE user_recover_link = 'code'

5) INDEX in id, side note: I may try Rick James' answer related for pagination.

SELECT a, b ... FROM posts_table WHERE id > 0 ORDER BY date DESC LIMIT 0, 10

6) INDEX in id

SELECT a ... FROM posts_table WHERE id > 0

7) INDEX in category

SELECT a, b ... FROM posts_table WHERE category = 'some-category'
     ORDER BY date DESC LIMIT 0, 10

8) INDEX in category

SELECT a, b ... FROM posts_table WHERE category = 'some-category'

9) INDEX in post_url

SELECT a, b ... FROM posts_table WHERE post_url = 'some-url'

10) INDEX in user_url

SELECT a, b ... FROM users_table WHERE user_url = 'some-url'

11) INDEX in user_id

SELECT a ... FROM posts_table WHERE user_id = '1' ORDER BY date DESC

12) INDEX in user_id

SELECT a ... FROM posts_table WHERE user_id = '1'

13) INDEX in msg_receiver_id, msg_sender_id, date

SELECT a, b FROM msgs_table m1
    WHERE (m1.msg_receiver_id = '1' OR m1.msg_sender_id = '1')
      AND EXISTS (SELECT max(m2.date) maxDate FROM messages m2
            WHERE m1.msg_receiver_id = m2.msg_receiver_id
              AND m1.msg_sender_id = m2.msg_sender_id
            GROUP BY msg_receiver_id, msg_sender_id
            HAVING m1.date = maxDate)
    ORDER BY m1.date DESC

14) INDEX in id

SELECT a, b ... FROM users_table WHERE id IN ('1', '2', ...)
    ORDER BY FIELD (id, '1', '2', ...)

15) INDEX in msg_url

SELECT a, b ... FROM msgs_table
    WHERE msg_url IN ('some-url-1', 'some-url-2')
    ORDER BY date ASC

16) INDEX in msg_receiver_id, msg_status

SELECT a, b ... FROM msgs_table
    WHERE msg_receiver_id = ?
      AND msg_status = 'unread'

NOTE: Since id is a PRIMARY KEY I do not need to use index anymore. Is this an okay to increase the queries speed performance?

1,2,3,4,9,10 -- all the same pattern; all good.  The PRIMARY KEY qualifies as an INDEX (and UNIQUE).

The PK is often faster than a secondary key.  So, do not add INDEX(id) to a table that already has PRIMARY KEY(id).  There can be only one PK on a table.

6 - good

7,11 - Needs improvement:  Composite INDEX(category, date), INDEX(user_id, date)

8,12 - handled by indexes for 7,11

14,16 - OK as you state.

edit 15 - INDEX(msg_url, date)

13 - discussed in the other thread

To summarize:

users_table:

PRIMARY KEY(id),
INDEX(user_email),
INDEX(user_link),
INDEX(user_recover_link)

post_table:

PRIMARY KEY(id),
INDEX(category, date),
INDEX(post_url),
INDEX(user_url),
INDEX(user_id, date),
INDEX(msg_receiver_id, msg_sender_id, date)

msg_table

PRIMARY KEY(id),  ??
INDEX(msg_receiver_id, msg_status),
INDEX(msg_url, date)


How to know who restarted and what caused SQL Server I need to know Who restarted SQL Server or What caused automatically to restart SQL Server, I have searched Event viewer for events 17163, 6005, 6006 , 6008 and 6009, but unsuccessful it had returned null,  i don't want information like last restart time where i can get it create_date from sys.databases of tempdb or start_time from sys.dm_os_sys_info.

What about setting up a server audit with this event : "Audit Server Starts and Stops Event Class" > "The Audit Server Starts and Stops event class occurs when the Microsoft SQL Server service state is modified". Never tried it, but you should be able to knwow which "LoginName" modified the SQL Server state. "EventSubClass" 1 is regarding shutdown.

https://msdn.microsoft.com/en-us/library/ms189244(v=sql.100).aspx

https://msdn.microsoft.com/en-us/library/cc280663(v=sql.100).aspx


Generate SQL Table DDL from a View How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql

(1) can remove primary key/constraint,

(2) don't care if null/not null added (would be nice to have however)

I have proposed sample question/answer below, feel free to code review, edit/optimize for other data types not included in table in future, special cases/issues/exceptions may have not accounted for. Is there any open source code for this by the way?

Table 1 and 2:

create table dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(255),
     ZipCode varchar(9)
)
create table dbo.CustomerTransaction
(
     CustomerTransactionId int primary key identity(1,1),
     CustomerId int,
     SalesAmount numeric (10,2),
     PurchaseDate datetime
)

View:

create view dbo.CustomerTransactionVw
as
select
     ct.CustomerTransactionId,
     ct.SalesAmount,
     ct.PurchaseDate,
     cust.CustomerId,
     CustomerName,
     cust.ZipCode
from dbo.CustomerTransaction ct
inner join dbo.Customer cust
  on cust.CustomerId = ct.CustomerId

Intended Table Create Script:

create table dbo.CustomerTransactionBigTable
(
     CustomerTransactionId int identity(1,1),
     CustomerId int,
     SalesAmount numeric (10,2),
     PurchaseDate datetime,
     CustomerId int,
     CustomerName varchar(255),
     ZipCode varchar(9)
)

Current Proposed Solution:

declare @TableCode varchar(max) = 'create table dbo.CustomerLargeTable
( ' +
    (select STUFF((
    SELECT ',
    '
    + c.name + ' ' +
case
    when t.name like '%char%' then t.name + '(' + cast(c.max_length as varchar(10)) + ')'
    when t.name like '%numeric%' or t.name like '%decimal%' then t.name + '(' + cast(c.precision as varchar(10)) + ',' + cast(c.scale as varchar(10)) + ')'
    else t.name
end
FROM .sys.columns c
inner JOIN sys.types t
    on t.user_type_id = c.user_type_id
    and t.system_type_id = c.system_type_id
where c.object_id = object_id('CustomerTransactionVw') and is_identity = 0
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))
+ '
)'

print @TableCode

Note: I am only interested in table structure DDL, not data DML

https://stackoverflow.com/questions/7254380/how-to-find-the-derived-column-types-of-a-view-in-sql-server-2005

Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

  This dynamic management function takes a Transact-SQL statement as a
  parameter and describes the metadata of the first result set for the
  statement.

--Demo set up
DROP TABLE IF EXISTS [dbo].[Customers]
CREATE TABLE [dbo].[Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ContactName] [nvarchar](30) NULL,
    [ContactTitle] [nvarchar](30) NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [Phone] [nvarchar](24) NULL,
    [Fax] [nvarchar](24) NULL
) ON [PRIMARY]
GO

DROP VIEW IF EXISTS CustomerView
GO
CREATE VIEW CustomerView
AS
SELECT *
FROM dbo.Customers
GO

-------------------------------
--The solution
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX)
    ,@cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerView', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
SET @sql = replace(@sql,',',',' + char(10))
print @sql

Results:

CREATE TABLE #tmp(CustomerID nchar(5),
CompanyName nvarchar(40),
ContactName nvarchar(30),
ContactTitle nvarchar(30),
Address nvarchar(60),
City nvarchar(15),
Region nvarchar(15),
PostalCode nvarchar(10),
Country nvarchar(15),
Phone nvarchar(24),
Fax nvarchar(24));


Try catch equivalent in Postgres Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.

The equivalent of TRY-CATCH error handling in PostgreSQL is a block of code in this way:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

Have a look at Postgres docs about Trapping errors

If you want to use it in your functions, keep in mind it can only be used inside PL/pgSQL functions.


Altering a table to add a New Column vs Create table with New Column I have Table A which i'm tasked to add a new column is_fully_complete bit(1), and i'm thinking:

Would it be better to

a.) Create a new table Table_A_new like Table_A and add the new column there before inserting the data from Table A?

or

b.) rely on Alter Table Syntax to add a new column.

i tried option B but on my local machine and it's taking too long for a 10-min time frame. Table A by the way has about 1.32 million rows, and is running an InnoDB engine. We're using Percona Mysql 5.5 if it helps

EDIT: i forgot to mention we're also using Ubuntu 10.04 on our servers along with Percona Mysql 5.5

This is a question which gets asked regularly in some form or other here. Take a look at my reply to a similar one not so long ago.

Basically, there are two main tools out there for this - either Percona's pt-online-schema-change (available here) or Shlomi Noach's oak-online-alter-table (from here).

There are a couple of other possible solutions mentioned which may or may not be more applicable to Windows (you didn't mention your OS) but you do specifically mention MySQL 5.5 which appears to preclude this (unless Percona have back-ported it?).

The usual disclaimers apply - about testing before implementing in production.

[EDIT - in response to OP's comment]

With replication, the situation is, apparently, the following (from here).

  REPLICATION

  If you use pt-online-schema-change to alter a table on a server with
  slaves, be aware that:

The tool is not tested with replication
The tool can break replication if not used properly
Although the tool sets SQL_BIN_LOG=0 by default (unless --bin-log is specified), triggers which track changes to the table being altered
still write statements to the binary log
Replicaiton will break if you alter a table on a master that does not exist on a slave
Update slaves first if columns are being added or removed
Do not use this tool in production before testing it

So, not exactly confidence inspiring, but not a total no-no either. Just follow the recommendations and test on a non-production machine.

Another possibility would be to use the relatively new online DDL capacity introducted in MySQL 5.6. From here, check a report of someone who tried to use Percona 5.6's online DDL - but, they apparently didn't apply changes to slaves before masters. All I can say is follow the instructions, test and see how it goes, whatever method you eventually decide on.

Did you check out the table here (from my original post to which I referred you)? It appears to be very similar situation to yours - add a column to a table with ~1,100,000 records - no locking, done in 30 seconds with pt-online-schema-change? Might be good for you? :-)