Updating column in underlying table causes massive transaction log growth I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry on the clustered index when associated with a large amount of records (~2.5 million in this case), the indexed view which in turn creates a massive entry in the transaction log. I'm wondering what I could try to avoid this massive entry into the transaction log, without disabling the index on the view? I know the field that is being updated is not a part of any index, it is just part of this view. My google-fu is failing me at present, so wanted to get this up while I continue searching to see if anything pops up here.

CREATE TABLE Big ( Id INT IDENTITY, SmallId VARCHAR(3) NOT NULL ); -- 11 million rows, 2.5 million where SmallId = 'xxx'
CREATE TABLE Small ( Id VARCHAR(3) NOT NULL, [Description] VARCHAR(128) NOT NULL ); -- 12k rows
CREATE VIEW [View] WITH SCHEMABINDING AS
    SELECT Id, b.SmallId, s.Description AS SmallDescription
    FROM Big b
    INNER JOIN Small s
        ON b.SmallId = s.Id;
CREATE CLUSTERED INDEX [PK_View] ON [View]([Id]);
UPDATE Small SET [Description] = 'x' WHERE Id = 'xxx'; --- massive amount of transaction log entries for PK_View

One idea might be to create a second view that doesn't persist 2.5 million copies of the same value.

CREATE VIEW dbo.innerview
WITH SCHEMABINDING
AS
  SELECT b.Id, SmallId = s.Id
   FROM dbo.Big AS b
   INNER JOIN dbo.Small AS s
   ON b.SmallId = s.Id;
GO
CREATE UNIQUE CLUSTERED INDEX PK_View On innerview([Id]);
GO

Then you can create a view around this, without an index:

ALTER VIEW dbo.View
WITH SCHEMABINDING
AS
  SELECT v.Id, v.SmallId, s.description AS SmallDescription
    FROM dbo.innerview AS v
    INNER JOIN dbo.Small AS s
    ON v.SmallID = s.Id;
GO

Now you can change the small description and it won't affect all those rows, and the queries against dbo.View should exhibit about the same performance as your current queries against the indexed version. Your indexed view shouldn't contain many, many copies of a value that is likely to change.

Of course this view doesn't need to be indexed at all IMHO. A lot of people think indexing a view magically speeds up the query; in a lot of cases it actually just slows down your overall workload. What is the indexed view gaining you that the clustered index on the source table (Big) doesn't already do? Transaction log madness aside, have you compared queries against the indexed view with a simple query like:

SELECT
  Id, -- please be specific, both tables have a column named Id
  b.SmallId, s.Description AS SmallDescription
FROM dbo.Big AS b
INNER JOIN dbo.Small AS s
    ON b.SmallId = s.Id;

?


does max_connect_errors not check Aborted_connects? In MySQL (5.5) which status var reflects the current number of connection errors that are compared against max_connect_errors by the server? I thought it was Aborted_connects but in my testing I can still connect to server after driving aborted_connects (in succession) well above max_connect_errors...

Asked differently, does mysql server check aborted_connects against any threshold variable? Is any behavior dependent on value of that status variable (or is just FYI for DBAs)?

EDIT 1

to clarify, I am less interested in how to reach max_connect_errors, and more in whether having a high aborted_connects will have any negative consequences.

For context:
we're using a layer 4 (TCP) load balancer to distribute JDBC connections of appservers' db connection pools across a cluster of MySQL read-only slaves. The load balancer (AWS ELB) has a TCP-based health check feature - it periodically checks health of the db servers by establishing a TCP connection with db server's mysql port. Since the LB does not speak 'JDBC', literally all it does is create a TCP connection and immediately disconnect. Each of these connections, however, increment aborted_connects on mysqld. I see no other side-effects, but wonder whether running up aborted_connects will eventually have negative consequences (for example, mysqld will ban the LB's IP, thus causing health check failures)

Thank you,
-nikita

max_connect_errors is on a per-host basis, so I doubt that it checks a status variable such as Aborted_connects.

This variable probably does not apply if you are testing using 'localhost' (which does not use TCP), for what happens if you lock localhost out? No one could connect (assuming a single root user and not being able to log in root remotely)!

So I felt like digging into the source on this one. In the file 'sql/hostname.cc' is a function ip_to_hostname. This function has the following comments (emphasis mine):

  Resolve IP-address to host name.

  This function does the following things:
      - resolves IP-address;
      - employs Forward Confirmed Reverse DNS technique to validate IP-address;
      - returns host name if IP-address is validated;
      - set value to out-variable connect_errors -- this variable represents the
        number of connection errors from the specified IP-address.

  NOTE: connect_errors are counted (are supported) only for the clients
    where IP-address can be resolved and FCrDNS check is passed.

There is also this comment with regards to localhost (or loopback ip like 127.0.0.1):

*connect_errors= 0; /* Do not count connect errors from localhost. */

Furthermore, the only time connect_errors is incremented is in the function 'inc_host_errors' of the same sql/hostname.cc file. This is only called from the sql/sql_acl.cc file. It looks like these are some of the situations:

1) There is an error with the packets (packet lengths don't match). This is also probably why the documentation says:

  If you get the Host 'host_name' is blocked error message for a given host, you should first verify that there is nothing wrong with TCP/IP connections from that host. If you are having network problems, it does you no good to increase the value of the max_connect_errors variable.

2) Invalid native password (function is native_password_authenticate)

3) Invalid old-style password (function is old_password_authenticate)

I might have missed a few things, but ultimately, max_connect_errors has nothing to do with aborted connections, but rather networking issues or invalid passwords.

For completion sake, I will mention I was looking at the Percona 5.5.15-rel21 source code.

So, what does Aborted_connects have to do with it? Well, according to this documentation, it gets incremented anytime:

A client does not have privileges to connect to a database.
A client uses an incorrect password.
A connection packet does not contain the right information.
It takes more than connect_timeout seconds to get a connect packet.

So, Aborted_connects is just an indicator for a DBA to know there might be a problem (eg: if it increases dramatically over a period of time, someone might be attempting to hack into the server, but you should be protected by max_connect_errors in this case).

If yours increases steadily from the healthchecks, you can still use it if it spikes abnormally.


Trying (and failing) to create a DVD cataloging database table design I've been trying (and failing) to create a DVD cataloging database. My initial design has a whole bunch of many to many relationships. Consequently, my attempts to normalize it has produced a whole bunch of Composite Entities.

It's a huge mess and I think I've almost certainly done something wrong somewhere.

Right now, it looks like this:

movies(id, tmdb_id, imdb_id, title, summary, release_date, runtime, poster_path, rating)
series(id, tmdb_id, imdb_id, title, summary, start_date, end_date, poster_path, rating, status)
seasons(id, tmdb_id, imdb_id, title, summary, season_number, poster_path, FK series_id)
episodes(id, tmdb_id, imdb_id, title, summary, airdate, still_path, episode_number, FK season_id)

genres(id, genre_name)
people(id, tmdb_id, imdb_id, name)
jobs(id, job_name)

movie_genres(FK movie_id, FK genre_id)
series_genres(FK series_id, FK genre_id)
movie_crew(id, FK movie_id, FK person_id, FK job_id, character_name)
season_crew(id FK season_id, FK person_id, FK job_id, character_name)
episode_crew(id, FK episode_id, FK person_id, FK job_id, character_name)

discs(id, type, features, status)
case(id, name, status, location FK disc_id)

disc_movie(FK disc_id, FK movie_id)
disc_episode(FK disc_id, FK episode_id)

The problem is, I'm not sure how to fix it.

I know the first four entities have a lot if common attributes. I don't know if I can rip them out and create a new entity and then have the four inherit from the common entity (????) or...?

Assuming I'm on the right track with that, I don't really know how to model it either.

Per Martin Fowler, there are basically three ways to model inheritance in a relational database:

Single Table Inheritance: everything goes into one table and you use nullable columns that may or may not be present for the
Class Table Inheritance: base class goes in one table with one-to-one relationships with sub-classes
Concrete Table Inheritance: each concrete class is given a distinct table (what you have now)

Some good answers here going into more detail:

https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database
https://stackoverflow.com/questions/1567935/how-to-do-inheritance-modeling-in-relational-databases

Which you choose depends on exactly what you want to do, and there is not always one right answer, although you could probably lean towards class table inheritance as a "default" that is probably correct, and then work from there.

I'd suggest using class table inheritance you generalise movies and episodes into a "work" (or whatever terminology you think best) and then have movie or series as a lightweight entitity used to classify these "works".

This is all just quick suggestions on how you might do it- remember, there is not necessarily One and Only One Right Way, it depends like Evan says on what exactly your use case is and what exactly you are trying to model.

One small thing to point out- don't use "case" as a table name. You CAN use it but you would have to escape it everywhere as it is a SQL reserved word and this is a pain in the neck and will make you wish you didn't. Use something else.


How to use GROUP BY and result with highest id value I used the following query to get the result

SELECT  `subjects`.*,`staffs`.`name`
    FROM  `subjects`
    LEFT JOIN  `staffs`  ON `staffs`.`uid` = `subjects`.`teacher`
    LEFT JOIN  `subject_student_selective`
       ON  `subject_student_selective`.`course` = `subjects`.`classid`
      AND  `subject_student_selective`.`subject` = `subjects`.`subject`
      AND  `subject_student_selective`.`student` = '12947'
    WHERE  `subjects`.`classid` = '26'
      AND  `subjects`.`active` = '1'
      AND  (`subjects`.`type`='normal'
              OR  `subject_student_selective`.`id`>0
           )
    GROUP BY  `subjects`.`subject`
    ORDER BY  `subjects`.`sort_order`

This gave the result but, I need to get the result with highest ID'ed Group By subject.

For the one row with the highest ID:

ORDER BY ID DESC LIMIT 1

For the "latest" information associated with each subject, the problem is considerably more complex.  Search for "groupwise max".  Here's one link:
http://mysql.rjweb.org/doc.php/groupwise_max


how do I get extra fields with in a column with calculations I have a table AR--X with columns as Region, Product type, pofit, Target, 1, 2, 3,....... till 31 ( days ) as columns.

In Product type Column, there are fields like  Electronic, Plastic, wood, other, xx.

Now I want:

Select region, product type, profit, 1,2... 31 from AR--X.

In the results I would like to see:

for Product type with extra fields with calculations

