SQL Server - Select where using split - without declaring function SQL Server. Compatibility level 120.

I have a table where one VARCHAR column contains semicolon-separated integral values. I would like to SELECT against the values in this column as if they existed in a column of ints somewhere.

Because of the compatibility level, I can't use STRING_SPLIT. I also cannot create any globally available functions.

I can however create a temporary table if needed. What's my course of action? Some sort of nesting of WHILE loops (One for the rows of the table, another for the semicolons in the string)?

update: This is perhaps a better explanation of what I want to achieve:

Let's say I have the following tables.

CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);

CREATE TABLE Customer
(
    CustomerId int
    , CustomerName varchar(30)
);

With some data

INSERT INTO Customer (CustomerId, CustomerName)
VALUES  (1, 'John')
    ,(2, 'Niels')
    ,(3, 'Frank')
    ,(4, 'Barbie')
    ,(5, 'Holly')
    ,(6, 'Ebeneezer');

INSERT INTO Service (ServiceId, CustomerIds)
VALUES  (1, '4')
    ,(2, '2;3');

And let's say I want to select Customer.CustomerName for any Customer who's ID shows up in the CustomerIds in any line in that table.

That is, I want to retrieve Niels, Frank and Barbie.

A possible solution might be Splitting Delimited Strings Using XML in SQL Server.  From that post:

  Problem

  This article will help developers looking for a way to split delimited
  strings in a single query using XML. We generally use a user defined
  function to do this, which you have probably found in many places that
  splits the string based on the delimiter passed. But, when it comes to
  separating the string in a single query without any help of a user
  defined function there are not many options. I have found a much
  simpler and shorter way of splitting any string based on a delimiter.
  I will be using the power of XML to do the splitting of the string
  instead of a user defined function.

  Solution

  Let's say for example there is a string 'A,B,C,D,E' and I want to
  split it based on the delimiter ','.

  The first step would be to convert that string into XML and replace
  the delimiter with some start and end XML tags.

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT @xml

Once the string is converted into XML you can easily query that using XQuery

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

| value |
|-------|
| A     |
| B     |
| C     |
| D     |
| E     |

UPDATE 2019-12-09

Based on your updated question, here is a sample solution

set nocount on;
drop table if exists Service;
CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);
drop table if exists Customer;
CREATE TABLE Customer
(
    CustomerId int
    , CustomerName varchar(30)
);
INSERT INTO Customer (CustomerId, CustomerName)
VALUES  (1, 'John')
    ,(2, 'Niels')
    ,(3, 'Frank')
    ,(4, 'Barbie')
    ,(5, 'Holly')
    ,(6, 'Ebeneezer');
INSERT INTO Service (ServiceId, CustomerIds)
VALUES  (1, '4')
    ,(2, '2;3');
-----------------------
;WITH ServicesAsXml
AS (
    SELECT ServiceId
        ,cast(('<X>' + replace(CustomerIds, ';', '</X><X>') + '</X>') AS XML) AS xmlcol
    FROM Service
    )
    ,ServicesSplit
AS (
    SELECT ServiceId
        ,T.N.value('.', 'varchar(10)') AS CustomerId
    FROM ServicesAsXml
    CROSS APPLY xmlcol.nodes('X') T(N)
    )
SELECT CustomerName
FROM Customer c
JOIN ServicesSplit ss ON ss.CustomerId = c.CustomerId

| CustomerName |
|--------------|
| Barbie       |
| Niels        |
| Frank        |


MySQL using swap during conversion to InnoDB I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions rows - 160 million rows (anywhere from 10G to 25G), but the same thing is happening regardless of table size.

During the "INSERT INTO ... SELECT * FROM..." MySQL starts using swap memory and I have to kill the conversion because it will take over 10 hours or so.  While researching the conversion process, I found multiple sources that say that innodb_buffer_pool_size should be about 70% of your server RAM, so I made it about that, but I think that may be what's causing the issue.

Here are some important my.cnf settings:

innodb_buffer_pool_size=64G
innodb_flush_method=O_DIRECT
innodb_log_file_size=224K
innodb_log_buffer_size=24M
innodb_flush_log_at_trx_commit=0
innodb_additiona_mem_pool_size=24M

key_buffer_size=8G
sort_buffer_size=4M
read_buffer_size=128K

Any ideas what I'm doing wrong/why it's going to swap?  Any ideas would be helpful.

To switch of MySQL engine you could :

1 Make a ALTER TABLE myTable ENGINE=InnoDB

2 Make a mysqldump of your table, then edit the CREATE TABLE statement to replace MyISAM by InnoDB and restore the dump in the new table (i called it myTable_InnoDB):

DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

.

DROP TABLE IF EXISTS `mytable_InnoDB`;
CREATE TABLE `mytable_InnoDB` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Best regards


master-master replication in mongodb Did anyone tried master-master replication in mongodb. i have used replica set and master-slave replication. But we want to go with 2 data centers and need a master in each data center. i am using latest 3.2 version. Can anyone tried this kind of architecture.

MongoDB doesn't do master-master replication; you can only have a single primary:

Replication in MongoDB
A replica set is a group of mongod instances that maintain the same data set. A replica set contains several data bearing nodes and optionally one arbiter node. Of the data bearing nodes, one and only one member is deemed the primary node, while the other nodes are deemed secondary nodes.


SQL Server Agent fails after installing SQL Server 2019 CU2 After installing CU2 our SQL server agent randomly stops working.  If you go to check on the status of the agent by opening Job Activity Monitor, it comes up blank for a while then throws an error:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  For help, click:
  https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A severe error occurred on the current command.  The results, if any,
  should be discarded. (Microsoft SQL Server, Error: 0)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4013&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

Any clue what could be causing this?

Thank you for reporting this. There is a feedback item in https://feedback.azure.com/forums/908035-sql-server/suggestions/39754837-ssms-18-4-sqlserver-2019-cu2-object-explorer-fails. All updates will be provided there as we investigate.
FYI, the bug introduced in CU2 with xp_sqlagent_enum_jobs is now understood and a fix will ship in CU3. Meanwhile, if you're hitting this issue, revert back to CU1. We apologize for any inconvenience we may have caused.
The Cumulative Update 2 for SQL Server 2019 article now has the following warning:

Known issue
There is a known issue with SQL Server Agent in SQL Server 2019 CU2. This will be fixed in the next CU release (SQL Server 2019 CU3).
If you are using SQL Server 2019 SQL Server Agent, it is advised to skip this CU upgrade, and wait for SQL Server 2019 CU3.
If you have already applied SQL Server 2019 CU2 and are experiencing issues with SQL Server Agent, please revert to SQL Server 2019 CU1.

Fixed in CU3
FIX: Error occurs when you interact with SQL Server Agent in SQL Server 2019


Can you search using an index while indexes are being updated in oracle11g? I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations.

Can I still search based on an index while that index is being updated in the database?

Here is what the 11.2 Concepts Guide says about doing selects while dml is changing an index (ephasis mine):

  The database automatically maintains and uses indexes after they are
  created. The database also automatically reflects changes to data,
  such as adding, updating, and deleting rows, in all relevant indexes
  with no additional actions required by users. Retrieval performance of
  indexed data remains almost constant, even as rows are inserted.
  However, the presence of many indexes on a table degrades DML
  performance because the database must also update the indexes.

So, you might see sightly degraded performance if you have a lot of indexes and a lot of DML taking place, but I suspect this is insufficient to cause the appearance of hanging.  I suggest you trace a session to see what is slowing the search down.


Truncate/delete tables for non-owner schema Is it possible to give truncate rights to user A to truncate tables owned by user B?

But I should be able to truncate each table of B by A as per wish.

Some procedures would be needed I suppose? Kindly help!!

Platform - Oracle

