sql server , set N prefix as unicode for all queries by default Is there any option in ms sql server which use N prefix for my database.
There are many query in my application which non of them use N prefix for nvarchar fields , and now I have to set N for all of them and it takes long time to do.
My collatation is Persian_100 and it need unicode (N prefix) so any advise is appreciated

select * from table1 where field1=N'حمید' ;

I want skip N prefix in my query , but it acts as unicode for all type of queries:

No, there's no way to do this.

Any option that did what you want would be extremely confusing because there would need to be a way to turn it off within the string literal syntax. There are other considerations (like storage size) to be aware of when choosing data types, so a setting like this could be very dangerous to put in play.

With regards to .NET applications that send parameterized T-SQL statements to SQL Server, the framework takes care of this for you because a .NET String inherently supports Unicode and things were built using that assumption. This applies to ORM-type libraries as well if they're programmed well.

Consider the following code snippet:

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = @"SELECT * FROM sys.server_principals WHERE name = @name";

IDbDataParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@name";
param.Value = "dbo";
cmd.Parameters.Add(param);

IDataReader dr = cmd.ExecuteReader();

What actually gets sent to SQL Server is this (line break added for clarity):

exec sp_executesql N'SELECT * FROM sys.server_principals WHERE name = @name',
    N'@name nvarchar(3)',@name=N'dbo'

You can see that the framework automatically used a Unicode type for the parameter (the length of 3 is a separate discussion entirely...).

The places where you need to modify code are in situations where SQL queries are not parameterized (or are unparameterizable, i.e., DDL) -- in other words, queries that have parameters concatenated directly into the string, or contain string constants. See my answer here for an example of how to avoid SQL injection issues when using dynamic SQL.

sqlcmd.Parameters.Add("@yourparam", SqlDbType.NVarChar, 10);
sqlcmd.Parameters["@yourparam"].Value = "حميد";


Import Database from Oracle .DBF files Recently my Oracle Installation has been crashed and I reinstalled the Oracle again. I have my old Oracle Data files (.DBF files) which have been recovered from old installation.

Now, I need to restore or reinstate these .DBF files into my new installation, so that I can get all my data and procedures.

Both Old and New Oracle Installation version: Oracle 12c

According to your description, the specific answer depends upon what and how the original backup was done. If it was a cold backup of OS file copy after the database was cleanly shutdown then you can restore the database BUT make sure you also have control and redo log files copied together with the DBF files. First copy all the complete and correct files to the target system in the exacly same location as these were in the original database before. Make sure that the version of the database is same. Then you can simply open the database normally.

Here is a similar post regarding the issue that you have mentioned here.
Mounting a database from database files copied from a previous installation

Cold Clonning Using controlfile backup in oracle 11g

If data file is consistent,you can try that:

shutdown immediate;
cp  youtablespace.dbf /u01/dev1/
startup mount;
alter database rename file '/u02/dev1/youtablespace.dbf' to '/u01/dev1/youtablespace.dbf';
select file_name from dba_data_files;
alter database open;


How can I copy the MySQL EXPLAIN result into a table? I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis.

I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how to populate it from executing an EXPLAIN command.

I figured out how to export a .sql file in MySQL Workbench that contains INSERT commands, but I have to edit this manually to make it work.  Is the EXPLAIN information stored somewhere so I could write queries to do it automatically?  I can't find it in either the information_schema or performance_schema.

If you are running on Linux OS you could save it into a file.

Example:

User='root'
Pass='text'
Host='10.0.0.223' (If it's remote)

On command line run:

mysql -u $User -p$Pass --host=$Host -e "EXPLAIN SELECT * FROM mysql.user LIMIT 0,2" | tee -a test.txt

mysql -e will execute the query on the command line.

tee -a will read from standard input and write to file test.txt

And you'll get:

You can use perl to replace the death lines to any delimiter you want (I used comma in this example).

perl -wnlpi -e 's/\s+/,/g;' text.txt

Example:

Hope this help.

create a table with same number of columns & same column name(for future reference) & then execute these:

"INSERT INTO my_audit_tbl
EXPLAIN SELECT * FROM tablename;"


Time sheet, rates, date dependent I am trying to figure out the best database design for the following;

I have individuals (stored in Agents table) that enter their billing or time worked each day for different projects. Now we have different rates that we pay based on the person and what the project is. And we also have to account for individuals getting pay cuts or raises over time. So....

I have the following tables;

  Agents   - table of each person
  Projects - table of all projects we work
  Rates    - table with AgentID, ProjectID, StartDate, EndDate, Rate
  Billing  - table with Date, AgentID, ProjectID

So my current implementation a billing entry must be made by an agent, and I have to create a join to the rates table based on AgentID, ProjectID, and Date between StartDate and EndDate. This just seems incredibly ugly and all the billing data can be easily changed if a change were made to the rates. I'm assuming most time sheets calculate the total rate and store it in the table so it never changes unless someone specifically goes back to update the data? I'm just wondering if there is a better method for tracking this sort of data. Thanks

I take it that your main concern is tracking the changing rates and charging the prevailing rate when work was performed. That's not really a problem.

There is a pattern I call Temporal Normal Form. It's a way to track changing data using normalization -- a pattern we all know and love.

So the Rates table will be normalized according to static data:

create table Rates_S(
    ID        int identity,
    AgentID   int not null,
    ProjectID int not null,
    constraint PK_Rates_S primary key( ID )
);

and varying data:

create table Rates_V(
    RateID    int not null,
    Effective date not null,
    Rate      number( 8, 2 ) not null,
    constraint PK_Rates_V primary key( RateID, Effective ),
    constraint FK_Rates_V_Rate_S foreign key( RateID )
        references Rates_S( ID )
);

Notice there is no EndDate field. The rate, once started, continues until changed. This eliminates Row Spanning Dependencies, where different fields in different rows must remain in synch.

So now let's see how much a billing entry will cost the project. We're looking for the rate that was in effect on the date the hours are logged:

select  b.ProjectID, b.AgentID, b.Date, b.Hours * v.Rate as Charge
from    Billing   b
join    Rates_S   s
    on  s.ProjectID = b.ProjectID
    and s.AgentID   = b.AgentID
join    Rates_V   v
    on  v.RateID    = s.ID
    and v.Effective =(
        select  Max( Effective )
        from    Rates_V
        where   RateID = v.RateID
            and Effective <= v.Effective );

Notice I've added how much time is being billed to the Billing table.

This looks like a lot to get one rate, but it is a pattern and once you write it a coupla hundred times, it will sink in. It is also quite efficient.

Microsoft SQL Server 2016 and Azure SQL database now support temporal tables, a feature that is designed to let you query and compare data for specific points in time. Changesets basically. It's also extremely simple to set up and use within existing databases.


How to add column to big table in MySQL I'm a PHP developer so don't be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB so what i tried:

Alter table in screen with table lock. Took ~30hours and nothing. So I just stopped it. First I made a mistake because I didn't end all transactions but the 2nd time was no multilock. Status was copy to tmp table.
Since I also need to apply partitioning for this table we decide to make dump, rename and make table with same name and new structure. But dump is making strict copy(at least I didn't found something else). So i added to dump a new column with sed and query it. But some strange errors began. I believe it was caused by charset. Table in utf-8 and file became us-ascii after sed. So I got errors(unknown command '\'') on 30% of data. So this is also a bad way.

What are other options to accomplish this and speed performance(I can do it with php script, but it will took ages). What will be performance of INSERT SELECT in this case.

Thanks for any advance.

Use MySQL Workbench. You can right-click a table and select "Send to SQL Editor" --> "Create Statement". This way no table "properties" will be forgotten to add (including CHARSET or COLLATE).
With this huge amount of data I'd recommend cleaning up either the table or the data structure you use (a good DBA comes handy). If not possible:

rename the table (ALTER) and create a new one with the CREATE script you get from Workbench. You can also extend that query with the new field you need
BULK LOAD the data from the old table to the new one:
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

This way you avoid indexing/etc to run record by record. The "update" to the table still will be slow (as the amount of data is huge) but this is the fastest way I can think of.
EDIT: Read this article to get details about the commands used in the above sample query ;)

i just had the same problem. A little workaround:

CREATE TABLE new_table SELECT * FROM oldtable;

DELETE FROM new_table

ALTER TABLE new_table ADD COLUMN new_column int(11);

INSERT INTO new_table
select *, 0
from old_table

drop table old_table;
rename table new_table TO old_table;


IN Clause - performance difference between listing values and query I changed this query

SELECT ...
FROM linkedServer.DB.Schema.Table1 t1
LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID
WHERE t1.BRANCH_ID NOT IN (
        '009991', '009992', '009993', '009994', '009995', '009996', '009999', '900001',
        '900002', '900003', '900004', '900005', '900006', '900007', '900008', '999991',
        '999992', '999993', '999994', '999995'
        )
GROUP BY ...

into this

SELECT ...
FROM linkedServer.DB.Schema.Table1 t1
LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID
WHERE t1.BRANCH_ID NOT IN (
        SELECT b.BRANCH_ID
        FROM TB_BRANCH b --25 rows in total
        WHERE b.START_DT = '99999999' --the result of this sub-query is fewer than list above.
        )
GROUP BY ...

then performance dropped.

Why is this huge change of Execution plan happening? How can I avoid performance drop while using a sub-query in IN clause?

