Import Data from 48 GB csv File to SQL Server I am importing huge data file of size around 48 gb using SQL Server default Import Tool. It keeps on executing fine for app. 13000000 Row insertions but after it the Task Fails with following error.
I cant open the csv as its damn huge neither can i move row by row in it and analyze the stats.
I am really confused how to handle this.

You can use powershell to fast import large CSV into sql server. This script High-Performance Techniques for Importing CSV to SQL Server using PowerShell - by Chrissy LeMaire (author of dbatools)
Below is the benchmark achieved :

5.35 million rows a minute for non-indexed tables and
4.35 million rows a minute for tables with clustered indexes.

The script even batches your import into 50K rows so that during import, it does not hog the memory.
Edit: ON SQL Server side -

Make sure you have proper recovery model set .. for such load, bulk recovery is best setting.
Autogrowth is set to proper MB and not Percent.
Both data and log files are provisioned big enough so there is minimal autogrowth events.
Do the import during non peak hours.
Follow best practices described in Data loading perf guide and or my answer.
Instant File Initialization is enabled and power setting is set to high perf.


Do I need to set tcpip_socket = true to allow remote connections to a postgresql database? I am trying to enable remote connections to my postgresql 9.1 db running on ubuntu 12.04.

This tutorial says to "set tcpip_socket to true" -- but it is for a centos server. I am on ubuntu.

I don't see this line included in my postgresql.conf.

$cat /etc/postgresql/9.1/main/postgresql.conf | grep tcpip_socket

And when I add it in, I get the error:

 "FATAL:  unrecognized configuration parameter "tcpip_socket"

Do I need to tell postgres 9.1 to "turn on" tcp_ip connections if its running on ubuntu 12.04?

All you should need is:

listen_addresses = '*'

and then to modify pg_hba.conf to permit connections from the desired source.

This is stated in the comments on the (very old) article you were reading.

Try using the main PostgreSQL documentation, or up to date tutorials, in future.

See:

Connection and authentication settings
Client authentication


Using Inteval Datatype to store result of calculation I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.

I don't believe you can.

The expression you use to create a virtual column must be deterministic, i.e. always return the same value when called with the same arguments. Involving the current date is fundamentally incompatible with that requirement.

You could use a plain old view though.

create table mytab (hiredate date);
create view myview
as
 select
   hiredate,
   numtodsinterval(sysdate-hiredate, 'day') delta
 from mytab;

insert into mytab values (to_date('20120101', 'YYYYMMDD'));
commit;
select * from myview;

Output:

HIREDATE        DELTA
01/01/12    247 9:15:55.0


How to transfer data from mysql 5.5.24 to 5.1.36 I've had a server that is not booting up, it had mysql 5.5.24. I was able to  copy the whole /data directory.

I will be temporarily using an older backup server that has mysql 5.1.36 and cannot be upgraded due to NET framework issues.

I was wondering if I could just overwrite the /data directory to have the database running?

I ask this because I usually use mysqldump to backup and restore, which as I understand backups by generating sql instructions and rebuilds the database by running such queries.

To me it seems logical to just replace the raw data and have the database working as expected. But I'm worried something might break considering im going back to an older version. Which could teoretically be unable to interpret a newly implemented way to store the raw data.

Update: The database seems to be running fine with the overwritten raw data. If anyone knows of a possible problem please let me know.

A backup is just a boat anchor until you verify that you can reload from it.  So, give it a try.  Set up a 5.5 version (if you can find such an old version) and try the dump.

It may work as is.

How are you doing the backup?  mysqldump?  If so...

At worst, you might need to edit the dump file to fix some old syntax.  (I don't remember any from 5.1 to 5.5.  Before that, there was TYPE=InnoDB changing to ENGINE=InnoDB.)

If you are copying a directory tree, then "files are files"; there is the backup is just a filesystem; it does not care what the bits mean.

So, I am puzzled as to why you talk about "5.1", yet you talk about "/data".


Replication on a clustered Instance Can I set up Replication on a clustered Instance of 2 Nodes. If yes then is it mandatory to have a 3 separate Instances for Distributor, Publisher and lastly Subscriber . If there is a fail over then will the replication break ?

Replication doesn't care about or interact with clustering.

Replication is set up from SQL instance to SQL instance, the physical host that the instance happens to be running on at that moment isn't relevant.

If a failover occurs, a momentary interruption in replication will be experienced as the instance goes offline, but will resume once SQL comes up on the other host.

  If yes then is it mandatory to have a 3 separate Instances for Distributor, Publisher and lastly Subscriber?

What? No.

You can have a single SQL instance be the distributor, publisher, AND subscriber if you want to. (Not common, but possible to push changes from one database to another on the same host.)

A more typical configuration is to have two SQL instances (a publisher/distributor and a subscriber).

But all of those business decisions are completely independent of clustering.


Informix help - SQL DBA I am a SQL Server DBA with minimal Informix 11 experience.

I'm tasked with extracting an entire Informix database (circa 8GB according to systables in Informix) into a SQL Server database. I plan to do this using linked server use OPENQUERY.

However I am worried about the impact this may have. It's not a large database at all and I know Informix does row level locking (was worried it may do full table locking) and I'm going to run it when the application is not busy.

Does anyone have any advice regarding this?

My main concern, regardless of the migration mechanism used, would be for the consistency of the migrated data.   How are you going to ensure that you don't miss changes while the data is transferred.

One of the classic Informix tools could be DB-Export, which (by default) locks the database so it isn't being changed while the export occurs.  It gives you the schematic and unloaded (text) data files.

Another option would be to make an archive of the existing system and then restore that into a new (temporary) instance, and you could then run your migration against the temporary instance without affecting the working instance.  But you'd still have to worry about later changes.

If your existing machine is not too woefully under-powered, you should be OK running the export as you suggested, but you still face the issue of ensuring consistency of the migrated data if users are changing the source system while you are migrating.


How to handle process blocking in SQL Server Profiler? I have run a SQL Profiler Trace which detected a process blocking. Now what should i do, if it always brings the same blocking event, each time duration is getting bigger. I have only one transaction which created a block, and I want it to be fixed in a trace only once, why it keeps repeating?

Thanks

The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time.

To configure the Blocked Process Threshold and frequency at which reports are generated, use the sp_configure command to configure the blocked process threshold option, which can be set in seconds. The threshold can be set from 0 to 86,400.


Adding multiple files to a tablespace In Oracle, we have huge amount of data imported daily, which requires us to create data files off and on in a tablespace.
Wonder if we create two data files simultaneously, will it use both when one is full? Or it will use only latest one?

The algorithms used to allocate space in different datafiles in a single tablespace are not documented except in the database administration guide
where it just says:

the database can allocate extents for a segment in any data file in
the tablespace

It can change between two releases but it is likely based on same round robin algorithm.
Here is a short demo with Oracle 19 which shows that some round robin algorithm is used:
SQL> select banner from v$version where rownum=1;

BANNER
------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> create tablespace test;

Tablespace created.

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> select tablespace_name, file_id, file_name from dba_data_files where tablespace_name='TEST';

TABLESPACE_NAME     FILE_ID FILE_NAME
-------------------- ---------- ------------------------------------------------------------
TEST                120 /u02/oradata/CDB2/datafile/o1_mf_test_hhxspzt3_.dbf
TEST                121 /u02/oradata/CDB2/datafile/o1_mf_test_hhxsq112_.dbf
TEST                122 /u02/oradata/CDB2/datafile/o1_mf_test_hhxsq2kt_.dbf

SQL> --
SQL> create table t tablespace test as select * from dba_objects ;

Table created.

SQL> select distinct file_id
  2  from dba_extents
  3  where segment_name='T'
  4  and   segment_type='TABLE'
  5  and tablespace_name='TEST';

   FILE_ID
----------
       121
       120
       122


How can I rearrange these two queries to get the second column from inside the IN() How can I get the random.id column from inside this first select

select id, name from random where id IN(1,2,3,4);
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Frank     |
|  2 | Tracey    |
|  3 | Bos       |
|  4 | Charlotte |
+----+-----------+

select `where`, `who`, COUNT(*) from cards where who = "." OR who IN(select name from random where id IN(1,2,3,4)) GROUP BY `who`, `where`;
+-------+--------+----------+
| where | who    | COUNT(*) |
+-------+--------+----------+
| pack  | .      |       78 |
| hand  | Frank  |       15 |
| hand  | Tracey |       15 |
|       | .      |       20 |
+-------+--------+----------+
4 rows in set (0.00 sec)

out when using the second more complicated select, above. Both tables have more columns than show and I do not want to add an extra row to the random table simply to define the . as comes out of the cards table. I think it should be possible to rearrange the second query to provide the desired output of where, id, count but sadly it eludes me.

The results I would like would look like below

+-------+-----------+--------+----------+
| where | random.id | who    | COUNT(*) |
+-------+-----------+--------+----------+
| pack  | NULL      | .      |       78 |
| hand  | 27        | Frank  |       15 |
| hand  | 32        | Tracey |       15 |
|       | NULL      | .      |       20 |
+-------+-----------+--------+----------+

The random.id column can be NULL or a consistent number, zero would be fine, and the who column is only a nice to have. Thanks for the welcome.
The Mysql version is 8.0.19-0ubuntu0.19.10.3 however it may well be better if the version was considered to be broader as I hope to run it on a server over which I have no control.

~~~

Well I have an answer of sorts, not what I was looking for but by divide and conquer two "simpler" queries supply me with the data I need. The data has changed so the numbers in the answers look different but the rules are still the same.

select `where`, 0 AS id, who, COUNT(*) AS cnt from cards WHERE who = "." AND `order` != 0 GROUP BY `where`;
+---------+----+------+-----+
| where   | id | who  | cnt |
+---------+----+------+-----+
| pack    |  0 | .    |  63 |
| dis_top |  0 | .    |   1 |
| discard |  0 | .    |   1 |
+---------+----+------+-----+

The data when there is no who defined.

SELECT `where`, id, who, cnt FROM random AS a RIGHT JOIN (SELECT `where`, who, COUNT(*) AS cnt FROM random AS b INNER JOIN cards AS c ON b.name = c.who GROUP BY `who`, `where`) AS d ON a.name = d.who;
+-------+------+-----------+-----+
| where | id   | who       | cnt |
+-------+------+-----------+-----+
| hand  |    1 | Frank     |  11 |
| hand  |    2 | Tracey    |   7 |
| table |    2 | Tracey    |   2 |
| hand  |    3 | Bos       |  11 |
| hand  |    4 | Charlotte |  11 |
+-------+------+-----------+-----+

The data when there is a who defined.

I have not claimed to have answered my own question as I still think it must be possible to do it a single query, but....

#Inner Join both the tables:

SELECT random.id, `where`, `who`, COUNT(*)
FROM cards
JOIN random
ON cards.who=random.name

#I assume you want all the values from `who` column from table:cards, so there's no need to use WHERE again.


Separate databases for front-end and back-end The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more.

I was thinking the best thing to do would be create two databases and synchronize them daily. However:

I do not know how to do this
I am worried synchronization will be slow & block db access. I really don't like the idea of taking the site offline, even for 10 minutes at 3 am.

So I am wondering:

Is separate databases the best solution for this problem, or would something else be better?
How would I synchronize the database without interfering too much with the 'user experience'?

Thanks very much!

BTW running PHP/MySQL on 'could' servers.

From your comments, one solution might be to make a Master-Slave replication setup (link to mysql replication here)

I would make the backend the Master, and the front-end the slave. If your front-end needs to write (contact forms, tracking etc) you would update your code in the Front-end to read from the slave, and write to the master.

The downside is, depending on the load, your backend-writes might be delayed a few seconds or more. But as an anecdote, I've got a server that handles 120 commands /second (averaged, as reported by munin) and the slave server isn't behind by more than 3 seconds.


replication breaks after upgrading master I have a set up of replication with master 5.1.30 and slave 5.5.16
and the replication is working good

Now i have upgraded mysql master to 5.1.47

As far as i know we have to turn off the log bin with sql_log_bin=0 before using mysql_upgrade program in order to up grade the replication setup as well

but the problem here is the binary log was not turned off while mysql_upgrade program is running

The reason i found is in 5.1 the sql_log_bin is a session variable
and mysql_upgrade program runs in another session

so how to upgrade the replication as well along with the server with any breakage on replication setup.

any suggestions are really useful.....

The mysql_upgrade program tries to realign the mysql.user table with latest grants for that MySQL Major Release. Since you only did a minor upgrade (5.1.30 -> 5.1.47) the mysql_upgrade was needless. The grant tables for the Slave have no bearing on Replication since the Slave has to authenticate back at the Master.

Fixing MySQL Replication is just a case of repointing replication from the last know executable position and starting from there. Here is how you do this:

Run SHOW SLAVE STATUS\G and choose the following two fields

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000504
        Read_Master_Log_Pos: 326579313
             Relay_Log_File: relay-bin.081210
              Relay_Log_Pos: 326579450
      Relay_Master_Log_File: mysql-bin.000504 <--- Choose This
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 326579313 <--- Choose That
            Relay_Log_Space: 326579450
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

From the above display, your would choose mysql-bin.000504 and 326579450.

Simply do the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000504',master_log_pos=326579450;
START SLAVE;

Give it a Try !!!


INSERT INTO table FROM SELECT * with nextval() specification I need to copy records within the same table changing only one field.
My table has default-generated sequence entry_id_seq, however I am not sure id column is SERIAL (how to check that?).

\d tab returns just this

      Column      |              Type              |            Modifiers
 -----------------+--------------------------------+------------------------
       id         |             integer            |            not null
...
Indexes:
"tab_entry_pkey" PRIMARY KEY, btree (id)

So the problem is: when I try copy record in a simplified manner:

insert into tab_entry select * from tab_entry where id = 3052;

it throws error

ERROR:  duplicate key value violates unique constraint "tab_entry_pkey"
DETAIL:  Key (id)=(3052) already exists.

Default sequence doesn't generate next value by default. Is there any concise syntax allowing inserting and changing single field without full table specification aka FROM tab(col1, col2, col3, ..., col N)?

The table has many fields, so I don't want to write them all, as this will affect code readability.
I want something like this, but this syntax doesn't work

insert into tab_entry(id, *) select nextval('seq'), * from tab_entry where id = 3052;

And will this SELECT nextval('seq') approach work if there be multiple records at once?

Psql and Postgres version is 9.6.2.

As noted in the comments there is no special syntax for such task.
You could to use combination of functions to_json(b), json(b)_set and json(b)_populate_record:

--drop table if exists t;
create table t(i serial primary key, x int, y text, z timestamp);
insert into t values(default, 1, 'a', now()),(default, 2, 'b', now());

insert into t
select n.*
from t, jsonb_populate_record(
  null::t,
  jsonb_set(
    to_jsonb(t.*),
    array['i'],
    to_jsonb(nextval('t_i_seq')))) as n;

select * from t;

But I think it is not much simpler then just enumerate all columns. You always can use this relatively simple query to get the columns list of table:

select string_agg(attname,',' order by attnum)
from pg_attribute
where attrelid = 'public.t'::regclass and attnum > 0;


On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements? we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to poorly designed db architecture) that, each, suck away dozens of gigabytes of RAM from our servers and have execution plans in the range of megabytes stored in SQL cache.