a Field with a name as 'extra' = Target - (Electronic+ Plastic+ wood +other+ xx )
a Field with a name as 'Total'= Sum(Electronic+ Plastic+ wood +other + xx )
a Field with a name as '% of total' = total/Target*100.

Is this possible?

Generated columns(MySQL/MariaDB) make these fields possible.

Depending on what you are doing the 1-31 columns for days may not make sense. It will be hard to do partial month retrievals. Your queries appear to be month totals so it might be ok.


How do I create databases / logins / users with minimal privileges when installing an application? I have an application installation package that needs to

Create 1 or more databases (current process is to restore the database from a .BAK file made from a template that is adjusted periodically)
Create SQL Server logins for the server (two SQL logins a MyAdmin and MyUser account)
Create SQL Server users for each database (one for each SQL login)

At the moment I do this within the installation package by asking for SA credentials. I am trying to reduce the privileges required to install the application, since I am (understandably) getting pushback from DBAs who don't want to provide SA credentials to my installation package.

I have tried, prior to installation, creating a SQL Login with:

exec sp_addsrvrolemember 'MyAdmin', 'diskadmin'
-- FYI the MyAdmin account will run backup/restore/duplicate commands
grant create any database to MyAdmin
grant alter any database to MyAdmin

If I do this I can still successfully create the database by restoring from .BAK as MyAdmin, but when I try to use the database (so I can create users in [New Database]) I get an error.

use [New Database]

generates

The server principal "MyAdmin" is not able to access the database "New Database"
under the current security context

Something tells me that I am taking the wrong approach. Should I be creating the database with a script instead of restoring from a .BAK? Can I use an account with less privileges than SA to execute all of these things, and if so what privileges would this account require? Should I remove these things from the installer package and have a separate script that can be run under the SA or another account that has the required privileges?

What I am looking for is a process that will create what I need with minimal privileges. If possible, I would like to minimise the changes required to the process I am currently using.

If the DBA doesn't want to grant sysadmin or securityadmin role memberships (needed to create logins), have the DBA create those logins prior to installation and add MyAdmin to the dbcreator fixed server role.  The dbcreator role membership will provide the permissions to create, alter, and drop any database. No server-level permissions need be granted to MyUser before installation since the login will have CONNECT by default.

The MyAdmin account will own any databases created or restored under that security context. As the database owner, the account will be mapped to the dbo user and have full database permissions for administrative functions, including creating users and modifying schema. This account should be used only by the installer.  Routine database access should be done as MyUser with only the needed permissions granted.

--A sysadmin or securityadmin role member creates logins
--before instalation and adds MyAdmin login to dbcreator role.
CREATE LOGIN MyAdmin WITH PASSWORD='$&Sl~dg$30=asz';
CREATE LOGIN MyUser WITH PASSWORD='MM20&%93.f04}y';
ALTER SERVER ROLE dbcreator
    ADD MEMBER MyAdmin;
GO

--Installer connects with MyAdmin login.
--Database will be owned by login running RESTORE (MyAdmin)
RESTORE DATABASE ApplicationDatabase
FROM DISK='D:\SqlBackups\ApplicationDatabase.bak'
WITH
      MOVE 'ApplicationDatabase' TO 'D:\SqlDataFiles\ApplicationDatabase.mdf'
    , MOVE 'ApplicationDatabase_log' TO 'L:\SqlLogFiles\ApplicationDatabase.ldf'
    , STATS=5, REPLACE;
GO
--Database owner will access resotred database as dbo user.
USE ApplicationDatabase;
GO
--dbo user can create user mapped to SQL login MyUser.
CREATE USER MyUser;
GO


Elevate transaction isolation in Maria DB I am migrating code from Microsoft SQL to Maria DB. In the MSSQL stored procedure a transaction will be started with repeatable read and then later conditionally elevated to serializable if needed. In Maria DB this does not seem possible.

When trying to set the isolation level after the transaction has already been started, I get the error "Transaction characteristics can't be changed while a transaction is in progress."

Is there a way to do this in Maria DB without making the entire transaction serializable?

EDIT:

Here is an example of the Microsoft SQL code I am trying to migrate.

CREATE PROCEDURE -- ...
    -- ...
AS BEGIN

    -- check if there is a transaction already in progress
    -- if not, start a new one, otherwise elevate isolation to serializable
    DECLARE @TxNested INT = @@TRANCOUNT;
    IF @TxNested = 0 BEGIN TRANSACTION;
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRY
        -- lots of code
        -- commit transaction if we are the one that started it
        IF @TxNested = 0 COMMIT TRANSACTION
    END TRY BEGIN CATCH
        -- ...
        -- rollback transaction if we are the one that started it
        IF @TxNested = 0 ROLLBACK TRANSACTION
        -- ..
    END  CATCH

END

EDIT 2:

It is not possible to elevate the isolation level on a transaction that is already started and LOCK TABLES does not work inside of stored procedure.
Is there a way to take range locks even if the current isolation is not serializable?

In this case we can acquire a range lock using SELECT FOR UPDATE even while in repeatable read isolation. The documentation is not very clear that it will provide a lock for the entire range or just the selected rows.

I tested it by first starting a transaction with a SELECT FOR UPDATE

START TRANSACTION;
SELECT * FROM `Foo` FOR UPDATE;

I purposely left the transaction hang. Then using a second connection attempted to INSERT INTO the locked table.

INSERT INTO `Foo` (`Bar`) VALUES ('Bar');

The insert would not complete until the transaction was committed.


Comparing execution plans How does the optimizer compare different execution plans of the same query?
Let's say we have two complex and structurally different execution plans, how can I know if they are providing the same result?

The task of an optimizer is to create several execution plans and compare them (typically by costs). Each execution plan can be seen as logical transformation.

For example: a simple join would always be the cartesian product of two tables. Each row from the first is read and combined with each row in the other table. Than the join condition is checked and the resulting row is used or discarded.

That would be our live without optimizer. The optimizer is using set theory and boolean algebra to transform it. Each transformation leads to the same result. For instance tt doesn't matter if you first sort two sets independent and join them afterwards or first join them and sort them as one.

Other optimizations are the way rows are fetched or compared. There are for example many join algorithms like nested loop join, hash join or sort-merge join. Each algorithm has different advantages and disadvantages. But they operate on the same data. The only (hypothetical) way a execution plan could lead to other results is a difference in rows stored and the index. For that reason the indices have to be always updated as a row is inserted/updated/deleted.


mysql server password change while restoring I restored all database backup from another server's dumpfile to my machine. At the end my mysql server's password changed to that mysql server's password. Why it got overwritten? What i need to do to prevent this?
Thanks & Regards,
Praveen.

According to me you have also dumped the mysql  database which contains the users privileges in the users table of mysql database.  & one more-thing you may have used --add-drop-database  or --add-drop-table while generating the dump

i.e     mysqldump -u user -p passwordd --add-drop-database  --all-databases > /home/dump.sql

and when you have restored it on your local machine as your dump contains the
drop table statements.so it had firstly dropped your user table in mysql database and then restored new one from the dump thats why you are facing the problem. so don't include the mysql database while generating the dump.

please see various option of mysqldump  using mysqldump --help.

Thanks....


Connecting to local database on Azure VM Using DBforge to try and connect to a local MSSQL database on an Azure Ubuntu 18 VM, I am getting this error:

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

I have:

Enabled SSH on the inbound rules.
I can SSH into the Azure VM using PuTTY
I have tried both the host name (xx.eastus.cloudapp.azure.com) and the public IP address (13.82.xx.xx) to try and connect, both give this error.
I can connect to the website running on the VM by using the host name, and going to the public IP address shows the Apache landing page.
The website is running 100%, and I can log in, so the database instance is running.
I followed the advice here, and I confirm my server is listening on port 1433. (Log entry: Server is listening on [ 'any' <ipv4> 1433].)
Port 1433 is not blocked by a firewall. I have disabled the Azure firewall for testing purposes.
Various other answers suggest I must go into services.msc etc., but this is a Linux VM, so those won't work either.

I am getting the same error using SSMS, and none of the answers here seem to work for my specific case. All of them speak about connecting to an Azure SQL database, which this is not.

Any idea what else I can try?

Turns out I have neglected to set up a subnet in Azure portal. I am connected now.


Table Partitioning. What is the correct process for deleting .ndf and .ldf files? Please go easy it’s a steep learn curve..

OK steps done so far

Create files,
Create file groups,
Create partition function,
Create partition scheme,
Partition table associate files with filegroups
Put data in the table.

All is good

Now to reverse

The drop data

Drop the partition and associations,
Drop partition scheme,
Drop partition function,
Drop file groups

But when I deleted the files from the folder in my SSMS, I than had problems validating my Databases.

I restored the files and SSMS is fine.

My question what is the correct process for deleting .ndf  and .ldf files?

You can't delete the LDF file: it's essential.

For the NDF, you need to show that it's not used to SQL Server. You'd use DBCC SHRINKFILE with EMPTYFILE. Substitute xxx based on sys.database_files

DBCC SHRINKFILE (xx, EMPTYFILE)


Modify a value inside of a nested dynamic column In the table (items), I have a dynamic column (attr).

I have a very simple nested dynamic column.

INSERT INTO items (attr) VALUES (COLUMN_CREATE('level1', COLUMN_CREATE('level2','level2_value')));

Which when viewed with:

SELECT COLUMN_JSON(attr) FROM items;

Returns:

  { "level1" : { "level2" : "level2_value" } }

How can I modify the "level2_value" value?

Things I've Tried

Example 1

UPDATE items SET attr=COLUMN_ADD(attr, 'level1', COLUMN_ADD(attr, 'level2', 'NEWVALUE')) WHERE id=1;

  Results in: { "level1" : { "level1" : {"level2" : "level2_value"},
  "level2" : "NEWVALUE" } }

Example 2

UPDATE items SET attr=COLUMN_ADD(COLUMN_GET(attr, 'level1' AS CHAR), 'level2', 'NEWVALUE') WHERE id=1;

  Results in: { "level2" : "NEWVALUE" }

After MUCH trial and error... This is the structure required to modify an item nested in a dynamic column.

UPDATE item SET attr=COLUMN_ADD(attr, "level1", COLUMN_ADD(COLUMN_GET(attr, "level1" as blob), "level2", "NEWVALUE")) WHERE id=1;

Which turns;

  { "level1" : { "level2" : "level2_value" } }

INTO

  { "level1" : { "level2" : "NEWVALUE" } }