You are using a linked server to access Table1 and Table2.

The first query is sent as is to the other server and executed there returning only the rows you want.

The second query is doing a join between a local table TB_BRANCH  and a remote table Table1. To do that it fetches all rows from Table1 and all rows from Table2 to your local server and does the join operations locally.

The drop in performance is there because it takes time to move the entire contents of Table1 and Table2 from the remote server to the local server.

  How can I avoid performance drop while using a sub-query in IN clause?

Move the content of TB_BRANCH (25 rows) to the remote server to avoid doing a join between tables on different servers.

One way of moving the rows from TB_BRANCH to the remote server is to execute the query on the remote server using sp_executesql with the values as a parameter in a XML structure. Unpack the XML to a table variable and use the table variable in your main query.

declare @X varchar(max);
set @X = (
         select b.BRANCH_ID
         from dbo.TB_BRANCH as b
         where b.START_DT = '99999999'
         for xml path('')
         );

-- Create a SQL Statement
declare @SQL nvarchar(max);
set @SQL = '
declare @B table(BRANCH_ID varchar(10) primary key);

insert into @B(BRANCH_ID)
select B.X.value(''.'', ''varchar(10)'')
from (select cast(@X as xml)) as T(X)
  cross apply T.X.nodes(''/BRANCH_ID/text()'') as B(X);

select *
from Schema.Table1 as t1
  left outer join Schema.Table2 as t2
    on t1.ORDER_ID = T2.ORDER_ID
where t1.BRANCH_ID not in (
                          select B.BRANCH_ID
                          from @B as B
                          );';

exec linkedServer.DB.Schema.sp_executesql @SQL, N'@X varchar(max)', @X

I have guessed the data type of BRANCH_ID to be varchar(10) you need to modify that in two places in the script if it is something else.

The XML parameter has to be a varchar(max) instead of as XML since the XML datatype is not supported with remote queries. Table Valued Parameters is not supported either so that is not an option here.

The query plan of this query is not very interesting. It will just be a Remote Scan. If you want to know what the plan looks like on the remote side you have to capture the plan there.

Subqueries are always low on performance when involved with joins.
Try replacing the subquery with a LEFT JOIN TB_BRANCH b ON t1.BRANCH_ID = b.BRANCH_ID WHERE b.START_DT <> '99999999' and see if the performance improves.


Hide Table Structure Is there a way to restrict a user from viewing the table structure? I just want them to have a SELECT, INSERT, UPDATE, DELETE access only on that table.  I want to restrict a specific programmer on the following:
    1.  He is not allowed to see any table definition
    2.  He is not allowed to create Stored Procedure
    3.  Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.
    Overall, for security reason, we don't want that programmer to have any of design of the database.

This seems a strange request but it is possible.

GRANT SELECT ON YourTable TO FOO;
GRANT INSERT ON YourTable TO FOO;
GRANT UPDATE ON YourTable TO FOO;
GRANT DELETE ON YourTable TO FOO;
DENY VIEW DEFINITION ON YourTable TO FOO;

They will of course be able to see the names of the columns by issuing a SELECT * and be able to get the datatypes with a bit more effort by select into a new (possibly #temp) table. They will lose intellisense on that table.

You also say

  Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.

In that case probably they don't need any permissions at all on the table. If the table and stored procedures have the same owner you just need to grant exec permissions on the procedure and the access to the table in the procedure will still succeed through ownership chaining.

DbDefence can hide database schema and close database completely even for DBA. It is free for SQL Server 2005 and 2008. As well as free for other SQL Server for small databases.

Disclaimer: I'm associated with the vendor.


What does this margin symbol in the SSMS text editor mean? While editing text in SSMS I mistakenly pressed a random keyboard combination, which I think began with Ctrl-K, and found this diagonal arrow symbol appeared in the text margin where the breakpoints normally go:

Can anyone tell me what it means?

SQL Server 2016 13.0.15900

I found the answer on Stack Overflow, as soon as I remembered that SSMS is a 'special flavour' of the Visual Studio Shell.

In short, it's a Task List item, which is toggled on/off with Ctrl-K, Ctrl-H. Items can be displayed with the menu View/Task List.

As per mssqltips documentation by @Arshad Ali SQL Server Management Studio keyboard shortcuts Hit CTRL+K followed by CTRL+K again to toggle the bookmark on the line.

You can press CTRL+K followed by CTRL+N to move to the next bookmarked line from the current cursor location likewise to move back to the last bookmarked line you can hit CTRL+K followed CTRL+P from the current cursor location.

To clear all bookmarks from the current window you can hit CTRL+K followed by CTRL+L.

For further your ref here , here and here


How to search a MySQL database with encrypted fields Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt.

How would one search those fields anyway?

Decrypting each record step by step is no option: Suppose I have multiple of thousands of records. It would take too much time and space to decrypt each record and check if each single record matches the search.

UPDATE 2012-09-07

Adding further details to the database schema would be OK, since I'm about to implement a new application. Furthermore, I need to extend applications currently running in production. But even for those application, adding further details would be OK.

UPDATE 2012-09-08

Encryption is the kernel of this question.

Access restrictions, as proposed by some answers, already apply - but do not fit the formal requirement to encrypt data.

This formal requirement is not Payment Card Industry Data Security Standard [PCI].

Obviously they are not meant to be viewed, therefore searching on them would be problematic.

One trick I have used in the past is to hash the encrypted data before encrypting it, and storing the hash in an indexed column.  Of course, this only works if you are searching on the whole value; partial values will not have the same hash.

You could probably extend this by making a "full text" index of hashes, if you needed to, but it could get complicated really fast.

ADDENDUM

It's been suggested that I add a footnote to my answer per a fairly lengthy debate in chat about vulnerability to dictionary attacks, so I will discuss this potential security risk to the above approach.

Dictionary Attack:  A dictionary attack is when someone pre-hashes a list of known values, and compares the hashes to your hashed column in the database.  If they can find a match, it's likely that the known value is actually what is being hashed (It's not definite though, because hashes are not guaranteed to be unique).  This is usually mitigated by hashing the value with a random "salt" appended or prepended so the hash will not match the dictionary, but the above answer cannot use a salt because you lose the searchability.

This attack is dangerous when dealing with things like passwords: if you create a dictionary of popular password hashes, you can then quickly search the table for that hash value and identify a user that has such a password and effectively extract credentials to steal that user's identity.

It is less dangerous for items with a high degree of cardinality, like SSN's, credit card numbers, GUIDs, etc. (but there are different risks [read: legal]  associated with storing these, so I am not inclined to advise on storing them).

The reason for this is in order for a dictionary attack to work, you need to have pre-built a dictionary of possible values and their hashes.  You could, in theory, build a dictionary of all possible SSNs (a billion rows, assuming all formatting permutations are removed; multiple dozens of trillions of entries for credit cards)... but that's not usually the point of a dictionary attack, and basically becomes comparable to a brute-force attack where you are systematically investigating every value.

You could also look for a specific SSN or credit card number, if you're trying to match a SSN to a person.  Again, usually not the point of a dictionary attack, but possible to do, so if this is a risk you need to avoid, my answer is not a good solution for you.

So there you have it.  As with all encrypted data, it's usually encrypted for a reason, so be aware of your data and what you are trying to protect it from.

Assumming you are searching in SQL and against the full value and not partial (e.g. LIKE 'value%')...when capturing the search data, encrypt that data using the same algorithm used when the data was encrypted and search for that.

For example:

What would have been:

SELECT FieldA, FieldB
FROM Table1
WHERE FieldC = 'Value'

Might instead look like:

SELECT FieldA, FieldB
FROM Table1
WHERE FieldC = 'hsk&%67ghhks83'


Changes in Oracle Database implicit conversion rules for select statement Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while the new hardware is running Oracle 11G R2. Everything went smoothly and the application ran normally again.

The last few days our users reported problems with the software. After some research the application runs a sql statement which tries to filter a varchar column with a number thereby forcing an implicit conversion of the column.

In the previous version of Oracle everything went fine but now an "ORA-01722: invalid number" is thrown.

Might there be some setting we have missed for the new database?

We already contacted the developing company but it will take some time.

Sample table and data:

create table conversion (
  col1 varchar2(10)
  );

insert into conversion values ('test2');
insert into conversion values ('42');
insert into conversion values ('test3');

Simplified statement throwing the error:

select count(*) from conversion where col1 = 42;

Thanks in advance and best regards
Jan

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :)

Don't really see any solutions here except updating the SQL query. This is why nobody recommends the usage of implicit conversions. They could also cause a slight decrease of performance.

EDIT: If you don't have access to the query and have to wait for the dev team, but instead you have enough privileges on the database, and know for sure that the VARCHAR column holds only number, you could try to alter the table and convert the column type.

When you leave it on database to decide, it will try to convert char to number, because it can compare numbers more fast.


database schema design for school system I am trying to create a database to store information about a school.

I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes and each class has a list of students.

The department name will be unique. However, teachers may span across departments. Another thing to note is that if teacher A teaches a class called 'Maths' and teacher B also teaches a class called 'Maths' then these should be different entities in the database (I think). For example, I may want to get a list of students that take the class 'Math' with 'teacher A' only.

So far I have this:

departments(department_id, department_name)
teachers(teacher_id, teacher_name);
departments_and_teachers(department_and_teachers_id, department_id (FK), teacher_id (FK))
student(student_id, student_name)
teachers_and_students(department_and_teachers_id, student_id);