We would like to restrict certain users from using SORTs and HASH MATCHes, specifically ORDER BY, SELECT DISTINCT and JOINs of any kind. Our intention is to let users pull their data, but work on it only on their side.

Could this be achieved in SQL Server?
Is there any DB engine that supports that?
What would be your thoughts about this?

I know this would beat the principle of a relational database, but we're looking for some way to enforce that the engine is properly used and not abused by uneducated users.

We are already considering the implementation of Resource Governor to allocate a certain amount of machine resources for these bad queries.

The answer by kevinsky is one path towards a solution for you.

A different path for your specific issue is to create a second reporting (and maybe a third dev) server(s).  With copies of the production data.  Your reporting people and development people can do all kinds of stupid, and not impact production.

There are multiple ways of achieving this, depending on how fresh your report/dev data needs to be.

A common solution is a nightly backup and restore to the secondary server. If you need really current data (like no lag or one second) look into SQL Server Always On


Fast database for a small microcontroller I am using a PIC32, which is a 32-bit processor clocked at 80 MIPS with about 64-128KB of RAM available. It will be accessing a microSD card - up to 4 GB, on a FAT32 filesystem. Running all of this is pushing it, but I need a compact database that can be easily ported to this platform and one which is fast. Does anyone have any suggestions?

A ported version of SQL Lite is probably your best bet. See this thread.


Poor performance on inner join using dates and between I am having trouble optimizing a query that does an inner join using a date range. The purposes of the query is take daily data and summarize by week.

Select  pcw.EndDate WeekEndDate, h.Store, SUM(h.DeliveryChargesTotal) DeliveryChargesTotal
from Daily_GC_Headers h
inner join PeriodCalendar_Weeks pcw
on h.SalesDate between pcw.StartDate and pcw.EndDate
where SalesDate between @StartDate and @EndDate and isCanceled = 0
group by pcw.EndDate, h.Store

Simplified schema of Daily_GC_Headers table (13.8 million rows; about 5.4 million match criteria in WHERE clause):

Store - Varchar(10) (PK)
SalesDate - Date (PK)
TicketNumber - SmallInt (PK; starts over 1 each day at each store.)
IsCanceled - Bit
DeliveryChargesTotal - Decimal(9,2)

Simplified schema of PeriodCalendar_Weeks Table (570 rows; 53 match the criteria):

Year - smallint (PK)
Period - tinyint (PK)
Week - tinyint (PK)
StartDate - Date
EndDate - Date

This query takes about 15 seconds in SSMS. Querying Daily_GC_Headers by itself (and just grouping by Store) takes 2 seconds. A query against PeriodCalendar_Weeks is "instant".

DBCC SHOW_STATISTICS indicates that the stats are both tables are current (we run a weekly job to update them). I've tried clearing the plan caches.

The execution plan is strange. For example, it is doing an Eager Spool on PeriodCalendar_Weeks. The estimated rows is 156.6 but the actual rows is 153,971. It then filters the results of that first spool and does a Lazy Spool. The estimated/actual rows of that 2nd spool is 5.4 million, even though the underlying table has less than 600 rows in it.

What should I be looking for or doing to optimize this?

Additional Information

For sake of clarity, I initially described an oversimplified PK on the Weeks table. I have update the schema above to show the full key. The PK described for the Headers is (and was) the full key.

Screen shot of some rows from the Weeks table:

Stats from the Weeks table:

Some stats from Headers table. There seems to be an histogram record for about every 5-10 days for the entire history in the table (3 years).

Much more efficient to do this without having to go back and join to the periods table.

DECLARE @StartDate DATE, @EndDate DATE;

Select @StartDate = Min(StartDate), @EndDate = MAX(EndDate)
from dbo.PeriodCalendar_Weeks pcw
where (pcw.Year = @Year and pcw.Period < @Period)
  or  (pcw.Year = @Year and pcw.Period = @Period and pcw.Week <= @Week)
  or (pcw.Year = @Year -1 and pcw.Period >= @Period);

SELECT
  WeekEndDate = DATEADD(DAY, 6, DATEADD(WEEK, SalesWeek, @StartDate)),
  Store,
  DeliveryChargesTotal = dct
FROM
(
  SELECT DATEDIFF(DAY, @StartDate, SalesDate)/7, Store, SUM(DeliveryChargesTotal)
  FROM dbo.Daily_GC_Headers
  WHERE SalesDate BETWEEN @StartDate AND @EndDate AND isCanceled = 0
  GROUP BY DATEDIFF(DAY, @StartDate, SalesDate)/7, Store
) AS x (SalesWeek, Store, dct)
ORDER BY WeekEndDate, Store;

A filtered index may help, if many rows exist where isCanceled = 1 (these are just possible suggestions, depending on cardinality of Store, and may not be the most optimal):

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate) INCLUDE (Store, DeliveryChargesTotal)
  WHERE isCanceled = 0;

If there are very few rows where isCanceled = 1, this may be better:

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate, IsCanceled) INCLUDE (Store, DeliveryChargesTotal);