Mysql self Join query with Limit never terminates I'm running an experiment with the query
Select  Distinct table_1.id1, table_2.id1
    FROM  image as table_1, image as table_2
    WHERE  table_1.id2 = table_2.id2
    LIMIT  K;

When I run the query without limit, it terminates after 8 hours. However, the query does not terminate for Limit 80000.
I'm running this on cloudLab with 100GB Ram and Below is the image of my.cnf and query plan. I'm not sure what is the bottle neck for my query. How should I solve this problem
Query Plan text:
| -> Limit: 80000 row(s)
  -> Table scan on <temporary>
        -> Temporary table with deduplication
            -> Limit table size: 80000 unique row(s)
                -> Inner hash join (table_2.id2 = table_1.id2)  (cost=48913990425076.15 rows=48913988916980)
                    -> Table scan on table_2  (cost=0.01 rows=22116507)
                    -> Hash
                       -> Table scan on table_1  (cost=2224194.70 rows=22116507)

Your comma between two table means that you make a cross join and then select only the fitting rows.
do a proper JOIN with ON Clause, like
Select Distinct t1.id1, t2.id1
FROM image as t1 INNER JOIN image as t2
ON t1.id2 = t2.id2
LIMIT K;

Also have an index on id2 in the table image


large insert script (generated from tasks) - errors when running from sqlcmd I have a large generated .sql file for the data from our UAT database.  I'm trying to load that into my (localdb)\v11.0 using sqlcmd.

I was able to use the following:

sqlcmd -S (localdb)\v11.0 -d myDB -I c:\temp\mydatafile.sql

The .sql file is 5GB. Everything seemed to be doing well; however, at the end of the output in the command window I see an error:

  Msg 102, Level 15, State 1
  Incorrect syntax near...

Apparently the data that it is pulling from the DB doesn't work in the insert statements.  Since the file is 5GB it won't open in an editor, so I can't fix it.

I did a quick data compare and I see records not in my localdb that are in the UAT database.  So that leads me to believe that it did not finish inserting all the data.

Is there a way to skip over these types of errors using sqlcmd?

It would appear that if I can't open the file, yet sqlcmd is running into errors when running that file that the next best thing is knowing what table has that error.

My next question is, is there a way to message out what table has that error so I can exclude it from the original script?

I don't have a 5gb file to test against, so I have no idea if this proposed solution will work for you.  Your data may not lend itself to this proposed solution, but I thought I'd offer it anyway.

I created a table called SqlCommands

USE [Test]
GO
CREATE TABLE [dbo].[SqlCommands](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TextData] [varchar](max) NOT NULL,
 CONSTRAINT [PK_SqlCommands] 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]
GO

For testing purposes, I used the SSMS scripting wizard to script one of my small tables (data) as 'inserts' into a test file.

I then used a Powershell script to read that test file (which are basically SQL insert commands into the above SqlCommands table.  The table has an identity column and the Powershell script only references the 'TextData'  column.  The ID identity column will allow me to select from this table in the correct order.  Here is the Powershell script:

$database = "test"
$server = "."
$table = "dbo.SqlCommands"

$ff="C:\Test\TestData.txt"
write-host "Input file used is $ff"
$inrec=""
$counter=0

if ([System.IO.File]::Exists($ff))
{
  $obj=new-object System.IO.StreamReader($ff)

  $inrec=$obj.ReadLine()
  while ($inrec -ne $null)
  {

     #replace single quotes with double quotes so the insert will work for literals
     $inrec = $inrec -Replace("'","''")

     Invoke-SqlCmd -Database $database -ServerInstance $server -Query "insert into $table (TextData) values('$inrec')"
     $counter++

     $inrec=$obj.ReadLine()
  }
  $obj.close()

  write-host "$counter lines read"
}
else
{
   write-host "Source file ($ff) does not exist."
}

write-host "All done now!"

Now, I have my SQL file in a table.  I wrote a cursor to select from the table ordering by ID to make sure I'm executing the commands in the correct order.  The cursor script reads each row from the SqlCommands table and checks to see if it is a 'GO' statement ('GO' statements were automatically added by the scripting wizard when I scripted the table data).  If the row is NOT a 'GO', the current row is concatenated to @cmd and the next row is read.  If the current row is a 'GO', I execute the accumulated @cmd and blank @cmd out for the next series of statements.  The EXEC is wrapped inside a TRY/CATCH.  In the event of an error, I'm able to display the row number (Identity ID column) so I can run selects against the SqlCommands table 'around' that row number to find any problems.

SET NOCOUNT ON

DECLARE @cmd VARCHAR(max) = ' '
DECLARE @Id INT
DECLARE @textdata VARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT ID
    ,TextData
FROM [Test].[dbo].[SqlCommands]
ORDER BY ID

DECLARE @count INT

SET @count = 0

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @Id
    ,@textdata

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @textdata = 'GO'
    BEGIN
        BEGIN TRY
            --print @cmd
            EXEC (@cmd)
        END TRY

        BEGIN CATCH
            PRINT 'Error occurred at line ' + convert(VARCHAR(100), @Id)
            PRINT error_message()
        END CATCH

        SET @cmd = ' '
    END
    ELSE
    BEGIN
        SET @cmd += @textdata
    END

    FETCH NEXT
    FROM _CURSOR
    INTO @Id
        ,@textdata
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

PRINT @count

If any errors exist, I can use the row number to try and find the error

select * from [Test].[dbo].[SqlCommands] where id between 25 and 30


Replace 2 spaces by 1 space in all values of a column I need to replace 2 spaces by only 1 space for all entries of 1 column in an Oracle database. I found this and tried it:

SELECT REGEXP_REPLACE('MODEL', '[[:space:]]{2,}', ' ') new_string
FROM ops.TBL_PRODUCT_DEIN_DESIGN;

MODEL is the column name. But it has not replaced the double spaces.

How to use that solution or what could be an alternative?

You don't need to use a regular expression for that. Instead, you could use the replace function in SQL like this:

replace(MODEL, '  ', ' ')

where MODEL is your column, '  ' is the character(s) that you want to replace and ' ' is the character(s) you want to replace with.


Drop and Recreate All Objects in Database Project I have a database project in Visual Studio Team Services Git, and want to deploy into database on actual server.

(a) Is there setting in publish profile,  to drop all objects and recreate?
(b) or is there setting to create whole DDL script, rather than finding schema comparison discrepancy? Want to conduct this from Visual Studio, I know SSMS has option to Generate Scripts for all objects.

Want to conduct for all tables, sprocs, views, not just simple example below. Plan to redeploy and repopulate data warehouse everyday.

Example, say this in Source control database project.

Source Control:

create procedure dbo.SelectTestOne
as
select 1

And actual server on localdb is

Local Server Discrepancy:

create procedure dbo.SelectTestOne
as
select 2

Predeployment Script:

If I create a Script.PreDeployment, which drops all objects,

drop procedure dbo.SelectTest

The Final publish profile Script, will still do an alter, instead of recreate.  So question is how do I drop all objects and recreate them? (I know Redgate has this option)

drop procedure dbo.SelectTest
GO

GO
PRINT N'Altering [dbo].[SelectTest]...';

GO
ALTER procedure dbo.SelectTest
as

select 1
GO
PRINT N'Update complete.';

GO

This is accomplished through the CreateNewDatabase command line publish parameter:

/p CreateNewDatabase=True

  Specifies whether the target database should be updated or whether it should be dropped and re-created when you publish to a database.

You can also set this in the publish xml file:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    ... other properties ...
    <CreateNewDatabase>True</CreateNewDatabase>

Or you can use the publish UI (which sets the above mentioned XML property):

I consider the documentation on this to be a little hard to find, so here's a link to it: Microsoft Docs - SqlPackage.exe


Combine the effects of separate delete and insert rows How to I combine the two rows below:

Date          Id   For_Id   Type         New_Value    Old_Value
------------------------------------------------------------------
2015-08-13    6    327      DELETE       NULL         Value1
2015-08-13    6    327      INSERT       Value2       NULL

...into a single row, removing the NULLs, matching rows based on the dates?

Date          Id   For_Id   New_Value    Old_Value
-------------------------------------------------
2015-08-13    6    327      Value2       Value1

If I'm understanding your needs correctly, this would probably work...

SELECT t1.Date, t1.Id, t1.For_Id, t1.New_Value, t2.Old_Value
FROM table t1 JOIN table t2
ON t1.Date = t2.Date
AND t1.Id = t2.Id
AND t1.For_Id = t2.For_Id
WHERE t1.New_Value IS NOT NULL
AND t2.Old_Value IS NOT NULL;


What happens when you create the primary key of a table on a different filegroup? Creating a table on a different filegroup is absolutely no problem when using Sql Server.

create table common.test
(
    id int,

    CONSTRAINT [pk_test] PRIMARY KEY CLUSTERED (id ASC) ON [common_index],
) ON [common_data]

But what happens to the data in the background? Are they really stored on two different filegroups? Because when you look at the table properties in Management Studio it show common_index as storage.

If this is true, why does it even work to define them on two seperate filegroups?

What would happen with the stored data when the primary key is dropped and recreated on another filegroup?

It's all about clustered vs non-clustered indexes actually.  Primary key has nothing to do with it.  A clustered index IS the data. The actual table data is contained in the data pages of the clustered index.

If you put a clustered index on a table then the data is stored where you tell the clustered index to be.  If you changed your code above to be a non-clustered primary key then the data would be in [common_data] (and it would be a heap as it happens) and the non-clustered index for the primary key would be in [common_index].  If you then created a clustered index and pointed it to [uncommon_data] then the table data would be moved to the new filegroup and nothing would remain on [common_data].

Oh, and the reason you can do specify two separate filegroups is because you are working with two different parts of the syntax.  The ON [common_data] (FG for the table) tells SQL where to put the table.  The ON [common_index] (the FG for the index) tells SQL where to put the index.  In this case because it's a clustered index it forces the data to go with it.


Db design for historical option data (~200GB) I need some help determining how to best design my tables. I have about 200GB worth of data I wish to place in a table (historical option price data). The table looks something like:

String symbol
Date date
Date exp;
double strike;
double ask;
double bid;
<20 other columns>

95% of the time I will be fetching the current date's data. But the other 5% of the time I want to analyze historical data.

Most of the queries would involve some combination of symbol/date/exp.

I'm not sure how to best organize the table/s in terms of efficiency. Would it be best to put the data into two tables: historical and current? Or an index on the date? Or maybe an extra boolean column (isCurrent) which indicates which data is current or not?

If it matters, I'm using java/hibernate/mysql under the covers.

Thanks

The best first strategy is always to keep your transactional data all together in one, normalized data store.  This minimizes the risk of data integrity problems and keeps the amount of code you have to write to a minimum.

Often, people will find that there is some kind of concern around performance.  This leads them to consider denormalization.  The best thing is to wait until your volume testing proves that there is a real problem with performance, rather than assuming that there will be one because your table is "big".

Given that you have a large amount of historical data that you don't use most of the time, it is possible that you may benefit from denormalization.  You really should test this out before assuming it to be the case though.  If it is the case, then one approach which might work well for you is to keep two data stores: (a) one where your current values are kept and (b) one where all values, including current and historical are kept.

If this makes sense to do (for performance reasons), you should build some automation to track all changes to the current data and record them in your history table.  This is best done with triggers in DBMS that support them.

What almost never makes sense is keeping current values in one place and historical values (excluding current values) in another place.  I have never come across a situation where you need to query only historical records, excluding current values.  This means that your queries would have to be twice as complex in order to use current + historical values if you segregate them in this way.

This is what I would recommend to you:

Keep you data in one place if you can (definitely index on date)
Denormalize if you have to, but keep current values in your history table.
Do not use a bit flag for "is_current".  This almost never indexes well.


Update Query DeadLock in SQL Server when ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled Scenario:

We had learnt that the read operations are locked by MSSQL, causing other operations to slow down.

Later, we decided to turn on two factors.

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT for the entire database.

Upon enabling them, we face update query dead lock from multiple customers (having SQL Server 2008, 2012, 2014)

Blocking Transaction:-

On identifying and running the procedure to identify blocked query, we find that select query on table X has blocked the update / delete process of multiple tables.

One such blocked update statement is

BLOCKED UPDATE: UPDATE "ManagedComputer" SET "AGENT_EXECUTED_ON"= @P0 , "STATUS_UPDATED_TIME"= @P1 , "REMARKS"= @P2 , "MODIFIED_TIME"= @P3  WHERE ( ("ManagedComputer"."RESOURCE_ID" =  @P4 ) );

BLOCKING SELECT: SELECT "UserParams"."USER_ACCOUNT_ID", "UserParams"."PARAM_NAME", "UserParams"."PARAM_VALUE"  FROM "UserParams" WHERE  (("UserParams"."USER_ACCOUNT_ID" = 2) AND ("UserParams"."PARAM_NAME" COLLATE SQL_Latin1_General_CP1_CS_AS = 'stop_agent_workflow')) ;

Isn't the sole purpose of READ_COMMITTED_SNAPSHOT is not to block Update/ Insert statements ?

Query that I executed to find the blocking and blocked statement is as described in the article here

Writers block other writers of the same row regardless of isolation level. To avoid blocking during data modifications, examine execution plans to ensure only needed rows are touched by the query and perform query and index tuning as needed.

The READ_COMMITTED_SNAPSHOT database option avoids readers from blocking writers in the default READ COMMITTED isolation level by using row versioning instead of locking for statement level read consistency but at the cost of additional overhead for the row version store and in-row version data. You do not need to set ALLOW_SNAPSHOT_ISOLATION to use READ_COMMITTED_SNAPSHOT.  ALLOW_SNAPSHOT_ISOLATION allows use of the separate SNAPSHOT isolation level, which provides transaction level (not just statement level) consistency using row versioning.


Can spatial types be used to store the dimensions of 3-dimensional objects? Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). Weight is made up of pounds and ounces, but is also stored in a single column for the same reason.