which I think is good database design? However, it doesn't include any information about the 'classes' and I'm not sure what the best way of altering the schema would be. For each department and teacher combo I need to add a list of classes that are taught. Is it plausible to do something like this:

classes(class_id, class_name)
departments_teachers_and_classes(dtc_id, department_and_teachers_id (FK), class_id(FK));
department_teachers_classes_and_students(student_id, dtc_id)

EDIT - new suggestion based on answer received

departments(department_id, department_name)
teachers(teacher_id, teacher_name)
classes(class_id, class_name, teacher_id (PK), department_id (PK))
student(student_id, student_name);
classStudents(student_id (PK), class_id (PK))

To your specific question of (easily satisfied) plausibility, the answer is yes.  Since you seem to be looking for a broader critique. You seem to be mostly on the right track, but a few things are making your description overly complex.

It looks like your Classes table is more like what I would consider a course to be and your departments_teachers_and_classes is more of what I would expect a Classes table to be.  A course would be Math, but a class would be the Math course taught by a particular teacher during a particular school term.  A class is like an instance of a course.

To carry this change through, your departments_teachers_classes_and_students could simply be ClassStudents.

It seems as though you are using the department concept on several different levels.  You should decide where the department belongs and stop referencing it everywhere else.  You've said that a teacher can teach for multiple departments, so we can't put the department in the teachers table.  This leaves Classes and Courses.  Whichever you decide, it need only have a foreign key to the Departments table (unless you decide that one can be in multiple Departments).  This eliminates the Departments_and_teachers table.

Teachers_and_Students could be replaced with a teacher foreign key in the Classes table and students/classes foreign keys in a ClassStudents table.

In short, for the portion of the design you have described, you probably need the following tables:

Departments
Teachers
Students
Courses
Classes
ClassStudents
Terms

Why go through all of this when there is an excellent FREE hosted start of the art on-line LMS available?

kornukopia

Check it out!


Stats for usage of columns in queries I am using MS SQL Server 2014. Have some kind of DWH. Is there some way to find some kind of stats, which columns from database are used in select clause and how many times, and which columns are used in where clause and how many times ?

This specific information is not fully stored in SQL Server.

You do however have a few options to make a small analysis.

You can check the index_usage stats ( SYS.DM_DB_INDEX_OPERATIONAL_STATS) and cross reference this to the columns included in the index. This will, however, not give you exactly which columns are used or selected or used in a where clause. But you can derive a lot of information here to check for more optimal indexes.
You can use the following query from the exec_query_stats

 SELECT
     OBJECT_NAME(qt.objectid)
   , qs.execution_count AS [Execution Count]
   , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
   , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
   , qs.total_worker_time AS [TotalWorkerTime]
   , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
   , qs.max_logical_reads
   , qs.max_logical_writes
   , qs.total_physical_reads
   , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
   , qt.text
 FROM
     sys.dm_exec_query_stats AS qs
 CROSS APPLY
     sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

This will give you the execution count of queries on you SQL server (not all, only cached). Here you can go through the query text field and cross reference to your tables but this will be a lengthy process. You can also check the execution stats for stored procedures & then cross reference this to the columns in the stored procedures, but this will again be a tough process.

Use a third party tool which captures most of your queries and query through your queries (this will be slow and a lot of data).

The most important resource to any SQL optimizer is the statistics collected for different tables within the catalog. Statistics is the information about indexes and their distribution with respect to each other. Optimizer uses this information to decide the least expensive path that satisfies a query. Outdated or missing statistics information will cause the optimizer to take a less optimized path hence increasing the overall response time.
Following table lists SQL commands for different database that is used to update statistics. For further details, refer to the reference manual for your particular RBDMS

Oracle: ANALYZE command or DBMS_UTILITY package

DB2:    RUNSTATS command

MS SQL Server:  UPDATE STATISTICS

Informix:   UPDATE STATISTICS

Sybase ASE: UPDATE STATISTICS

You should read this it might help.

http://web.synametrics.com/top10performancetips.htm


Concurrency Issues when multiple users uses Portal (MySQL and PHP) We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no other user can do anything in portal, not even login.

All tables are in MyISAM Engine.

I cannot provide you the link as its in our internal server.

Server is 4core Xeon Processor with 32 GB Ram.

The framework calls somewhere, somehow the command mysqldump, and there you need to give him the --lock-tables=false option.


How to get the PC models with identical speed? I am trying to get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).

Result required as : model with the bigger number, model with the smaller number, speed, and RAM. The result I am expecting is

╔═══════╦═══════╦═══════╦═════╗
║ model ║ model ║ speed ║ ram ║
╠═══════╬═══════╬═══════╬═════╣
║  1233 ║  1121 ║   750 ║ 128 ║
║  1233 ║  1232 ║   500 ║  64 ║
║  1260 ║  1232 ║   500 ║  32 ║
╚═══════╩═══════╩═══════╩═════╝

and table structure is

╔═══════╦═══════╦═════╗
║ model ║ speed ║ ram ║
╠═══════╬═══════╬═════╣
║  1121 ║   600 ║ 128 ║
║  1121 ║   600 ║ 128 ║
║  1121 ║   750 ║ 128 ║
║  1232 ║   450 ║  32 ║
║  1232 ║   450 ║  64 ║
║  1232 ║   500 ║  32 ║
║  1232 ║   500 ║  64 ║
║  1233 ║   500 ║  64 ║
║  1233 ║   750 ║ 128 ║
║  1233 ║   800 ║ 128 ║
║  1233 ║   900 ║ 128 ║
║  1260 ║   500 ║  32 ║
╚═══════╩═══════╩═════╝

The query I am trying is this and I am using MySQL

select pca.model, pcb.model, pca.speed, pca.ram
from pc pca
where pca.speed in (
    select pcb.speed
    from pc pcb
    where pca.speed = pcb.speed
);

This should do the trick:

select distinct
  pca.model, pcb.model, pca.speed, pca.ram
from pc pca
join pc pcb
  on (pca.speed = pcb.speed
  and pca.ram = pcb.ram
  and pca.model > pcb.model)

SELECT DISTINCT a.model, b.model, a.speed, a.ram
FROM pc a, pc b
WHERE a.speed=b.speed AND a.ram=b.ram AND a.model>b.model


How can I monitor the progress of an import of a large .sql file? I am importing a 7 GB foobar.sql to restore a table in a local database.

$ mysql -h localhost -u root 'my_data' < foobar.sql

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline 5.1

How can I monitor its progress?

If you're  just importing from a dump file from the CLI on *nix, e.g.

mysql -uxxx -pxxx dbname < /sqlfile.sql

then first install pipe viewer on your OS then try something like this:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

which will show a progress bar as the program runs.

It's very useful and you can also use it to get an estimate for mysqldump progress.

pv dumps the sqlfile.sql and passes them to mysql (because of the pipe operator). While it is dumping, it shows the progress. The cool thing is that mysql takes the data only as fast as it can progress it, so pv can show the progress of the import. I do not have any proof. But it seems so. I guess there is some buffer used, but at some point I think mysql does not read any more data when it is still busy processing.

I'm so surprised no one just posted 'mysql -v' as an option. If it gets stuck, the output will stop.


SSIS Packages get stuck in SQL Agent jobs We run about a dozen scheduled SQL Agent jobs which run SSIS packages.

Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's a bunch of jobs still running. Sometimes we don't actually notice it for days. Further investigation seems to indicate that the package never starts:

DTEXEC process has 0 or very little CPU time
No logging or data activity occurs in the database

There is no loss of functionality in the actual database server, we don't notice anything (except stale data) until we check SQL Agent status, or start a job and it doesn't finish

We can stop and restart the jobs and they simply get stuck again
SQL Commands and CMDEXEC steps still work fine.

One job's first step copies a file with XCOPY then runs a package. The job completes the file copy then gets stuck on the package

There are a few customisations

Most packages are run from a master package, but the issue occurs for standalone packages
We have a custom built tool which queries and starts jobs via T-SQL but this is not in use
We use a framework called BIPA for our logging

Looking in the SQL Server logs, there seem to be a lot of AD type windows authentication errors. However these happen all the time regardless of the SQL Agent issue

We don't use the SSIS service. Packages are run from the file system using the SSIS step.

I can't think of anything further to troubleshoot at this stage. Does anyone have any ideas?

EDIT:

If you're interested I narrowed this down to our SSIS framework which is called 'BIPA'. I deleted the BIPA logging addin and we no longer have this problem. Based on the .net wait's happening in SSIS I believe it just couldn't instantiate the provider somehow. Why it only does this at night I don't know.

Strange thing is it does run fine when run interactively from DTEXEC (i.e. in a batch file) but won't run in SQL Agent (as a different user of course)

I think you should debug SSIS package in visual studio, if you have solution file.

I got this situation couple of month before, when I execute the package they stuck on the data flow.
I had resolved my problem by removing the transaction script from the package. Because I importing bulk of data and transaction create kind of deadlock situation....

Hope this will work for you


How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora? I have access to a ORACLE 11g server using the following listener.ora

# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.111)(PORT = 1521))
    )
  )

and it serves all the instances on that server, as I can verify by

lsnrctl status

My problem is, that when I make a fresh install of Oracle 11g in a virtual machine, I have to use a listener.ora like