Both are worth trying on a test system, as well as moving Store into the key in either case, or moving IsCanceled to the INCLUDE list in the latter case. On my system, I found the best results with everything but the date in the INCLUDE list:

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate) INCLUDE (Store, IsCanceled, DeliveryChargesTotal);

Again, you will need to test if any of these work out, or if the query above gives a different/better recommendation directly from SQL Server.


Cluster Configuration I’m looking at some of the SQL servers in a (new to me) SQL estate. The servers are configured for AOAGs; there are two nodes, both are part of the WSFC. However, if I look at the SQL Server advanced properties the 'Clustered' attribute = 'No'. Would we expect them to be in a SQL cluster sat on a WSFC? In which case I would expect the 'Clustered' attribute = 'Yes'.

This will be Yes if the server is configured as a failover cluster, this does not change based on being  a part of a WSFC or Always On AG.

Source:

  IsClustered:  Server instance is configured in a failover cluster.

1 = Clustered.

0 = Not Clustered.
"
NULL = Input is not valid, an error, or not applicable.

Query to check this:

SELECT SERVERPROPERTY ('IsClustered')


Sql Server Configuration Manager I have two SQL Server named instances, InstanceA and InstanceB on one computer, and they correctly show up under Sql Server Configuration Manager | SQL Server Network Configuration.

However, when I try to configure SQL Native Client 11.0 Configuration | Client Protocols, there is no option to specify to which instance the setting will be applied. It appears that this is server (machine) level setting that would apply to every instance installed on it.

Am i missing something here? How to allow one protocol, say Named Pipes only for InstanceA and TCP/IP only for InstanceB? (without using aliases, preferably)

Thanks

You are confusing Server Network Protocols and Client Network Protocols. Essentially, Server Network Protocols change the way in which SQL Server listens for incoming requests and Client Network Protocols change the way in which an application sends a request to SQL Server.

As the Client Network Protocols are a shared component and apply to all applications that use that library you cannot set a different protocol per instance that you wish to connect to.

Direct from the Client Network Protocols link below:

  At the simplest level, a SQL Server client can reside on the same machine as an instance of SQL Server. Typically, however, a client connects to one or more remote servers over a network. The client/server architecture of SQL Server allows it to seamlessly manage multiple clients and servers on a network. The default client configurations suffice in most situations.

More information regarding the two can be found in the links below:

Server Network Protocols

Client Network Protocols


Getting Data Type ID in MySQL So, In SQL, We have the following query to find the system data type ID of columns in SQL Server;

select name, system_type_id from sys.columns where object_id = OBJECT_ID(N'sometable')

It gives the following output;

name       system_type_id
rowid        56
Client       167
IsCurrent    104

The system_type_id represents the int value of data type of that column.

Whereas, when I tried to convert (find an equivalent) in MySQL, I couldn't find the ID of column's data type. The following is the query I have managed to find so far;

SELECT column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'someSchema' AND TABLE_NAME = 'someTable';

It gives the following output.

# column_name,   data_type
    'rowid',      'int'
     'Client',    'varchar'
     'IsCurrent', 'tinyint'

Any suggestions how I can achieve the earlier output in MySQL ?

I discussed How is INFORMATION_SCHEMA implemented in MySQL? about 4 years ago.
SQL and PostgreSQL have ObjectID enumeration (oid) of object types. MySQL made the INFORMATION_SCHEMA database a more simplified representation of table, column, and index metadata. The metadata tables are temporary MEMORY tables with no indexes.
Last year in Adding new tables -- memory usage increases I discussed what happens to memory with the INFORMATION_SCHEMA does when you increase the number of tables.
Believe me, adding an oid for data types to the MySQL INFORMATION_SCHEMA would just bloat the INFORMATION_SCHEMA. In this particular instance, I greatly appreciate MySQL not having oid values exposed.
If you really want to learn about how mysql represents data types under the hood I refer you to pages 48-53 of "Understanding MySQL Internals" (ISBN 0-596-00957-7).

Those pages refer to sql/table.h and sql/field.h in the source code. You can download the source code and look it over. Then, judge for yourself how and why oid is not included.
SUGGESTION
Create your own. For example, to make oids for columns do this
DROP DATABASE IF EXISTS my_metadata;
CREATE DATABASE my_metadata;
USE metadata
CREATE TABLE objects ENGINE=MEMORY
SELECT data_type FROM information_schema.columns WHERE 1=2;
ALTER TABLE objects ADD COLUMN oid INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE objects ADD UNIQUE INDEX (data_type);
INSERT INTO objects (data_type)
SELECT DISTINCT data_type FROM information_schema.columns;


SQL: How to delete Millions of Records from a Table without disturbing the server A software of ours ran out of control and did put in millions/billions of uneccecary records in a table.
How can I delete them without disturbing the server?

This is what I have tried:
- setting transaction log to simple
- deleting them in batches in a script
- putting the virtual machine on a SSD
It still took 3 weeks before I aborted.

I don't really want to copy the data I want to keep and then delete the table.

You need a way to estimate the amount of time that your process will take. I also recommend a simple mechanism for logging how much progress that your code makes. That way you can avoid surprises when it runs 3 weeks when you weren't expecting it to. So start by getting that estimate. You say "millions/billions" of rows in the question but there's a big difference between the two. Which is it? You'll also want to design your looping code in a way such that the last batch takes about as long as the first batch. That way you'll get predictable performance. I don't know what kind of criteria you're using to identify bad rows so I can't give any advice for that.

The basic strategy that you want to use for the delete depends on the structure of the table and the percentage of bad rows in it:

If you're deleting the majority of rows from a heap then realistically you can't design a looping paradigm that runs in constant time. The best I know if is to delete the top N rows in a loop and to stop when you delete less than N rows. Be sure to use the TABLOCK hint when doing the DELETE. Otherwise SQL Server will not free up pages from deleted rows which means that the final batch will essentially do a full scan of all of the previous rows in the table.
If you're deleting the majority of rows from a clustered table then loop over the clustered index. Avoid looping over the same row more than once.
If you're deleting a minority of rows from a heap or clustered table, consider creating a nonclustered index that will help identify the next batch of rows to delete. If possible you could consider a filtered index so you don't do more work than necessary when creating it. Avoid looping over the same row more than once.

There are a few things that might improve the speed of your process:

The delete part of the query will run single threaded so increasing CPU single core speed and getting at least 2 dedicated CPUs for your VM will help. If your VM is oversubscribed for resources that can lead to unpredictable performance. Here I'm assuming that the query to identify rows to delete is simple enough that it will be run entirely in serial. If not the number of CPUs available to SQL Server may become important.
Increasing the disk speed of your data and log files can help. As a general rule of thumb, switching to simple recovery model will not reduce the amount of data logged to the transaction log except if minimal logging is used. Minimal logging cannot be used for DELETE statements. All you gained by switching was not having to do transaction log backups.
Disabling all nonclustered indexes before doing the delete will help. Rebuild them after all of the bad rows are deleted.

The fastest delete is the one that you don't have to do. You said that you don't want to have to copy the data. That's fine, I assume you have good reasons for that. But know that you might be passing on a solution that runs in hours as opposed to days or weeks.


Gigantic gaps in IDENTITY column This question is about strange behavior in SQL Server 2008 R2 SP3 Standard Edition x64.

I have read many questions about IDENTITY increments in SQL Server and strange gaps between them. But basically people say it is related to transactions or restarts. In my case (using a named instance) gaps are simply gigantic.

Application inserted 130 000 records in table, and "Arithmetic overflow IDENTITY conversion to int" error occurred. We truncate the table, and bulk insert 70 000 records. During bulk insert IDENTITY column maximal value is above 9 000 000!

As it was not normal load but mass insert - there was no rollback, restart nor any other issue during this operation.

So gaps are not in the size of 1000 but tenths of thousands.

As I do not control application code (it is commercial software bought from other company) I am simply afraid what can I do as Administrator of such database? Have I to truncate table once every 6 months? Monitor in real time ID column?

Gaps are not in the size of 1000 but tenths of thousands. Are there any pupils experiencing similar issue?

It is very much normal for there to be gaps in identity values (caused by rolled-back transactions, over estimated pre-allocations, and so forth) as you are already aware, though that sort of sparse use of values is rather unusual and indicates that there is something going on which you need to be aware of (either a design issue that may be unavoidable and you just need to plan around or a fault that needs fixing).

The first thing I would ask is are you touching the IDENTITY column at all in the bulk insert? If you are working with IDENTITY_INSERT ON or similar circumstances and the imported data contains a large number there then that is the source of the problem. Another thing to check for is hidden processes that might touch the table - do you have any triggers that might be firing as data is added to the table and doing something odd?

Otherwise, if you could provide more detail in your question that might help someone help you:

The structure of the table in question (and any others touched by the same bulk process): column/key/index definitions and any triggers
The code you are using for the bulk insert (TSQL, a call to BCP, what-ever method you are using)
A sample of the data (perhaps anonymised if it is sensitive)
When you performed your test was the database otherwise quiet or was it active with other transactions potentially being processed?
Are the gaps following any sort of regular pattern, or do they seem random?

