Sql Code Masterz

Count the days in current month- Count the total number of days in current month 1.) select day(dateadd(mm,1, getdate())-day(getdate())) 2.) SELECT (CASE MONTH(GETDATE()) WHEN 1 THEN 31 WHEN 2 THEN (CASE YEAR(GETDATE())%4 WHEN 0 THEN 29 ELSE 28 END) WHEN 3 THEN 31 WHEN 4 THEN 30 WHEN 5 THEN 31 WHEN 6 THEN 30 WHEN 7 THEN 31 WHEN 8 THEN 31 WHEN 9 THEN 30 WHEN 10 THEN 31 WHEN 11 THEN 30 WHEN 12 THEN 31 END) AS LastDayOfMonth

Microsoft SQL Server roles Introduction Roles are the new SQL Server 7.0 feature, which was not available in the previous versions. SQL Server roles act like Windows NT local groups. Microsoft SQL Server supports several different types of roles. There are: server roles database roles application roles In this article, I want to tell you about each kind of roles, about how you can add new role, how you can drop existing role, how you can return information about the roles, and so on. Server Roles Server roles are defined at the server level and exist outside of users databases. There are only fixed server roles, so you cannot add, delete or modify server role. You can only add users as a member of the server roles. There are seven fixed server roles: sysadmin serveradmin setupadmin securityadmin processadmin dbcreator diskadmin The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database fun

Filestream Storage As the name may suggest, Filestream enables the storage of unstructured data such as documents and videos.  This feature integrates with the NTFS files system by using the nvarchar(max) data type to store data on the file system.  This feature is great for when you’re currently storing documents inside of your SQL Server system that are larger than 1 MB and fast read access is important. Sparse Columns Sparse columns are very similar to other types of database columns, except that they are specialized and optimized for null values.  If you have a table that holds a lot of NULL values, first consider your table design.  If your design is solid or cannot be altered, then your table may be a candidate to take advantage of sparse columns.  The rule from Microsoft is to consider using Sparse columns when 20-40 percent of the values in a column will be NULL. New Date & Time Data Types Prior to SQL Server 2008, there was no way to native data type to store time rel

What's new in SQL Server 2008 SQL Server 2008 will be released in 2 nd quarter of 2008. SQL Server 2008 will also be part of a joint launch with Windows Server 2008 and Visual Studio 2008 on February 27, 2008 in Los Angeles, CA. Transparent Data Encryption SQL Server 2008 enables encryption of entire databases, data files, and log files, without the need for application changes. Encryption enables organizations to meet the demands of regulatory compliance and overall concern for data privacy. Some of the benefits of transparent data encryption include searching encrypted data using either range or fuzzy searches, more secure data from unauthorized users, and data encryption. These can be enabled without changing existing applications. External Key Management SQL Server 2008 provides a comprehensive solution for encryption and key management. To meet the growing need for greater security of information within data centers, organizations have invested in vendors to manag

Deleting Columns and Parameters A few types of objects appear in Schema View , even though you cannot delete them from Schema View . The examples are columns in a table and parameters of a stored procedure or function. Both these types of objects appear in Schema View so that you can better understand the table, procedure, or function without having to open its definition. However, each column and parameter is defined within the file that defines the entire table, procedure, or function. To delete these items, you must modify the definition for the table, procedure, or function. To delete a column from a table If Schema View does not appear, open the View menu, point to Other Windows , and click Schema View . In Schema View , click the table that contains the column that you want to delete. - or - In Solution Explorer , click the file containing the table that contains the column that you want to delete.

How to: Delete Database Objects To delete a database object from a database project , you can delete the object from Schema View , or you can delete the file that contains the object definition from Solution Explorer . You must follow a different process to delete table columns and the parameters of a stored procedure or function. After you delete one or more objects from a database project, you must build and deploy the changes to your database server. Deleting Database Objects To delete an object from a database project If Schema View does not appear, open the View menu and click Schema View . In Schema View , click the object that you want to delete. On the Edit menu, click Delete . You can also click the object and press the DELETE key. A confirmation dialog box appears. Click OK in the confirmation dialog box. The object is deleted from the database project in b

MySQL Empty Database / Delete or Drop All Tables MySQL drop all tables syntax: DROP DATABASE { mysql-database-name } Method #1: Empty database with root user In order to use this procedure you must have the drop and create database privilege (otherwise you will drop database but not able to create it again). Login as MySQL root or admin user to drop atomstore database: $ mysql -u root -p Now drop database: mysql> DROP DATABASE atomstore; Now create database again: mysql> CREATE DATABASE atomstore; Exit and close the session: mysql> quit Method #2: Drop all tables using shell script w/o root access I've small handy shell script that removes all tables without dropping and creating MySQL database again. #!/bin/bash MUSER= "$1" MPASS= "$2" MDB= "$3"   # Detect paths MYSQL= $ ( which mysql ) AWK= $ ( which awk ) GREP= $ ( which grep )   if [ $# -ne 3 ] then echo "Usage: $