You can give DROP ANY TABLE system privilege to A, then he will be able to truncate everything in any schema (not just in B's though) .

If you don't want to do that, a simple stored procedure created by B will do the job (procedure or function in Oracle are by default executed with definer rights) . Then you give need execute permission to A on this procedure.
For instance,

create procedure truncate_my_table(tab_name varchar2)
as
begin
    execute immediate 'TRUNCATE TABLE ' || tab_name;
end;

You may also want to add parameter validation (for example,  I'd do DBMS_ASSERT.ENQUOTE_NAME(DBMS_ASSERT.QUALIFIED_SQL_NAME(tab_name)) ) , error handling and logging to the procedure.


Change the row position of a record without messing anything else MySQL? Hi I would like to ask if there is a smart way to change the record position of a table in mysql. My problem is this: I have a table with statistics data and when I recover all the details in a table called "details" I get the following result:

id | name
 1 | Residents
 2 | In age from 0 to 6
 3 | In age from 6 to 14
 4 | In age from 14 to 30
 5 | In age from 30 to 66
 6 | Total school population
 7 | Students in kindergarten
 8 | Students in high school
 9 | ...
10 | ...
11 | 66 years and over

I would like the 11th record to be right after the 5th, thus right after "In age from 30 to 66". Is there a smart way to do that?

Thanks!

A database is NOT Excel. The position of a column in a table is - in regard to normal USE of the table - completely arbitrary.

To receive results from a table, you query it - and in that query, you can modify the (selected) column order to get your desired results.

In the same vein, storing computed values (your columns 2-5, 11) primarily makes sense in a time-critical context (i.e. when storage (space and speed) is no issue, but data retrieval has to be extremely quick). I'm not really seeing that in your scenario.

It would be better to simply store the age and to compute which category it belongs to in your select query.


I agree a pivot should get you what you need, but you should be aware that the column names are going to be fixed.  Something like the following should work. You could also extend this example to use dynamic column names if you use dynamic SQL.

SELECT
    *
FROM
(
    SELECT
        [from]  AS [Sender]
        , DATENAME(DW, [date]) + N'' + CAST(DATEPART(DAY, [date]) AS NVARCHAR(4)) AS [FormmattedDay] -- This output must match the 'IN' clause below
    FROM <YourTable>
) as s
PIVOT
(
    COUNT([from]) -- Aggregate
    FOR [FormmattedDay] IN ([Monday 5], [Tuesday 6], [Wednesday 7], etc.) -- Match to pivoted columns
)as [pivot]

Here is my standard disclaimer for PIVOT queries.  Try not to do them if you don't have to, and let the application layer format the data instead.  If you go that route your query would be much nicer.


mysql: show which users have permissions to a DB Is there a way to show all users that have permissions on a database?

I'm looking for something like:

> show_permissions_for_db <DB>;
+----------+----------------------+
| User     | Permissions          |
+----------+----------------------+
| jeff     | read                 |
| jill     | read, write          |
| jack     | read, write, grant   |
+----------+----------------------+

Have a look at the Query

SELECT
 md.host `Host`,
 md.user `User`,
 md.db `Database`,
 REPLACE(RTRIM(CONCAT(
 IF(md.Select_priv = 'Y', 'Select ', ''),
 IF(md.Insert_priv = 'Y', 'Insert ', ''),
 IF(md.Update_priv = 'Y', 'Update ', ''),
 IF(md.Delete_priv = 'Y', 'Delete ', ''),
 IF(md.Create_priv = 'Y', 'Create ', ''),
 IF(md.Drop_priv = 'Y', 'Drop ', ''),
 IF(md.Grant_priv = 'Y', 'Grant ', ''),
 IF(md.References_priv = 'Y', 'References ', ''),
 IF(md.Index_priv = 'Y', 'Index ', ''),
 IF(md.Alter_priv = 'Y', 'Alter ', ''),
 IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
 IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
 IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
 IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
 IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
 IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
 IF(md.Execute_priv = 'Y', 'Execute ', ''),
 IF(md.Event_priv = 'Y', 'Event ', ''),
 IF(md.Trigger_priv = 'Y', 'Trigger ', '')
 )), ' ', ', ') AS `Privileges`
FROM
 mysql.db md WHERE db='DB_NAME';

It may give result as you desired.

For Reference have a look at How to Get a List of Permissions of MySQL Users.


Remote update/restore of database through SSMS How can I update or restore a database through SQL Server Management Studio by connecting "remotely" to it?

If I know the "IP address" of machine, what else do I need to do? If a port number is required, how can I find it?

If you can connect to the server and you have the rights to do so, you can restore a database. The other thing that you'll need is to be able to get the backup files on to the servers hard drives or a network share that the SQL Server instance has rights to read.


Ramifications of Deleting Transaction Log Backup What are the ramifications of having deleted a backup of the SQL Server transaction log for a database that's in Full Recovery mode?

Caveat: I'm a developer, not a DBA, and we're a "one person" shop.

The saga: We were running out of space on our hard drive, needed to refresh Staging, and didn't have enough room to do so.  I took a transaction log backup (which apparently had not been done in a long time, if ever), tried to Shrink the trans log, but that only reduced the size by about 40GB instead of the 175GB it was taking up.  I then didn't have enough room for the upcoming nightly backup, so - in my endless wisdom - I deleted the TLOG backup I'd just taken.  Restored a copy of Prod to Staging using the 40GB I'd freed up by shrinking the transaction log the first time.  The nightly production backups succeeded and all was well.  Then this morning I took another backup of the Prod transaction log and tried to shrink it again.  This time it worked and free up most of the 135GB that it was taking up.

So... have I potentially ruined the integrity of my Production database?  What if in a few days I encounter a disaster and need to do a restore... will the fact that I deleted the first transaction log backup prevent me from doing that?

So order of operation:

1st tlog backup
delete 1st tlog backup
full backup
tlog backup
no catastrophe, just concerned over integrity

If this is correct then you will be fine. As long as the LSN or chain of (backup) events since the last full backup is not broken you should have no issues with recovery.


Postgres SQL: View with a nested table I have 2 tables users & groups:
user_id | title         group_id | title
----------------        ------------------
U_1     | User 1        G_1      | Group 1
U_2     | User 2        G_2      | Group 2
                        G_3      | Group 3

I have a mapping table between users and groups (a many to many relationship):
members
group_id | user_id | user_type
------------------------------
G_1      | U_1     | admin
G_1      | U_2     | member
G_2      | U_1     | admin
G_3      | U_2     | admin

I'm looking to create a materialized view with nested tables as follows
user_id | title  | groups
----------------------------------------
U_1     | User 1 | group_id | user_type
        |        |----------------------
        |        | G_1      | admin
        |        | G_2      | admin
----------------------------------------
U_2     | User 2 | group_id | user_type
        |        |----------------------
        |        | G_1      | member
        |        | G_3      | admin

In my node.js app, I'm basically expecting the following
[
{
    "user_id": "U_1",
    "title": "User 1",
    "groups": [{"group_id": "G_1","user_type": "admin"},{"group_id": "G_2","user_type": "admin"}]
},
{
    "user_id": "U_2",
    "title": "User 2",
    "groups": [{"group_id": "G_1","user_type": "member"},{"group_id": "G_3","user_type": "admin"}]
},
]

This was easy to achieve using MySQL (cast multiset), but I'm having trouble with postgres... Plz help...

There is no such thing as a "nested table" in Postgres, but it seems you just want a JSON representation of the group assignments for each user.
The following query does this:
select u.user_id,
       u.title,
       jsonb_build_object(
              'user_id', u.user_id,
              'title', u.title,
              'groups', jsonb_agg(jsonb_build_object('group_id', g.group_id, 'user_type', m.user_type))) as groups
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by  u.user_id, u.title;

Given your sample data, the above returns:
user_id | title  | groups
--------+--------+-----------------------------------------------------------------------------------------------------------------------------------------
U_1     | User 1 | {"title": "User 1", "groups": [{"group_id": "G_1", "user_type": "admin"}, {"group_id": "G_2", "user_type": "admin"}], "user_id": "U_1"}
U_2     | User 2 | {"title": "User 2", "groups": [{"group_id": "G_1", "user_type": "member"}, {"group_id": "G_3", "user_type": "admin"}], "user_id": "U_2"}

Online example


MySql - Read only on slave I want to make my MySql slave read only, i've been looking and found the read_only option, but its says that users with super privalges can still write (if i understand the text correctly), here is the grants for my app:

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%'

will the app be able to write to the slave?

Here are the grants you just mentioned

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%'

Based on this, you should not be able to write to the Slave because SUPER is a system level privilege (SUPER only appears in mysql.user as Super_priv) and not a DB level privilege (SUPER does not appear in mysql.db). The first GRANT lacks SUPER privilege. The second GRANT does not have a SUPER privilege context at all.

Having SUPER privilege has a lot of firepower because you can run the following

CHANGE MASTER TO
KILL
SET GLOBAL
PURGE BINARY LOGS

Not every user needs this. Giving the SUPER privilege to just anyone can actually hamper a DBA from logging into mysql if max_connections is reached.


Noob question about user accounts I'm new to this whole database thing. Got a uni project which I was hoping y'all could help with.

So I'm creating a webapp that is essentially an online art gallery store. The database will contain an inventory of items and possibly a customer list (this is what I'm not sure about). I want to allow for various levels of manipulation to the inventory list.

I want user 1 let's call that "sales" to add new items, edit and delete. I also want sales to to the same for a customer list.
I want user 2 "artists" to be able to add new, edit what they added and delete what they've added to the inventory list, along with their own info.
I want user 3 "customer" to be able edit one field in the inventory list (quantity required), along with their own info.

So would this come under rules and privileges?? I want an admin account to oversee the db and all connections. Would I need to create separate rules for all others? Do i add type 1 "sales" as admin with certain rights? Can I contain new users type 2&3 in a customer list and have that list manipulate the inventory list with rules? E.g. So if customer list is type 2, user is an "artist" and can add new stock, but if they are type 3, they are "customer" and can only edit quantity.

Again I am a noob. I hope what I'm asking makes sense. Thanks for your help kind folks.

In a scenario like this, there are often two completely separate kinds of "user accounts".

First you have your database's user account - this is (or at least, should be) required for the webserver to access the database.

Then, you have your application's user accounts. Everyone using the application should have their own account.

While it may be possible to assign individual user accounts for all application users to the database itself, this is often not practical. In most cases, I would keep these two things separate.

So, your application would have its own user table, with passwords and other user information. I'd also have a role table, listing out the types of users you can have: sales, artist, or customer. You would add the id of the appropriate row in the role table into the user table, and would determine what options are provided on-screen based on those roles.

At the database level, you could use just one user. That user has sufficient access to the database to run any commands any user might need; control of which users can run which commands is based on the application-level user.

You could have 4 users. One, "login" (use better names, by the way) would only be able to access the information required to authenticate your application-level users. Once they're authenticated, you could have one user for each of the three roles, with their rights within the database limited by what's needed for that role.

Practically speaking most websites I've worked on (including e-commerce sites) have used a single account for all DB connections, and controlled access at the application level.

If this is for a class assignment, and the class isn't about security, then I'd keep things simple (at first at least - you can play around if you've finished the basics early - you don't want an over-complicated security system to prevent you from completing the basic course requirements). Worry more about SQL injection attacks and that sort of thing; even with the four DB user solution suggested, one artist could potentially use a SQL injection attack to destroy other artists' data.


Error from Program Compatibility Assistant when installing SQL Server 2008 I am new to this so please do not mind if this is dumb question.

I am receiving an error while installing SQL Server 2008 on Windows 8.1
I have .net 4. I have windows installer 4.5

From Mark Sinkinson's enigmatic comment, you'll notice that that earlier versions are not compatible on Windows 8. 2008 & 2008R2 are supported, but you need to update them to their most recent service packs.

I agree though, Program Compatibility Assistant isn't the most assisting.

Basically, you just need go through the "Run the program without getting help" option, and then when the installation is finished apply the most recent Service Pack (2 for 2008R2, 3 for 2008).

This MSDN blog post goes into further details on this.

Not really your question but you might want to head over to Brent Ozar's website for a handy checklist/guide for SQL Server installations. I've done quite a few of them but still have that to hand whenever I do a new one.


Automated backups sql server IaaS We have installed sql server on a virtual machine and we have registered the machine so that way SQL Server IaaS Agent Extension (SQLIaaSAgent) gets enable and runs on Azure virtual machines to automate administration tasks for sql server. One of those task is automate the backups as you can see the option in the next image

Now, to perform the automated backups we need an storage account, we have created that storage account and inside the storage account a blob container, but the problem we have right now is that when we are selecting the storage where we want the backups to go we can only select the storage account but not the container, So where are the backups going? we can't find them in the storage account, how can we be sure the backups are being made? and how can we select the blob storage in this feature?. thanks in advance

From

  An Azure storage account to use for storing Automated Backup files in
  blob storage. A container is created at this location to store all
  backup files. The backup file naming convention includes the date,
  time, and database GUID.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-backup

If you turn that feature off in the portal, you can still use the underlying engine feature SQL Server managed backup to Microsoft Azure, which allows you to specify the container.


Request Sch-M lock on a table I have access to a database in read uncommitted mode and need to do maintenance on a table in an operative environment. I need to acquire an exclusive lock on a table, do a job, then release the lock.
During this maintenance even read queries should not get uncommitted data back.
This question and answer looks promising. I do:
begin transaction;
SELECT TOP (1) 1 FROM a WITH (TABLOCK);

but then, a SIX lock is acquired on table a. This however does not lock the table from select queries. How can I acquire Sch-M lock on the table? At most I can get the Sch-S lock with:
SELECT TOP (1) * FROM a WITH (TABLOCK);

this still does not block select queries on the table.

There are any number of ways to do literally what you ask, but I would generally advise against using any of them. In most cases, all you need do is prevent any changes to the table while your process completes. This can be done with a TABLOCKX hint inside the transaction:
SELECT TOP (0) NULL FROM Production.Product AS P WITH (TABLOCKX);

You could also temporarily revoke permissions, or use an application lock. Other alternatives exist; the best approach depends on the details.
If you really want to acquire Sch-M (a system lock), one way is to call sp_compile e.g.:
-- AdventureWorks sample database
BEGIN TRANSACTION;
EXECUTE sys.sp_recompile @objname = N'Production.Product';
SELECT * FROM sys.dm_tran_locks AS DTL WHERE DTL.request_session_id = @@SPID;
ROLLBACK TRANSACTION;

If the entire contents of the table are invalidated by whatever maintenance you are doing, you'll naturally get a Sch-M lock when you perform a TRUNCATE TABLE. Again, what is best depends on details that have not been provided.


MySQL table design / architecture, table is too big I have a MySQL DB that contains a lot of text, I'm fetching data from a website and inserting it into a table.

I'm using a SSD HD (100GB) for the DB and I'm out of space, I think that something in the table structure made it too big, I can't predict the size of all the columns so I'm using varchar\text\medium text for most of the fields. when I insert all the data to the DB I monitor the errors and when I see that a certain field is too small for the data I'm trying to insert I'm increasing the size of the field (e.g. from varchar(1000) to varchar(2000)).

until now I have about 1.8M~ rows, I think that I'm doing something wrong.

here is the structure of my table -

CREATE TABLE `PT` (
  `patID` int(11) NOT NULL,
  `Title` varchar(450) DEFAULT NULL,
  `IssueDate` date DEFAULT NULL,
  `NoFullText` tinyint(1) DEFAULT NULL,
  `Abstract` text,
  `ForeignReferences` varchar(15000) DEFAULT NULL,
  `CurrentUSClass` varchar(2200) DEFAULT NULL,
  `OtherReferences` mediumtext,
  `ForeignPrio` varchar(900) DEFAULT NULL,
  `CurrentIntlClass` varchar(3000) DEFAULT NULL,
  `AppNum` varchar(45) DEFAULT NULL,
  `AppDate` date DEFAULT NULL,
  `Assignee` varchar(300) DEFAULT NULL,
  `Inventors` varchar(1500) DEFAULT NULL,
  `RelatedUSAppData` text,
  `PrimaryExaminer` varchar(100) DEFAULT NULL,
  `AssistantExaminer` varchar(100) DEFAULT NULL,
  `AttorneyOrAgent` varchar(300) DEFAULT NULL,
  `ReferencedBy` text,
  `AssigneeName` varchar(150) DEFAULT NULL,
  `AssigneeState` varchar(80) DEFAULT NULL,
  `AssigneeCity` varchar(150) DEFAULT NULL,
  `InventorsName` varchar(800) DEFAULT NULL,
  `InventorsState` varchar(300) DEFAULT NULL,
  `InventorsCity` varchar(800) DEFAULT NULL,
  `Claims` mediumtext,
  `Description` mediumtext,
  `InsertionTime` datetime NOT NULL,
  `LastUpdatedOn` datetime NOT NULL,
  PRIMARY KEY (`patID`),
  UNIQUE KEY `patID_UNIQUE` (`patID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

what should I do? I have about 20% of the data (which means I'm going to need 350GB~ space) what is the performance impact here? should I divide the table into several tables over several HDs? I'm going to use sphinx to index and query the data in the end.

should I partition the table and use another disk for the partitioned table? will it help to create several tables out of this one? what would be the best way of doing so?

most of the queries will be SELECT, I'm going to search on about 4-5 text columns in this table (full text search + Sphinx).

Thanks!

Well first there is no way that all of this should ever be in one table. You need related tables for information such as Assignee and Inventor. Please read about normalization.

But in reality are you sure that mysql is the way to go for this since you seem mostly to want to search on data that is not always in the same form. Personally, for this type of thing I would look at NoSQL databases for the text parts in conjuction with a relational database for the data that is easy to describe and determine the size of.


How to Resolve a Case of "The tablix 'xyz' contains an invalid DataSetName in SSRS? I am getting the following stubborn error in an SSRS 2012 report:

Deploying report 'abc'.
Warning : The tablix 'xyz' contains an invalid DataSetName (Expected: ‘DataSet1’; Actual: ‘DataSet2’). The DataSetName of the inner data region or group must be the same as the DataSetName of the outer scope, unless a relationship is specified on the inner scope or on the dataset.

When looking through the visual studio GUI, the dataset name is correct, so the GUI shows nothing that would merit this warning. What is the best way to resolve this?

I found a solution, so am posting for others to benefit.

The tablix receiving the warning was a tablix nested inside of another tablix. I had checked and rechecked that nested tablix, and nothing in the Visual Studio GUI was amiss. It was reporting the correct dataset.

Finally I resorted to opening up an XML editor and opened the RDL file, and sure enough, the XML file had the wrong dataset in the XML.

It was in the definition of the tablix, at this point:

<DataSetName>DataSet2</DataSetName>
<Left>0.74in</Left>
<Height>0.875in</Height>
<Width>5.64866in</Width>
<Style>
  <Border>
    <Color>LightGrey</Color>
    <Style>Solid</Style>
  </Border>
</Style>

The solution was to delete the

<DataSetName>DataSet2</DataSetName>

line from the XML entirely, because a nested tablix should not specify a dataset name, because by definition a nested tablix must use the dataset from the outer tablix.

The reason why this warning had come about was because originally I created the tablix as a standalone tablix, so it did have a dataset. After some report changes, I copied and pasted the tablix into another tablix. At that point, apparently, visual studio kept the dataset name in the paste, but did not support changing it from the GUI.


Designing a Course Equivalency Database I am trying to create a database to store information regarding equivalent courses transferring from one school to another. I am struggling to conceptualize the relationship in a way that can translate into a database. I currently have two tables, each holding the courses offered by each school. These tables contain the course code, the course name, the course description and the number of credit hours.

School 1 offers courses A, B, C, D.
School 2 offers courses W, X, Y, Z.

The equivalent courses could be:

A = W  --Taking class A at School 1 is equivalent to taking class W at School 2.

A+C = Y   --Taking classes A and C at School 1 is equivalent to taking class Y at School 2.

D = X+Y+Z  --Taking class D at School 1 is equivalent to taking classes X, Y and Z at School 2.

And so on. One or more classes at School 1 will be equivalent to one or more classes at School 2. Every course from School 1 needs to have an equivalent course at School 2, as there is a "General Elective" catch all. However, not every course at School 2 will be represented.

I'm not extremely technical and am more interested in just design at this stage. Any help is greatly appreciated.

The courses have a many-to-many relationship, but the functional dependencies can't be expressed using a single intermediate table. Consider the following:

Course A School 1 = Course X School 2
Course A + B School 1 = Course Y School 2
Course C School 1 = Course Y + Z School 2

When a course from School 1 is contained in more than one equivalency, there is no way to determine from a tuple (row or record in the table) which courses are a single equivalency:

course_school1  course_school2
A               X
A               Y
B               Y
C               Y
C               Z

This is ambiguous, as it could mean either the statement above or that A = X + Y, C = Y, C = Z etc. You will need another table to express the notion of an individual equivalency. The schema would be something like:

CREATE TABLE EquivalencyBase (
    EquivalencyId int NOT NULL,
    CourseId varchar NOT NULL )

where EquivalencyId is a user- or application-generated key, but is not unique, and CourseId is a course from School 1. So the data in this table (Courses would probably have a generated primary key of type int/bigint, but I use the letters for clarity) for the relationships above would be:

EquivalencyId  CourseId
1              A
2              A
2              B
3              C

Then the equivalent courses from the second school would be expressed in another table:

CREATE TABLE CourseEquivalency (
    EquivalencyId int NOT NULL,
    CourseId varchar NOT NULL )

where EquivalencyId is a foreign key from EquivalencyBase. The data in this table would be:

EquivalencyId  CourseId
1              X
2              Y
3              Y
3              Z

To select the groups of equivalent courses, join the tables. Obviously another table would contain the course descriptions, etc., which would simply require an additional JOIN on CourseId.

SELECT
  eb.EquivalencyId,
  eb.CourseId AS School1Course,
  c1.Title AS Course1Title,
  ce.CourseId AS School2Course,
  c2.Title AS Course2Title
FROM EquivalencyBase eb
INNER JOIN CourseEquivalency ce
ON eb.EquivalencyId = ce.EquivalencyId
INNER JOIN Courses c1
ON eb.CourseId = c1.CourseId
INNER JOIN Courses c2
ON ce.CourseId = c2.CourseId
WHERE eb.CourseId = 'C'

Result:

EquivalencyId  School1Course  Course1Title  School2Course  Course2Title
3              C              Cowboy 101    Y              Yak herding
3              C              Cowboy 101    Z              Zoology

This design could be extended to map equivalent courses for multiple schools with the addition of a school identifier in each table.


What is best way to handle transaction in sql server when inserting in 15 to 20 tables in one batch What is best way to handle transaction in SQL Server when inserting in 15 to 20 tables in one batch.

Currently I am using single transaction for multiple insert statements in stored procedure.For this I am passing table type object to the stored procedure which does multiple table insert and updates. However, it is taking too much time.

CREATE PROCEDURE InsertRecurringInvoice]
(

    @InvoiceItems tabletype  object

    @InvoiceCredits table type
    @InvoiceShipmentAddress table type
    @InvoiceNotes table type
    @InvoiceAccountingEnteries table type
    @InvoiceAccountingEnteryRows table type
    ......15 table types

)
as

Begin

  begin transaction

  INSERT INTO [dbo].[InvoiceAccountingEnteries]
  SELECT * from InvoiceAccountingEnteries

  INSERT INTO [dbo].[InvoiceAccountingEnteryRows]
  SELECT * from InvoiceAccountingEnteryRows

  ...15 tabless

END

Transaction size is unlikely to be the primary cause of import slowness. There are legitimate reasons to break up transactions, even within a single table or to import a whole set of tables in a single transaction as you do.

Also, for most requirements, large or small depends on the number of rows, not the number of tables.

Reasons for larger transactions:

Easier to keep data consistency.
Can be simpler to write and maintain.

Reasons for smaller transactions:

Usually a smaller transaction log is required.
Less blocking time. Other SQL processes can be blocked, waiting for the insert to finish.
Smaller chunks are easier for the replication log reader to handle.

But none of these have much to do with insert speed. Every row is going to have to be written to the data table and to the log, whether it is in a large transaction or small one. (with standard options)

I would first look for other causes of slowness.

Are there non-clustered indexes? They get updated with the insert and can slow things down.
Is the clustering key such that the data will get added to the end of the table? That's going to be faster than making inserts at random places. You can also create a clustered index on the temp tables to get SQL to import them in a favorable order.

There are more advanced options for performance, but they usually come with tradeoffs, So I'd check the basics first.


Why BCP creates such big files? I'm creating some reports via BCP to be sent via emails.
create TABLE ##tempsss
    (
        create TABLE ##JOB_DataAssociacao_verContrato
(
     F1        VARCHAR(6)
    ,F2        VARCHAR(200)
    ,F3        VARCHAR(22)
    ,F4        char(1)
    ,F5         varchar(10)
    ,F6         varchar(15)
    ,F7         varchar(30)
    ,F8          varchar(10)
    ,F9          VARCHAR(18)
)  --it needs to be varchar to be able to use header
    )

insert into  ##tempsss
SELECT
...fields...
FROM some table or view
go
-------------------------------------------------------------
--passo 2 --

exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepath\filename.xls" -U sa -P password -w  -S servername'
-------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dba profile or something',
    @recipients = 'recipients email',
    @subject = 'email subject',
    @file_attachments='path\filename.xls'
-------------------------------------------------------------

drop table ##tempsss

with a 9k rows, I have a 2mb file. I just copy the content of this file, and paste inside an empty excel file and I have 160KB.
how can I use bcp but creating smaller files?
I'm havin gproblems to send files over 1mb via dbmail.
I set dbmail to send files with 10mb+. smtp is ok too.

Excel uses compression internally when saving a file, that is the most likely reason why you're seeing a much smaller Excel file.
I would recommend compressing the .bcp file prior to sending it via email.  Use a command-line compressor such as 7-Zip.
Also, I would recommend using the BCPs Native (-n) or Unicode Native format, via the -N option. Be careful using -c mode if you have Unicode (nvarchar/nchar) data types in your export.

Native format maintains the native data types of a database. Native format is intended for high-speed data transfer of data between SQL Server tables. If you use a format file, the source and target tables do not need to be identical. The data transfer involves two steps:

Bulk exporting the data from a source table into a data file
Bulk importing the data from the data file into the target table.

The use of native format between identical tables avoids unnecessary conversion of data types to and from character format, saving time and space. To achieve the optimum transfer rate, however, few checks are performed regarding data formatting. To prevent problems with the loaded data, see the following restrictions list.

From the Books Online for bcp.exe command-line utility:

-n
Performs the bulk-copy operation using the native (database) data types of the data. This option does not prompt for each field; it uses the native values.

For more information, see Use Native Format to Import or Export Data (SQL Server).

-N
Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL Server to another using a data file. It does not prompt for each field. Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode.

For more information, see Use Unicode Native Format to Import or Export Data (SQL Server).


Inserting multiple columns Is there a way to insert two or more columns in a table from a SELECT statement?

E.g.
If i do two insert:

INSERT INTO temp_table(dist0)
SELECT min(term1)
FROM graph_path
WHERE distance = 0
GROUP BY term2_id
ORDER BY term2_id;

AND

INSERT INTO temp_table(dist1)
SELECT min(term1)
FROM graph_path
WHERE distance = 1
GROUP BY term2_id
ORDER BY term2_id;

The result is:

dist0    dist1
1
2
3
          4
          5
          6

How can i get a result like this? :

dist0   dist1
1         4
2         5
3         6

Postgresql version 9.5

AS ASKED:
E.g. with term2_id

select term2_id, min(term1_id)
from graph_path
where distance = 1
group by term2_id
order by term2_id;

RESULT:

term2_id   min
2          1507
3          107
5          1

We insert rows, not columns, in a table.

You need a query that returns both results, combined in 3 rows. Assuming that the term2_id values should match:

INSERT INTO temp_table
  (dist0, dist1)
SELECT
  min(term1) FILTER (WHERE distance = 0),
  min(term1) FILTER (WHERE distance = 1)
FROM graph_path
WHERE distance IN (0, 1)
GROUP BY term2_id
ORDER BY term2_id ;


How to restore from a backup of a very old version of DB2 (version 6.1 backup)? I am helping a client with some legacy application that is still running DB2 6.1 on Windows 2000! I am helping them migrate to newer software/hardware infrastructure.

I have a backup of the DB2 6.1 database on Windows 2000.
As a first step I want to restore a new database image from this backup on a
newer version of DB2.
The Latest version of DB2 does not support restoring from a v6.1
backup.

Can I find a relatively new version of DB2 that will restore a v6.1 DB2
backup?

Any insight into options available for my scenario are appreciated.

Thanks,
Kiran

Upgrade via restore is usually supported for databases two versions down, so to move from DB2 6.1 to DB2 10.1 (current today) you'd need to move to DB2 8.2 first, then to DB2 9.5, then to DB2 10.1. There are many things that have changed between all these versions, so it's very unlikely that the process will be smooth. Your best option would be to extract the database DDL statements and data, then create a new DB2 10.1 database and load data into it.


Freeing Unused Space SQL Server Table I have a table in SQL Server 2012 Express with a lot of unused space.

I need to free up space in the database.

|        NAME |   ROWS |     RESERVED |         DATA | INDEX_SIZE |       UNUSED |
|-------------|--------|--------------|--------------|------------|--------------|
| MyTableName | 158890 |  8928296 KB  |  5760944 KB  |   2248 KB  |  3165104 KB  |

How do I get SQL to release the 3165104KB?

I've already tried:

Alter table MyTableName Rebuild
DBCC CLEANTABLE (MyDbName,"MyTableName ", 0)
ALTER INDEX ALL ON MyTableName REORGANIZE ;
ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF)

Here is the table:

CREATE TABLE [dbo].[MyTableName](
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [DateScan] [datetime] NULL,
    [ScanImage] [image] NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED
(
    [ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The only thing we have done is replaced ScanImage on every row with a much smaller image (this is how so much unused space is there).

The only thing we have done is replaced ScanImage on every row with a
  much smaller image (this is how so much unused space is there)

From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in).

Example code that achieved the best space reduction for me with the table structure in the question is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRAN

SELECT [ImageID],
       [ScanImage]
INTO   #Temp
FROM   [dbo].[MyTableName]

ALTER TABLE [dbo].[MyTableName]
  DROP COLUMN [ScanImage]

/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD

ALTER TABLE [dbo].[MyTableName]
  ADD [ScanImage] IMAGE NULL

UPDATE [dbo].[MyTableName]
SET    [ScanImage] = T.[ScanImage]
FROM   [dbo].[MyTableName] M
       JOIN #Temp T
         ON M.ImageID = T.[ImageID]

DROP TABLE #Temp

COMMIT

Everything is in a transaction so if the machine crashes it will be rolled back. Could probably do with some error handling or at least SET XACT_ABORT ON. I used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; to prevent any concurrent modifications from happening during or after the copy and being lost.

The number of LOB pages reserved after reducing the size of an image in all rows was as follows:

+--------------------------------------------------+---------------------+-------------------------+
|                      Event                       | lob_used_page_count | lob_reserved_page_count |
+--------------------------------------------------+---------------------+-------------------------+
| Inserted 10,000 rows with 100,000 byte data each |              135005 |                  135017 |
| Updated all rows to 10,000 byte image data       |               31251 |                  135012 |
| Reorganize                                       |               23687 |                   25629 |
| Drop and re-add image data                       |               13485 |                   13489 |
+--------------------------------------------------+---------------------+-------------------------+


MYSQL any way to improve this query's performance? MYSQL version 5.6.23

CENTOS 7

Hi,

I have the following query that is coming from an application that I am hoping to make more efficient  from the mysql side of things.

I have an index built on each of the columns in tables 'files', 'filesystem', and 'instance'.

SELECT t0_f.pk, t0_f.created_time, t0_f.md5_check_time, t0_f.filepath, t0_f.file_tsuid, t0_f.file_md5, t0_f.file_status, t0_f.file_size, t0_f.instance_fk, t0_f.filesystem_fk
FROM files t0_f, filesystem t1_f_fileSystem, instance t2_f_instance
WHERE (t0_f.file_status = 0
AND t0_f.file_tsuid
IN ('1.2.840.10008.1.2', '1.2.840.10008.1.2.1', '1.2.840.10008.1.2.2')
AND t1_f_fileSystem.pk = 1
AND t2_f_instance.sop_cuid = '1.2.840.10008.5.1.4.1.1.2'
AND t0_f.created_time < '2015-03-26 09:29:58'
AND t0_f.filesystem_fk=t1_f_fileSystem.pk
AND t0_f.instance_fk=t2_f_instance.pk)

Here is the explain:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1_f_fileSystem', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', '100.00', 'Using index'
'1', 'SIMPLE', 't0_f', 'ref', 'instance_fk,filesystem_fk,file_tsuid,file_created,file_status', 'filesystem_fk', '9', 'const', '74220028', '100.00', 'Using where'
'1', 'SIMPLE', 't2_f_instance', 'eq_ref', 'PRIMARY,sop_cuid', 'PRIMARY', '8', 'pacsdb.t0_f.instance_fk', '1', '100.00', 'Using where'

Would this maybe benefit from a 'combined' index or would there be any other way to improve the 'explain' short of changing the query as I have no control over that.

If there is anything else that I can post to help, please let me know!

thanks

UPDATE

I created an index 'files_compress' (the first index you suggested) and its estimate went down to 3.1M rows with a 'Index Range Scan'.  I added the 2nd one as well, but it didn't change the results.

Here is the new 'Explain'

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1_f_fileSystem', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', '100.00', 'Using index'
'1', 'SIMPLE', 't0_f', 'range', 'instance_fk,filesystem_fk,file_tsuid,file_created,file_status,files_compress,files_compress_2', 'files_compress', '772', NULL, '3192823', '100.00', 'Using index condition; Using where'
'1', 'SIMPLE', 't2_f_instance', 'eq_ref', 'PRIMARY,sop_cuid', 'PRIMARY', '8', 'pacsdb.t0_f.instance_fk', '1', '100.00', 'Using where'

Do you think there is any more room for improvement?  I know that its way better than 74M rows!

thanks again!

On files, add these compound indexes:

INDEX(filesystem_fk, file_status, file_tsuid, created_time),
INDEX(filesystem_fk, file_status, created_time)

(I am not sure which will help more.)


Architecture of table for lot of inserts per second in SQL Server 2019 We are in process of development of project which will process around 51 000 000 records daily (~around 500-600 inserts per second). We choose Always On solution which will contain:

One Primary Server
Three replicas which has to work in synchronous mode

Main table of this project will contain 45 columns with several data types. Like I wrote above to this table we will have to insert around 500-600 rows per second (idenepndly, I mean that will be procedure insertMainTable which will insert one row to this table will be executed 500-600 times per second).

According to this efficiency we decide to create this table as partition table. Our idea is to create 32 partitions which base on smallint column in this table. Due to this solution we have idea to create 16 file groups with 32 files (so 1 file group will contain 2 files). So every partition will be in different file. What is more every 2 files will be on different filegroup. I was testing this solution and have results below:

https://stackoverflow.com/questions/60339635/sql-server-2019-always-on-performance

My question is:

Do you think that this kind of solution is enough/to complicated comparing to goals? What would you recommend?

I have some experience with high throughput tables, partitioning and Availability Groups, so I feel confident enough to post this as an answer, although the best I can really do is raise points for you to consider.

First - your 3 synchronous replicas are going to be the slowest links in your chain.  Every commit is going to have to wait for all three to confirm before the client receives the commit signal.  I don't know what to tell you here but make sure you are on fast network and fast disks.

Second - I don't think you need the multiple files to make your solution fast and it may slow things down.  If you move items between partitions then if they are in the same filegroup that's fairly fast and meta-only.  However, if each partition has it's own file/filegroup and you move things around, the system has to pick that data up and physically move it instead of just updating an index.

Third - What is the goal/point of your partitioning?  You should be able to run many hundreds of inserts to a single table.  And SQL 2019 has improvements for highly concurrent inserts to avoid the hot spot issue.  Have you tested it without all the partitioning overhead?

Are you going to be updating this data?  Are you going to be purging the data later?  If you are purging the data later then http://kejser.org/table-pattern-rotating-log-ring-buffer/ may be of use to you.  I've implemented that pattern in my production environment to great success.

What are the datatypes for this 45 column table?  If you are using VARCHAR(MAX), XML or other large datatypes then there may not be much that can be done without re-architecting the table.

Partitioning isn't really great at speeding table access up.  It's main benefit is in allowing for rapid maintenance activities.  Such as quickly loading new data, or discarding old data.


Select permission to each table I have three schemas Production, HRecourse and Sales,tables in every week create and drop , I have six Users P_User1, P_User2, HR_User1, HR_User2 and S_User1 and S_User2. I have to grant then how i can Select permission to each table in their respective Schemas. how i tackle this scenario with SQL Scripts.

nstead of dropping and re-creating, why not just keep the same tables and truncate them and re-populate them? Then you don't have to mess with permissions each week.

However, if you want to keep doing this the hard way, this script might help.

SQL Server 2008+:

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

;WITH [schemas] AS
(
  SELECT id = [schema_id], name
  FROM sys.schemas
  WHERE name IN (N'Production',N'HResource',N'Sales')
),
[users] AS
(
  SELECT u, [schema] = s FROM
  (
    VALUES
    (N'HR_User1',N'HResource'),
    (N'HR_User2',N'HResource'),
    (N'P_User1', N'Production'),
    (N'P_User2', N'Production'),
    (N'S_User1', N'Sales'),
    (N'S_User2', N'Sales')
  ) AS v(u,s)
)
SELECT @sql += N'
GRANT SELECT, UPDATE, INSERT ON '
  + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' TO ' + QUOTENAME(u.u) + ';'
FROM [schemas] AS s
INNER JOIN [users] AS u
ON s.name = u.[schema]
INNER JOIN sys.tables AS t
ON s.id = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;

If you're stuck on 2005, you'll need to make a couple of changes (and start thinking about your next move):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

;WITH [schemas] AS
(
  SELECT id = [schema_id], name
  FROM sys.schemas
  WHERE name IN (N'Production',N'HResource',N'Sales')
),
[users] AS
(
  SELECT [user]    = N'HR_User1', [schema] = N'HResource'
    UNION ALL SELECT N'HR_User2',            N'HResource'
    UNION ALL SELECT N'P_User1',             N'Production'
    UNION ALL SELECT N'P_User2',             N'Production'
    UNION ALL SELECT N'S_User1',             N'Sales'
    UNION ALL SELECT N'S_User2',             N'Sales'
)
SELECT @sql = @sql + N'
GRANT SELECT, UPDATE, INSERT ON '
  + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' TO ' + QUOTENAME(u.u) + ';'
FROM [schemas] AS s
INNER JOIN [users] AS u
ON s.name = u.[schema]
INNER JOIN sys.tables AS t
ON s.id = t.[schema_id];

PRINT @sql;
-- EXEC sp_executesql @sql;


SQL Server automatic transaction log truncation Have looked through msdn but cant find answers to these:

Any help much appreciated:

In simple recovery mode:

Is there any TSQL or alternative functionality that will provide a notification either written to the event logs or a message or an alert , stating that the Microsoft automatic transaction log truncation is happening i.e when a checkpoint is reached sql server truncates.
(According to this: http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx)
Is there anyway to find out how long it takes for automatic truncation to happen /how long it takes for the transaction log to be truncated?
Does the SQL Server condition performance alerts happen in real time or in intervals or say 5 mins?

Thanks all!

No physical truncation actually occurs, the size of the file would not get smaller.

Parts of the logfile (virtual logfile/vlf) are merely flagged as 'the content is allowed to be overwritten'. If such an event exists, and I know of none, there would be no practical duration.

If you are referring to 'auto-shrink', I would highly recommend you read this post 'Auto-shrink – turn it OFF!' by Paul Randal.


How to Compare Strings In Two Different VARCHAR Columns? I am in need of a way to compare the content of two varchar columns, named fistname and lastname correspondingly, and if the content exists in firstname, then remove it from lastname.  Ideally, I would like to do this w/o an update query, but if that is the only way to achieve it then I can go that route.

Here is sample DDL and DML:

Declare @BadData Table
(
    firstname varchar(500)
    ,lastname varchar(500)
)

Insert Into @BadData (firstname, lastname) Values
('Bridget Jones', 'Jones, III'), ('Butch', 'Jones'), ('Key West', 'West')
,('Bob Marly', 'Junior')

Select * From @BadData

Which gives the resultset of:

firstname       lastname
-------------   ----------
Bridget Jones   Jones, III
Butch           Jones
Key West        West
Bob Marly       Junior

My desired resultset is:

firstname       lastname
-------------   --------
Bridget Jones   , III
Butch   Jones
Key West
Bob Marly       Junior

I want to remove the text (that is, the content) from the lastname column if it exists in the firstname column.

How can this be done via a UDF or case statement in SQL Server 2008 R2?

First I've used an split string function that I've borrowed (again) from this answer. And I've added an ID column (I suppose your table has some PK field to identify every record.)

CREATE TABLE MyTable
(
    Id int IDENTITY,
    FirstName varchar(500),
    LastName varchar(500)
)

INSERT INTO MyTable (FirstName, LastName)
VALUES ('Bridget Jones', 'Jones, III'), ('Butch', 'Jones'), ('Key West', 'West'),('Bob Marly', 'Junior');
GO

CREATE FUNCTION dbo.fnSplit(@Input Varchar(1000), @Splitter VarChar(10))
RETURNS TABLE AS
RETURN
    SELECT Split.a.value('.', 'VARCHAR(1000)') AS Data
    FROM (SELECT CAST ('<M>' + REPLACE(@Input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes ('/M') AS Split(a);
GO

Now, using a CROSS APPLY with your data:

    SELECT *
    FROM   MyTable t1
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) > 0;
GO

You can identify the records where LastName contains any word of FirstName:

Id | FirstName     | LastName   | Data
-: | :------------ | :--------- | :----
 1 | Bridget Jones | Jones, III | Jones
 3 | Key West      | West       | West

Using ID's returned from previous query you can update your table:

WITH found AS
(
    SELECT Id, FirstName, LastName, Data
    FROM   MyTable t1
    CROSS APPLY fnSplit(t1.FirstName, ' ') t2
    WHERE CHARINDEX(t2.Data, t1.LastName) > 0
)
UPDATE     T1
SET        T1.LastName = RTRIM(LTRIM(REPLACE(T1.LastName, Data, '')))
FROM       MyTable t1
INNER JOIN found t2
ON         t1.Id = t2.Id;
GO

2 rows affected

And this is the final result:

SELECT * FROM MyTable;
GO

Id | FirstName     | LastName
-: | :------------ | :-------
 1 | Bridget Jones | , III
 2 | Butch         | Jones
 3 | Key West      |
 4 | Bob Marly     | Junior

dbfiddle here


Updating SQL Server 2008 R2 using slipstreamed source I have a requirement to create a SQL Server 2008 R2 SP1 setup media. I have done this using slipstream method as recommended by Microsoft. I can only use this to create new installation. But is there a way I can use this media to update an existing SQL Server 2008 R2 instance?

Thanks in advance,

Dheena

I don't know of a way to do this. When you build the slipstreamed install, it is no longer an SP1 installer, it simply updates the files that the initial setup program uses during installation. It does not build an RTM installer and a separate SP1 installer that are somehow daisy-chained.

So I think the answer is: you will have to download SP1 again, and run it separately from any media where you have created a slipstreamed install.


Difference between EXEC() and sp_executesql with no parameters? I just learned about the new sp_executesql stored procedure.  I like the fact that there's a way to execute parameterized code from within SQL Server.

But, I'm wondering what the difference is between using the sp_executesql stored procedure when you don't have any parameters versus just calling EXEC directly.  Also, is there a performance implication?

exec('select * from line_segment')
exec sp_executesql N'select * from line_segment'

Also, is there a difference between 2005 and 2008 or do they handle these the same?

sp_executesql supports parameterisation, whereas EXEC only accepts a string.

Only performance differences that may arise are due to the parameterisation i.e. a parameterised sp_executesql call is more likely to have a reusable cached plan. An EXEC call is likely to lead to lots of single use space wasters in the plan cache.


How to copy a tablespace to another database without using Data Pump in Oracle 11G I have a database on a legacy system that has some data that needs to be exported to another environment. Everything I need is contained in one tablespace. Unfortunately, I am unable to use Data Pump due to previously botched patching (thousands of invalid objects, including streams packages, which can't be recompiled).

Can I use RMAN or other methods to back up this tablespace, move it to another network (by physical media transfer - networks are completely segregated) and restore it to an existing database?

This is the bare minimum assuming the simplest scenario.

1. Original export/import:

Source database:

export everything from the tablespace

exp userid=user/password tablespaces=example file=example.dmp

Destination:

copy example.dmp from source
create empty tablespace at the desired location, create users manually, then:

imp userid=user/password full=y file=example.dmp

or 2. Transportable tablespace with original export/import:

Source database:

put tablespace into read only mode:

alter tablespace example read only;

export metadata for transporting the tablespace

exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=example file=example_tts.dmp

Note that you get an EXP-00044 error here if you do not connect with SYSDBA.

Destination database:

copy example_tts.dmp from source
copy the datafile itself from source to the desired location (example01.dbf in below example)
create users manually, then:

imp userid=\'/ as sysdba\' transport_tablespace=y datafiles=/oradata/S112/example01.dbf file=example_tts.dmp

finally make the tablespace writable:

alter tablespace example read write;

More details can be found in the documentation:

Original Export

Original Import


Master DB Ran Out Of Space My master server ran out of space today (at 20:22). My transactions logs ended up broken into multiple files:

-rw-rw----  1 mysql mysql 228M Jul 31 20:22 mysql-bin.000914
-rw-rw----  1 mysql mysql  18K Jul 31 20:23 mysql-bin.000915
-rw-rw----  1 mysql mysql 1.5K Jul 31 20:24 mysql-bin.000916
-rw-rw----  1 mysql mysql 8.9K Jul 31 20:25 mysql-bin.000917
-rw-rw----  1 mysql mysql 1.2K Jul 31 20:25 mysql-bin.000918
-rw-rw----  1 mysql mysql 4.0K Jul 31 20:25 mysql-bin.000919
-rw-rw----  1 mysql mysql  982 Jul 31 20:26 mysql-bin.000920
-rw-rw----  1 mysql mysql 1.5K Jul 31 20:26 mysql-bin.000921
-rw-rw----  1 mysql mysql 2.1K Jul 31 20:26 mysql-bin.000922
-rw-rw----  1 mysql mysql  915 Jul 31 20:27 mysql-bin.000923
-rw-rw----  1 mysql mysql  17K Jul 31 20:28 mysql-bin.000924
-rw-rw----  1 mysql mysql  31K Jul 31 20:28 mysql-bin.000925
-rw-rw----  1 mysql mysql  54M Aug  1 02:00 mysql-bin.000926

This broke replication on my 4 slaves. Looking over other threads it seems I will have take a dump from the master and rebuild all my slaves. Is this correct?

Here's the slaves output.

Last_IO_Errno: 1595
Last_IO_Error: Relay log write failure: could not queue event from master
Master_Log_File: mysql-bin.000927
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000244
Relay_Log_Pos: 167
Relay_Master_Log_File: mysql-bin.000927

Skipping over the transactions that processed when it was out of space are fine. I tried advancing the position and file to the current file but that also didn't work.

stop slave;
change master to master_log_file='mysql-bin.000926', master_log_pos=0;
START SLAVE;
show slave status\G

which gave me:

Master_Log_File: mysql-bin.000926
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000926
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'

I'd prefer not having to rebuild my slaves if possible. Thanks.

This isn't an exact answer because I don't know how I did resolved the issue but the following options did resolve my issue. When I tried to create a mysql dump to re-build the server I got:

  mysqldump: Error: Binlogging on server not active

Which to me indicated there was something wrong with the mysql configuration on the master because binary logging is enabled in the my.cnf. I also looked at the most recent binary log file for the past 6 hours and it was only 2.8K. I ran

service mysql restart

which got my binary logs working as intended. The new binary log began functioning again as expected (within 5 minutes 2.8M). So now my log file became:

-rw-rw----  1 mysql mysql  54M Aug  1 02:00 mysql-bin.000926
-rw-rw----  1 mysql mysql 2.8K Aug  1 10:58 mysql-bin.000927
-rw-rw----  1 mysql mysql 2.4M Aug  1 11:02 mysql-bin.000928

I then went back to my slaves and re-ran the change master commands to the new bin log and it worked as expected.

stop slave;
change master to master_log_file='mysql-bin.000928', master_log_pos=0;
START SLAVE;

With this show slave status\G now shows:

Master_Log_File: mysql-bin.000928
Read_Master_Log_Pos: 2439078
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 299660
Relay_Master_Log_File: mysql-bin.000928
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:


MariadB Europe/Athens Timezone I am using Debian 8, mariadb 10.4, nginx 10.3, php7.3-fpm.
My timezone configuration was like this:

Debian: Europe/Athens ( through tzselect command )
PHP: Europe/Athens , Default timezone UTC
Mariadb: Europe/Athens

The problem:

My records where inserted on DATETIME fields with UTC timezone ( 3 hours earlier).

I guess it the default timezone on php that makes the problem, but I don 't know how to solve it.

DATETIME is like taking a picture of your local clock.

Check these:

SHOW VARIABLES LIKE '%zone%';

SELECT NOW();

If NOW() does not show Athens time, then something is set incorrectly.


Are there major drawbacks/risk of using oracle blob to store files Recently I was asked to do production support on an existing software.

In summary, this system is a web service that allows you to upload a file and provides you with an identifier so that you can ask for your file later.

My concern is that the files are stored as BLOB in an Oracle database.

For the moment, the system is stable, but in the near future our business wants to upload 100.000 to 200.000 PDF per year (10 to 50 Mb each).

When it comes to the Database

Should I just care about the disk space?
Are there other aspects I should worry about?
Or should I really try to switch to file system storage?

A few additional information:

There is no need for transactions.
All files are unrelated.
Files will never be updated.
Files will stay there "forever" (5 to 10 years).
The software needs to be updated for other reasons anyway.

It really comes down to cost vs. importance.  The more important the files are, the more beneficial storing them in a database becomes.  Here are some things to consider.

Pros of database file storage:

Security - The same security model your data has, same users, etc.
Backups - Usually the database is backed up more frequently, tested more frequently, and has more thorough recovery documentation.  Backups can also be faster with block change tracking.
Recovery - Old versions of files can be restored to any point in time as far back as the backups are kept.
Auditing Available.
Scalable - The more files you have the more important this becomes.
Transactional integration with other data and code.
Flashback Query (for Oracle)
Locking Available.
Read consistency.

Cons of in database file storage:

Initial setup may take longer.
Specific expertise required.

Tom Kyte said it this way:

  If this data is valuable to your business, if this data is necessary
  for your business, if the loss of this data would damage your ability
  to do business - it has no business NOT being in the database.


SQL - Emptying Secondary Data File We have two filegroup PRIMARY and SECONDARY. Objective is to merge SECONDARY partition with PRIMARY.
On running below command, following error is coming:
DBCC SHRINKFILE (N'ABC_Secondary', EMPTYFILE);
GO

DBCC SHRINKFILE: Heap page 3:54888929 could not be moved.
Msg 2555, Level 16, State 1, Line 7
Cannot move all contents of file "ABC_Secondary" to other places to complete the emptyfile operation.
The statement has been terminated.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 1105, Level 17, State 2, Line 7
Could not allocate space for object 'dbo.Contact' in database 'ABC' because the 'SECONDARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Completion time: 2021-01-23T10:51:18.9766221-07:00
See below database usage:
DbName  FileName    type_desc   CurrentSizeMB   FreeSpaceMB
ABC     ABC             ROWS    1791360.000000  357524.062500
ABC     ABC_log         LOG      904.000000       858.640625
ABC     ABC_Secondary   ROWS    483274.000000   437450.437500

the 'SECONDARY' filegroup is full

Read Database Files and Filegroups.
To move all your data off of a File to other Files in the same Filegroup, use DBCC SHRINKFILE (N'SomeFile', EMPTYFILE);
To move your data off of a Filegroup to some other filegroup rebuild it targeting the new Filegroup, using ALTER TABLE REBUILD for a heap, or by droping the clustered index and recreating it on the new filegroup.


Comparing two XML query plans Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did differently? Management Studio is being particularly ridiculous about laying them out with vast amounts of whitespace, and it won't let me reformat them to print on a single page even...

Thanks!

Give SQL Sentry Plan Explorer a try.  I am not sure it does exactly what you are asking about, but it is a great tool and free!

SQL Sentry Plan Explorer


Postgresql query plan differs with limit value making the query slower for lower limits I have this query:

SELECT  "table_1".* FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
  AND "table_1"."table_2_id" = 15797104
ORDER BY "table_1"."id" ASC LIMIT 1

The explain for the query looks like:

Limit  (cost=0.57..1208.61 rows=1 width=48)
    ->  Index Scan using table_1_pkey on table_1  (cost=0.57..46047923.71 rows=38118 width=48)
            Filter: ((deleted_at IS NULL) AND (table_2_id = 15797104))

This is incredibly slow as this table is absolutely huge.

Now, if I increase the LIMIT to 10:

SELECT  "table_1".* FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
  AND "table_1"."table_2_id" = 15797104
ORDER BY "table_1"."id" ASC LIMIT 10

This query runs a lot faster (from over 30minutes to less than a second) and the query plan looks like this:

Limit  (cost=11044.69..11044.71 rows=10 width=48)
    ->  Sort  (cost=11044.69..11139.98 rows=38118 width=48)"
        Sort Key: id
        ->  Index Scan using index_table_1_on_table_2_id on table_1  (cost=0.57..10220.97 rows=38118 width=48)
              Index Cond: (table_2_id = 15797104)
              Filter: (deleted_at IS NULL)

Note that instead of using the table_1_pkey index, it uses index_table_1_on_table_2_id. This makes much more sense. I imagine Postgresql is using the table_1_pkey to avoid doing a sort operation, but that actually ends up being a lot slower.

SELECT  count("table_1".*) FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
  AND "table_1"."table_2_id" = 15797104

returns 5000. And the whole table_1 table contains over 200M rows.

Is there a way to trick Posrgres into using the second query plan on the first query?

Generally this kind of problem occurs because the query planner thinks it will find the one row you're looking within the first 1% of the table, but it doesn't.  One solution for this can be to increase the stats collection on those columns, with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 5000.

However, in this particular case, I think simply changing the table_2_id index to a partial index will cause the query planner to favor it:

CREATE INDEX table_2_not_deleted ON table_1 ( table_2_id ) WHERE deleted_at IS NULL;

The query planner tends to favor partial indexes which match portions of the WHERE clause exactly.  If that doesn't work, I'd try creating an index which covers everything:

CREATE INDEX problem_solver_dex
ON table_1 ( table_2_id, id )
WHERE deleted_at IS NULL


MySQL Using a trigger to update quantity on another table if part is used I am new to triggers but want to know how I can use one in my database.
I have created a table for a maintenance log, a parts inventory, and a parts_used junction table. When maintenance is carried out the parts and log id's a inserted into parts_used. I want to be able to take subtract 1 from the quantity in the inventory for that part.

Maintenance log

CREATE TABLE maintenance_log (
    idlog int  (4) AUTO_INCREMENT,
    idVehicle int (2),
    description VARCHAR (80),
    entry_date DATE(),
    release_date DATE,
    idMechanic int (2),
    FOREIGN KEY (idVehicle) REFERENCES vehicles(idVehicle),
    FOREIGN KEY (idMechanic) REFERENCES mechanics(idMechanic),
PRIMARY KEY (idlog)
);

Parts

    CREATE TABLE parts (
    idParts int(3) AUTO_INCREMENT,
    part_description VARCHAR (60),
    quantity int (2),
PRIMARY KEY (idParts)
);

parts_used

CREATE TABLE parts_used(
    idParts int (3),
    idlog int  (4),
CONSTRAINT PK_partUsed PRIMARY KEY
    (
        idParts,
        idlog
    ),
    FOREIGN KEY (idParts) REFERENCES parts(idParts),
    FOREIGN KEY (idlog) REFERENCES maintenance_log (idlog)
);

My current trigger just returns ERROR with no other message. This is what I am using.

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
BEGIN
           UPDATE parts
           SET parts.quantity = parts.quantity - 1
           WHERE parts.idParts = NEW.idParts
END

DELIMITER ;

Can anybody help or offer any advice?

You need to have DELIMITER directives both before and after the CREATE TRIGGER statement, you need to end individual simple statements with semicolons, and you need to end the complete CREATE TRIGGER with the delimiter specified in the first DELIMITER directive:

DELIMITER //

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
BEGIN
  UPDATE parts
    SET parts.quantity = parts.quantity - 1
    WHERE parts.idParts = NEW.idParts;
END
//

DELIMITER ;

However, in this case your entire trigger body is a single UPDATE statement, so you can omit both the BEGIN ... END statements and the DELIMITER directives:

CREATE TRIGGER subtract_quantity
AFTER INSERT ON parts_used FOR EACH ROW
  UPDATE parts
    SET parts.quantity = parts.quantity - 1
    WHERE parts.idParts = NEW.idParts;

(The DELIMITER directives allow the parser to distinguish between the end of a compound-statement trigger or procedure body and the ends of the individual simple statements it contains, and are only required by some MySQL clients.)


When to break down attributes in tables into sub-tables for normalisation I am currently developing a data model. This model contains an entity "Asset", which has many numerical attributes, volume, quantity, price, etc...

I also have string attributes that are part of the Asset table. Say one of those is Asset Type, this can be either a currency forward, stock, fund, index, etc... This is a good representation of the general cardinality of these string attributes, they have at most 10-15 different entries. The cardinality for the Asset table though, is in the tens of thousands.

Should I create a separate table for my Asset Type attribute and reference it using a foreign key in my Asset table, or is it simpler to leave the Asset table un-normalised and store these attributes directly, as strings, in the Asset table?

Let's say you create the table and give it two columns - AssetTypeId and AssetTypeName. Both will be unique by definition so both will be candidate keys. This table is normalized.

Since both columns are a candidate key whichever of these is used in Asset it will still be normalized.

One consideration may be on-disk data size. With only about a dozen asset types a single byte would suffice for AssetTypeId whereas AssetTypeName may require 20 or more. With "tens of thousands" of assets the saving would be small (100kb or so). With lots of columns this may add up, however, so its worth thinking about. If you are using columnar storage or data compression likely this saving is already happening under the covers within the DBMS.

The flip side to having a separate tale is if you need to return the asset type name to the client an additional join will be required. You know your workload best. You must determine where the balance of benefits lies with your application. Neither way is "wrong".


How to influence the planner on Postgresql I have the following:

a complex query, 'match', that returns about 200 rows (ids)
a huge table, 'usernames', indexed on id that maps them to usernames

When I try to left join the query (1) with the table (2) the planner decides to do a seq scan on the (2) table. This ends up taking about 90ms on my machine.

i.e:

   ->  CTE Scan on match l  (cost=0.00..3349.54 rows=167477 width=4) (actual time=3.458..5.188 rows=200 loops=1)
   ->  Hash  (cost=1638.00..1638.00 rows=100000 width=18) (actual time=66.537..66.537 rows=100000 loops=1)
         Buckets: 8192  Batches: 2  Memory Usage: 2542kB
         ->  Seq Scan on usernames r  (cost=0.00..1638.00 rows=100000 width=18) (actual time=0.019..27.595 rows=100000 loops=1)
 Planning time: 16.261 ms
 Execution time: 77.425 ms

If I add ('limit 200') in the query (1), the query time drops down to 15ms

   ->  CTE Scan on match l  (cost=0.00..4.00 rows=200 width=4) (actual time=3.623..6.163 rows=200 loops=1)
   ->  Index Scan using usernames_userid_idx on usernames r  (cost=0.29..6.71 rows=1 width=18) (actual time=0.017..0.017 rows=1 loops=200)
         Index Cond: (l.id = userid)
 Planning time: 4.682 ms
 Execution time: 10.093 ms

I understand that this is due to the planned not estimating correctly the number of returned rows of my 'match' query.

Is there anyway to hint the planner to do an Index Scan on the join, instead of the Seq Scan?

The complete query is:

EXPLAIN ANALYSE

WITH match AS

 (SELECT
   dist_match.match_userid AS id
     FROM

      (SELECT
        me.into_game AS into_game, r5.miles, r5.useridy AS match_userid
        FROM
          (SELECT l.id AS userid,
           category,
           r2.game,
           r3.game AS into_game,
           distance AS into_distance
           FROM userids l
           LEFT JOIN category r1 ON l.id = r1.userid
           LEFT JOIN games r2 ON l.id = r2.userid
           LEFT JOIN into_games r3 ON l.id = r3.userid
           LEFT JOIN into_distances r4 ON l.id = r4.userid
           WHERE l.id = 68931) AS me
         LEFT JOIN user_distances r5 ON me.userid = r5.useridx
         WHERE r5.miles < me.into_distance) AS dist_match
       LEFT JOIN games r6 ON (r6.userid = dist_match.match_userid
       AND r6.game = dist_match.into_game) ***LIMIT 200***)

SELECT l.id,name FROM match l LEFT JOIN usernames r ON l.id = r.userid;

You can influence the planner with

set local enable_seqscan = off;

to see if it helps.

See more at http://www.postgresql.org/docs/9.5/static/runtime-config-query.html


How can I UPDATE multiple ROWs in a Single Query with multiple WHERE clauses? How can I simplify these Queries

UPDATE `table` SET `col1` = 'abc', `col2` = 'xyz' WHERE `col3` = '1';
UPDATE `elbat` SET `col1` = `a`, `col2` = 'x' WHERE `col3` = '1';
UPDATE `elbat` SET `col1` = `b`, `col2` = 'y' WHERE `col3` = '2';
UPDATE `elbat` SET `col1` = `c`, `col2` = 'z' WHERE `col3` = '3';

Into something like this?

UPDATE `table` a, `elbat` b
SET a.`col1` = 'abc', a.`col2` = 'xyz' WHERE a.`col3` = '1',
SET b.`col1` = `a`, b.`col2` = 'x' WHERE b.`col3` = '1',
SET b.`col1` = `b`, b.`col2` = 'y' WHERE b.`col3` = '2',
SET b.`col1` = `c`, b.`col2` = 'z' WHERE b.`col3` = '3';

to run a Single-Query instead of Four-Queries

Remarks
It is possible to update rows based on some condition. It is also possible to update multiple tables in one statement in MySQL.
Whether the latter is a good idea is debatable, though. The target tables would be joined together for the update, and when I say "joined", I mean it in a broader sense: you do not have to specify a joining condition, in which case theirs would be a cross join. In a cross join, when at least one of the tables has more than one row, the other table will inevitably have its rows duplicated in the joined set. If both have multiple rows, both will have them multiplied. Somewhat counter-intuitively, MySQL will still update each affected row just once, yet I would refrain from multi-table updates in such scenarios, even if solely because of the counter-intuitiveness.
Method 1
Anyway, moving on to your specific example, there is indeed no joining condition, only a filter on each table. You can specify those filters in the WHERE clause of the UPDATE. Now in order to select which value to update each column with, you can use a CASE expression. This is what the complete UPDATE statement might look like:
UPDATE
  A, B
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = CASE B.col3
             WHEN '1' THEN 'a'
             WHEN '2' THEN 'b'
             WHEN '3' THEN 'c'
           END,
  B.col2 = CASE B.col3
             WHEN '1' THEN 'x'
             WHEN '2' THEN 'y'
             WHEN '3' THEN 'z'
           END
WHERE
  A.col3 = '1'
  AND B.col3 IN ('1', '2', '3')
;

You can see that you have to repeat the same set of conditions in a CASE expression both for B.col1 and for B.col2. Is there a way to avoid that?
Method 2
Yes, there is. You can arrange the target values for B.col1 and B.col2 as well as the filtering values for B.col3 as a derived table and join it to B in the UPDATE clause, like this:
UPDATE
  A,
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
WHERE
  A.col3 = '1'
;

The join is also acting as a filter for B, so you can omit the one in the WHERE clause.
You can find a demo for each method at  db<>fiddle:

Method 1
Method 2

Better way
Finally, as have been remarked both at the beginning of this post and in the comments, you can have a separate UPDATE statement for each table. The result would be clear in intention both to the reader of your script and to the database engine. A simpler script enables the latter to have more options for optimisation.
Use either of the methods above for the table B update, but do both tables separately:
UPDATE
  A
SET
  A.col1 = 'abc',
  A.col2 = 'xyz'
WHERE
  A.col3 = '1'
;

UPDATE
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
;

There is also another reason for splitting the updates in separate statements. Since for a single UPDATE statement the tables need to be joined, it is important that both tables have rows intended for the update. If one table has no matching rows, then, even if the other does, neither will be updated. This is because an empty set cross-joined to a non-empty set still results in an empty set.
So, the single UPDATE statement would have no rows to work with if at least one table had no rows matching the condition(s). That would not happen with separate UPDATEs, because each would work with its own table regardless of the contents of the other, therefore, absence of rows in one table would not affect the update of the other.


How to return all the unique column values on a per column basis for all metadata tables? assume multiple tables of various sizes

table one

col one, col two, col three,...
a,d,j
a,e,k
a,e,m
b,e,o
c,e,o
d,e,o
f,e,o

return (preferably in this format)

col one, col two, col three
a,d,j
b,e,k
c, ,m
d, ,o
f, ,

the idea is to be able to give a summary of the data to the users to check data quality prior to a migration

I'd use DENSE_RANK() to match values from distinct rows.

And MAX() OVER() to detemine which column has the biggest number of values (only for the ORDER BY purposes, you can skip this if the order doesn't matter).

Here is a working example:

create table test2 (
colone char(1), coltwo char(1), colthree char(1));

insert into test2 values
('a','d','j'),
('a','e','k'),
('a','e','m'),
('b','e','o'),
('c','e','o'),
('d','e','o'),
('f','e','o')
;

WITH cte AS(
    SELECT
        colone, DENSE_RANK () OVER (ORDER BY colone) as c1rn,
        coltwo, DENSE_RANK () OVER (ORDER BY coltwo) as c2rn,
        colthree, DENSE_RANK () OVER (ORDER BY colthree) as c3rn
    FROM test2
) , cte2 as (
    SELECT
        t1.colone, t2.coltwo, t3.colthree,
        MAX(t1.c1rn) OVER() as max_c1rn,
        MAX(t2.c2rn) OVER() as max_c2rn,
        MAX(t3.c3rn) OVER() as max_c3rn
    FROM cte  t1
    FULL OUTER JOIN
    cte t2 ON t1.c1rn = t2.c2rn
    FULL OUTER JOIN
    cte t3 ON
    t1.c1rn = t3.c3rn
    OR
    t2.c2rn = t3.c3rn
)
SELECT
colone, coltwo, colthree
FROM cte2
GROUP BY colone, coltwo, colthree
ORDER BY
    CASE WHEN colone IS NULL THEN 1 ELSE 0 END, colone,
    CASE WHEN coltwo IS NULL THEN 1 ELSE 0 END, coltwo,
    CASE WHEN colthree IS NULL THEN 1 ELSE 0 END, colthree
;

Hopefully your table is not very big, it will probably be scanned as many times as many columns you have, so it may take quite a lot of time.


TABLOCK for both truncate and subsequent bulk insert I'd like to truncate a table and then run a bulk insert on it with some new data.
I tried a query something like this:
BEGIN TRANSACTION;

TRUNCATE TABLE db.dbo.table;

BULK INSERT db.dbo.table
FROM 'file.txt'
WITH (TABLOCK, FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a');

COMMIT TRANSACTION;

However it seems like I can query a time between the truncate and the insert. Granted this is a small time window but I would like to eliminate it entirely.
How can I edit the query so the TABLOCK (or better TABLOCKX?) locks the table from when the truncate begins to when the bulk insert ends?

The table lock is not taken until a statement containing that hint is executed. Since TRUNCATE cannot be hinted we must add another statement that can be. A SELECT will suffice. We want it to be fast so a restrictive predicate is good. Something like
begin transaction;

select primary_key
from dbo.table (tablockx)
where 1=2;

.. etc.

It would be nice if we could use sp_getapplock for this but it cannot, at the time of writing, address schema objects.


Will restarting SQL Server clear my transaction log? I have a client that restarts his SQL Server instance to clear an application problem he is encountering.

I find this is very odd, I don't think you should ever restart the database in normal operation. I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory? He's in simple recovery mode - what I can do to check if this is the case?

Do we know of any other reasons why restarting the SQL Server service would make a problem disappear for a few days?

I believe his transaction log is simply full and restarting may be counting as a checkpoint? Is this a valid theory?

No, this is not right. That is the most brutal thing you can do to sql server to just clear out the log (which makes things worse). If there is a long running transaction, then restarting your sql server will put that database in recovery until the rollback happens.

since you are running in simple recovery mode, ONLY a CHECKPOINT - manually or sql server running it at regular intervals will allow SQL Server to reuse the transaction log (the log will be marked for truncation).

Note that TRUNCATE_ONLY is not supported in sql server 2008 and up, but that was used to do a FORCE log truncation (sql server 2005 and earlier versions) - BACKUP LOG <database_name> WITH TRUNCATE_ONLY

Please read  - Why Does the Transaction Log Keep Growing or Run Out of Space? (putting it in bold to emphasis to read it until you fully understand and digest the 2 best answers).

Also, make sure you have a good auto growth setting for your database.

  Do we know of any other reasons why restarting the MS SQL service would make a problem disappear for a few days?

You should start with How to analyse SQL Server performance


PL/SQL block fails in job but succeeds in SQL*Plus I created a PL/SQL block which gathers certain data from tables in my Oracle 12c database and writes them into a .csv file.
I cannot share the whole PL/SQL block but can outline the code

--create directory
str := q'{create directory DIR as '/tmp/tmp_dir'}';
execute immediate (str);

--get filename
select 'tmp_stat' || '.csv'
into filename
from dual;

--open file
tmp_file := UTL_FILE.fopen('DIR', filename, 'W', 32767);

--write header
UTL_FILE.put_line (tmp_file, 'col1;col2');

-- gather and write data
for stat in ([select statement]) LOOP
UTL_FILE.put_line (tmp_file, stat.dat);
END LOOP;
--close file
UTL_FILE.fclose (tmp_file);

When I run my code in SQL*Plus it works fine. But when I turn it into a job with the following options

Raise Events: None
Maximum Run Duration (minutes): None
Priority: Medium
Schedule Limit (minutes): None
Maximum Runs: None
Maximum Failures: None
Job Weight: 1
Instance Stickiness: TRUE

When I try to run the job with the exact same code (except the / to mark the end of the block), I get the error

ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation ORA-06512: at line 61

I don't have a line 536 in my code, but line 61 is

tmp_file := UTL_FILE.fopen('DIR', filename, 'W', 32767);

Any suggestions on why the error is raised when executing the code is a job?

What happened is that I had all the permissions and privileges set right except I forgot to create the directory on the second node too.

So whenever the job ran on the second instance (which it by chance did) the error was raised that the file could not be written.

Of course it had to be as simple as that but I want to post it as an answer anyway.

When working with an Oracle RAC your mantra should always be "Keep in mind the other instances."


Schedule database backup question Good day. This may be a stupid question and you can give me a stupid answer if you need to. I know you can schedule a task to backup a sql database at logon, shutdown, specific time, etc. But What I would like to know is the following:

Is it possible to set a sql database to run at a specific time but if there is a power failure it will run when the pc/server comes back up?

So for example I schedule a full database backup on say Friday evening at 18:00 (an hour after everyone has left), but before the scheduled time there is a power outage and the ups shuts down the server (we have it configured to shutdown the server if our generator is not started in 30 minutes) and once power is back the server automatically starts up but it is after the scheduled backup time, is there a way to get it run at boot.

This isn't mission critical but more of a piece of mind/convenience thing.

TLDR - want to know if a scheduled task will run at boot as server could be powered off due to lack of electricity.

You can schedule an agent job to run at startup (there's an agent schedule for that), but you would have to add logic to the job to see if it "missed" the previous schedule. for instance, you can check the backup history for the relevant database and see how old the most recent backup is, and then conditionally perform the backup.


Database Design For Social Network apps I'm going to build a social network app. Each user will have photos, videos, details, posts etc.

My question is when a new user registers, should I generate many new tables for that user or have one table for all users which may be contain billions of rows?

And which is best database for social network system: MySQL or CassandraDB?

Please help me!

One table for all users.  Period.  End of discussion.  (This is a common question; the answer is always the same.)

When you get to a million rows, we can discuss some other issues and solve them before you embark on the road to a billion.

What is best for project X?  If it will have a billion rows, you will need a database expert to tweak whatever underlying system you are using.  Any system can do it.  But it won't be trivial.

Focus on getting to a million rows.  Reconsider all of your design decisions at that point.  Any decisions made today will not survive to 1 billion.

Would you like me to repeat myself all over again?


Installing Full Text Search on SQL Server 2008 R2 Express I can't for the life of me install full text search on my SQL Server 2008 R2 instance.

Every installer I have tried shows me this:

Feature selection only shows Replication under features http://www.vme2-server.co.uk/images/sql.jpg

I've tried:

SQLEXPRADV_x64_ENU.exe
SQLEXPRWT_x64_ENU.exe
SQLEXPR_x64_ENU.exe

Any ideas what i'm doing wrong?

The SQL Server is reporting a version of 10.52.4000.0

Thanks in advance,

Dave

If you want to use Full text search, you need the 'Advanced Services' edition of SQL Server Express 2008, which can be obtained at Microsoft

However, you need to uninstall the current version first.


Performing aggregations and calculations: SQL (CTE) vs MDX I’ve hit a wall and would like some expert advice.

I’m building a data warehouse and don’t quite understand whether the calculations (also time aggregations) should be done in SQL (T-SQL and CTE) or MDX.
So Currently I have the following approach

Define the level of time aggregation needed for the calculations. (currently I’d like to do it in SQL)

Day
Week
Month
Quarter
Year YTD (year-to-date)
QTD (quarter-to-date)
YTW (year-to-week)
YTM (year-to-month)

Calculate the calculations that can be calculated on the column level and at the level of each time aggregation (again I’d like to do it in SQL)
And then do the row level calculations (for this I’m thinking about using MDX

Changes with previous period:

Day change with previous day
Week change with previous week
Month change with previous month
Quarter change with previous quarter
Year change with previous year

Changes compared to same period of last period

Day vs same day last week
Day vs same day last month
Day vs same day last year
Week vs same week last month
Week vs same week last year
Month vs same month last year
Quarter vs same quarter last year

The reason I’m thinking about doing steps 1 and 2 in SQL is that I’ m visualizing it as a table that has all the variables grouped to a level of time aggregation of YTD, YTM, YTW, etc and then in the same table there would also be calculations in columns...and then if I use this table as one of the underlying tables of the DW then in the DW I would use MDX for the parallel period calculations.

I would like to know if this is a good approach...or what is the best approach/place to do calculations?

Row-level YTD calculations on a database table are not very useful unless you have a unit of analysis for your YTD figures that can be processed additively (i.e. you're not going to find your reports trying to aggregate two YTD figures).  This essentially limits their usefulness to summary tables.

This type of aggregate table is of limited use with a cube, as the cube gives you the aggregation for free.  If you have a time dimension on the cube you can make calculated YTD measures that encapsulate the calculations. with an expression such as Aggregate (YTD([Date].[Date].[CurrentMember]), [Measures].[SomeMetric]).  You don't even have to specify the date.

The calculations you describe can all be done relatively easily in MDX with hierarchy functions such as PrevMember and ParallelPeriod, and this is more flexible and much less effort to implement than working off an aggregate table generated in the underlying database.  You're better off doing al the YTD and period-period comparisons in the cube.