As a work-around you might consider using a BIGINT for the column instead, though if it is referred to by foreign keys elsewhere that could be a big job to change (and if the value isn't purely internal to the database you need to consider if the code touching it can cope with values beyond the 32-bit limits).


Why can't I change the isolation levels of my connection in MySQL? I am trying to experiment a bit with the isolation level of MySQL in a test environment.
I do the following:

mysql> set @@session.tx_isolation='READ-UNCOMMITED';
ERROR 1231 (42000): Variable 'tx_isolation' can't be set to the value of 'READ-UNCOMMITED'

This also fails:

mysql> update information_schema.session_variables set variable_value='READ-UNCOMMITED' where variable_name='TX_ISOLATION';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

What am I doing wrong here? I am root. So it should not be a rights issue.

You need to try
SET tx_isolation = 'READ-UNCOMMITTED';

or
SET SESSION tx_isolation = 'READ-UNCOMMITTED';

You could also declare it at the start of the transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Give it a Try !!!
CAVEAT
Please note that you misspelled READ-UNCOMMITED (Missing T). It should be READ-UNCOMMITTED
You cannot change tx_isolation in the INFORMATION_SCHEMA database because it is an in-memory read-only database.


Joining Three Tables Using Composite Key I know there are a few posts on this topic, but can't seem to find one that is similar to mine, so hopefully someone can provide some insight!!

I have three tables: acct_rec, income_rec, and advance_billing. These tables house invoice data. acct_rec is the main table that has every invoice and some data around it. income_rec houses only invoices where we received income. advance_billing houses invoices that have been billed to the client.

So the tricky part is that sometimes an invoice will only exist in either income_rec or advance_billing. Sometimes an invoice will be in both tables.

The columns they all have in common are ar_inv_nbr, ar_seq_nbr, and ar_type. income_rec and acct_rec have two additonal fields that need to be joined on: job_number and job_comp_number.

These are the columns of interest I'm looking to retrieve:

acct_rec
inv_amount
inv_advance_amt
inv_date
post_period
ar_inv_nbr
ar_inv_seq
ar_type

income_rec
ar_inv_nbr
ar_inv_seq
ar_type
rec_amt

advance_billing
hours
quantity
line_total
ar_inv_nbr
ar_inv_seq
ar_type

So for example, if an invoice is only in income_rec I'd like to retrieve that row from income_rec along with the data for that invoice from acct_Rec. There should then be NULL's for all the fields that are in advance_billing.

And if an invoice is in advance_billing but not in income_rec, I'd like to accomplish the same: return all data from advance_billing but NULL's for income rec data.

If an invoice is in both tables, then return the data from both tables. if there are multiple rows for the invoice in the tables (which there can be) group all rows to create one row.

I'm including the DDL for the tables below:

CREATE TABLE [dbo].[ADVANCE_BILLING](
[AB_ID] [int] NOT NULL,
[SEQ_NBR] [smallint] NOT NULL,
[AB_FLAG] [smallint] NOT NULL,
[JOB_NUMBER] [int] NOT NULL,
[JOB_COMPONENT_NBR] [smallint] NOT NULL,
[FNC_CODE] [varchar](6) NOT NULL,
[FNC_TYPE] [varchar](1) NULL,
[ADV_BILL_NET_AMT] [decimal](14, 2) NULL,
[COMMISSION_PCT] [decimal](9, 3) NULL,
[AR_INV_NBR] [int] NULL,
[AR_INV_SEQ] [smallint] NULL,
[AR_TYPE] [varchar](3) NULL,
[AR_INV_VOID] [smallint] NULL,
[CREATE_DATE] [datetime] NULL DEFAULT (getdate()),
[LINE_TOTAL [decimal](14,2)

CREATE TABLE [dbo].[ACCT_REC](
[AR_INV_NBR] [int] NOT NULL,
[AR_INV_SEQ] [smallint] NOT NULL,
[AR_TYPE] [varchar](3) NOT NULL,
[CL_CODE] [varchar](6) NOT NULL,
[DIV_CODE] [varchar](6) NULL,
[PRD_CODE] [varchar](6) NULL,
[OFFICE_CODE] [varchar](4) NULL,
[AR_INV_DATE] [datetime] NULL,
[AR_DESCRIPTION] [varchar](40) NULL,
[AR_INV_AMOUNT] [decimal](15, 2) NULL,
[AR_POST_PERIOD] [varchar](6) NULL,
[AR_COS_AMT] [decimal](14, 2) NULL,
[AR_STATE_AMT] [decimal](14, 2) NULL,
[AR_COUNTY_AMT] [decimal](14, 2) NULL,
[AR_CITY_AMT] [decimal](14, 2) NULL,
[AR_ADVANCE_AMT] [decimal](15, 2) NULL

CREATE TABLE [dbo].[INCOME_REC](
[AB_ID] [int] NOT NULL,
[SEQ_NBR] [smallint] NOT NULL,
[JOB_NUMBER] [int] NOT NULL,
[JOB_COMPONENT_NBR] [smallint] NOT NULL,
[AR_INV_NBR] [int] NULL,
[AR_INV_SEQ] [smallint] NULL,
[AR_TYPE] [varchar](3) NULL,
[AB_FLAG] [smallint] NULL,
[CREATE_DATE] [datetime] NULL
[INCOME_REC] ADD [REC_AMT] [decimal](14, 2) NULL

I hope this wasn't too long winded, I just wanted to provide as much detail as possible with regards to my question!

If anyone has any insight, that would be awesome! I've tried a billion different join combinations and cannot get this to provide the right results!!

Thanks!!

If we were dealing with 0-1 income_rec rows and 0-1 advance_billing rows for any given acct_rec row, and if AR_INV_NBR, AR_INV_SEQ, and AR_TYPE uniquely identify a single acct_rec row, then this is trivial:

SELECT ...
  FROM acct_rec ar
         LEFT OUTER JOIN income_rec inc ON (    ar.AR_INV_NBR        = inc.AR_INV_NBR
                                            AND ar.AR_INV_SEQ        = inc.AR_INV_SEQ
                                            AND ar.AR_TYPE           = inc.AR_TYPE
                                            AND ar.JOB_NUMBER        = inc.JOB_NUMBER
                                            AND ar.JOB_COMPONENT_NBR = inc.JOB_COMPONENT_NBR
                                           )
         LEFT OUTER JOIN advance_billing bill ON (    ar.AR_INV_NBR = bill.AR_INV_NBR
                                                  AND ar.AR_INV_SEQ = bill.AR_INV_SEQ
                                                  AND ar.AR_TYPE    = bill.AR_TYPE
                                                 )

However, we apparently have to deal with the possibility that we have multiple rows in each of the three tables with the same three key values (AR_INV_NBR, 'AR_INV_SEQ, andAR_TYPE`). This would allow cross-products amongst the various tables to come in, which is probably the issue you've had.

Taking a relatively simple example, where there is 1 row in acct_recwith our three key values, 3 rows in income_rec, and 2 rows in advance_billing. The LEFT JOIN approach above would give us six rows:

ar + inc(1) + bill(1)
ar + inc(1) + bill(2)
ar + inc(2) + bill(1)
ar + inc(2) + bill(2)
ar + inc(3) + bill(1)
ar + inc(3) + bill(2)

Aggregates would result in twice the expected values for the income_rec columns, and 3 times the expected values for the advance_billing columns.

So, we need a solution where only one of the JOINed tables is allowed to have multiple rows.

The script below:

creates a temp table with one unique row for each of the AR_INV_NBR-AR_INV_SEQ-AR_TYPE combinations seen in acct_rec;
generates the actual results by:

creating a sub-query to match our unique account records with all matching records from advance_billing, aggregating the results so we have one row for each acct_rec row;
take our unique account temp table, and LEFT JOIN it with income_rec, and with our aggregated advance_billing sub-query;
and aggregate the income_rec values, to handle multiple rows from that table (by this point, we've combined any multiple rows from acct_rec and advance_billing into a single row).

Here's the query:

CREATE TABLE #ar
     (
       [AR_INV_NBR] [int] NOT NULL
                ,[AR_INV_SEQ] [smallint] NOT NULL
                ,[AR_TYPE] [varchar](3) NOT NULL
                ,[AR_INV_DATE] [datetime] NULL
                ,[AR_INV_AMOUNT] [decimal](15, 2) NULL
                ,[AR_POST_PERIOD] [varchar](6) NULL
                ,[AR_ADVANCE_AMT] [decimal](15, 2) NULL
     )
;

-- populate a temp table with 1 unique row
--   for each AR_INV_NBR, AR_INV_SEQ, AR_TYPE in acct_rec
INSERT INTO #ar (AR_INV_NBR, AR_INV_SEQ, AR_TYPE, AR_INV_DATE, AR_INV_AMOUNT, AR_ADVANCE_AMT)
SELECT AR_INV_NBR, AR_INV_SEQ, AR_TYPE
      ,MAX(AR_INV_DATE)
      ,SUM(AR_INV_AMOUNT)
      ,SUM(AR_ADVANCE_AMT)
  FROM acct_rec
 GROUP BY AR_INV_NBR, AR_INV_SEQ, AR_TYPE
;

-- Update temp table with the maximum post period from the acct_rec row(s)
--   with the maximum date for each AR_INV_NBR, AR_INV_SEQ, AR_TYPE
-- Don't want to jsut take MAX of all post periods, in case we've got rows
--   that fall before and after the restart of the post period count
--   (something that could possibly happen at year-end, quarter-end, daily, or never).
UPDATE ar
   SET AR_POST_PERIOD = pp.AR_POST_PERIOD
  FROM #ar ar
         INNER JOIN (SELECT t.AR_INV_NBR, t.AR_INV_SEQ, t.AR_TYPE, t.AR_INV_DATE
                           ,MAX(act.AR_POST_PERIOD) as AR_POST_PERIOD
                       FROM #ar t
                              INNER JOIN acct_rec act ON (    t.AR_INV_NBR  = act.AR_INV_NBR
                                                          AND t.AR_INV_SEQ  = act.AR_INV_SEQ
                                                          AND t.AR_TYPE     = act.AR_TYPE
                                                          AND t.AR_INV_DATE = act.AR_INV_DATE
                                                         )
                    ) pp ON (    ar.AR_INV_NBR  = pp.AR_INV_NBR
                             AND ar.AR_INV_SEQ  = pp.AR_INV_SEQ
                             AND ar.AR_TYPE     = pp.AR_TYPE
                            )
;

-- Now that we know we have one unique row for each set of values in acct_rec,
--   we can bring in the values from the other tables.

SELECT ar.AR_INV_NBR, ar.AR_INV_SEQ, ar.AR_TYPE
      ,ar.AR_INV_AMOUNT, ar.AR_ADVANCE_AMT, ar.AR_INV_DATE, ar.AR_POST_PERIOD
      ,SUM(inc.REC_AMT) as REC_AMT
      ,bill.HOURS, bill.QUANTITY, bill.LINE_TOTAL
  FROM #ar ar
         LEFT  JOIN INCOME_REC ir ON (    ar.AR_INV_NBR = inc.AR_INV_NBR
                                      AND ar.AR_INV_SEQ = inc.AR_INV_SEQ
                                      AND ar.AR_TYPE    = inc.AR_TYPE
                                      AND EXISTS (SELECT 1 FROM acct_rec
                                                   WHERE AR_INV_NBR = inc.AR_INV_NBR
                                                     AND AR_INV_SEQ = inc.AR_INV_SEQ
                                                     AND AR_TYPE = inc.AR_TYPE
                                                     -- Drop the following two lines if they won't actually eliminate anything
                                                     AND JOB_NUMBER = inc.JOB_NUMBER
                                                     AND JOB_COMPONENT_NBR = inc.JOB_COMPONENT_NBR
                                                 )
                                     )
         LEFT  JOIN (SELECT ar1.AR_INV_NBR, ar1.AR_INV_SEQ, ar1.AR_TYPE
                           ,SUM(bill_1.HOURS) as HOURS
                           ,SUM(bill_1.QUANTITY) as QUANTITY
                           ,SUM(bill_1.LINE_TOTAL) as LINE_TOTAL
                       FROM #ar ar1
                              INNER JOIN ADVANCE_BILLING bill_1 ON (    ar1.AR_INV_NBR = bill_1.AR_INV_NBR
                                                                    AND ar1.AR_INV_NBR = bill_1.AR_INV_NBR
                                                                    AND ar1.AR_TYPE    = bill_1.AR_TYPE
                                                                   )
                      GROUP BY ar1.AR_INV_NBR, ar1.AR_INV_SEQ, ar1.AR_TYPE
                    ) bill ON (    ar.AR_INV_NBR = bill.AR_INV_NBR
                               AND ar.AR_INV_NBR = bill.AR_INV_NBR
                               AND ar.AR_TYPE    = bill1.AR_TYPE
                              )
 GROUP BY
       ar.AR_INV_NBR, ar.AR_INV_SEQ, ar.AR_TYPE
      ,ar.AR_INV_AMOUNT, ar.AR_ADVANCE_AMT, ar.AR_INV_DATE, ar.AR_POST_PERIOD
      ,bill.HOURS, bill.QUANTITY, bill.LINE_TOTAL
;

NOTE: This query may be overly complicated, because I've made assumptions to try to make it cover the worst-case scenarios. If AR_INV_NBR, AR_INV_SEQ, AR_TYPE act as a unique key on the acct_rec table, then the #ar temp table can be dropped. If the stated JOIN using JOB_NUMBER and JOB_COMPONENT_NBR between acct_rec and income_rec is not needed (for example, if there's no chance that we'd find an income_rec row where the two values don't match up to any rows in acct_rec), then the EXISTS clause in the income_rec JOIN can be dropped.

ALSO NOTE: by the same token, the code does assume that JOB_NUMBER and JOB_COMPONENT_NBR are actually in acct_rec (the  DDL posted doesn't include them), and those two columns in advance_billing should be ignored. If either assumption is incorrect, the query could change significantly.


Useful formats for the results of a database "audit"? My company has tons of disparate databases - Oracle, SQL Server, and (shudder) MS Access - lying around, and the CIO wants to bring in a third party to track them all down and document them. But, rather than having them just give us a big ole ERD, my manager (who is writing the project's requirements) wants to specify that the findings be given to us in some useful format. We were thinking Visio, but that's just a big ERD that's zoom-able. Any suggestions?

ER-Assistant. Great ERD, very small and compact. 620Kb total. Its basic plain black/white ERD's but some like that.

Its free and uses Crows foot notation, but windows only.

http://highered.mcgraw-hill.com/sites/0072942207/student_view0/e_r_assistant.html

Another popular choice is Workbench from my research. Here is the link.

http://dev.mysql.com/downloads/workbench/5.1.html


Only first column in ORDER BY clause is sorted It seems like I've misunderstood the concept of 'Order by'.

I have a table with this structure and data:

CREATE TABLE TestTest (Value1 Int, Value2 Int);

INSERT INTO TestTest VALUES
(1, 10),
(2, 9),
(3, 8),
(4, 7),
(5, 6),
(6, 5),
(7, 4),
(8, 3),
(9, 2),
(10, 1)
;

With the query below:

Select Value1 , Value2
from TestTest
order by Value1 desc,Value2 desc

I expect both columns Value1 and Value2 from 10 to 1 because I use DESC for both columns.

But I see this output:

Why isn't Value2 also in descending order?

You are ordering rows of data, not each column separately.

The row (10, 1) comes before the row  (1, 10)  because of your ORDER BY clause.

The Value2 only comes into play when there is a tie on the first column.

As further explained by Hellion, as far as the database is concerned, the pair (10, 1) is an indivisible unit: it's not two values, it's one set (that happens to contain two values). You can't just break the set into pieces and swap the pieces into different sets willy-nilly. As I said (and as Darko shows in another answer), the ORDER BY clause sorts by the first specified column (Value1), and then if there is more than one row with the same number in Value1, it sorts that sub-set of rows by Value2.

As another illustration, you can also consider the example suggested by A C:

  See also the way the dictionary sorts words: AA, AB, AC, BA, BB, BC, CA, CB, CC... Now replace the first letter with your first column value (even if it's more than one digit) and the second letter with the second column value and there you go - same principle. (Yes, I sorted ASCending for clarity -- DESCending sorted dictionaries are hard to find.)


Where is Connection String Located as it relates to Availability Group Listeners? I am trying to teach myself about Availability Group Listeners and see that I need to update a "Connection String." I know that I have a connection string in my web.config file on the Web server, but is this where it needs to be updated? Is there somewhere in SQL Server Management Studio where it needs to be updated?

I've updated the connection string there, replacing the server name with the new Availability Group Listener Name that I've created, then updated my database table to replace the server name there with the AG Listener name too. When I update the name in the database table, my records will not open in the GUI where I'm accessing them from a website.

I think this will be a problem because if there is a failover, I'll need to manually update the name of the server for each record of the table to point to the new server location. I figured that if I put in the listener name in place of the server name, this would never need to be updated in the event of a failover.

Any thoughts? I've researched until my brain is mush and really need some help with fully understanding how to get these listeners working. Thanks in advance!

If you've set-up an Always On Availability Group Listener (and you should), then you'll have a new virtual name to use in your connection string. So if your SQL Servers are SQL01 and SQL02, and there's an availability group called SQLAG01 - then you'll specify this. You could continue to specify SQL01 in your connection string, but if the Always On Availability Group has failed over to SQL02, it won't work. But if you specify the listener name, all will be well.

Make sure that you've got the latest SQL Server Native Client drivers installed so that you can use the extra settings like Multisubnetfailover and potentially ReadIntent


OPTIMIZE FOR variable error I'm having a problem specifying a value within an OPTIMIZE FOR statement. I'd like to optimize the query with the value as a string, but I must be doing something wrong, because SQL gives the following error:

  The value specified for the variable "@test" in the OPTIMIZE FOR
  clause could not be implicitly converted to that variable's type.

The example below is slightly contrived but the problem is the same as the one I'm having with my actual query.

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(max)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test = 'one'))

What am I doing wrong?

It is because you are declaring your @test variable as nvarchar(max) and comparing that to a non lob literal in the OPTIMIZE FOR clause.

From the docs on OPTIMIZE FOR :

OPTIMIZE FOR ( _@variable\_name_ { UNKNOWN | = literal_constant } [ , ...n ] )

You should change the length of the variable  to the size of the highest possible character length of the value that will be stored in it.

DECLARE @test nvarchar(3);

DB<>Fiddle

NVARCHAR(MAX)  and OPTIMIZE FOR

In theory you are able to optimize for nvarchar(max) fields, but it appears that the literal has to be over 4000 (for nvarchar) characters long:

When creating 4000 spaces:

SELECT REPLICATE(' ', 4000);

And adding them to the end of the constant:

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(max)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test  = N'one                                                                                ' ))

It works too.

You cannot use CAST() or CONVERT() to explicitly change the constant to NVARCHAR(MAX)
DB<>Fiddle

Extra

If the variable is lower than nvarchar(max) but the constant is over 4000 (for nvarchar) characters, it still works.:

declare @TEMP table(asWord nvarchar(max), asNumber int)

insert into @TEMP (asWord, asNumber) values (
    'one',
    1
), (
    'two',
    2
)

DECLARE @test nvarchar(3)
SET @test = 'one'

select * from @TEMP
where asWord = @test
OPTION (OPTIMIZE FOR(@test  = N'one                                                                                ' ))

Because all the spaces after the literal value are 'cut off' and it is treated as a nvarchar(3):

<ColumnReference Column="@test" ParameterCompiledValue="N'one'" ParameterRuntimeValue="N'one'" />

If we change the variable to nvarchar(4)

DECLARE @test nvarchar(4)

One more space is present in the ParameterCompiledValue

<ColumnReference Column="@test" ParameterCompiledValue="N'one '" ParameterRuntimeValue="N'one'" />


How can i prevent decryption in SQL Server procedures I came across in internet tool to decrypt procedures, I checked my database with this tool to decrypt my procedures and it works fine, now the problem is I wish to prevent this kind of tools from decryption of my procedures.

Any methods to prevent this kind of tool from decryption of my procedures?

SQL Server does not actually encrypt the definitions of objects, it obfuscates them instead. It is a bit misleading I am afraid as all it is really doing is making it unreadable to humans [excluding the use of tools as you previously mentioned].

Also SQL Server needs to know the contents of the procedures, functions & views for when it needs to recompile the execution plan etc... so real encryption would prevent SQL Server from being able to do this. As a result this is not possible.

My best recommendation if you are worried about IP & copyright is to secure your software with Licensing. You may not be able to prevent someone from reverse engineering your code but you can sue the pants off them if they do.


Can I update my Microsoft Enterprise SQL Server? I just have a quick question about SQL Server and the various tools that come with it.

I updated my SQL Server Management Studio to the latest version because I wanted to use the STRING_AGG function, but apparently that is separate from the actual SQL Server.

So I am trying to update my actual SQL Server. I downloaded this link, is it the correct one? (SQL Server® 2017 for Microsoft® Windows Latest Cumulative Update)
https://www.microsoft.com/en-us/download/details.aspx?id=56128

I ran it but it seems that it didn't upgrade anything. All it seems to do was create an instillation center. Is that where I have to go (I need admin privileges for every action so it is difficult for me to check)?

Is it even possible to do this upgrade. I work for a company and we use Enterprise Edition. We connect to two different third-party databases. Will updating on my local computer work? Or do I have to speak to the DBA and get it updated on some main server?

Given that you work for a company that is using Enterprise Edition I'm going to assume you aren't touching one of their servers.  If you are please don't. As mentioned in the comments this could be a very expensive mistake and if the work you are doing is on any company asset please contact the company's DBA. That's what they are there for.

That said, let me explain a few things.

SQL Server Management Studio is an interface used to connect to the SQL Server Database Engine (among other things).  It's version is completely separate these days from the database engine.  So you could very easily be on SSMS 18 while working with SQL Server 2008.
While some functionality is actually part of the GUI things like STRING_AGG are actually functions of the language T-SQL that is not part of the GUI and requires you update the database engine.

It looks like you got this far on your own but I just wanted to be sure.

Now, if you are trying to set yourself up a home lab, or are you a company workstation and have permission to install a single user development version of SQL Server then it's perfectly reasonable to want to upgrade SQL to try out new functionality. In this case you'll want the Developer Edition of SQL Server.  It's a free single user license that can only be used for development/experimentation. It's also has the same functionality as the Enterprise version of SQL Server so you'll have everything you want.

You can get it here: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

If your home machine/workstation has the resources I would recommend installing more than one version of SQL on it. The version you are working on at work, and the latest edition that you want to play with.

Last thing, if you are working on a shared environment you really need to contact your DBA and make sure that nothing has been broken. Upgrading SQL can have implications on existing functionality and could potentially break an existing application.


Reducing the number of tables used - mySQL I have a table for tax implementation in my application, but I am not sure the design I have done for the table. Can someone review it?

val_taxes is the table which has all types of tax listed in the table

CREATE TABLE `val_taxes` (
  `id` int(255) NOT NULL,
  `tax_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tax_value` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `val_taxes` (`id`, `tax_name`, `tax_value`) VALUES
(1, 'SGST', '9.00'),
(2, 'CGST', '9.00'),
(3, 'Service', '1.00'),
(4, 'State', '0.25');

val_tax_group is the table where multiple tax can be grouped

CREATE TABLE `val_tax_groups` (
  `id` int(255) NOT NULL,
  `tax_group_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `val_tax_groups` (`id`, `tax_group_name`) VALUES
(2, 'GST'),
(3, 'Chennai Tax');

tax_mapping is the table for mapping the val_taxes to val_tax_groups

CREATE TABLE `tax_mapping` (
  `id` int(255) NOT NULL,
  `valtaxgroups_id` int(255) NOT NULL,
  `valtaxes_id` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `tax_mapping` (`id`, `valtaxgroups_id`, `valtaxes_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 3),
(4, 2, 4);

Given all the tables and their descriptions, is the architecture correct? Or can we reduce it down to two tables without any duplications?

You have 2 entities (taxes and groups) joined as M:N. 3-table structure for to store this data is optimal in most cases. Additional recommendations:

1) declare id field in val_taxes and val_tax_groups as a primary key and alter their datatype to INT or BIGINT;

2) remove id field from tax_mapping table structure;

3) alter valtaxgroups_id and valtaxes_id datatype to INT or BIGINT;

4) define (valtaxgroups_id,valtaxes_id) field combination as primary key;

5) create foreign key references from valtaxgroups_id to val_tax_groups.id and from valtaxes_id to val_taxes.id.

– Akina


All actions denied on MS SQL Server on Go Daddy Hosting I have a website hosted on GoDaddy and I need to create a database for it.

I headed to the Databases section and created a SQL Server Database, then on my local pc I tried to access the database via SQL Server Management Studio. I was able to login to the database but I cannot make any operations. I get a message saying "it does not exist or you do not have permission".

I decided to go to the Security tab, then Login and tried to change my username that I'm using to systemadmin role but received:

  Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

What could be the problem? There are no other accounts on it. The default sa account is disabled and I can't enable it because it will prompt no permissions etc.

I don't understand it. Why does GoDaddy allow me to create a database but with no permissions or rather I cannot alter it?

You can't use sysadmin (sa) if you register shared hosting. You need to create your database via their control panel, then you can manage your database via your CP. Or some providers allow to connect remotely. For more information, just contact your hosting provider. Good luck


MySQL too many connections on shared server We have run a PHP/MySQL blog site on a shared server for years with no problem (my code not Wordpress). I'm not an system or database guy except for what I need. Our host (BlueHost) moved our site to a new server, and our capacity to serve our audience has dropped significantly. We're experiencing a seasonal spike now, so that makes it worse. So much so that we're also using another identical site to bear the load (GoDaddy). Not ideal. I don't have admin privileges obv, but I can see that we get 1000 max connections on GoDaddy and 150 on BlueHost (not sure what it was before).
I've been tracking the Threads_connected and Threads_running for both sites (again identical sites). GoDaddy never gets above ~25 connected and ~2 running. BlueHost will start to stack up Threads_connected until it hits the 150 limit mark. I'm trying not to break our resource allotment, but even with a lot of (and sometimes all) traffic redirected over to GoDaddy, BlueHost will stay above 120 for an extended period of time. Not sure if it's a caching issue or what.
I've tried doing an 'inline' declaration for timeouts when I open my PHP/PDO connection to MySQL, but I'm somewhat skeptical that it's fixing the problem.
$dbh_ip_check -> exec("SET session wait_timeout=10");
$dbh_ip_check -> exec("SET session connect_timeout=10");

These are the current global variables:
mysql> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 20       |
| delayed_insert_timeout      | 40       |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 10       |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 3600     |
+-----------------------------+----------+
We don't get a huge amount of traffic, but our spikes are probably seeing >100 people hit the site at a time, and that would continue for a half hour or more.
Any recommendations for fixes on a shared server? Thanks.
Current server variables/status:
Show global status copy/paste: https://paste.debian.net/1182642/
Show global variables: https://paste.debian.net/1182643/
ETA:

  [~]# iostat -xm 5 3
Linux 4.19.150-76.ELK.el7.x86_64 (boxxxxx.bluehost.com)         01/27/21       _x86_64_ (12 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          51.92    5.08   25.79    2.47    1.35   13.39

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util

  [~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) 800000
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 178732
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) 800000
open files                      (-n) 100
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 15240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 25
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

max_connections is probably set to 151.  And this is probably because of running a newer version of MySQL.
Assuming BlueHost allows you to change the config file (and they may even have a UI for doing such), change that to, say, 1000.
Having hundreds of 'simultaneous' connections is not, in itself, a "bad" thing.  However, it may be indicative of some other problem.
Do the connections hang on longer than necessary?  Is there some form of 'connection pooling'?  What is the client -- does it have a limit on connections to it?  Are you also having CPU or other performance problems?  How long does one user stay online and connected?


Efficient method to update character length of columns in large table I have a table with 80+ million records. There are 2 columns which are currently varchar(MAX), which I would like to update to varchar(255) to save space. It is not an issue if data will be truncated. This table has 3 non-clustered indexes on it.

SQL Server version: 2012 Web Edition

A solution that would require table locking or taking the database offline would be acceptable.

What I have tried:

ALTER COLUMN: SSMS does not let me use the ALTER COLUMN statement because there is an index that uses that column.
Updating in batches: does not work efficiently (or at all) since the unique identifier (or date field) that would be needed to keep track of which records were updated is not indexed and therefore the queries just hang. Also I read that updates would generate a lot of logs.
Adding a new table and inserting in batches: does not work because of same reason above.
SSMS Export Wizard: Creating a new table with the correct column data types and exporting from original works within a few hours ONLY IF I don't create the indexes on the table. If I first create the indexes and then run the export, the process gets stuck at about 25/30 million. The first few million imports fairly quickly but gets slower as it goes on. I can confirm this after I had it run for 12 hours. In the former case, I am unsure how to build these indexes as I am concerned about how much memory/cpu/space it will end up taking on my server
Do work on test server and re-attach: This last point wasn't tried yet but is next on list if I don't get another idea: Transfer/copy database (via file transfer) to test server, try applying the change there without having to worry about performance costs and then re-attaching to original server. (I can also see how long and how much space an index rebuild would take, but then again that wouldn't offer me accurate information for the production environment.)

Any ideas or comments on how to accomplish this?

I would suggest the following steps

When possible, change your database recovery mode to Simple Recovery if otherwise
Drop your index(es) on the two varchar(max) columns (as you will need to rebuild it anyway)
Run the following

update MyTable Set MyCol_X=substring(MyCol_X, 1, 255), MyCol_Y=substring(MyCol_Y, 1, 255);
Alter table to change the column data type

alter table MyTable alter column MyCol_X varchar(255);

alter table MyTable alter column MyCol_Y varchar(255);
Rebuild your indexes that were dropped
Set recovery mode to previous state before step 1 is executed


What are the limits of Postgresql's large object facility? I've not found an equivalent of the about PostgreSQL page that covers the limitations of the large object facility in particular, but reading between the lines I think the following is true:

No more than 32TB of large objects can be stored (reason: they are all stored in a single table named pg_largeobject, and the per-table size limit is 32TB (assuming default page size)).
No more than 2^32 large objects can be stored (reason: the primary key of pg_largeobject is an oid which is a 32-bit quantity).
pg_largeobject cannot be partitioned (reason: because it's a system catalog?)

Is this all correct?

These 3 points are correct. We might add

A large object cannot exceed 4TB for PostgreSQL 9.3 or newer, or 2GB for older versions. This is based on the release notes:

  E.11.3.5. Data Types

  Increase the maximum size of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata)

Also, the binary contents are sliced into tiny chunks of 2000 bytes in pg_largeobject. There is one row per chunk, so when importing large contents (large by today's standards), the number of rows in this table tend to grow quickly.
Although that does not imply a hard limit, users should be aware of that for performance reasons.


Non clustered index on multiple columns I have a table which has around 50 columns. But there are only 6 columns on which we perform regular search. When I am trying to create a non clustered index on it I am getting the warning of 900 bytes. This would be a problem as we also do bulk update/inserts. So can onyone help me in finding a solution?

If I put few columns out of those 6 in INCLUDE, will that cause any performance issue?

Lets say the datatypes for the columns are -

     ColumnA (nvarchar(255),null)
     ColumnB (Int, null)
     ColumnC (nvarchar(255),null)
     ColumnD (Datetime,Null)
     ColumnE (nvarchar(255),null)
     ColumnF (nvarchar(Max),null)

Primarily these columns are used in WHERE to pull out data.

Thanks,
Unmesh

The size of the nvarchar(255) columns is 510 bytes each. Even two of them will cause a warning and potential issues with insert/update operations.

What you want to do is find a combination of one (or less) of the nvarchar columns plus the int and datetime columns that will give you the most unique values.  Then index on that combination including the other columns in order to create a covering index.

If you need more than one of the nvarchar(255) columns actually indexed in order to create a highly selective index (lots of unique values) then you are down to two options.

If you are certain that you will never have more than ~445 characters (remember these are unicode so there are 2 bytes per character and you need room for the datetime and int columns) in the combination of the 3 nvarchar(255) columns then you can create the index and keep your fingers crossed that you aren't wrong.

If you can't be sure they won't go over that size (and if you can you should think about shrinking the size of the columns) then your best bet is to do what @MichaelJSwart suggested in the comments above and create a hashed column to index on.  The biggest down side to this option is that it will require changing your code.  On an up note while you are creating your hash column(s) and changing your code you can work in an index on the nvarchar(max) column (by hashing it).


Massive Transaction Logs - what are my options? I have a fairly crazy scenario where I am processing ~250 million inserts and 250 million deletes for a table that contains data that is only relevant for the moments that it is in the database.  Once we get new data for that key, we delete the old and insert the new.

I am using an azure database, and we have been running with very few issues for a few years now.  The problem is this:  Microsoft has recently started charging their PIT backups.  While I'm not opposed to them charging fair prices,  it seems that I never really thought about HOW MUCH data we are storing for PIT recover for this table. Last month we had just over 8 TB in transnational logs.  That means our PIT storage fees were ~$2000.   I am running a small company and for us that is debilitating.   Microsoft has been helpful and offered us some refunds to get us through, however the base problem still exists.

My question is this:  is there anything I can do to lower my logging?  From my research, I'm not sure Simple Recovery mode will solve this issue, and I don't think Azure supports simple recovery either.

I am proficient is SQL (enough to get by and optimize things occasionally), but by no means an expert. Is there anything I can do.  is it possible to not log a table. Is it possible to not log a Sproc?  Do I have options to reduce my Logging on azure?

As a last resort, is there another technology that would work for storing the data in this one table ( this would cause a large re-write, but for $2K a month I would consider it).

I'm a little desperate here! Any and all suggestions are welcome.

Maybe you could have a look to minimal logging in SQL Server (but this requires changing database recovery model to simple or bulk-logged) and means that you cannot restore/recover database using point in time recovery (I have never used Azure so I don't know if this is possible).


Error when modifying Notify Operator task in maintenance plan I'm unable to modify a Notify Operator task in my new maintenance plan.
SQL Server Management Studio displays this error after double clicking on the task:

  Property IsMailHost is not available for database '[msdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access right.

The other tasks in the maintenance plan are working successfully.
I'm using SQL Server 2016 Standard (13.0.1601.5) and SQL Sever Management Studio 16.5.
The database mail is also configured with a public profile.

From the message you posted I can see MSDB is not set for Mail Host Status.

  Database.IsMailHost Property -- Gets a value that specifies whether
  the database is configured as a mail host.

To find whether the Database is a Mail Host or not, use the below PowerShell command. I tested it on SQL Server 2014.

Use the below code in Powershell ISE to check: (For Default Instance Only)

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("MSDB")
Write-Host "Database is a mail host :" $db.IsMailHost

The MSDB database is the Database Mail host database. This database contains the stored procedures and messaging objects for Database Mail.

By default, Database Mail is not active.

You must explicitly enable Database Mail by using either

The Database Mail Configuration Wizard

or

The Database Mail XPs Option of the sp_configure system stored procedure.

The following example enables the Database Mail extended stored procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO


One database listening on two database names I know this is a sort of odd request. I myself have never had to even think about a scenario like this until today. I'll go straight to the point to make the question easy to read. I suspect the right answer will involve more of "thinking outside the box" than a hidden feature or capability that somehow I'm not aware of.

Here is the thing: I need a PowerBI dashboard to query my SQL Server, but the PowerBI dashboard will have a connection string that queries a database (let's call this database "DatabaseOne_Report") whereas the SQL Server will host a database with a different name (let's say "DatabaseOne").

So when the PowerBI dashboard runs a query to DatabaseOne_Report.dbo.MyTable I want SQL Server to provide data from DatabaseOne.dbo.Table (notice the "_Report" suffix is gone from the database name). Is that even possible somehow?

I can anticipate your first question would be why I am using a connection string with the wrong database name. The answer is that this dashboard connects to different environments and these environments host this database using either 1 of 2 different names for reasons that are beyond the scope of what I'm asking here. The bottom line is that we are trying to avoid re-writing the connection string of the PowerBI dashboard and hope we can use a single connection string to tackle both possible DB names.

I read a lot of questions in different forums to tackle aliases of 2 databases hosted in different boxes, but never seen anybody asking 2 databases hosted in the same box but with potentially two different names.

I thought about creating a empty "shell" of a database with the other name with nothing but synonyms, but that would be a lot of configuration... I was hoping to find something simpler.

Thanks!

I see two options.

SQL Alias as mentioned in the comment by McNets.  But I am not sure if it will work in your scenario, when it does work it is a great tool.
Use SQL Replication to create a copy of the database DatabaseOne named DatabaseOne_Report on the same sever.  Depending on a few variables, the two versions of the database will be completely in sync to delayed a bit.


INSERT failed in SQL Job because of incorrect SET options 'QUOTED_IDENTIFIER' I created a sql job to query the plan cache and get the serial plans alone and then clear it from the plan cache.
As part of step 1 in the SQL job,i filter only the serial plans and insert it into a table i created.
When i run the job,in step 1 itself i get the below error;

INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.

I did research and learned that it might be due to the SET options.
So i checked the table i created and it is created with

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

I saw posts where people have the same issue ,and solved it after changing the above SET options to ON.
I have this SET already ,but still same error.i am not sure why i am getting this error.

I have this line in the query where i get the serial plans.

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )

This is how it looks like :

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ),
relop AS (
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
--------------------
--------------------
----------------------
))
INSERT INTO table_name
select * from relop
WHERE relop.total_relop = relop.serial_relop

When i run it on the query window,there is no issues.
When i put that in a job,i get this error.

Any suggestions?

It's not the way the table was created, it's the options your query runs with.

Agent does indeed run with the wrong settings.

SELECT *
FROM   sys.dm_exec_sessions
WHERE  is_user_process = 1
AND    (   ansi_nulls = 0
      OR   ansi_padding = 0
      OR   ansi_warnings = 0
      OR   arithabort = 0
      OR   concat_null_yields_null = 0
      OR   quoted_identifier = 0 );

The simple fix would be to use the correct options at the beginning of the job step that does the insert.

For instance, if I create this table, an insert works just fine:

USE master

CREATE TABLE dbo.whatever (id INT, thing AS id * 2);

INSERT dbo.whatever ( id )
VALUES ( 0 )

But from an Agent job step it fails, unless I put this first:

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;

For reference: Create Indexed Views


Handling large DB tables that have millions of records So I have a large table(Table1), with about 5 million records. I have a second table(Table2) with about 108 million records. I am using a key value from Table1 to find any matching records in Table 2( there will be between 10-150 records per match). I am creating a new table for those records and deleteing them from Table2. Like a backup except not all records in Table2 will match something from table1.  I have tried a whole match "select * from" which is way too large and have tried going one record at a time from Table1 but calculated it would take 35 days to run after letting it run for 2 days.  So I guess i am looking for a good solution for handling large tables that isn't going to take forever and that doesn't run out of memory.

Thanks, feel free to point me to another question if it matches but i couldn't find any

You can try this:

create table Table3 as (select * from Table1 A where
exists(select * from Table2 B where A.key=B.key));

And then delete from Table2 using newly created table Table3

delete from Table2 A where exists(select * from Table3 B where A.key=B.key);


Set Trigger on ALTER_SERVER_ROLE I've tried to add a trigger to the ALTER_SERVER_ROLE command using:
CREATE OR ALTER TRIGGER XXX
    ON ALL SERVER
    FOR ALTER_SERVER_ROLE AS

It creates fine but doesn't fire when using the ALTER SERVER ROLE [sysadmin] ADD MEMBER [Test]
Any ideas why it doesn't fire?
Using SQL2019 CU8

OK, found it after sifting through the intellisense and trying everything that looked right
FOR DDL_SERVER_SECURITY_EVENTS AS


Why is VACUUM FULL necessary here? Queries are running very slowly on a table with 80k rows.

SELECT count(*) FROM task takes <100ms. SELECT type, count(*) FROM task GROUP BY type takes 30,000ms.

Basically, any query that is not index-only takes excruciatingly long.

I kill the connections and vacuum repeatedly.

rivet=> VACUUM VERBOSE task;
INFO:  vacuuming "public.task"
INFO:  scanned index "task_id_idx" to remove 1830 row versions
DETAIL:  CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.36 s
INFO:  scanned index "task_type_queue_idx" to remove 1830 row versions
DETAIL:  CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.25 s
INFO:  "task": removed 1830 row versions in 41 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  index "task_id_idx" now contains 87135 row versions in 23995 pages
DETAIL:  1830 index row versions were removed.
23488 index pages have been deleted, 23428 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  index "task_type_queue_idx" now contains 8218 row versions in 28362 pages
DETAIL:  915 index row versions were removed.
28219 index pages have been deleted, 28140 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "task": found 666 removable, 3874 nonremovable row versions in 98 out of 1005331 pages
DETAIL:  3174 dead row versions cannot be removed yet, oldest xmin: 70916502
There were 16134 unused item pointers.
Skipped 0 pages due to buffer pins, 1003551 frozen pages.
0 pages are entirely empty.
CPU: user: 0.15 s, system: 0.00 s, elapsed: 0.83 s.
INFO:  vacuuming "pg_toast.pg_toast_133792"
INFO:  index "pg_toast_133792_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_133792": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 70916502
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

But with no success.

Then I vacuum "full."

rivet=> VACUUM (VERBOSE, FULL) task;
INFO:  vacuuming "public.task"
INFO:  "task": found 931 removable, 83522 nonremovable row versions in 1005331 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 3.30 s, system: 0.28 s, elapsed: 29.96 s.
VACUUM

Now my table scan query over 80k rows takes <200ms.

Why was VACUUM FULL necessary?

I notice that before full vacuum there were 1M pages, and now there are <400.

Your table clearly has had a troubled history and became extremely bloated.  However, nothing in the log files you showed us indicate what the nature of that history is.

It could be that autovacuum was turned off for a time, or misconfigured and so ran too slow to keep up, or that there was a transaction held open for a very long time which prevented vacuum from being effective, or maybe someone was constantly locking the table in a mode stronger than ROW EXCLUSIVE, causing autovacuum to yield before finishing everytime it tried to run.


How to store thousands of properties related to a record in PostgreSQL? I have a database of approximately 300K records. For each of theses records there are up to 1600 related properties. Each of these properties have a simple key=>value format. This data is very static and nearly all operation will be reads.

Below are the options I'm thinking about to solve this problem.

1) A column for each property