Likewise, it seems that x,y and z dimensions are really just one value as well, with three properties since there is little need to know x without y and z in an application (but you may want to query on them discretely).

In OOP, we usually create objects to handle special primitive types like dates, money, weights, and dimensions. But unlike the other primitives, you can't simply add dimensions together and stuff them in a decimal column and hope to get them back out again.

You could store dimensions in a varchar with a delimiter, like 7;4,2;2, but this would make any queries for certain values difficult. ie. "get the record that has the longest (any dimension) side".

Of course, the conventional approach of storing x,y, and z in separate decimal columns would also have issues with a "get the longest side" query, since the database wouldn't treat them as one value collection. (You would have to get the longest of each, then the longest of the three results).

So I'm looking into the spatial data types which seem designed for map making, but it occurs to me that a data type designed to hold coordinates in 3d space would probably work just as well for defining 3d objects since it would still be a collection of x, y and z treated as a cohesive unit in one field with the ability to query coordinates separately.

Unfortunately, I'm having a hard time finding good information on using spatial types and I'm not sure if they can be used in this manner.

Can spatial types be used to store the dimensions of 3 dimensional objects?

Can spatial types be used to store the dimensions of 3-dimensional objects?

Yes, of course ST_MakePoint takes a Z cord (and even an M cord, hello time!)

  Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). Weight is made up of pounds and ounces, but is also stored in a single column for the same reason. [...] Likewise, it seems that x,y and z dimensions are really just one value as well, with three properties since there is little need to know x without y and z in an application (but you may want to query on them discretely).

Dollars are a unique function of cents; pounds a unique function of ounces (x,y,z) are nothing like that. They're three coordinates for discrete spatial dimensions.

  You could store dimensions in a varchar with a delimiter, like 7;4,2;2, but this would make any queries for certain values difficult. ie. "get the record that has the longest (any dimension) side".

There is no point in reinventing the wheel. You could store them a lot of ways. They tend to be stored something ISO/IEC 13249-3:2011, detailed in PostGIS.

  Of course, the conventional approach of storing x,y, and z in separate decimal columns would also have issues with a "get the longest side" query, since the database wouldn't treat them as one value collection. (You would have to get the longest of each, then the longest of the three results).

The conventional approach to store (x,y,z) is with a spatial 3d type.


MongoS clients not seeing databases after config server change to CSRS The three config servers were migrated to CSRS and WiredTiger last week. Followed the documentation here : https://docs.mongodb.com/manual/tutorial/upgrade-config-servers-to-replica-set/

Since the change, when connected to a mongoS, we cannot see the databases after using show dbs and we cannot connect using --authenticationDatabase admin --username root --password xxxxx --authenticationMechanism SCRAM-SHA-1

There are no errors in any logs, neither mongos, on the config servers, on the mongod machines. rs.conf and rs.status are OK.
The mongos have been stopped and restarted a few times to try and rectify this, with no luck.

There were no errors during the migration process. BUT a NEW dbpath was used so the initial config server didn't use the existing details but in fact created 'empty' details in the new dbpath.

Basically when doing the migration, the first cfg server does not change it's dbpath nor it's port throughout the migration procedure. Personally I found this unclear in the procedure, especialy when you are migration from SCCC to CSRS and using WiredTiger.


59 s for just simple select command mysql> select  count(*) from num;
+----------+
| count(*) |
+----------+
|  9918530 |
+----------+
1 row in set (59.18 sec)

It is horrible, 59.18 sec for just a simple select command!
How to improve MySQL performance in my windows10?