# listener.ora Network Configuration File: C:\app\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )

   (SID_DESC =
     (GLOBAL_DBNAME = ORCL)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = orcl)
    )

   (SID_DESC =
     (GLOBAL_DBNAME = BMBK)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = BMBK)
    )

 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 169.254.200.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\oracle

Without the SID_LIST_LISTENER enties, i.e. using a listener.ora like that from the other server I get

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-NOV-2011 10:14
:24

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Startdatum                27-NOV-2011 10:14:08
Uptime                    0 Tage 0 Std. 0 Min. 18 Sek.
Trace-Ebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\app\oracle\product\11.2.0\dbhome_1\network\admin\
listener.ora
Log-Datei des Listener    c:\app\oracle\diag\tnslsnr\WSV-BK-W7en64-S\listener\al
ert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.254.200.102)(PORT=1521)))
Der Listener unterst³tzt keine Services
Der Befehl wurde erfolgreich ausgef³hrt.

The last message translates to

The listener supports no Services.

I have no idea, why these two servers behave differently. I found no hints in configuration files nor in registry keys.

Edit:

I guess the answer is somewhere buried in this post by Burleson Consulting, but I don't find the spot, where it is actually configured.

Bit late to the party on this one...

The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 environment. If the listener is started after the database it can take a while for it to register, and there may be situations where it doesn't do so at all.

You can attempt to make it register with an alter system register command. The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

If alter system register doesn't make the service appear in the lsnrctl status output then I'd suspect it's unable to either identify the hostname or resolve it, or it's resolving to a different address than the one the listener is on, or LOCAL_LISTENER is set to something invalid for the virtual box. You can set the LOCAL_LISTENER to either match the listener.ora directly, e.g:

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;

Or you can use an alias that has those details in your tnsnames.ora. For example, if you add a tnsnames.ora entry like:

MY_LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )

Then you could:

alter system set local_listener='MY_LISTENER' scope=both;
alter system register;

The only real advantage I can see of using the tnsnames.ora version, apart perhaps from brevity, is that you can change the configuration in the SQL*Net files rather than in the database; and even that only really seems useful if you're cloning databases between machines, or have your listeners and databases running under different accounts (e.g. with a grid user for RAC/HA).

There's more on LOCAL_LISTENER here.

Edit: And this seems quite comprehensive.

I had a similar problem and found that just shuttiing down and restarting the instance caused it to register correctly.

This can be done by using the sqlplus command line. You will need to login as a user with SYSDBA priveledges (eg system) syntax:

sqlplus name/pass as SYSDBA

then run 'shutdown' and wait for it to finish. then run 'startup' and wait for it to finish.

fingers crossed you should now have an instance registered with the listener.

To check that this is the case you can fire up lsnrctl and run the command 'status'. if it hasnt worked you'll still see the message 'The listener supports no services' otherwise you'll see something like

Service "horse" has 1 instance(s).
 Instance "horse", status READY, has 1 handler(s) for this service...


Oracle database installation directory using SQL I have server that has had Oracle installed by someone else. I am able to connect to it using SQL developer and do all the operations, but I want to know the location where Oracle is installed.

I need this details to execute exp, imp, expdp, impdp, etc. commands using putty to perform import/export and data dumps.

Is there any way I can get the Oracle installation directory details using SQL command? Does Oracle store the installation path in a database?

I am sure there is a even smarter way, but if you use the default trace file directories, which is usually the case, you will in most cases find both ORACLE_BASE and ORACLE_HOME via

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

This gives you the absolute path to the trace files and they are inside ORACLE_BASE and ORACLE_HOME respectively.

HTH KR Peter

Edit: Have a look here for a more straight forward approach: https://dba.stackexchange.com/questions/97390/query-to-get-oracle-home-path-in-oracle-11g

On the database server (VM), use the provided oraenv script to set up your working environment.  This reads the entries in /etc/oratab and sets not only the PATH environment variable, but also the Library Include Paths as well.

$ source oraenv
ORACLE_SID = [grid] ? MYSID
The Oracle base remains unchanged with value /u01/app/oracle_homes/product/dbhome_1

Of course, for this to work, you have to have the Oracle binaries in your PATH already, so that the shell can find oraenv!  To set this up by default, copy part of the entry from /etc/oratab into your .bash_profile:

[/etc/oratab]
MYSID:/u01/app/oracle_homes/product/dbhome_1:N

[~/.bash_profile]
export PATH=${PATH}:/u01/app/oracle_homes/product/dbhome_1/bin


error 2013 (hy000) lost connection to mysql server during query while load of mysqldump I'm trying to load mysqldump and I keep getting following error:

  ERROR 2013 (HY000) at line X: Lost connection to MySQL server during
  query

/etc/my.cnf:

[mysqld]
max_allowed_packet = 16M
net_read_timeout = 30
net_write_timeout = 60
...
[mysqldump]
max_allowed_packet = 16M