This falls apart quickly as the max number of columns available in postgres is ~1600. So really, this isn't an option.

2) Create an hstore or JSONB column to store the key value columns.

This seems to work but I've read there are practical limits to how much data should be stored in hstore or JSONB. There are a lot of key/values and I think both hstore and JSONB will store these in TOAST which could have a negative impact on performance.

What potential issues/practical limits should I be aware of if using approach #2?

What are some other approaches to solving this problem?

You could create a table to hold the key/value pairs like such:

CREATE TABLE KEY_VALUE (
  ID BIGINT, -- THIS COULD BE A FKEY TO YOUR '300K' RECORD TABLE
  KEY VARCHAR,
  VALUE VARCHAR
);

As long as this table is indexed properly (an index on id/key, I imagine), getting any key/value pairs you are interested in should still be very quick, even if it's millions of rows large. Granted - this solution as a viable option really depends on what scope of data you expect to store with your key/value pairs (is it all text? or numbers? etc..). Perhaps adding a 3rd column to say what type of data the value is would help.

EDIT

https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway

Just to be clear, btw - I figured this might work given the sheer number of different key->value pairs you said you would have..  If it were a small number, I would probably just have a "details" type of table where each value was stored in a column.


Two relationships to same table i have one transaction table, each transaction has 5-6 contacts involved. each contact has the same fields like name, address, cell, email etc.