With ENGINE=InnoDB, the entire table must be scanned to perform the count.  Actually, any index can be used, and the Optimizer picks the smallest index (or the PRIMARY KEY if no secondary indexes exist.

If the table (or index) is not currently cached in the buffer_pool, then the query will take longer because of all the I/O necessary.

The reason for having to touch all the rows is that there could be other connections adding/deleting rows during the count, and InnoDB is obligated to provide the answer that is compatible with various transactional integrity requirements.  (Your Question has been asked many times; other Answers may provide more details.)


Create user with only SELECT and INSERT permissions I'm working on a project that requires a database user to have only SELECT and INSERT permissions for all tables in a database.

The following question provides a solution but I'd have to run the script every time I add a new table. Seems like it's specific to SQL Server 2000.

How do I create a user in SQL-Server that only has access to one table, and can only insert rows

There must be an easier way?

In my case, I have only a single schema dbo. So, instead of adding permissions to each table, it's easier to add it to the schema.

GRANT SELECT, INSERT ON SCHEMA::dbo to [my_user]

This way, each time a table is added to that schema the permissions will be applied automatically. For all my projects the schema rarely change (compared to the number of times a new table is added) so it's acceptable to run this one if a new schema is added.

Maybe other answers would provide a solution where the permissions can be applied at the database level?


Why am I NOT getting a mutating table error in trigger? It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:

A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.

However, I cannot understand why this demo trigger is not failing with a "mutating table" error when I perform an insert into emp using SQL Developer or SQL*Plus:
CREATE OR REPLACE TRIGGER emp_bri
  BEFORE INSERT ON emp
    FOR EACH ROW
BEGIN

  SELECT max(id) + 1 INTO :NEW.id FROM emp;
  UPDATE emp SET salary = 5000;

END emp_bri;

The insertion completes successfully with the next id value and updates all emp records. I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0. I have read about compound triggers but the sample does not use them.

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'


Versioning data in a database I have a database to hold data about projects.

Projects have lots of data associated with them that is segmented into other tables such as project_contacts and project_images. Those are one-to-many relations where 1 project can have many contacts and many images.

However, I need to be able to keep "versions" of projects. So, if a project is updated, the previous data about the project is still present. This is a large issue with many-to-many relationship. For example, there is a categories table, and a project_categories table - many projects can have many categories.

When a project updates, not every piece of data will be updated. For example, if a project gets a new contact, it won't always have a new image, so the images table won't be updated.

One solution I have considered is to have a versions table with an auto-incrementing ID; a project_id column and a time column. Each table will also get a version_id column. Whenever a project is updated, a new row is created in versions. Then, when the tables are updated, the version_id gets entered too. This means that the latest row about a project in project_images might be 3 whereas the latest version in project_contacts might be 27.

Because the version_id is incrementing, it's really simple to get the latest version of a single project, as you can join all the tables by the highest version_id.

Where this idea seems to fail for me is if I want to get the version of a project from a specific date.

Could I use my current solution to address that problem?

One potential method is to create a date_time column and a version_id column on an indexing table, essentially what you have described. The difference that you should make though is a new data insert onto all project-related tables whenever version_id is incremented. Something like this psuedoSQL...

insert <current date_time>, (select MAX(version_id) from versions)+1 into versions
insert (select (* - version_id - <data that is being updated>) from project),updating_column=new_data, (select MAX(version_id) from versions) into <project related tables>

This is a rudimentary method of thinking about this, but this may lead you down the right track. The major downside with this concept is that you have a lot of data running around that is essentially redundant but is logically simple to implement.

The other, more storage-smart way to think about this relationship is to keep track of what data gets updated. Your versions table would now have a foreign-key column for all data that can potentially be updated as part of your projects as well as a version_id primary key. Instead of inserting version_id into your project tables, you would insert column_version_id.

The idea would conceptually work like this. When you are querying for all data at a point in time you will first hit the versions table. From there it will join all your project tables in based on column_version_id.

While this concept has better storage there are more moving parts to the logic and is also less flexible with changes in how your project data is managed.


Why does searching for LIKE N'%�%' match any Unicode character and = N'�' match many? DECLARE @T TABLE(
  Col NCHAR(1));

INSERT INTO @T
VALUES      (N'A'),
            (N'B'),
            (N'C'),
            (N'Ƕ'),
            (N'Ƿ'),
            (N'Ǹ');

SELECT *
FROM   @T
WHERE  Col LIKE N'%�%'

Returns

Col
A
B
C
Ƕ
Ƿ
Ǹ

SELECT *
FROM   @T
WHERE  Col = N'�'

Returns

Col
Ƕ
Ƿ
Ǹ

Generating every possible double byte "character" with the below shows that the = version matches 21,229 of them and the LIKE N'%�%' version all of them (I've tried a few non binary collations with the same result).

WITH T(I, N)
AS
(
SELECT TOP 65536 ROW_NUMBER() OVER (ORDER BY @@SPID),
                 NCHAR(ROW_NUMBER() OVER (ORDER BY @@SPID))
FROM master..spt_values v1,
     master..spt_values v2
)
SELECT I, N
FROM T
WHERE N = N'�'

Anyone able to shed any light as to what is going on here?

Using COLLATE Latin1_General_BIN then matches the single character NCHAR(65533) - but the question is to understand what rules it uses in the other case. What's special about those 21,229 characters that match the = and why does everything match the wildcard like? I presume there is some reason behind it that I am missing.

nchar(65534) [and 21k others] work just as well as nchar(65533). The question could have been phrased using nchar(502)  equally as � - it behaves the same both as LIKE N'%Ƕ%' (matches everything) and in the = case. That's probably quite a big clue.

Changing the SELECT in the last query to SELECT I, N, RANK() OVER(ORDER BY N) shows that SQL Server can't rank the characters. It seems that any character not handled by the collation is considered equivalent.

A database with a Latin1_General_100_CS_AS collation produces 5840 matches. Latin1_General_100_CS_AS cuts down the = matches quite considerably, but doesn't change the LIKE behaviour. It does seem like there is a pot of characters that has got smaller in later collations that all compare equal and are ignored in wildcard LIKE searches then.

I am using SQL Server 2016. The symbol � is the Unicode replacement character, but the only invalid characters in the UCS-2 encoding are 55296 - 57343 AFAIK and it is clearly matching perfectly valid code points such as N'Ԛ' that are not in this range.

All these characters behave like the empty string for LIKE and =. They even evaluate as equivalent. N'' = N'�' is true, and you can drop it in a LIKE comparison of single spaces LIKE '_' + nchar(65533) + '_' with no effect. LEN comparisons yield different results though, so it's probably only certain string functions.

I think the LIKE behaviour is correct for this case; it behaves like an unknown value (which could be anything). It happens also for these other characters:

nchar(11217) (Uncertainty Sign)
nchar(65532) (Object Replacement Character)
nchar(65533) (Replacement Character)
nchar(65534) (Not a Character)

So if I want to find all the chars that represent uncertainty with the equal sign I would use a collation that supports supplementary characters like Latin1_General_100_CI_AS_SC.

I guess these are the group of "non weighted characters" mentioned in the documentation, Collation and Unicode Support.

How one "character" (which can be comprised of multiple Code Points: surrogate pairs, combining characters, etc) compares to another is based on a rather complex set of rules. It is so complex due to needing to account for all the various (and sometimes "wacky") rules found in all of the languages represented in the Unicode specification. This system applies to non-binary Collations for all NVARCHAR data, and for VARCHAR data that is using a Windows Collation and not a SQL Server Collation (one starting with SQL_). This system does not apply to VARCHAR data using a SQL Server Collation as those use simple mappings.

Most of the rules are defined in the Unicode Collation Algorithm (UCA). Some of those rules, and some not covered in the UCA, are:

The default ordering / weight given in the allkeys.txt file (noted below)
Which sensitivities and options are being used (e.g. is it case sensitive or insensitive?, and if sensitive, then is it upper-case first or lower-case first?)
Any locale-based overrides.
The version of the Unicode standard is being used.
The "human" factor (i.e. Unicode is a specification, not software, and is thus left up to each vendor to implement it)

I emphasized that final point regarding the human factor to hopefully make it clear that one should not expect SQL Server to always behave 100% according to the specification.

The overriding factor here is the weighting given to each Code Point, and the fact that multiple Code Points can share the same weight specification. You can find the basic weights (no locale-specific overrides) here (I believe the 100 series of Collations is Unicode v 5.0 -- informal confirmation in the comments on the Microsoft Connect item):

http://www.unicode.org/Public/UCA/5.0.0/allkeys.txt

The Code Point in question – U+FFFD – is defined as:

FFFD  ; [*0F12.0020.0002.FFFD] # REPLACEMENT CHARACTER

That notation is defined in section 9.1 Allkeys File Format of the UCA:

<entry>       := <charList> ';' <collElement>+ <eol>
<charList>    := <char>+
<collElement> := "[" <alt> <weight> "." <weight> "." <weight> ("." <weight>)? "]"
<alt>         := "*" | "."

Collation elements marked with a "*" are variable.

That last line is important as the Code Point we are looking at has a specification that indeed starts with "*". In section 3.6 Variable Weighting there are four possible behaviors defined, based on Collation configuration values that we have no direct access to (these are hard-coded into the Microsoft implementation of each Collation, such as whether case-sensitive uses lower-case first or upper-case first, a property which is different between VARCHAR data using SQL_ Collations and all other variations).

I do not have time to do the full research into which paths are taken and to infer which options are being used such that a more solid proof can be given, but it is safe to say that within each Code Point specification, whether or not something is considered "equal" is not going to always use the full specification. In this case, we have "0F12.0020.0002.FFFD" and most likely it is just levels 2 and 3 that are being used (i.e. .0020.0002.). Doing a "Count" in Notepad++ for ".0020.0002." finds 12,581 matches (including supplementary characters that we have not been dealing with yet). Doing a "Count" on "[*" returns 4049 matches. Doing a RegEx "Find" / "Count" using a pattern of \[\*\d{4}\.0020\.0002 returns 832 matches. So somewhere in this combination, plus possibly some other rules I am not seeing, plus some Microsoft-specific implementation details, is the full explanation of this behavior. And to be clear, the behavior is the same for all of the matching characters as they all match each other as they all have the same weight once the rules are applied (meaning, this question could have been asked about any one of them, not necessarily Mr. �).

You can see with the query below and changing the COLLATE clause as per the results below the query how the various sensitivities work across the two versions of Collations:

;WITH cte AS
(
  SELECT     TOP (65536) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1 AS [Num]
  FROM       [master].sys.columns col
  CROSS JOIN [master].sys.objects obj
)
SELECT cte.Num AS [Decimal],
       CONVERT(VARBINARY(2), cte.Num) AS [Hex],
       NCHAR(cte.Num) AS [Character]
FROM   cte
WHERE  NCHAR(cte.Num) = NCHAR(0xFFFD) COLLATE Latin1_General_100_CS_AS_WS --N'�'
ORDER BY cte.Num;

The various counts of matching characters at different collations is below.

Latin1_General_100_CS_AS_WS   =   5840
Latin1_General_100_CS_AS      =   5841 (The "extra" character is U+3000)
Latin1_General_100_CI_AS      =   5841
Latin1_General_100_CI_AI      =   6311

Latin1_General_CS_AS_WS       = 21,229
Latin1_General_CS_AS          = 21,230
Latin1_General_CI_AS          = 21,230
Latin1_General_CI_AI          = 21,537

In all of the collations listed above N'' = N'�' also evaluates to true.

UPDATE

I was able to do a little more research and here is what I found:

How it "probably" should work

Using the ICU Collation Demo, I set the locale to "en-US-u-va-posix", set the strength to "primary", checked show "sort keys", and pasted in the following 4 characters that I copied from the results of the query above (using the Latin1_General_100_CI_AI Collation):

�
Ԩ
ԩ
Ԫ

and that returns:

Ԫ
    60 2E 02 .
Ԩ
    60 7A .
ԩ
    60 7A .
�
    FF FD .

Then, check the character properties for "�" at http://unicode.org/cldr/utility/character.jsp?a=fffd and see that the level 1 sort key (i.e. FF FD) matches the "uca" property. Clicking on that "uca" property takes you to a search page – http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3Auca%3DFFFD%3A%5D – showing just 1 match. And, in the allkeys.txt file, the level 1 sort weight is shown as 0F12, and there is only 1 match for that.

To make sure that we are interpreting the behavior correctly, I looked at another character: GREEK CAPITAL LETTER OMICRON WITH VARIA Ὸ at http://unicode.org/cldr/utility/character.jsp?a=1FF8 which has a "uca" (i.e. level 1 sort weight / collating element) of 5F30. Clicking on that "5F30" takes us to a search page – http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3Auca%3D5F30%3A%5D – showing 30 matches, 20 of them being in the 0 - 65535 range (i.e. U+0000 - U+FFFF). Looking in the allkeys.txt file for Code Point 1FF8, we see a level 1 sort weight of 12E0. Doing a "Count" in Notepad++ on 12E0. shows 30 matches (this matches the results from Unicode.org, though it is not guaranteed to since the file is for Unicode v 5.0 and the site is using Unicode v 9.0 data).

In SQL Server, the following query returns 20 matches, same as the Unicode.org search when removing the 10 supplementary characters:

;WITH cte AS
(
  SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM   [master].sys.columns col
  CROSS JOIN [master].sys.objects obj
)
SELECT cte.Num AS [Decimal],
       CONVERT(VARCHAR(50), CONVERT(VARBINARY(2), cte.Num), 2) AS [Hex],
       NCHAR(cte.Num) AS [Character]
FROM cte
WHERE NCHAR(cte.Num) = NCHAR(0x1FF8) COLLATE Latin1_General_100_CI_AI
ORDER BY cte.Num;

And, just to be sure, going back to the ICU Collation Demo page, and replacing the characters in the "Input" box with the following 3 characters taken from the list of 20 results from SQL Server:

Ὂ

Ὸ

shows that they, indeed, all have the same 5F 30 level 1 sort weight (matching the "uca" field on the character property page).

SO, it certainly does seem as though this particular character should not match anything else.

How it actually works (at least in Microsoft-land)

Unlike within SQL Server, .NET has a means of showing the sort key for a string via the CompareInfo.GetSortKey Method. Using this method and passing in just the U+FFFD character, it returns a sort key of 0x0101010100. Then, iterating over all of the characters in the range of 0 - 65535 to see which ones had a sort key of 0x0101010100 returned 4529 matches. This does not exactly match the 5840 returned in SQL Server (when using the Latin1_General_100_CS_AS_WS Collation), but it is the closest we can get (for now) given that I am running Windows 10 and .NET Framework version 4.6.1, which uses Unicode v 6.3.0 according to the chart for the CharUnicodeInfo Class (in "Note to Callers", in the "Remarks" section). For the moment I am using a SQLCLR function and so cannot change the target Framework version. When I get a chance I will create a console app and use a target Framework version of 4.5 as that uses Unicode v 5.0, which should match the 100 series Collations.

What this test shows is that, even without the exact same number of matches between .NET and SQL Server for U+FFFD, it is pretty clear that this is not SQL Server-specific behavior, and that whether intentional or oversight with the implementation done by Microsoft, the U+FFFD character does indeed match quite a few characters, even if it shouldn't according to the Unicode specification. And, given that this character matches U+0000 (null), it is probably just an issue of missing weights.

ALSO

Regarding the difference in behavior in the = query vs the LIKE N'%�%' query, it has to do with the wildcards and the missing (I assume) weights for these (i.e. �  Ƕ  Ƿ  Ǹ) characters. If the LIKE condition is changed to being simply LIKE N'�' then it returns the same 3 rows as the = condition. If the issue with the wildcards is not due to "missing" weights (there is no 0x00 sort key returned by CompareInfo.GetSortKey, btw) then it could be due to these characters potentially having a property that allows the sort key vary based on context (i.e. surrounding characters).


Accessing MySQL through dynamic IP address I am working on creating a MySQL database server for one of my school projects. I want to allow my teammates to access the server remotely using school network, but I discovered that their IP addresses varied whenever they reconnected to the school network, and it won't be desirable to manually add all the new IP addresses to the user table. Is there any trick I could use to overcome this problem?

you can use % as a wildcard to summarize source nets ands limit your attack vector.

so instead of 192.168.68.34, use 192.168.68.% assuming they use a /24 address space for vpn.


What are drawbacks of setting REPLICA IDENTITY FULL in PostgreSQL table I want to use the wal2json extension to capture the changes in the PostgreSQL database.
I need to find the diff between old and new rows.
Having searched the Internet for an answer, I realized that this can be achieved by setting the REPLICA IDENTITY FULL option on the table of interest.

I think this is exactly what I need, but I wonder are there any significant drawbacks of this setting?

The only disadvantage is that it will produce more data than are necessary to identify the affected row, but since that is what you want, it is not a disadvantage to you.


Database Schema for a User and his variable number of items I planning to create tables which hold data related to my users. These tables contain the items that each user owns, and there is no exhaustive list of all the item. The number of items owned by a user might vary from 0 to 100. So the number of items owned by a user is not fixed. For example, A user "Sam" might have "iPod, MacBook, Car, House", while some other user "Dave" might have "Bike, Plane, iPhone, TV ...." etc. So what is the best schema for the tables? I came up with the following idea. Please suggest improvements if any:

I will have a table which holds data as follows:

User           Item
=========      =========
Sam            iPhone
Sam            MacBook
Sam            Car
Sam            House
Dave           Bike
Dave           Plane
Dave           iPhone
Dave           TV
.
.

Your problem seems quite simple. Basically as I understand it you need to associate a collection of strings to a user. Your system would work ok, but I would recommend you replace the username with a UserId. That way the cost of updating client facing information is lessened, and you use less memory to store the data.

You didn't specify your target RDBMS but something like the following would work in SQL Server, and give you a good starting point for other RDBMS that use slightly different syntax.

CREATE TABLE dbo.Users -- Added the 's' so it wouldn't be a key word
(
   UserId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- FK to this instead of the UserName
   UserName NVARCHAR(50) NOT NULL UNIQUE, -- Unique constraint optional if not identifying
   <Other user details>
);

CREATE TABLE dbo.UserStuff
(
    UserStuffId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- Hook for targeted updates/deletes
    UserId INT NOT NULL, -- Not unique so can't be PK
    ItemDescription NVARCHAR(50) NOT NULL, -- Resize to fit needs
    <Other meta data you want like created timestamp>

    CONSTRAINT [FK_UserStuff_UserId_to_Users_UserId] FOREIGN KEY (UserId)
        REFERENCES dbo.Users(UserId)
);

If SELECT performance is poor then you can always add a covering index to the dbo.UserStuff table like so:

CREATE NONCLUSTERED INDEX [IDX_dbo_UserStuff_UserId] ON dbo.UserStuff(UserId)
   INCLUDE (ItemDescription);


Mitigate negative effects of shrinking Closely related to this question from a few weeks ago, but the answers didn't discuss how to do the dirty deed. Here's the situation:

We now 15 GB data in a database, but a logging bug in an application ran wild and ran this up to 80GB data giving us DB files around 130GB. We've fixed the bug, cleared out the affected tables, and I'd like to get some of the space back, maybe bring the DB back down to 40GB or so.

The biggest reason I'd like to do this is so that we can more easily restore backups to smaller drives on virtual test instances.

I get it: Shrink is evil, and will fragment both indexes and on-disk file. I'm sold. This is a one time event.

So how can I minimize the pain?
Seems like I should:

Use DBCC SHRINKFILE (DataFile1, 40000); to aim for 40 GB.
Immediately use some smart reindexing to reorganize and rebuild indexes
Defrag physical disks

Will that appropriately mitigate the side effects of a shrink? Or is this only going to end up on my application for the Evil League of Evil?

Here is my somewhat hand-wavy approach to shrinking files. This has worked pretty well for me for over ten years.

Shrinking files is not necessarily evil, but that doesn't mean that it is always the best thing to do.

Firstly, think about why you are shrinking. Most databases will only grow larger. If you expect to need the space in the foreseeable future, you probably don't want to shrink.

The best reason to shrink is that you are trying to recover from some mistake, where the size of the data file has grown well past anything that is required in the near future.

Another good reason is that you need to restore the affected database onto development or test servers that simply do not have the storage capacity to handle the extra, unused, space.

The worst reason to shrink is that you are trying to jam yet another database onto storage that is already nearly full. You are going to run out of space, accepting it is the first step. The next step is to work on a plan for more storage (or fewer databases), not robbing Peter to pay Paul.

Shrinking tempdb is almost always painful. It is hard to get timely shrinks without restarting the instance and restarting the instance is a bad habit.

Secondly, make sure that you leave extra space in the MDF and NDF files. Reindexing will need some working space. How much? It depends. Find the size of your largest table and use that as a guide. I've never gone wrong by leaving that much space in the data files. If you don't have enough space in your files, they will try to autogrow. If SQL Server lacks contiguous space in the file, you may have problems reindexing large tables; they won't ever seem to be property defragged after the reindex routines run.

Do not use auto shrink. It was intended for databases running on users workstations, not on dedicated production servers and was thought up so long ago that using it for any reason whatsoever isn't a good idea at this point.

Use dbcc shrinkfile, not dbcc shrinkdatabase. If you need to shrink a database with several *MDF*s, do it in a round-robin fashion, paying attention to the file groups that each file belongs to. In order to effectively spread I/O around, SQL Server wants files of equal size in a file group.

Generally, shrinkfile doesn't put a lot of load on the server, but when I shrink files I avoid peak demand periods and I like to watch a little more closely with Performance Monitor than I normally would.

Do not try to shrink out all of the space in one go. If you try to shrink out a large amount of space in one go, the dbcc command will often seem to go catatonic. Disk I/O will drop, but the command will not complete.

After running dbcc shrinkfile, you will want to reindex to put the data back in order. Depending on your tables, you may be able to do online reindexing, which is widely documented.

If you have shrunken the files too far the files will try to autogrow. You don't want that, so be careful to not shrink things too much. when in doubt, leave an extra GB.

File-level fragmentation tends to occur most on volumes that have lots of data files that are often grown. Frequent drop/recreation of databases may exacerbate the problem. If you are shrinking files, they should get smaller and there should be fewer fragments. There is no particular reason that defragging should make things worse, though it will put I/O load on your server, which may hurt if the storage is already heavily-loaded. Some admins swear by third-party defragging tools, but I have only used Microsoft's tool on servers. Again, I wouldn't do this during peak demand times.


Random selection of a row within a function I am trying to write a function so it can be used as a default value for a column in a table. It exists as a procedure at the moment (which cannot be used as a column default) and uses ORDER BY newid() several times. Apparently this cannot be used in a function.

Is there anyway of randomly selecting a row in a user defined function?

A Computed Column is a bad (probably even very bad) place to have a random value.

First, if you want this "default" value to remain consistent upon the row being inserted (as opposed to changing every time the row and field are selected), you would need to use the PERSISTED keyword so that the computed column is physical stored and not recomputed each time.

Second, while you can usually do either of the following in a computed column, they cannot be used in a PERSISTED computed column due to being non-deterministic:

directly use NEWID() and CRYPT_GEN_RANDOM()
pass NEWID() or CRYPT_GEN_RANDOM() as a parameter to a User-Defined Function

Third, you could potentially create a SQLCLR function that would be able to call .NET randomization methods, but due to the requirement of a PERSISTED computed column needing to reference only deterministic functions, you would have to mark it incorrectly on purpose as IsDeterministic = true, and the effect(s) of this could be bad. At the very least it would allow SQL Server to cache the function output, in which case multiple rows being inserted in the same query (either via an INSERT...SELECT construct or an INSERT INTO VALUES (), (), ()... construct) could easily have the same "default" value and hence not be random at all.

Fourth, you could attempt the "hiding NEWID() in a View so it can be referenced in a UDF" trick as shown in @Julien's answer, but you would need to test that to see if it actually does what you are wanting. You need to particularly test scenarios that insert multiple rows in a single statement since SQL Server does not guarantee that it will run a UDF more than 1 time.

In the end, I think you are FAR better off having this "default" field being a regular field (i.e. not computed) and either determining its value in the Stored Procedure that does the INSERT, or determined and set in an AFTER INSERT Trigger that updates this "default" field for all new rows.


too many entries for one single session and serila# in V$Session_longops I have a concern with the entries in v$session_longops.
I used a query to identify query running from past 25 minutes for sid=62 which is given below:

SELECT s.sid,
       s.serial#,
       s.machine,
       s.program,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       totalwork,
       sofar
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = 62
AND    s.serial# = sl.serial#
AND    sl.elapsed_seconds > 25*60
and sofar<>totalwork;

it returns no rows.

But when I check with v$session_longops and v$session I have many entries for the same sid.
I am using OEM 12c. Even there in sql monitoring I am able to see many queries running for more than 3 hours. I use oracle 11.2.0.3

Can anyone help me understand y is it happening? I am confused..

you should review your query,  missing s.sid = sl.sid.  Besides, wher yout lokk at v$session_longops and find many rows, they all meet the other conditions - sl.elapsed_seconds > 25*60 and sofar<>totalwork ?


How to use local variable in USE statement How to use local variable in USE statement? I get error:

  Msg 911, Level 16, State 1, Line 6
  Database 'DWSource_@Country' does not exist. Make sure that the name is entered correctly.

Code:

DECLARE @Country varchar(3)
SET @Country='UKR'

USE DWSource_@Country

SELECT  @Country as country,
        count(*) as n_ALL,
        sum(case when LEN(EPOSTA)>0 then 1 else 0 end) as EMAIL1,
        sum(case when LEN(EPOSTA_2)>0 then 1 else 0 end) as EMAIL2

FROM    [dbo].[_data_CRM_S_PARTNER]
WHERE   VIR = 'SM'
    AND PRAVNA_OSEBA = 0
    AND PROBLEMATICEN = 0
    AND ISNULL([STATUS],0) IN (0,1)
    AND ID_DBCLUSTER IN ('112','122','132','212','222','232','312','322','332','311','321','331')

Thanks in advance!

Br,
Anja

You can't pass a variable to USE. You can do this inside the dynamic SQL, or you can build a string that prepends each object name with the database prefix.

A cleaner dynamic SQL approach than the latter approach, IMHO, since it doesn't require you to inject the database name in front of every object in the dynamic SQL (and also, in this case, allows you to pass @Country as a parameter):

DECLARE @country CHAR(3) = 'UKR';
-- assume above is incoming parameter

DECLARE
  @db SYSNAME = N'DWSource_' + @Country,
  @exec NVARCHAR(300),
  @sql NVARCHAR(MAX);

SET @sql = N'SELECT  @Country as country,
        count(*) as n_ALL,
        sum(case when LEN(EPOSTA)>0 then 1 else 0 end) as EMAIL1,
        sum(case when LEN(EPOSTA_2)>0 then 1 else 0 end) as EMAIL2
FROM    [dbo].[_data_CRM_S_PARTNER]
WHERE   VIR = ''SM''
    AND PRAVNA_OSEBA = 0
    AND PROBLEMATICEN = 0
    AND ISNULL([STATUS],0) IN (0,1)
    AND ID_DBCLUSTER IN (''112'',''122'',''132'',''212'',''222'',
      ''232'',''312'',''322'',''332'',''311'',''321'',''331'')';

SELECT @exec = QUOTENAME(@db) + '.sys.sp_executesql'
EXEC @exec @sql, N'@Country CHAR(3)', @Country;

Both of those things at least somewhat reduce your risk for SQL injection, IMHO. Not that a malicious user could do much with a CHAR(3) but if you use that pattern in other ways with bigger variables, someone could easily pass @Country = '1''; DROP TABLE dbo._data_CRM_S_PARTNER; SELECT '''... and in the other solution this would be a problem (again, if the variable were bigger).

EXEC @exec method quasi-stolen from Erland Sommarskog's great article on dynamic SQL.

That all said, I do agree with Thomas that if you have the same query you want to run in multiple databases, you are better off creating the same stored procedure in each database, and having the app just connect to the desired database in the first place. Since the app has to know which country you're after, it shouldn't be that hard to just generate a connection string based off of that, and call a clean, no-dynamic-SQL stored procedure in that database.


Waitresource format explanation in deadlock xml I have a deadlock xml where two transactions are deadlocked on two resources. waitresource attributes of the process elements have values "OBJECT: 8:1269579561:4" and "OBJECT: 8:1269579561:0".

It looks like table objects locks but what are the third parts in the object description: ":4" and ":0"? Where can I find detailed explanation of the waitresource attribute?

It's the Lock Partition

  For large computer systems, locks on frequently referenced objects can
  become a performance bottleneck as acquiring and releasing locks place
  contention on internal locking resources. Lock partitioning enhances
  locking performance by splitting a single lock resource into multiple
  lock resources. This feature is only available for systems with 16 or
  more CPUs, and is automatically enabled and cannot be disabled. Only
  object locks can be partitioned.

You can see this by comparing the deadlock message in the errorlog with the deadlock xml in the system_health XEvents session, which you can query like this:

SELECT
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    timestamp_utc, datediff(s,timestamp_utc,GETUTCDATE()) sec_ago
FROM sys.fn_xe_file_target_read_file(
        cast(SERVERPROPERTY('ErrorLogFileName') as varchar(max)) +
        '\..\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

From the errorlog:

 Message OBJECT: 2:1013578649:15        CleanCnt:2 Mode:IS Flags: 0x1

from the deadlock xml:

 <objectlock lockPartition="15" objid="1013578649"


Use of CASE statement in a MySQL query I have 3 tables:

TASK (id_task (PRIMARY KEY), name)
PROJECT (id_project (PRIMARY KEY), name)
PROJECT_HAS_TASK (id_project, task_id)

PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK.

I'd like to return all the TASK records and a boolean value indicating if the task_id also exists in PROJECT_HAS_TASK. Is that possible to do with a SQL query? Or should I use a stored procedure?

EDIT :
Yes it is possible to do it with a SQL query. Thanks to both a1ex07 and bummi's help I ended up with this solution :

SELECT a.id_task,
Case WHEN b.id_task IS NULL then 0 else 1 end as HasProject
from TASK a
LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task);

If you need to check if there is any project that has task, use something like

SELECT a.id_task,
Case WHEN b.id_task IS NULL then 0 else 1 end as HasProject
from TASK a
LEFT join (SELECT DISTINCT id_task FROM PROJECT_HAS_TASK)b on (a.id_task = b.id_task)
;
-- if you need number of tasks for a project
Select  a.id_task,
COUNT(b.id_project) as num_projects
from TASK a
LEFT join PROJECT_HAS_TASK b on a.id_task = b.id_task
GROUP BY a.id_task;


Consolidating 2 Tables with same Values I am working on a project trying to consolidate two databases, DB1 and DB2, for my organization into a single database, DB3.

Initially our organization started with DB1.  A few years back they made a new database called DB2 and moved on to using only DB2.

Currently, I am working on the Orders Table.

Since new rows were inserted into DB2 without importing existing rows from DB1, Order-id Column values started from 1 instead of continuing from where the Order-id Column in DB1 had ended.

I have many Order-id Column values that are the same in both Tables that represent different orders.

For example Order-id = 100 in DB1 refers to order "X", whereas Order-id = 100 in DB2 refers to order "Y".

If I insert rows from both DB1.Orders and DB2.Orders into DB3.Orders there will be duplicate rows representing different orders.

As I have not come across this sort of situation before, I need a suggested approach for  dealing with this.

Sometimes, the lack of an uint type in SQL Server can come in handy. If Order-id is defined as identity(1,1) in DB2, you likely do not have any negative Order-ids.

Consider importing all the legacy DB1..Orders.Order-id fields as the same number, but multiplied by -1. If there's a FK to Order-id in DB1, you would use the same multiple-by-negative-one conversion when those records are loaded into their corresponding tables in DB2.

With this method, you also have the benefit of easily identifying the legacy orders originally from DB1 because their Order-id is always negative (just be sure to document this.)

Edit - added an example

-- Assumptions:
-- DB1 and DB2 are on the same server
-- The table Orders is in the dbo schema
-- Order-id is an integer-type in DB1 and DB2
-- Order-id >= 0 in DB1 and DB2
-- Order-id is an identity(a, b), where a and b >=0 in DB2

-- Overview: Insert records from DB1 into DB2, but reverse the
-- sign of Order-id (i.e., positive to negative).

Use DB2
Go

Set Identity_Insert dbo.Orders On;

Insert dbo.Orders
(Order-id, col2, col3)

Select (-1 * Order-id), col2, col3
From DB1.dbo.Orders;

Set Identity_Insert dbo.Orders Off;
Go

-- Have related records? Insert them here.


Speed up data transfer from db to application I have build an application that handles data stored in a MS SQL server database. This server is hosted externally of our company and has to be connected to via SSH because of company rules.

On a regular working day it takes about 7 minutes to retrieve a list of data, while it takes less than one minute to do the same outside office hours.

The database is relatively simple. There are 3 main tables which have a relation to each other on one field which is also set as primary key. These tables have relations with several other small tables where lists are stored. So the fields in the main tables are integer fields related to a small table where that integer points to a text field.

I'm trying to figure out why it takes 7 minutes during office hours and less than one 1 outside office hours.

There are about 12 users.

Anyone have some tips for me?

rg, Eric

Edit:  SQL code:

SELECT
t1.TicketNummer
,t1.SiteNummer
,t9.name AS Categorie
,t7.name AS Klant
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t5.FullName END) AS     AangenomenDoor
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.AangenomenOp ELSE t1.AangenomenOp END) AS     AangenomenOp
, (CASE WHEN t11.RapportNaam IS NULL THEN t1.aangenomenop ELSE t8.UitgevoerdOp END) AS     UitgevoerdOp
, (CASE WHEN t11.RapportNaam IS NULL THEN t5.FullName ELSE t8.UitgevoerdDoor END) AS UitgevoerdDoor
, (CASE WHEN t11.RapportNaam IS NULL THEN 'Via ESIT' ELSE t11.RapportNaam END) AS Rapport
,t8.VraagNummer
,t8.Uploaded
,t2.Name AS PrioCode
, t4.offertenummer
, t4.ponummer
, CASE WHEN t4.hersteldoor=0 THEN ''
       WHEN t4.hersteldoor=1 THEN 'Aannemer'
       WHEN t4.hersteldoor=2 THEN 'Eigen personeel'
       WHEN t4.hersteldoor=3 THEN 'Operator'
       ELSE 'Aannemer' END AS TeHerstellenDoor
, t12.refnraannemer
, t12.offertebedrag
, t13.name as Operator
, t4.operatorRefNr
,(CASE WHEN t1.IsManco ='true' AND t1.IsOpgelost = 'false' THEN 'Manco'
       WHEN t1.IsConstatering ='true' THEN 'Constatering'
       ELSE '' END) AS IsManco
,(CASE WHEN t1.IsOpgelost ='true' THEN 'Direct opgelost'
       WHEN t6.verwijderd = 'true' THEN 'Verwijderd'
       ELSE t3.name END) AS VerwerkingsGroep
,(CASE WHEN (year(t4.DatumTechnischGereed)<2000 or t4.technischgereed='false') THEN NULL     ELSE CONVERT(VARCHAR,t4.DatumTechnischGereed,20) END) AS DatumTechnischGereed
,(CASE WHEN (year(t4.DatumAdministratiefGereed)<2000 OR t4.administratiefgereed='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumAdministratiefGereed,20) END) AS     DatumAdministratiefGereed
,(CASE WHEN (year(t4.DatumFinancieelVerwerkt)<2000 OR t4.financieelverwerkt='false')     THEN NULL ELSE CONVERT(VARCHAR,t4.DatumFinancieelVerwerkt,20) END) AS     DatumFinancieelVerwerkt
, (CASE WHEN t4.financieelverwerkt = 'true' THEN 'Ja' ELSE '' END) AS financieelVerwerkt
, (CASE WHEN t4.AdministratiefGereed = 'true' THEN 'Ja' ELSE '' END) AS     AdministratiefGereed
, (CASE WHEN t4.TechnischGereed = 'true' THEN 'Ja' ELSE '' END) AS TechnischGereed
, t10.Name AS Aannemer
, t6.verwijderd
, t14.fullname AS MutatieDoor
,(CASE WHEN (year(t4.MutatieOp)<2000) THEN NULL ELSE t4.MutatieOp END) AS MutatieOp
FROM MainTickets AS t1
LEFT JOIN PrioCode AS t2 ON t1.PrioCode =t2.iD
LEFT JOIN TicketVerwerking AS t4 ON t1.TicketNummer =t4.TicketNummer
LEFT JOIN Verwerking AS t3 ON t4.VerwerkingsGroep =t3.ID
LEFT JOIN Gebruikers AS t5 ON t1.AangenomenDoor =t5.ID
LEFT JOIN TicketNummers AS t6 ON t1.TicketNummer=t6.TicketNummer
LEFT JOIN Klanten AS t7 ON t1.Klant=t7.ID
LEFT JOIN SubTickets AS t8 ON t8.TicketNummer =t1.TicketNummer
LEFT JOIN Categorie AS t9 ON t9.ID =t1.Categorie
LEFT JOIN Aannemers AS t10 ON t10.ID =t4.aannemeropdracht
LEFT JOIN FormIDs AS t11 ON t8.formidcode=t11.FormIDcode
LEFT JOIN OfferteAannemers AS t12 ON (t4.offertenummer=t12.offertenummer AND     t12.aannemer=t4.aannemeropdracht)
LEFT JOIN Operators AS t13 ON t4.operator=t13.id
LEFT JOIN Gebruikers AS t14 ON t4.mutatiedoor =t14.ID
ORDER BY t1.TicketNummer

During work hours, take a look at sys.dm_exec_requests and see what the wait_type column says.  This will tell you what the requests are waiting for.

Right before working hours, you could run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); and then look at sys.dm_os_wait_stats to see the accumulated stats during the day.

Do you have indexes defined on any of these tables?  If not, then table scans might be clogging your I/O system, and locks might be causing some blocking.

If people are running SQL Profiler traces during work hours, those could be slowing down the entire system.  You could detect those by quering sys.traces.

Running a careful profiler trace yourself might reveal some interesting facts about this query.  Try capturing a Showplan XML Statistics Profile event when it runs.  Maybe the query processor is choosing a very bad query plan.  Maybe the plan is generating intermediate tables that have a ridiculous number of records, or maybe there is a bad nested loop that would work better with an index or with a merge join.

Since this is an external server, another possibility is that you are simply overwhelming network bandwidth during working hours.

You mentioned in a comment that there was a lot of data coming back.  Adding some filters in the query might help-- if that is an acceptable solution for your application.


how to formulate query (conditional) We have following tables - Action, Employee and ActionAssignedTo

I am trying to write a query to fetch an Action with id : 10 that has been assigned to emp_id : 1000.
 the reply of query should also contain all the other assignees for the same action i.e. same action can be assigned to multiple people

select     Action.name,
           completed_At,
           group_concat(AssigneeNameTable.name) as assignedTo,
           group_concat(AssigneeNameTable.id) as assignedToId,
           ActionAssignedTo.action_Id as actionId
from       Action

inner join Employee
on         Action.emp_Id = Employee.id and Employee.vendor_Id = 1

inner join ActionAssignedTo
on         Action.id = ActionAssignedTo.action_Id
and        ActionAssignedTo.action_Id = 10

inner join Employee as AssigneeNameTable
on         ActionAssignedTo.emp_Id = AssigneeNameTable.Id

where     ActionAssignedTo.emp_Id = 1000
and       group by Action.id
limit 2

CREATE TABLE `Action` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `emp_Id` int(10) unsigned NOT NULL,
      `name` varchar(60) NOT NULL,
      `updated_action_at` datetime(3) DEFAULT NULL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `notes` varchar(400) DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `action_empId_fk` (`emp_Id`),
      CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,
    ) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1

CREATE TABLE `ActionAssignedTo` (
  `action_Id` int(10) unsigned DEFAULT NULL,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  KEY `actionassignedto_emp_id_foreign` (`emp_Id`),
  KEY `actionassignedto_action_id_foreign` (`action_Id`),
  CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES
  `Employee` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `mobile_Number` varchar(15) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1

Now, lets say we have a Action with id = 10 and its Assigned to 2 Employees with id: 1000 and id: 1001

ActionAssignedTo Table :

action_Id |  emp_Id
-------------------
10        |  1000
10        |  1001

My current query only returns a single record with emp_Id id : 1000. How can I modify the query so that the query returns both the assigness but it also checks that the

ActionAssignedTo.emp_Id = 1000 (as only Employee who either created or are assigned that action can view the action).

If I understand your question, you should think about it this way:

Select aat.action_id, aat.emp_id
    from ActionAssignedTo aat
    where aat.action_id = 10

gives you the keys you want.  You just expand that to include look-ups to the other tables to get the rest:

Select {whatever columns you want from any of the tables}
    from ActionAssignedTo aat
    inner join action a on a.id = aat.action_id
    inner join employee emp on emp.id = aat.emp_id
    where aat.action_id = 10

In your query, you are limiting the results to only action 10 in the second INNER JOIN and then further limiting the results to employee 1000 in the WHERE clause.  It's not employee 1000 and all the other employees who were also assigned action 10; it's just all the employees who were assigned action 10 (employee 1000 will be among them).  The solution (unless I'm totally misreading what you're trying to do) is fairly simple and your original approach was over-complicating things.

I hope this helps.

AMENDED:

I looked at your newly posted query.  Still looks like it;s getting in it's own way but it made me wonder if you're trying to test for Employee 1000 as either the assigner or the assignee (since you're joining the employee table to itself again).  So I added adding logic to do that additional test in the where clause on the off-chance that is the sticking point:

Select {whatever columns you want from any of the tables}
    from ActionAssignedTo aat
    inner join action a on a.id = aat.action_id
    inner join employee emp on emp.id = aat.emp_id
    where aat.action_id = 10
        and (aat.emp_Id = 1000 or a.emp_Id = 1000)

I'm inferring that ActionAssignedTo.emp_Id and action.emp_Id represent the assignee and assigner respectively - hope I got that right,

I hope this helps.


Why i can't use table name in subquery? I have a MS-ACCESS database. When i try to do this query:
SELECT *
FROM
(SELECT * FROM tblClients AS a1 LEFT JOIN
(SELECT TOP 1 lVersion, lRequestID AS lVerReqId, lClientId FROM tblClientsVersionsHistory WHERE ID = (SELECT MAX(ID) FROM tblClientsVersionsHistory WHERE lClientId = a1.Id)) AS a2
ON a1.Id = a2.lClientId)

It suggest me to enter a1.Id, but i want to use it from (SELECT * FROM tblClients AS a1).
Thanks in advance!

The a1 alias belongs to a table to which the a2 derived table is being joined, and you are trying to reference the alias from inside the derived table. You cannot do that in SQL. This is not just a limitation of MS Access.
If it was a correlated subquery and the alias in question was assigned at any of the outer levels, such a reference would be valid, but a derived table is not allowed to reference aliases of any of the tables it is being joined to. You may reference them in the derived table's ON joining predicate. In this case, a1.Id is a valid reference in ON a1.Id = a2.lClientId, and it would be equally valid if it was used in a correlated subquery in the ON clause, for instance:
ON EXISTS
  (
    SELECT
      ... something something ...
      ... a1.Id ...
      ...
  )

That would work.  But such references cannot be resolved inside the derived table itself. As a result, you cannot reference a1 inside a2.


If I add an additional column to handle special values, how will this impact my table size? I have a table TABLE1 with 400,000 row (by now) that has 20 columns.
I have a column DETAIL that usually stores 100 to 300 characters, that's why its size is 1024, but I have an special case. I need to store 1900 chars for one row only.
The solution that I found is creating another column, DETAIL2 to store remaining characters, but I'm afraid that this will cause my table size to increase a lot (just one row would have DETAIL2, others will be NULL).
Is this the right solution or is there a better way to store the special case?

Change the datatype of Detail to VARCHAR(2000) or TEXT*.  This will be enough to handle your 1900 length value plus a little padding for future exceptions.  This will add no additional overhead beyond the additional storage needed for that particular row.
If you add another column you will add 1 byte to all of the rows (which isn't a big deal) but now you have to contend with two columns with different names but the same purpose.
Postgres documentation explains storage requirements in more detail:
https://www.postgresql.org/docs/current/datatype-character.html
*The TEXT datatype is not ANSI compliant, if you do use it understand the implications.