I tried to increase these values, but I keep getting that error no matter what( What else can I do to overcome this error?

If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query.

Had this issue when innodb_buffer_pool_size was set too close to physical memory without a swapfile configured. MySQL recommends for a database specific server setting innodb_buffer_pool_size at a max of around 80% of physical memory, I had it set to around 90%, the kernel was killing the mysql process. Moved innodb_buffer_pool_size back down to around 80% and that fixed the issue.

@A_funs was right, inspecting the system log yields this:

Aug 14 08:04:15 centos-php55 kernel: Killed process 8597 (mysqld) total-vm:7395680kB, anon-rss:3351108kB, file-rss:0kB, shmem-rss:0kB
Aug 14 08:04:15 centos-php55 mysqld_safe[7848]: /usr/bin/mysqld_safe: line 200:  8597 Killed                  LD_PRELOAD=/usr/lib64/libjemalloc.so.1 nohup /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=1024000 --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock < /dev/null > /dev/null 2>&1

So it might very well be possible you're (like me) running out of memory on the machine. My problem was that MySQL was using too much memory so the scheduler was killing the process. Actually lowering innodb_buffer_pool_size fixed the issue.


How to remove multiple redundant indexes? I have a legacy app.
I wanted to find out which tables are the largest ones using script from Percona blog. It turned out some of my tables have indices that are larger than actual data by factor of 3 and more.
I've dumped schema off the MySql server and found that some tables have lots of what seem to be redundant indices.

Here's how one of the worst tables is created:

CREATE TABLE `pictures_relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pic_id` int(10) DEFAULT NULL,
  `url_id` int(10) DEFAULT NULL COMMENT 'id урла, с которого файл был/будет скачан',
  `is_stub` tinyint(1) NOT NULL DEFAULT '0',
  `item_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `item_type` varchar(8) NOT NULL,
  `is_file` tinyint(1) NOT NULL,
  `is_hide` tinyint(1) NOT NULL,
  `is_cover` tinyint(1) NOT NULL,
  `image_only` tinyint(1) DEFAULT NULL,
  `is_custom` tinyint(1) NOT NULL DEFAULT '0',
  `skip` tinyint(1) NOT NULL DEFAULT '0',
  `order` int(11) NOT NULL,
  `author` int(11) NOT NULL,
  `add_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `is_hide` (`is_hide`),
  KEY `is_cover` (`is_cover`),
  KEY `add_date` (`add_date`),
  KEY `item_id_2` (`item_id`,`module_id`,`item_type`,`is_file`,`is_cover`),
  KEY `order` (`item_id`,`module_id`,`item_type`,`is_file`,`order`),
  KEY `item_id` (`item_id`,`module_id`,`item_type`,`is_file`,`is_custom`),
  KEY `pic_id` (`pic_id`),
  KEY `item_id_3` (`item_id`,`module_id`,`item_type`,`is_file`,`skip`),
  KEY `item_id_4` (`item_id`,`module_id`,`item_type`,`is_file`),
  KEY `pic_id_2` (`pic_id`,`skip`),
  KEY `skip` (`skip`,`pic_id`),
  KEY `stub_url` (`is_stub`,`url_id`),
  KEY `stub_skip_url` (`is_stub`,`skip`,`url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6794944 DEFAULT CHARSET=utf8;

I can clearly see that indices order, item_id, item_id_2, item_id_3 and item_id_4 are mostly similar, but they differ a bit. Using convenience views for easily detecting redundant indexes from this DBA.StackExchange answer helps, but not much — it does show that item_id_4 is redundant to all others, but doesn't help with splitting/merging them into more sane indexes.

The question is:
Is there a Rule of Thumb to refactor these indexes into something sane, with less duplication?

Like, if I have idx1('a', 'b', 'c'), idx2('a', 'b', 'd'), idx3('a', 'b', 'e'), then I have to make it into idx1('a', 'b'), idx2('c'), idx3('d') and idx4('e')?

The question cannot be answered without seeing the SELECTs.

Here are some partial answers...

The only obviously redundant queries are pick_id and item_id_4. The rule is simple:  If all the column(s) of an index are exactly the first column (s) of some other index, then toss it.  (Note: This applies only to non-unique indexes.)
The next set of apparent duds are (is_*).  These all seem to be true/false "flags".  The Optimizer won't use such (with rare exceptions).  It will, on the other hand, consider INDEX(is_foo, ...).  That is a composite index starting with a flag.  (And, no, the low cardinality of the first column is not bad.)
5 columns in an index is pushing the max that it "should" have.  I would be tempted to keep one of the 5-column indexes starting with (item_id,module_id,item_type,is_file).  The ones I tossed would still be benefit, at some level, by the one I keep.
Reordering the columns depends on what the SELECTs look like.  If the user can request "any" combination of columns to filter, then punt.  Well, not totally punt...  Make a few combinations of 2 or 3 columns.
INDEX(a,b,c) is not the same as INDEX(a,b), INDEX(c).  Some queries benefit from one; some from the other.

See also: Cookbook on generating indexes

SELECT tab_left.TABLE_NAME,
CASE
WHEN tab_left.COLUMNS = tab_right.COLUMNS AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE)
THEN GREATEST(tab_left.INDEX_NAME, tab_right.INDEX_NAME)
ELSE tab_left.INDEX_NAME END REDUNDANT_INDEX_NAME,
GROUP_CONCAT( DISTINCT CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS
AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE)
THEN LEAST(tab_left.INDEX_NAME, tab_right.INDEX_NAME)
ELSE tab_right.INDEX_NAME END SEPARATOR ', ') INDEX_NAME
FROM (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME,
INDEX_TYPE,
IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`')
ORDER BY IF( INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema'
AND TABLE_NAME = TABLE_NAME
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_left
INNER JOIN (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME,
INDEX_TYPE,
IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE,
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY IF(INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS
FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_schema'
AND TABLE_NAME = TABLE_NAME GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_right ON tab_left.TABLE_NAME = tab_right.TABLE_NAME
AND tab_left.INDEX_NAME != tab_right.INDEX_NAME
AND tab_left.INDEX_TYPE = tab_right.INDEX_TYPE
AND CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS
AND (tab_left.IS_UNIQUE = 'NO' OR tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) THEN TRUE
WHEN tab_left.INDEX_TYPE = 'BTREE' AND INSTR(tab_right.COLUMNS, tab_left.COLUMNS) = 1 AND tab_left.IS_UNIQUE = 'NO' THEN TRUE
ELSE FALSE END
GROUP BY tab_left.TABLE_NAME, REDUNDANT_INDEX_NAME;


SQLCMD: "Login failed for user" on SQL Azure, but it works with SSMS I have an SQL Azure database, and I'm able to connect to it using SQL Server Management Studio. When connecting to it, it displays

myServer.database.windows.net (SQL Server 12.0.2000.8 - myUser)

However, when I'm using the SQLCMD utility on the same machine, against the same SQL Azure server, with the same credentials, it always answers

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user 'myUser'

The syntax I'm using is

sqlcmd -S tcp:myServer.database.windows.net -U myUser@myServer -P "myPassword"

I tried everything I could think of:

Added the port: myServer.database.windows.net,1433
Removed the tcp: protocol
Enclosed the user in double quotes, like the password
Tried the -I and -C command line options
Combinations of the above.

The error is always the same.

Is there some specific flag that has to be activated for SQLCMD to work with SQL Azure? Any other ideas?

The error was some encoding issue with Powershell. I suppose the password's special characters were not read correctly by Powershell.

In cmd.exe, it worked flawlessly.

Special character like $ sign is not working. I wasted 2 days trying and trying. Howcome Microsoft forgetting to miss such an important point or giving a fix to this. When you setup the DB Server , the password requirement says , it must contain special characters.  When you configure password with special character , Azure CLI does not work with this password. Quite surprisingly it does allow you to set password without any special character. If you do that Azure CLI works. Such Daunting silly things .


How to include empty string in string_agg? I've done a groklearning exercise up until the point of:

  Make sure to list all properties, even if there is some unknown
  address information; replace any missing parts with an empty string

Could someone guide me on how to make this work?

SELECT D.council_name, string_agg( P.house_number || ' ' || P.street  || ' ' ||    L.locality,  ', ' ORDER BY S.contract_date) AS commercial_properties
FROM Sales S, Properties P, Districts D, Localities L
WHERE S.property_id = P.property_id
AND P.locality_id = L.locality_id
AND P.district_code = D.district_code
AND P.primary_purpose = 'COMMERCIAL'
AND S.contract_date BETWEEN '2017-01-01' AND '2017-12-31'

GROUP BY D.council_name;

But it prints:

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY                                                                |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

Instead of:

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY, MACWOOD RD SMITHS LAKE                                        |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

It doesn't print "MACWOOD RD SMITHS LAKE" as it is missing the house_number.  How can I add the missing parts to the results?

You can use COALESCE to map null to an empty string for all attributes that may contain null:

string_agg( COALESCE(P.house_number, '')
         || ' '
         || COALESCE(P.street, '')
         ...
) AS commercial_properties

You can use a DEFAULT constraint for string_agg. It avoids storing the value field as NULL.


Counting non duplicates between 2 columns Suppose we have two columns: Start & End.
Both columns store an integer id.

For each id in the Start column, I want to be able to determine whether the same id exists in the End column. If not, the count should increment by 1.

How can this be done?

Should be fairly simple:

SELECT COUNT(DISTINCT a.Start)
FROM TableName a
LEFT OUTER JOIN TableName b ON a.Start = b.End
WHERE b.Start IS NULL

Try this:

Select Start, count(*)
From TBL
Where Start = End
Group by Start

Adding at the end

Having count(*) > 1

You'll see only rows with a match between your two columns.

If you want to check into a data set modify as follows:

Select Start
    From TBL
    Where Start in (
Select Distinct End From TBL)

With this one, you get only the Start value that you can find in End column.

Checking how many times this occurs:

Select Start, count(*)
        From TBL
        Where Start in (
    Select Distinct End From TBL)
    Group by Start


Migrating a database from SQL Server 2012 to the cloud I have a sample SQL Server database which is running on my computer. I have a cloud database account on AWS (free account). I want to migrate the database, with all tables, to the cloud.

How can I do that? Are there any open-source or free tools?

Have a look at Importing Data Into SQLServer on Amazon RDS.

Also, from codeplex SQL Database Migration Wizard with a reference video as well.

You can try out with nuodb.com as it has some simple steps wherein you can migrate your database from SQL Server to cloud. You just need to download some simple application which is free of cost and follow some simple instructions on how to install and use it.


How does SQL recursion actually work? Coming to SQL from other programming languages, the structure of a recursive query looks rather odd.  Walk through it step by step, and it seems to fall apart.

Consider the following simple example:

CREATE TABLE #NUMS
(N BIGINT);

INSERT INTO #NUMS
VALUES (3), (5), (7);

WITH R AS
(
    SELECT N FROM #NUMS
    UNION ALL
    SELECT N*N AS N FROM R WHERE N*N < 10000000
)
SELECT N FROM R ORDER BY N;

Let's walk through it.

First, the anchor member executes and the result set is put into R.  So R is initialized to {3, 5, 7}.

Then, execution drops below the UNION ALL and the recursive member is executed for the first time.  It executes on R (that is, on the R that we currently have in hand:  {3, 5, 7}).  This results in {9, 25, 49}.

What does it do with this new result?  Does it append {9, 25, 49} to the existing {3, 5, 7}, label the resulting union R, and then carry on with the recursion from there?  Or does it redefine R to be only this new result {9, 25, 49} and do all the union-ing later?

Neither choice makes sense.

If R is now {3, 5, 7, 9, 25, 49} and we execute the next iteration of the recursion, then we will end up with {9, 25, 49, 81, 625, 2401} and we've lost {3, 5, 7}.

If R is now only {9, 25, 49}, then we have a mislabeling problem.  R is understood to be the union of the anchor member result set and all the subsequent recursive member result sets.  Whereas {9, 25, 49} is only a component of R.  It is not the full R that we have accrued so far.  Therefore, to write the recursive member as selecting from R makes no sense.

I certainly appreciate what @Max Vernon and @Michael S. have detailed below.  Namely, that (1) all the components are created up to the recursion limit or null set, and then (2) all the components are unioned together.  This is how I understand SQL recursion to actually work.

If we were redesigning SQL, maybe we would enforce a more clear and explicit syntax, something like this:

WITH R AS
(
    SELECT   N
    INTO     R[0]
    FROM     #NUMS
    UNION ALL
    SELECT   N*N AS N
    INTO     R[K+1]
    FROM     R[K]
    WHERE    N*N < 10000000
)
SELECT N FROM R ORDER BY N;

Sort of like an inductive proof in mathematics.

The problem with SQL recursion as it currently stands is that it is written in a confusing way.  The way it is written says that each component is formed by selecting from R, but it does not mean the full R that has been (or, appears to have been) constructed so far.  It just means the previous component.

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows:

Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.

So each level only has as input the level above not the entire result set accumulated so far.

The above is how it works logically. Physically recursive CTEs are currently always implemented with nested loops and a stack spool in SQL Server. This is described here and here and means that in practice each recursive element is just working with the parent row from the previous level, not the whole level. But the various restrictions on allowable syntax in recursive CTEs mean this approach works.

If you remove the ORDER BY from your query the results are ordered as follows

+---------+
|    N    |
+---------+
|       3 |
|       5 |
|       7 |
|      49 |
|    2401 |
| 5764801 |
|      25 |
|     625 |
|  390625 |
|       9 |
|      81 |
|    6561 |
+---------+

This is because the execution plan operates very similarly to the following C#

using System;
using System.Collections.Generic;
using System.Diagnostics;

public class Program
{
    private static readonly Stack<dynamic> StackSpool = new Stack<dynamic>();

    private static void Main(string[] args)
    {
        //temp table #NUMS
        var nums = new[] { 3, 5, 7 };

        //Anchor member
        foreach (var number in nums)
            AddToStackSpoolAndEmit(number, 0);

        //Recursive part
        ProcessStackSpool();

        Console.WriteLine("Finished");
        Console.ReadLine();
    }

    private static void AddToStackSpoolAndEmit(long number, int recursionLevel)
    {
        StackSpool.Push(new { N = number, RecursionLevel = recursionLevel });
        Console.WriteLine(number);
    }

    private static void ProcessStackSpool()
    {
        //recursion base case
        if (StackSpool.Count == 0)
            return;

        var row = StackSpool.Pop();

        int thisLevel = row.RecursionLevel + 1;
        long thisN = row.N * row.N;

        Debug.Assert(thisLevel <= 100, "max recursion level exceeded");

        if (thisN < 10000000)
            AddToStackSpoolAndEmit(thisN, thisLevel);

        ProcessStackSpool();
    }
}

NB1: As above by the time the first child of anchor member 3 is being processed all information about its siblings, 5 and 7, and their descendants, has already been discarded from the spool and is no longer accessible.

NB2: The C# above has the same overall semantics as the execution plan but the flow in the execution plan is not identical, as there the operators work in a pipelined exection fashion. This is a simplified example to demonstrate the gist of the approach. See the earlier links for more details on the plan itself.

NB3: The stack spool itself is apparently implemented as a non unique clustered index with key column of recursion level and uniqueifiers added as needed (source)

My knowledge is in DB2 specifically but looking at the explain diagrams seems to be the same with SQL Server.

The plan comes from here:

See it on Paste the Plan

The optimizer does not literally run a union all for each recursive query. It takes the structure of the query and assigns the first part of the union all to an "anchor member" then it will run through the second half of the union all (called the "recursive member" recursively until it reaches the limitations defined. After the recursion is complete, the optimizer joins all records together.

The optimizer just takes it as a suggestion to do a pre-defined operation.


Unexpected CASE evaluation logic I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition? is related but doesn't appear to cover this situation and relates to SQL Server).
In the following example, I wish to calculate the MAX(amount) between a range of months that differs based on how many months are between the start and paid dates.
(This is obviously a constructed example but the logic has valid business reasoning in the actual code where I see the issue).
If there are < 5 months between the start and paid dates then Expression 1 will be used otherwise Expression 2 will be used.
This results in the error "ORA-01428: argument '-1' is out of range" because 1 record has an invalid data condition that results in a negative value for the start of the BETWEEN clause of the ORDER BY.
Query 1
SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
          MAX(amount)
             OVER (PARTITION BY ref_no ORDER BY paid_date ASC
             ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
             AND CURRENT ROW)
       ELSE
-- Expression 2
           MAX(amount)
             OVER (PARTITION BY ref_no ORDER BY paid_date ASC
             ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
       END
    END
  FROM payment

So I went for this second query to first eliminate anywhere this can occur:
SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
       ELSE
          CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
             MAX(amount)
                OVER (PARTITION BY ref_no ORDER BY paid_date ASC
                ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
                AND CURRENT ROW)
          ELSE
             MAX(amount)
                OVER (PARTITION BY ref_no ORDER BY paid_date ASC
                ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
          END
       END
  FROM payment

Unfortunately, there is some unexpected behaviour that means that the values Expression 1 WOULD use are validated, even though the statement will not be executed because the negative condition is now trapped by the outer CASE.
I can get around the issue by using ABS on the MONTHS_BETWEEN in Expression 1, but I feel like this should be unnecessary.
Is this behaviour as expected ? If so 'why' as it seems illogical to me and more like a bug ?

This will create a table and test data. The query is simply me checking that the correct path in the CASE is being taken.
CREATE TABLE payment
(ref_no NUMBER,
 start_date DATE,
 paid_date  DATE,
 amount  NUMBER)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('01-01-2016','DD-MM-YYYY'),3000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('12-12-2015','DD-MM-YYYY'),5000)

INSERT INTO payment
VALUES (1001,TO_DATE('10-03-2016','DD-MM-YYYY'),TO_DATE('10-02-2016','DD-MM-YYYY'),2000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('03-03-2016','DD-MM-YYYY'),6000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('28-11-2015','DD-MM-YYYY'),10000)

SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN '<0'
       ELSE
          CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
             '<5'
         --    MAX(amount)
         --       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
         --       BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         --       AND CURRENT ROW)
          ELSE
             '>=5'
         --    MAX(amount)
         --       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
         --       BETWEEN 5 PRECEDING AND CURRENT ROW)
          END
       END
  FROM payment

So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
   ELSE
      CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC
            ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
            AND CURRENT ROW)
      ELSE
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC
            ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
      END
   END
FROM payment

You still get ORA-01428: argument '-1' is out of range?

I don't think this is a bug. I think it is an order of operation thing. Oracle needs to do the analytics on all of the rows returned by the resultset. Then it can get down to the nitty gritty of transforming the output.

A couple of additional ways around this would be to exclude the row with a where clause:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
   -- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         AND CURRENT ROW)
   ELSE
   -- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END
END
FROM payment
-- this excludes the row from being processed
where MONTHS_BETWEEN(paid_date, start_date) > 0

Or you could embed a case in your analytic like:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
               ROWS BETWEEN
               -- This case will be evaluated when the analytic is evaluated
               CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0
                THEN 0
                ELSE MONTHS_BETWEEN(paid_date, start_date)
                END
              PRECEDING
              AND CURRENT ROW)
   ELSE
-- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END
END
FROM payment

Explanation

I wish I could find some documentation to back up the order of operation thing, but I haven't been able to find anything...yet.

The CASE short-circuit evaluation happens after the analytic function is evaluated. The order of operations for the query in question would be:

from payment
max over()
case.

So since the max over() happens before the case, the query fails.

Oracle's analytic functions would be considered a row source. If you execute an explain plan on your query, you should see a "window sort" which is the analytic, generating rows, which are fed to it by the previous row source, the payment table. A case statement is an expression that is evaluated for each row in the row source. So it makes sense (to me at least), that the case happens after the analytic.

Looks like it's windowing what makes Oracle to start evaluate all expressions in CASE.
See

create table t (val int);
insert into t select 0  from dual;
insert into t select 1  from dual;
insert into t select -1  from dual;

select * from t;

select case when val = -1 then 999 else 2/(val + 1) end as res from t;

select case when val = -1 then 999 else 2/(val + 1 + sum(val) over())  end as res from t;

select case when val = -1 then 999 else sum(1) over(ORDER BY 1 ROWS BETWEEN val PRECEDING AND CURRENT ROW) end as res from t;

drop table t;

First two queries run OK.


How can I reset a mysql table auto-increment to 1 in phpMyAdmin? I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:

ALTER TABLE tablename AUTO_INCREMENT = 1

I am curious if there is a way to do this from within phpMyAdmin.  Something like a check box to reset the auto-increment or something else along those lines?

Not that there is anything wrong with the command line approach.  More one of those curiosity things I keep thinking on...  Thanks in advance!

phpmyadmin
Perhaps you could just select the phpMyAdmin Operations tab:

In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
Click on the Operations Tab
In the Table Options box find the auto_increment field.
Enter the new auto_increment starting value
Click on the Go button for the Table Options box.

Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html
Supplemental Info
For an empty table, another way to reset the auto_increment attribute is to run
TRUNCATE TABLE mydb.tablename;

Don't run this if you have data in it. If you want to hose the data, then be my guest.
In phpmyadmin, just click the SQL tab, enter the command, and run it.
For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.
First, optimize the table
OPTIMIZE TABLE mydb.mytable;

Next, locate the highest value for the auto_increment column (say it is id)
SELECT MAX(id) maxid FROM mydb.mytable;

Suppose the answer returns 27. Goto the Operations tab and enter 28.

SET @num := 0;
UPDATE tablename SET id = @num := (@num+1);
ALTER TABLE tablename AUTO_INCREMENT = 1;


Postgres 10 not returning disk space after dropping indexes I had several indexes that totaled ~1TB, according to pg_relation_size(). I dropped them.

I expected to get a TB of disk space back at the OS level, but this did not happen. Maybe 15GB total (I didn't note the precise number at the time).

I used the following query to determine the sizes which I pulled from the wiki (https://wiki.postgresql.org/wiki/Index_Maintenance):

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

I used the value in the "index_size" column. The size matched my expectations based on the fields in the index and the size of the table.

For example, the remaining index on one of the tables is for the primary key and is, according to that query, 147GB on a 448GB table containing 7 billion records. Given that there are only a few fields, this seems like a reasonable size to me.

It's not table data so a VACUUM FULL/pg_repack should not be necessary. lsof shows no open deleted files. I can't even address them at this point.

How do I reclaim this space? Why didn't it come back immediately?

Have you previously used pg_upgrade -k to get to this version, and then didn't delete the old version's cluster directory?  If so, that old directory has hard links to the index data files which cause the underlying storage to be retained even when the link in the new version's directory gets unlinked.

I expected to get a TB of disk space back at the OS level, but this did not happen.

Why would expect that?  Database != File.

Postgres, like most DBMSs, manages a whole load of files on your behalf and will only release space back to the operating system when it really, really has to (usually when you force it to).

Question: What is the most likely operation after dropping an Index?

Answer: Re-creating that Index (or something very like it).

Indexes are generally there to improve query performance so permanently getting rid of one would be unusual.

Because of this (and many other reasons) Postgres tends to "hoard" space for itself, on the assumption that it will use that space for something at some point.  Reusing existing space is quick, as opposed to releasing space and later extending files at the operating system level, which is really slow, by database standards.


Multiple Database in One Server Problems Our company has 5 different databases. None of the multiple databases relate or utilize cross database joins.
We have ample resourcing and budget.

Our company is debating between:

5 databases on 1 server instance,
or 5 databases on 5 different servers.

Question: What are some specific problems, obstacle issues which can occur, if we place everything on 1 single server? My background is not DBA, so would like to learn examples I can discuss with team.

Background:

The argument for 1 server instance at company: We have "Resource governor" and "AlwaysOn Availability Groups" with Failover clustering.
Resource governor will handle Memory, Cpu, io workloads between databases. So along with AlwaysOn DR, it can handle runaway queries, bad design, memory issues.

Databases are 100GB each, critical company information, 50 million rows minimum

We process 300 transactions/sec

RTO = 10 min

The answer is classic -"It depends !"

Sit down with your stake holders and present then the pros and cons of the 2 options. Being in the business of client hosting, I have faced this situation and below is my view point :

  None of the multiple databases relate or utilize cross database joins. We have ample resourcing and budget.

This means that the dbs are decoupled or independent. I would say, club the dbs depending on the criticality and uptime SLAs and then distribute them between 2-3 servers (VM would be another option as well). This way, you dont put all your eggs in the same basket.

Why ?

Putting all 5 dbs on one server will be a single point of failure. An issue with a single DB puts all your other DBs at risk. Isolate your risk as much as possible but that will cost you additional licensing and maintenance overhead which you have by saying - ample resourcing and budget.
Even if you have Resource Governor enabled, it does not govern buffer pool. It only governs "working memory of a query e.g. sort, hash etc". In 2014 and up, you can limit IOPS using Resource Governor.
You have one solution for a given server hosting all your dbs to do backups - full and log, integrity checks, index maintenance. If there is an issue with your server and that job fails midway, other dbs will be impacted except you have inbuilt mechanism to detect and fix the issue and continue with the rest.
An external issue with OS affects all your estate being in one place.
Even with AGs, you have a fix amount of worker threads and they get shared with all your dbs on a single server. Having dbs spread out depending on the criticality will alleviate that problem.
Tempdb is one per server. So imagine all your dbs being on the same server might be a bottleneck.
Problem isolation will be difficult if you have one server serving all your dbs.

I have written a sort of similar answer Which is better: one database per application, or just one database?

I agree it does depend, but there are some questions that @Kin is not asking. These are important when you are deciding how to move forward on a project.

What is your budget for this project?

What version of SQL best fits my needs for this project?

What does your infrastructure look like?

VM, Spinning disks or solid state, if VM how many cores are you willing to pay for (licensing question)

Will you need to replicate your data off for reporting purposes (best practice is to not let reporting queries run against your OLTP database (yes, there are exceptions to this))?
How do you plan to handle security for each database?

Is there any sensitive data in these databases? If so, how do you plan to handle that.
Is there one account that is used to access each database, or are there

This is how I would start, as I have about 20 plus servers that fit your requirements. First option would be one server

16 cores on 2 sockets
96 gigs of ram (16 gigs to the OS and 80 to SQL)
Different drive for OS partition
Different drive for SQL installation (not os partition)
Different drive for your data files
Different drive for you log files (minimum of 100 gigs)
Different drive for tempDB datafiles (minimum of 100 gigs)

minimum of 8 tempDB files or more depending on need (start these guys off at 2.6 gigs and allow them to grow at 16mb increments)

Second Option: 5 individual instances

1 main IP address for the server, and 5 additional IPs for each instance

This allows you run each instance on that IP on the default port of 1433
Then configure each instance with a port like 6019 so you can connect via SERVER\Instance.
Assign a DNS alias to the IP address for each of the 5 instances so you can configure your applications to connect to that instance via an alias or use servername\instance

Adjust memory to 136 gigs. Assign each instance 24 gigs of ram (adjust accordingly based on load)

Also make sure to adjust your Cost Threshold for Parallelism and Max Degree of Parallelism settings for both options. Once you have your server in place work with your SA and Network teams to configure and setup your HA and DR plans.

This is how I would go about it.  :)


Foreign Key Constraint fails I have the following tables:

// Base Scans
CREATE TABLE `basescans` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL DEFAULT NULL,
    `status_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `status_id_fk` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=29

// Statuses
CREATE TABLE `statuses` (
    `id` INT(10) UNSIGNED NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4

Trying to save the first table fails when I put in that foreign key constraint. Can't figure out why. Both of the columns referenced in the constraint have the same type, size, etc:

INT(10) UNSIGNED NULL

They only have a difference default value. One has a default value of NULL, the other is AUTO_INCREMENT.  I didn't think that made a difference for foreign key constraints but I could be wrong.

Both tables are InnoDB and UFT8. What am I missing here?

UPDATED: My specific error:

/* SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`db`.<result 2 when explaining filename '#sql-31c2_22ac1e1'>, CONSTRAINT `status_id_fk` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) */

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table.  Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Using query 1 and 2 for drop your table

1) SET FOREIGN_KEY_CHECKS = 0;

2) DROP TABLE TableName;

Here the TableName, which table you want to drop


Database size - MDF too large? I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery model is SIMPLE and the .ldf is at 6Gb.

For whatever reason, the .mdf is 7.5Tb. Now, there are only maybe 2-3 additional columns in ANALYSIS tables and not many NVARCHAR(MAX) columns which, from what I (may have mistakenly understood - please correct me if I'm wrong) may be causing additional space allocation. That's after shrinking the database just now - it was at ~9Tb prior to that. Any thoughts?

And, please, let me know if you have additional questions - I'm very new to database administration and optimization efforts (I usually don't do this side of the job :)).

Many thanks!

Andrija

In your size estimates, have you taken into account the amount of space taken by indexes? Also if you have text fields that are set as multi-byte (N[VAR]CHAR rather than [VAR]CHAR) and the input files are UTF-8 or plain one-byte-per-character then that will push your storage requirements up by up to a factor of two. Furthermore remember that if you have a clustered key/index on a table the size of this affects all other indexes on the table because they include the clustered key value for every row (so to give an extreme example if a table has an NCHAR(10) key where an INT would do and that is your clustered key/index you are not only using an extra 16 bytes per row in the data pages you also waste 16 bytes per row in every other index on that table).

Also, some space will be allocated but unused, either because the DB engine has left some space allocated after deletes so that it can be used again quickly for new data in that table or because the pattern of inserts and deletes has left many pages only part full.

You can run:

SELECT o.name
     , SUM(ps.reserved_page_count)/128.0 AS ReservedMB
     , SUM(ps.used_page_count)/128.0 AS UsedMB
     , SUM(ps.reserved_page_count-ps.used_page_count)/128.0 AS DiffMB
FROM sys.objects o
JOIN sys.dm_db_partition_stats ps ON o.object_id = ps.object_id
WHERE OBJECTPROPERTYEX(o.object_id, 'IsMSShipped') = 0
GROUP BY o.name
ORDER BY SUM(ps.reserved_page_count) DESC

to get a quick look at what tables are taking up space.

Also EXEC sp_spaceused run within that DB will return two result sets. The first lists the total space allocated in the filesystem for the data files and how much of that is unallocated, the second lists how much of the allocated space is used for data pages, for index pages, or is currently unused.

sp_spaceused will return the space used by a given object too, so you can loop this to build a table for analysis:

-- TEMP TABLES FOR ANALYSIS
CREATE TABLE #tTables (sName NVARCHAR(MAX), iRows BIGINT, iReservedKB BIGINT, iDataKB BIGINT, iIndexKB BIGINT, iUnusedKB BIGINT)
CREATE TABLE #tTmp (sName NVARCHAR(MAX), iRows BIGINT, sReservedKB NVARCHAR(MAX), sDataKB NVARCHAR(MAX), sIndexKB NVARCHAR(MAX), sUnusedKB NVARCHAR(MAX))
-- COLLECT SPACE USE PER TABLE
EXEC sp_msforeachtable 'INSERT #tTmp EXEC sp_spaceused [?];'
-- CONVERT NUMBER-AS-TEXT COLUMNS TO NUMBER TYPES FOR EASIER ANALYSIS
INSERT #tTables SELECT sName, iRows
                     , CAST(REPLACE(sReservedKB, ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sDataKB    , ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sIndexKB   , ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sUnusedKB  , ' KB', '') AS BIGINT)
                FROM #tTmp
DROP TABLE #tTmp
-- DO SOME ANALYSIS
SELECT sName='TOTALS', iRows=SUM(iRows), iReservedKB=SUM(iReservedKB), iDataKB=SUM(iDataKB),  iIndexKB=SUM(iIndexKB), iUnusedKB=SUM(iUnusedKB) FROM #tTables ORDER BY sName
SELECT * FROM #tTables ORDER BY iReservedKB DESC
-- CLEAN UP
DROP TABLE #tTables

The above code will output all the table sizes in one list, plus a single row for the totals. If needed you can use the various system views (like sys.objects and sys.dm_db_partition_stats used in the first query above, see http://technet.microsoft.com/en-us/library/ms177862.aspx for much more detail) to get more details such as the space used by each index.

There are three classes of unused space in a data file:

That which is not allocated to anything (this shows in the first resultset from sp_spaceused with no object specified)
That which is allocated to an object (reserved) but not currently used (this shows in the "unused" count in sp_spaceused's output.
That locked in part-used pages (this will look to be used as everything is allocated in single page chunks, one page being 8,192 bytes long). This is harder to detect/calculate. It is due to a mix of two factors:

Split pages. As data gets added you often end up with part empty pages (the storage engine could always normalise page contents, but this would be very inefficient), and as rows are deleted page contents are not automatically packed (again they could be, but the extra I/O load is generally far from worth it).
The storage engine won't split a row over multiple pages (this along with the page size where the 8,192 byte-per-row limit comes from). If your rows are fixed size and take 1,100 bytes each then you are going to "waste" at least 492 bytes of each data block allocated to that table (7 rows take 7,700 bytes and an 8th won't fit so the remaining bytes won't be used). The wider the rows, the worse this may be. Tables/indexes with variable length rows (which are far more common than completely fixed length ones) generally fair better (but are less easy to calculate the matter for).
Another caveat here is large objects (TEXT columns, [N]VARCHAR(MAX) values above a certain size and so on) as they do get placed off-page, just taking 8 bytes in the main row data to hold a pointer to the data elsewhere) so can break the 8,192 bytes-per-row-limit.

tl;dr: Estimating expected database sizes can be a lot more involved than it is natural to initially assume.

Using SQL Management Studio,
1.Right Click on the Database Then
2.Click on Tasks-> Shrink -> Files

You will see a dialog that shows :
a. Currently Allocated Space
b. Available Free Space + (%free)

If your %Free is over 50% you might consider shrinking the file. I have seen this hit as much as 90%. If I decide to shrink the file I usually set it to 2 or 3 gigs more than the current allocated space. Most of my databases are less than 50gigs. So if you have a much larger file then you might make it 10 gigs large. I usually only worry about shrinking if I am going to move the database to another server, you can read all about shrinking issues on any sql page.


Conversion of a varchar data type to a datetime data type resulted in an out-of-range value I am trying to run a simple query to get all rows created in November:

SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000'
AND '2014-11-30 23:59:59.997';

SMSS returns:

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime:

Do I need to tell the server that 'Created' is datetime? If not, why am I getting this varchar message?

Edit: The value in the database was YYYY-MM-DD. Reply from @SqlZim below says that I need to use convert() to tell sql what format the date is in the db - and to replace the space character with the letter T:

select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');`

I checked your profile and saw that you are in the UK. If your sql server is set to use the dateformat dmy then that explains your issue. Without using the 'T' instead of the space in the datetime string, Sql Server won't recognize it as ISO8601 format.

Try this:

select count(*)
  from dbo.profile
  where [created] between convert(datetime,'2014-11-01T00:00:00.000')
                      and convert(datetime,'2014-11-30T23:59:59.997');

Querying using dates and/or datetimes can be tricky, to make sure you are getting what you are looking for I recommend reading:

Aaron Bertrand's Bad habits to kick : mis-handling date / range queries
Robyn Page's SQL Server DATE/TIME Workbench

edit: to clarify the out of range value in your error message would be from interpreting the month as 30 and the day as 11.

the below code, get the current session dateformat,
gets an error when when converting to datetime,
then sets the dateformat to ymd
and last but not least test the conversion (casting) again and it works

-- set the dateformat for the current session
-- if you use this date format you get the following error message:
--Msg 242, Level 16, State 3, Line 9
--The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
set dateformat dmy

-- set the dateformat for the current session
--this one does not give an error message
set dateformat ymd

-- The conversion of a varchar data type
-- to a datetime data type resulted in an out-of-range value.
select cast('2017-08-13 16:31:31'  as datetime)

-- get the current session date_format
select date_format
from sys.dm_exec_sessions
where session_id = @@spid

-- set the dateformat for the current session
set dateformat ymd

-- this should work
select cast('2017-08-13 16:31:31'  as datetime)

select @@version

  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct
  28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise
  Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2
  Datacenter 6.3  (Build 9600: ) (Hypervisor)


Applying (log backup) LSNs to the secondary node of an Availability Group I have a test instance configured as always-on go down today due to a restart of the primary node and the file-share going down together. I had a bunch of problems in order to get the secondary functioning again during which I had to remove it from the AG and add it again. During this time (the time when it went down till now) the log backup was running on the primary and when I tried to apply these backups to the secondary I got the following error:

  This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.

When I looked at sys.dm_hadr_database_replica_states:

last_hardened_lsn: 43000043815500001

When I looked at msdb.dbo.backupset (backup time):

backup time: 9:50 am
first_lsn: 43000043783000001
last_lsn : 43000043887200001

My question is, would there be any way to start applying this log backup starting from LSN 43000043815500001?

I am ok with restoring everything and then connecting my AGs since this is a very small instance, just want to know if there was an easier way as opposed to doing everything from scratch. I looked up online and did see the option for stopbeforemark or stopatmark but I am looking for something like stopaftermark.

You have 2 options :

Easy one - since this is test instance and its secondary.

Stop the log backup on primary.
Remove the db/s from AG.
Take a differential backup or full backup if dbs are small.
restore then on secondary with norecovery.
take an additional log backup on primary.
restore the additional log backup taken on secondary with norecovery.
join the dbs to AG.

A little bit harder one -

install sp_restoregene on primary.
Remove the db/s from AG.
Run it for the db/s in question and it will generate the full and log backup sequence with stopat.
Restore the sequence on secondary.
Join back to AG.

(I am telling above is a little bit harder since if you have not done a point-in-time recovery and you are doing it for the first time, test it first to be comfortable).

Your AG should be configured as automatic failover in case primary goes down. It will do the magic; there's no need to do anything like you had to do in the future.

Also you need to configure backup preferences to run on primary only.


MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded I have database session enable, when website got thousands of visitor. It gives me error

[30-Apr-2014 04:27:12 America/Denver] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
 try restarting transaction' in public_html/lib/Cake/Model/Datasource/DboSource.php:458
Stack trace:
#0 public_html/lib/Cake/Model/Datasource/DboSource.php(458): PDOStatement->execute(Array)
#1 public_html/lib/Cake/Model/Datasource/DboSource.php(424): DboSource->_execute('UPDATE `yuldi_d...', Array)
#2 public_html/lib/Cake/Model/Datasource/Database/Mysql.php(400): DboSource->execute('UPDATE `yuldi_d...')
#3 public_html/lib/Cake/Model/Model.php(1821): Mysql->update(Object(cake_sessions), Array, Array)
#4 public_html/lib/Cake/Model/Datasource/Session/DatabaseSession.php(117): Model->save(Array)
#5 [internal function]: DatabaseSession->write('66336365f5fc91c...', 'Config|a:3:{s:9...')
#6 [internal function]: session_write_close()
#7 {main}
  thrown in public_html/lib/Cake/Model/Datasource/DboSource.php on line 458

I want to know, how can i check MySQL insert queries per second, is there any commands to check that using ssh ?
is there changes in my.cnf required ?

If you wan't to check the queries/s in live you can use the mysqladmin tool with some options:

[root@db-prod-01 ~]# mysqladmin extended-status -uroot -p -i 1 -r | grep 'Com_insert '
Enter password:
| Com_insert                            | 59863746      |
| Com_insert                            | 1             |
| Com_insert                            | 2             |
| Com_insert                            | 9             |
| Com_insert                            | 2             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 2             |
| Com_insert                            | 3             |
| Com_insert                            | 6             |

extended-status: return the result of SHOW GLOBAL STATUS;
-i 1 -r:  Execute the SHOW GLOBAL STATUS; every second (one line by second)
| grep 'Com_insert': Shows only INSERTS statements

Note that Com_insert is a cumulativ counter the first line shows the nominal counter value and at each iteration of mysqladmin, a substraction is made to show you how there were insertions during the time you setted (here -i 1 is each 1 second).

By the way, if you want some infos for your error (1205) it's the InnoDB Engine that should check because it's the only way to see which of your query (or queries) is/are stucking your DB.

You can find infos on current and past transactions that lock your DB with:

SHOW ENGINE INNODB STATUS; -- In a MySQL prompt

Max.

Laravel Database Session also caused similar issue. I think you can refer to it based on situation below:-
The Garbage Collection function of the Laravel Database Session to cause sessions table locked when performing deletion of the expired session.
There are two ways to reduce this issue:
First, Ignore Garbage Collection function by manually amend the DatabaseSessionHandler.php located at \vendor\laravel\framework\src\Illuminate\Session to comment gc function, but it will be caused the session will never expired until perform logout.
For example:
  public function gc($lifetime)
    {
        //$this->getQuery()->where('last_activity', '<=', Carbon::now()->getTimestamp() - $lifetime)->delete();
    }

Second, add Index of the column last_activity to table 'sessions' for reduce the range of lock from entire table into row-level. Be careful while performing this action, it will cause down time while perform add index depends of the row size of you
sessions table
Note: Take your own risk while perform both actions, better test of localhost environment then update to the production side.