So the best thing I guess is to use the same contact table and relate it 5 times to the transaction table.. So I don't need to make the same kind of table 5 times.

The problem starts when I connect with the entity-framework this only takes one relation.

My question is should I make 6 copies of contact for each contact type or I would be better with 6 join tables (A table with just the 2 id's so I could do a join)?

I think its a very common problem but I couldn't find clear information what the best way is.

EDIT:

The best thing is to normalize the tables. Create a contact_type table with a record for each contact type. Then create a contact_type_xref junction table that contains the identifier from the contact table and the identifier from the contact_type table. Then load the contact types associated to each contact into the contact_type_xref, and remove the duplicate contact records from contact. Here's an example:. Once you fix the database design, you can tackle the entity-framework problem (which will probably disappear).


How to catch error from sql server on powershell? I have written powershell script that executes all sql from folder, but I want to stop executing on error? Is it possible to catch the error with powershell that was happened on SQL server? This is powershell code

function runSqlScript{
    param([string]$sqlPath)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString.connectionString
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = Get-Content $sqlPath
    $command.ExecuteNonQuery()
    $connection.Close()
}

function runScriptsFromFolder{
    param( [string]$pathToFolder)
    Get-ChildItem $pathToFolder -Filter *.sql| ForEach-Object {
        runSqlScript($_.FullName)
    }
}

Powershell supports try/catch/finally. This allows you detect and handle errors within the scope of try block.

If you simply want to stop the PS script if any errors occur, including non-SQL errors, wrap the main code in a try block:

try {
    runScriptsFromFolder -pathToFolder "C:\SqlScripts"
}
catch {
    throw
}


Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks We've got a strange discrepancy on one of our SQL Server 2014 instances between the amount of memory being used for cached plans as per sys.dm_exec_cached_plans and the amount of memory used as per sys.dm_os_memory_clerks looking at the CACHESTORE_SQLCP type (which I understand is for adhoc query cached plans).

If we query the cached plans as below:

select cp.cacheobjtype, cp.objtype,
  sum(cast(cp.size_in_bytes as money))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype;

then we appear to have about 90 MB being used in total for cached plans, with only 2MB being used for Adhoc plans.  There are only 300 plans in the cache too.

However, if we look at the dm_os_memory_clerks view as below:

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type = 'CACHESTORE_SQLCP'

then it is reporting that roughly 12 GB is being used.  Our instance has approx. 300 GB of RAM in it.

We'd like to understand the discrepancy, and ideally take some steps to ensure that the plan cache is used effectively (i.e. has more than 300 plans in it to improve the cache hit ratio which is currently very poor).  Being able to account for this space would be the first step.

Any thoughts on what the discrepancy could be and why this space is not being used for caching plans?

FWIW - and I'm only posting this as a temporary answer because there's no way I could make this a digestible comment - I ran these queries and saw some discrepancies as well:

select cp.cacheobjtype, cp.objtype,
  sum(cast(cp.size_in_bytes as DECIMAL(19,4)))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype
ORDER BY sizeMB DESC;

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type LIKE N'CACHESTORE[_]%'
ORDER BY pagesMB DESC;

Most notably, the CACHESTORE_SQLCP total in the second query is less than the four Compiled Plan results in the first query, and there was substantial memory clerk usage under other cachestores as well. This is a lowly VM that doesn't have 300 GB of RAM but still demonstrates that these numbers do not always correlate and add up nicely.


Incorrect syntax near the keyword 'is' in SQL server vb6 Set rs = con.Execute("select IIF(MAX(bill_bno) IS NULL,1,MAX(bill_bno)+1) from rec_all      where rw=0 and iif(month(bill_date)&gt;=1 AND month(bill_date)&lt;=3,year(bill_date)-1,year(bill_date))=" &amp; IIf(Month(Date) &gt;= 1 And Month(Date) &lt;= 3, Year(Date) - 1, Year(Date)))

how to change vb6 code (MS access) database to SQL Server 2012?

Try this:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
FROM   rec_all
WHERE  rw = 0
       AND
       (CASE WHEN MONTH(bill_date) >= 1 AND MONTH(bill_date) <= 3
             THEN (YEAR(bill_date) - 1)
             ELSE YEAR(bill_date) END)
       =
       (CASE WHEN MONTH(Date) >= 1 AND MONTH(Date) <= 3
             THEN (YEAR(Date) - 1)
             ELSE YEAR(Date) END)

or this one:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
FROM   rec_all
WHERE  rw = 0
       AND
       (
       (MONTH(bill_date) >= 1 AND MONTH(bill_date) <= 3 AND (YEAR(bill_date) - 1) = (YEAR(Date) - 1))
       OR
       ((MONTH(bill_date) <> 1 OR MONTH(bill_date) <> 3) AND (YEAR(bill_date)) = (YEAR(Date)))
       )


ensureIndex failed to create index on primary shard: Cannot accept sharding commands if not started with --shardsvr After creating shards in replica set and also setting up config servers, when I try to add a collection to my shards then I get below error :

  {
    "ok" : 0.0,
    "errmsg" : "ensureIndex failed to create index on primary shard: Cannot accept sharding commands if not started with --shardsvr"
  }

I checked it and have started my shard server with --shardsvr. Still I face the same issue.

Current MongoDB Version : 3.4.6 (In which I am trying to create shards)

I check on same version on windows server with mongodb 3.4.6. Please find below scripts.

1) run shard servers

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod1" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod1\log.int1" --port 27000 --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod2" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod2\log.int2" --port 27001 --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod3" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod3\log.int3" --port 27002 --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --shardsvr --replSet rs0 --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod4" --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongod4\log.int4" --port 27003 --logappend

2) config servers

"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --configsvr --replSet rsconfig --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0" --port 26050 --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0\log.cfg00" --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --configsvr --replSet rsconfig --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg1" --port 26051 --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0\log.cfg01" --logappend
"C:\Program Files\MongoDB\Server\3.4\bin\mongod.exe" --configsvr --replSet rsconfig --dbpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg2" --port 26052 --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_Shardcfg0\log.cfg02" --logappend

3) mongos mongo router

"C:\Program Files\MongoDB\Server\3.4\bin\mongos.exe" --configdb rsconfig/BSS-BC4-Benchmark2:26050,BSS-BC4-Benchmark2:26051,BSS-BC4-Benchmark2:26052 --logappend --logpath "C:\Mongodb_Databases\MongoDB_Shard\WT_mongos\log.mongos0" --bind_ip 127.0.0.1,10.10.180.39

4) Connect any shard instance in replica and initiate replica set using rs.initiate()

rs.initiate(
  {
    _id : "rs0",
    members: [
      { _id : 0, host : "Mongo-Test-PC:27000" },
      { _id : 1, host : "Mongo-Test-PC:27001" },
      { _id : 2, host : "Mongo-Test-PC:27002" },
      { _id : 3, host : "Mongo-Test-PC:27003" }
    ]
  }
);

5) Connect any config server and initiate replica set for config server using rs.initiate() - name must be different from shard replica

rs.initiate();
rs.status();
cfg = rs.conf();
cfg.members[0].priority = 3;
rs.reconfig(cfg);
rs.add({ host:"Mongo-Test-PC:26051", priority: 2});
rs.add({ host:"Mongo-Test-PC:26052", priority: 1});
rs.status();

6) Connect mongos and ser read pref and add shard.

db.getMongo().getReadPref();
db.getMongo().setReadPref('secondary');
db.getMongo().setReadPref('primaryPreferred');
db.getMongo().getReadPref();

sh.addShard("rs0/Mongo-Test-PC:27000");

sh.status();

create database shardtest
sh.enableSharding('shardtest');

use shardtest

db.createCollection("shardcollection");

Hope this will help you.